How to Budget - Step 6: Review your Expenses
You’re almost there! Only one more week until I describe how to create your budget. Before you can do that, you’ll want to make sure that the income and expenses you’ve entered don’t have too many mistakes. In this post, I’ll talk hot to review the expenses and income you've entered in the spreadsheet to make sure you have an accurate starting point for your budget.
Before getting to that topic, here are your budgeting tasks for this week:
Continue using and refining your expense tracking system.
Continue to enter your income and expenses into the spreadsheet.
Make sure to update the number of months you have been entering information on the Basic Inputs tab.
Review the first two columns of the Budget tab, as described in the rest of this post.
Make Sure Categories are Right
Over the past several weeks, you’ve been entering the category name with each income and expense line item. Mistakes I’ve made include using more than one variation on some of my category names, such as household expense and household supplies. I also sometimes misspell one or more of category names.If you’ve made similar mistakes, you’ll want to correct these mistakes so you have exactly one line in your budget for each type of income and expense. Here are the steps to find and correct these mistakes:
Go to the Budget tab.
Review the category names to see if there is more than one row in Column A that corresponds to the same category.
If there is, figure out which category name you want to use.
Make note of all of the incorrect names.
Go to each of the Bank Transactions, Cash Transactions, Credit Card Transactions, Less-Than-Monthly Expenses, and Income tabs.
Hold down the Alt Key and hit E.
Hold down the Alt Key and hit F.
Enter one of the incorrect names in the box next to “Find What.”
Hit the Find Next button.
Any time Excel finds the incorrect category name, replace it with the correct name.
Repeat steps 9 and 10 on each of the tabs listed in Step 5 until the incorrect label no longer appears on the Budget tab.
Then repeat steps 6 through 11 for any other incorrect names.
You’ll know you are done when each category name appears exactly once on the Budget tab.
Make Sure Amounts Look Reasonable
Once all of the category names appear only once and have the names you want, you’ll want to make sure that the values in Column B look reasonable. These values are the totals of the values you entered on the various tabs, adjusted to either an annual or monthly basis depending on the choice you made in Cell B5 on the Basic Inputs tab. Two reasons these amounts could look wrong are (1) you entered the wrong amount for a transaction or (2) you entered an incorrect value in the “How Many Times a Year” column.
If a number looks too high or too low, you can use the following steps to help find the problematic input:
Identify the category name in Column A of any value in Column B that looks too high or too low.
Go to each of the Bank Transactions, Cash Transactions, Credit Card Transactions, Less-Than-Monthly Expenses, and Income tabs.
Hold down the Alt Key and hit E.
Hold down the Alt Key and hit F.
Enter a category name that has an unexpected value in the box next to “Find What.”
Hit the Find Next button.
Look in the Amount column of any row in which Excel finds your category name.
Does the amount look right? Common entry errors are to transpose digits (i.e., enter them in the wrong order) and put the decimal point in the wrong place.
Fix any errors in the amount.
Look in the “How Many Times a Year” column.
This column can be blank for any row that contains an expense you pay every month.
For payments made less than once a month, the entries in this column should be the numbers of times per year you make payments of the amount shown. For example, if you pay your auto insurance bill twice a year, the semi-annual amount should be in the Amount column and 2 should be in the “How Many Times a Year” column.
Repeat steps 7 through 12 on each of the tabs listed in Step 2 until the amount on the Budget tab for this category looks reasonable.
Next Steps
Next week, I will talk about how you can create your budget using the income and expense information you have tabulated so far and corrected.
