How I Budget

Anonymous left a comment a long time ago (sorry for taking so long to get this posted!) wondering how I budget using my Excel spreadsheet. You've all seen my monthly budget reports which is a snapshot of my monthly budget. And I've shared my 2011 budget which is my master spreadsheet for 2011 all ready to be updated with real numbers. But here's the never-seen-before behind the scenes edition! :)

Basically I have 14 tabs in my Excel spreadsheet. The first tab is my budget. I have the main column on the left for all my categories and then 12 columns for monthly budgets followed by a grayed divider column and then 12 more columns for my actuals. Of course those 12 columns are for each month of the year. I hide the months that are not currently in use so I can just see the current month at any given time.

I enter my data directly into this budget tab for all my monthly bills since there is only one number going into them and I can plainly see which have been paid that month and which are still outstanding. Another tip? I highlight my budgeted amount in yellow when it hasn't been paid yet and change that to green when it has been paid. It's just another visual reminder as to which bills are unpaid for that month. Another tip? In the actuals cell I enter the confirmation code as a comment for any online bill paying along with the date it was paid. Here is October's current snapshot for bills:



See? All paid or scheduled to be paid for this month. Maybe I should clarify. I color it green if I have sent in the payment, scheduled the payment or if it is on auto-pay. It's yellow if there is an action left for me to do.

Then, for my fluxtuating monthly categories, the discretionary bills, I have those linked to 12 other tabs in my spreadsheet, one for each month. As we spend money during the month and I reconcile that spending with my budget, I input the amount into the appropriate category on my monthly spreadsheet tab and it auto fills it into my budget for me. Here is a snapshot of my September tab:

All these charges are auto summed and put into the respective categories on my budget tab. One last bit of Excel conditional formatting that I take advantage of: greater than/less than. I have it set up such that if my actuals are under budget, it turns the cell green and if my actuals are over my budget it turns the cell red. It's another visual reminder of how we're doing with our budget during the month. Here is a current snapshot of our October budget vs. actuals:



Hey, look at that - we're still all green for this month. Yay! And that's the basics. I also have one more tab in my spreadsheet for my credit cards. Here, I list out what charges have been posted to our accounts, then also list them in the appropriate budget categories. I also color code these charges yellow for not paid and green for paid so I never pay them late and incur a finance charge. I can then also easily add up the columns and make sure they reconcile with my credit card statement.

And that's how I budget in one long drawn out explanation! Let me know if you have any questions and I'll answer them in the comment section of this post.

If anyone would like my budget template, just leave a comment with an e-mail address (or e-mail me at debtmarathon @ yahoo dot com and I'll e-mail it to you. I know there are a million other ways to employ Excel (or Quicken or Mint or YNAB or anything else) to budget for your family but this is my way and it works really well for me. I usually spend ~ 1 hour/week (usually Monday evenings) updating our budget for the previous week's charges and I'm done. If it's not easy, you won't keep up with it so if nothing else - make a budget that works for you and stick to it!