When Madison ran my first guest post in early July, How to Get Control of Your Budget, several commenters asked for more information about the “detailed tracking system” I mentioned. Madison has graciously agreed to host another post so I can share my system with you – let’s dig in!
I use an Excel spreadsheet to manage my budget, track my spending, and monitor my savings. To use this system, you must have a budget already in place. If you don’t, you might want to start one and return to this post at a later date.
My spreadsheet has 5 tabs: Summary, Checking, Savings, High Interest Savings*, and Budget Tracking. Below I’ll explain each tab in detail. I recommend you download and open the full spreadsheet to follow along. I’ve entered some sample transactions to help you see how everything fits together.
In order to maximize rewards, I pay for everything I possibly can with a credit card that I pay in full every month. This system helps me make sure I stay within my budget AND have enough money to pay my credit card each month. If you don’t use credit cards (or are sticking to cash while you get out of debt!), you can still use this system.
This tab shows your budget and balances in your checking, savings, high interest savings, and credit card accounts. You’ll also notice a section called “Direct Deposit Totals.” My company lets me direct deposit into multiple accounts, so I split my check based on my budget categories.
All my budgeting is based on pay periods: I split monthly costs such as rent and my student loan payment and take half the cost out of each paycheck. Money that will be spent in this pay period goes into my checking account, money that will be spent in the next month (like rent) in my savings account, and money that will be saved indefinitely (e.g., emergency fund), spent irregularly (e.g., gifts), or spent in more than a month (any long term savings goal) in a high interest savings account. If you can’t direct deposit to multiple accounts, you can achieve the same thing through auto-transfers each time you get a paycheck.
You’ll notice my budget categories are pretty simple. “Food” is both eating out and groceries. I basically incorporate anything not named (e.g., entertainment, haircuts, small household purchases) into “Personal.” “Savings” is a fixed amount that I split into different categories in the “High Interest Savings” tab – we’ll get there in a bit. You can choose to have more or less budget categories – this is all about finding a system that works for you. Please note that the numbers here are just a sample and not an indication of what I think you should spend in each category!
For each account, there is a line called “according to me” and a line called “according to them.” “According to me” is an auto sum based on the information you enter in the other tabs (Checking sums all the categories in the checking tab, Savings sums all the categories in the savings tab, etc.) “According to them” is what the bank or credit provider says your account balance is – a number you can get by logging into online banking. The number in each “difference” line should always be zero – if anything doesn’t balance, either you made a mistake in your tracking or your bank made a mistake when calculating its balance.
You can adjust this sheet to include as many budget categories and accounts as you wish, as long as you include new tabs and/or update the existing ones as necessary. If you need help, please let me know via the comments and I’ll get back to you.
Checking, Savings, & High Interest Savings
I’ll discuss these three together because they are similar. This is where the actual tracking comes in. Within each tab are sections for each of the categories that are held within that tab. For each category there are columns for date, place, deposit, and withdrawal. Each time you get paid, you “deposit” the correct amount in the first line of each category. Each time you spend money, you enter the transaction in the “withdrawal” for the appropriate category. Each category gives you a running balance at the bottom, which feeds the total balance on the “Summary” tab. In order for this to work, you must enter every transaction – I recommend doing it at least every 3-4 days if not more often.
If you do your spending on a credit card like I do, you can use the “credit card” section within each tab to hold the money until your payment is due. Every time you make a withdrawal from one category, simply “deposit” the same amount of money in the “credit card” section. Your account balance stays the same, but the amount you have available in your spending categories goes down. You’ll see some examples within the spreadsheet. If you don’t use a credit card for your spending, you can delete this section or just ignore it. If you frequently withdraw cash, do your best to assign it to a category when you withdraw it. You can always adjust if your actual spending is different from your predicted spending.
A key section in each of these tabs is “holding.” This is for when you transfer money that will only sit in the account temporarily. For example, I transfer my rent money from my savings account to my checking account each month. I record it as a “withdrawal” within “rent” in the “savings” tab. I then make a “deposit” equal to the amount of the transfer within the “holding” category of the “checking” tab. Once the check clears, I make a withdrawal from the “holding” category. This ensures that all account balances are accurate at all times, even when you have money flowing in and out.
In the high interest savings tab, you will notice I split my savings into several categories. At the top of each category, I include the target amount for each paycheck and/or the overall goal. The “other” account is used for any unexpected expenses, like a donation if a friend is participating in a charity event. I also use it as a cushion if I go over my budget in a pay period.
This tab allows you to monitor your spending over time. You can track your spending in each category and see where you’ve gone over budget. This is the only tab that isn’t 100% automatic. The food, transportation, clothes, and personal lines add automatically for the current pay period based on the withdrawals in the “checking” tab. You have to manually enter your spending (which should be pretty fixed) for the other categories, including the categories that reside in your high interest savings account.
The spreadsheet will sum all lines to give you a total and compare it to your total budget for the month. It uses conditional formatting to compare each number to your budgeted numbers and turns the cell red if you are over in any category or in total. If you see lots of red, it may be an indication that you need to adjust your budget or reign in your spending.
To continue the tracking over time, follow these steps:
- At the end of each month, take the current amount that is left in your budget for the period (found in line 12) and transfer it to your high interest savings or wherever you would like the surplus to go.
- Copy the numbers in the current period (in this example, cells G2:G12) and paste them into the next period.
- Then copy the current column, place your cursor in the first cell (G2 in this case) and use paste special -> paste values to get rid of the formulas and leave just the numbers.
- Save work to this point under the current file name.
- Save a new version (I use dates in my file name such as “Budget Tracking July 15-31”).
- Delete all spending/withdrawal entries in the three account tabs.
- Start tracking for the next period!
This is a way easier system in practice than it might seem on paper. It’s always harder to explain something than it is to do it, and I’m still new at this blogging business! If you have trouble with controlling your spending, I challenge you to try it for one month and report your progress in the comments. I’d be happy to answer any questions via the comments or in separate emails. Thanks for reading and happy tracking!
*If you don’t have a High Interest Savings account, you should! Check out Madison’s page for referral links.
Budget Tracking Spreadsheet
Download the Budget Tracking Worksheet to begin.