How to Budget Step 2: Tracking Expenses
Now that you’ve found a system for tracking expenses for budgeting, it is time to start recording them in your spreadsheet. In Getting Started with Budgeting, I talked about how to track your expenses. This week, I’ll focus on the following steps from my very first post (as numbered in that post):
Enter all of the checks and cash transactions from my checkbook into the spreadsheet and identify the type of expense.
Enter all of the transactions on my credit cards into the same spreadsheet, identifying the type of expense.
When I am tracking my expenses for budgeting, I start with my checkbook and enter all of the transactions from my check register, including line items for cash and payment of credit card bills. I then enter the cash transactions I’ve tracked. I’m not very good at tracking cash expenditures and my husband is even worse, so unaccounted-for cash is a big line item in our budget. If your budget it is at all tight, tracking your cash expenses will be very important in setting priorities and meeting your goals so I encourage you to do better than I do.
The rest of this post will outline how to do these steps using the spreadsheet I created for you (that you hopefully downloaded last week, but I’ve attached it to this week’s post in case you didn’t get a chance to do so).
This Week’s Budgeting To Do List
Here are your tasks for this week:
Continue using and refining the expense tracking system you developed last week. You’ll want to do this task for at least one month, so keep at it!
Think about the categories you want to use for budgeting.
Start tracking your expenses for budgeting:
Enter your expenses into the spreadsheet.
Enter your cash transactions into the spreadsheet.
Enter your credit card transactions into the spreadsheet.
Spreadsheet
The spreadsheet I created is flexible, so you can either follow my approach of tracking one or both of cash and credit card expenses separately or you can enter them all in the same place. The benefit of my approach is that you’ll know the amount of your untracked expenses. The disadvantage is that you have to enter data on three different tabs and will have a couple more entries related to credit card transactions. I suggest using whatever approach is easiest for you, as the end product is the most important part, not how you get there.
Each time you open the spreadsheet, you may need to click the “Enable Editing” button at the top of the screen and then the “Enable Content” button. By clicking these buttons, you’ll be able to enter your data, save the spreadsheet and use the macros.
Budgeting Categories
When you create your budget, you’ll want to look at your expenses in various categories. Examples of categories are utilities, groceries, restaurants, treats (coffee, Dairy Queen, a fun purchase, whatever you buy that you don’t really need), insurance, car maintenance, rent or mortgage payments, retirement savings, emergency savings and so on. You can create whatever categories you want! As you are creating them, you’ll want to keep the following in mind:
Your categories should separate discretionary purchases from necessary ones. For example, you could create a category for food that includes all of your groceries and restaurant purchases. Unfortunately, eating at restaurants is usually much more expensive than cooking at home. If you need to cut back on your expenses to meet your financial goals, knowing how much you spend at restaurants is important as you could cook at home more often and save some money.
You don’t want too many categories. My budget has about 30 categories in it. While I find that number to be a lot when trying to figure out my future expenses, I find I need that many to understand where my money is going. If you live a very simple life, you may need only 10 or 15 categories, but beware of the previous point if you end up with only a very few categories. If you try to use too many categories, you’ll find yourself forgetting the category names and will find the creation of the budget itself more challenging.
Category names should be meaningful. When you enter each transaction, you are going to also enter its category. These category names will appear on the summary that you will use to inform your budget. Because you’ll want your budget line items to be useful, you’ll want to start by creating meaningful category names.
Category names should be short enough that you don’t mind typing them. You’ll also want to be able to remember them from one day to the next. If you misspell a category name or create more than one variation, for example household goods and house stuff, you’ll have the chance to change them later, but that can be tedious.
Bank Transactions
What to Enter
On the Bank Transactions tab, you’ll record the expenses you pay from your bank account. These payments will include everything you pay with a check along with any payments you make directly from your bank account such as automatic payments, on-line bill payments and the like.
If you are going to track your cash purchases separately, you’ll put the amount of cash you withdraw from the bank or are given as income on this tab and put the actual cash purchases on the Cash Transactions tab. Otherwise, you can your actual cash purchases directly on this tab. More details are provided below.
If you are going to track your credit card purchases separately, you’ll put the total amount of each credit card bill on this tab and put the actual transactions on the Credit Card Transactions tab. Otherwise, you’ll put your actual credit card purchases directly on this tab. For credit card transactions, regardless of whether they are entered on this tab or the separate Credit Card Transactions tab, you’ll want to use the charges on each credit card bill, not the amount you paid. If you enter just the amount you pay, you’ll could be either over- or under-stating your current expenses, depending on whether you have been increasing or decreasing your spending over time. More details are provided below.
Data to Enter
Rows 1 through 6 of the Bank Transactions tab briefly summarize these instructions for tracking your expenses for budgeting.
You’ll enter your information starting in Row 10. I’ve highlighted the cells for inputs in light green. For each transaction, you can enter the purchase date in Column A and where you made the purchase in Column B. These columns are not used elsewhere by the spreadsheet, but can be very helpful when you look at your total expenses and wonder where and when you made purchases in various categories. When I create the financial statements for our farm (a process very similar to this part of the budgeting process), my husband often questions the totals in some categories. Being able to tell him where and when the purchase was made increases the credibility of the calculations.
Enter the amounts of your purchases in Column C and the corresponding category in Column D. If the things that you bought in one purchase fall into more than one category, you’ll want to have one row on this tab for each category with the corresponding amounts.
The last entry for each purchase (Column E) lets the spreadsheet know if this row has a purchase you make less often than once a month. For example, you might buy holiday gifts once a year and you may make significant birthday gift purchases a few times a year. On the other hand, you probably pay your utility bills and buy groceries every month. For purchases you make every month, you can either leave this column blank or you can enter 12 (for 12 months a year). For purchases you make less often, enter the number of times per year you make these purchases. Continuing the previous examples, you’ll probably want to enter 1 for holiday gifts and a number between 1 and 12 for birthday gifts (e.g., 3 if you give significant gifts for each of your parents and your significant other = 3 people).
If you choose to enter cash transactions on the separate tab, you’ll need to identify all cash withdrawals and cash you are given on this tab as well. Put the word “Cash” in both Columns B and D.
If you choose to enter credit card transactions on the separate tab, put the word “Credit Card” in Column D of this tab. In Column B of this tab, enter “Credit Card” followed by one space and then a number. For the first bill, use the number 1. Increase the number for each subsequent bill, so the second one will say “Credit Card 2” in Column B, the third will say “Credit Card 3” and so on.
Cash Transactions
If you choose to enter your cash transactions separately (which I highly recommend), you’ll want to go to the Cash Transactions tab of the spreadsheet.
What’s on this Tab
Rows 1 through 6 briefly summarize these instructions. Rows 9 through 12 show you how much cash you have tracked on this tab as compared to how much cash you have withdrawn from your bank account. The value in Cell C12 is the amount of cash you have withdrawn from the bank, but not tracked. You’ll want to keep this number as small as possible.
Data to Enter
You can input the information about your cash transactions starting in Row 16. I’ve highlighted the cells for inputs in light green. For each transaction, you can enter the purchase date in Column A and where you made the purchase in Column B. These columns are not used elsewhere by the spreadsheet, but can be very helpful when you look at your total expenses and wonder where and when you made purchases in various categories.
Enter the amounts of your purchases in Column C and the corresponding categories in Column D. If the things that you bought in one purchase fall into more than one category, you’ll want to have one row on this tab for each category with the corresponding amounts.
The last entry for each purchase (Column E) is an indicator of whether it is a purchase you make less often than once a month. See the discussion above for details on how to complete this column.
Credit Card Transactions
If you choose to enter your cash transactions separately (which I think is much less important than your cash transactions as your credit card bill will list all of your purchases), you’ll want to go to the Credit Card Transactions tab of the spreadsheet.
What’s on this Tab
Rows 1 through 6 briefly summarize these instructions.Rows 9 through 30 compares the total charges on each bill, as recorded on the Bank Transactions tab, with the transactions you have tracked on this tab as discussed below. The values in Column C of this section are the total amounts on each bill. The values in Column D show the total charges recorded on this tab for each bill. Column E shows the charges on your bill that you didn’t track. You’ll want to keep the numbers in Column E as small as possible.
Data to Enter
You’ll enter the information about your credit card transactions starting in Row 34. I’ve highlighted the cells for inputs in light green. For each transaction, you can enter the purchase date in Column A and where you made the purchase in Column B. These columns are not used elsewhere by the spreadsheet, but can be very helpful when you look at your total expenses and wonder where and when you made purchases in various categories.
Enter the credit card bill number on which these charges appear in Column C. This number will be the same number you used on the Bank Transactions tab when you created the line item for the credit card bill whose transactions you are entering.
Enter the amounts of your purchases in Column D and the corresponding category in Column E. If the things that you bought in one purchase fall into more than one category, you’ll want to have one row on this tab for each category with the corresponding amounts.
The last entry for each purchase (Column F) is an indicator of whether it is a purchase you make less often than once a month. See the discussion above for details on how to complete this column.
Need more Rows?
On one or more of the tabs, you might need more rows for tracking your expenses for budgeting. The instructions for how to add more rows depend on whether you are using Windows, an Apple or Google Sheets. The instructions are provided below and on the Instructions tab of the spreadsheet.
Windows or Apple
Put your cursor in any cell in the row above where you want to insert rows. This row must be above the one with the note in Column L that says “Don’t go below this row.”
Click on the “Insert 10 Rows” button on that tab.
Google Sheets
Put your cursor in any cell in the first row where you want the new rows inserted.This row must be above the one with the note in Column L that says “Don’t go below this row.”
Hold the shift key and move the cursor down until the number of highlighted cells equals the number of rows you want to add.
From the menu at the top, select Insert and then Row Below.
Find the instruction at the top of the tab that tells you which columns need to be copied.
Go to the last row above the ones you inserted.
Put your cursor in the leftmost column of the ones that need to be copied in that row.
Hold down the shift key and use the right arrow to highlight the cells in all of the columns that need to be copied. Let go of the shift key.
Hold down the Ctrl key while you hit C. Let go of the Ctrl key.
Move your cursor so it is in the leftmost column of the ones that need to be copied in the first inserted row.
Hold down the shift key and use the down arrow to highlight the cells in all of the rows that you inserted plus the first row below the ones you inserted. Let go of the Shift key.
Hold down the Ctrl key while you hit V.
If you make a mistake at any time, you can always undo what you’ve done but holding down the Ctrl key and hitting Z. You can do this several times to undo several steps.
One Last Tip
As with tracking expenses for budgeting, you’ll want to find a process for recording them that works for you. Some of you may find it easiest to record your expenses in your spreadsheet every day or even right after you make each purchase. Others of you may find it easier to record them once a week. When you record them isn’t as important as getting them into the spreadsheet, so find an approach that works for you and stick with it!
