Ok so I have used my last budget spreadsheet for a year now and I had a few things that I wanted to change. Here they are. I won’t bother explaining Dave’s budget form, if you have not read my first post on this, please read the post My First Dave Spreadsheet.
My list of complaints from the first spreadsheet were (keep in mind that I’m the nerd)
Only one month at a time. I didn’t like starting each month from scratch.
All of my budget categories (like insurance, home repairs, ect) had to be tracked in another spreadsheet
I didn’t have a year end total for everything. Not really necessary but any self respecting nerd needs to be able to answer the question… How much did you spend last year on “Gas & Oil Changes”. Now I can.
As I have already described the why of this spreadsheet in by previous post, I will go into the how in this one.
Lets start off with some basic info on how each months sheets are laid out.
Click on each picture to see it full size.
This screenshot shows several different items
Shows the total of all income for the month
Shows Dave’s Categories and his recommended percentages for each
Shows the percentage of total income for each category as well as the Total $$ spent in that category
Shows the monthly total for each line item for the entire budget.
You will notice that some of the columns on the left are color coded. The colors represent the following….
Green = Cash, as in Envelope, these map to Dave’s suggested categories
Yellow = Budget Savings. These are the categories that I actually moved money from my checking account into my savings account (which needs to be tracked, more on that later)
Red = Debt = Stop! Any line in Red shows a payment to debt. Hopefully None!
White = Normal category. Nothing special down with these.
At the bottom of each monthly page you will see some totals.
In each of the numbered columns you will see the total for that pay period of the categories that I need to do something with.
- The Green row shows how much cash I need to take out of my account and put in the envelopes.
- The Yellow row shows how much money I need to transfer into my budget savings account
(I also have two other savings accounts for my mortgage escrow and my Christmas club)
- The Blue row shows how much I need to transfer for escrow savings (real estate taxes)
- The orange row show how much I need to transfer to my Christmas club account.
- The Red row shows how much of each pay period went to paying down debt (Hopefully None)
On the left you will see the total for the month for each of the colored categories.
What is Budget Savings?
Budget Savings is a separate savings account that I transfer money to for items that I don’t have to pay every month but I have money set aside for every month. An good example of this is Car Insurance. I pay ~ $300 every 3 months for car insurance. So I have my monthly budget set up to take $50 out of every paycheck and transfer that to my budget savings account. So I will put $100 into this account in January, February and March and then move $300 back to checking when I write the check. When I do that, I will put $300 in the red area of the budget savings sheet for car insurance of that particular month.
On the “Budget Savings” sheet listed after “Dec” you will see the amounts for each budget category transferred from each of the monthly sheets.
There are few things to look at on this sheet. The first is the at the very top of the sheet where you will enter in any carry over savings for each category that you have at the start of the year. These numbers are used to calculate totals for the categories as you add and remove money throughout the year.
As you scroll down the Budget Savings sheet you will see that the category sections are repeated for each month. The total in that category is the sum of the total for that category at the end of the previous month plus any additions for that month (listed in green) minus any subtractions for that month (listed in red).
The green section is automatically filled in for you from the respective monthly sheet, which is why there are 4 columns, one for each week.
The red section needs to be filled in by you as you pull money out of that category. As you can see from the screen shot at the right, there was a $150 car repair in the 4th week of February. You will notice that at the end of January “Car Repairs” had $250. Then after removing $150 in February, there was $100 remaining.
The very last tab is the “Summary” page. This page looks very much like the individual monthly pages. The main difference is instead of having 4 columns representing the 4 weeks per month, there are 12 columns representing each month.
The “Total” column works just like it does on the monthly sheets but now you can see what you have spent on that category year to date.
Also the percentages work just like the monthly ones but for the whole year.
The Setup Tab
The very first Tab is called “Setup” all you need to do on this tab is enter the year for the budget. This will make sure that the dates at the top of each month is correct.
Ok so onto the download… in a minute….
I have actually gone through 3 revisions of this excel sheet.
In the first revision I simply had the 12 blank months listed. By blank I mean no data or references to other months (although the Budget Savings and Summery did pull data from monthly sheets). But me being as lazy as I am thought that this was a waste of time with data entry, because most of the data from month to month was the same. So I figured I could improve on it….
Which brings me to the the second revision. In the second revision I set up each month to pull it’s data from the previous month. By doing this I got some benefits and some drawbacks. The befits was reduced data entry. If I updated my Gas budget in March from $125 to $150 , then April would be updated to $150 and so would each of the reaming months. Then in April if I update Gas again to $175 then May would be updated to $175 as well as the other remaining months and so on… The problem with this approach is that I threw off my Summary tab a lot (because the whole year was filled out in January) and it threw off the Budget Savings tab a little (because it would calculate all of the months) but this is not that big of a deal as the Budget Savings tab has totals for each month and I just had to know that all future moths data was bogus (not very clean for my taste). So I figured I could improve on it….
Which brings my to the Third revision. This is the version available for download. In this version I have a button at the top of each month (except January) that copies the data from the previous month, so I get the benefit of fast data entry, AND all future months still are blank, so I get the summary and budget savings tabs to look correct throughout the year.
Not So Fast!.
There is one draw back. The third revision requires that Macros be enabled. This is not a real big deal but with so many people afraid of viruses, they may have macros disabled. You don’t have to have macros enabled to use this file, if you don’t you will just have to manually enter the data for each month. No Biggie. As part of giving back to the community I have post the source code for the macro on this blog and you can view it here angus65.com/development/excel-budget-vba-code/
The Excel File can be downloaded from here
Thanks for stopping by.