|File Type(s):||Excel Spreadsheet (.xls & .xlsx); OpenOffice Document (.ods)|
|File Size:||85kb (.ZIP file)|
|Last Update:||March, 2009|
Way back in late 2008, a reader emailed me to ask if I had a budgeting spreadsheet which broke down each month into four separate "budgetable" periods. She suggested that a budget of this sort would work better for her than would my popular Spending Plan spreadsheet might.
The idea of breaking each month into four separate budgetable "boxes" intrigued me, and I got to work. Here's what I came up with (screenshots from Excel 2007):
The BoxBudget spreadsheet is pretty much a one-page deal. Simple is good, after all. Pictured above is the BoxBudget worksheet filled out with some sample figures just so you can get an idea of what goes where.
As is the case with most of my spreadsheets, there's nothing very complicated in the BoxBudget tool. If it looks like something you need, just download a copy below:
For those readers who want some guidance on the BoxBudget workings, well, just keep reading!
When you extract the ZIP file (tutorial), you'll actually find three spreadsheets. One is for Excel 2007 (.xlsx); one is for Excel 97 through Excel 2003 (.xls); and one is for OpenOffice (.ods).
Please use the spreadsheet that's appropriate for the program you use.
Section 1: Starting Cushion
At the very top left of the spreadsheet, you're prompted to enter what's called the "Starting Cushion."
Here you'll enter whatever amount of spending cushion or savings you had at the end of the prior month. For many folks, I imagine, this would simply be whatever's left in their purse, wallet, and/or checking account(s).
In the sample above, our fictional couple Bob and Ann Spendbig had $240 left in their checking account at the end of last month. That amount becomes their "Starting Cushion" for this month.
Working with Four Monthly Periods
As we can see, the premise of the BoxBudget worksheet is to break each month up into four separate budgetable time periods. Each period (Days 1-7, 8-14, 15-21, and 22-31) has its own yellow "summary" box.
Those yellow boxes are, as you might expect, pretty important. They'll tell you where you stand for the month at any given time! (In the shot above, Bob and Jane Spendbig will, if all goes according to plan, have a checking account balance that went from $240 at month's start to $17.34 at month's end. Hmmm ... not so good.)
The formulas work out like so:
[Starting Cushion] plus [Period 1 Total Income] minus [Period 1 Total Expenses]
[Period 1 Cushion or Shortfall] plus [Period 2 Total Income] minus [Period 2 Total Expenses]
[Period 2 Cushion or Shortfall] plus [Period 3 Total Income] minus [Period 3 Total Expenses]
[Period 3 Cushion or Shortfall] plus [Period 4 Total Income] minus [Period 4 Total Expenses]
Beneath the four summary boxes, then, are each period's INCOME and EXPENSES inputs:
These green and red sections are where you'll enter all your income and expense data for the month. (Duh ... right?)
As the month progresses, you'll simply revise and/or add to these figures so that the numbers in the worksheet match precisely what you're actually spending (and bringing in, of course!).
Section 2: Income (Green Cells)
Pretty self-explanatory, I hope. In our sample screenshot below, Bob and Jane are 17 days into the current month, and they've entered all their received income in the previous periods (Days 1-7 and 8-14), as well as their expected income for the rest of the month (Days 15-21 and 22-31).
Each period allows for up to five INCOME items, for a maximum of twenty INCOME items per month. (You could always combine an item or two if you need more space.)
Section 3: Expenses (Red Cells)
The red sections are where you'll enter your expenses for the month:
Each period has space for up to sixty EXPENSE items, for a total of two hundred forty EXPENSE items per month. Since there's only so much vertical space on your screen at any one time, you may need to scroll up/down to see all your EXPENSE items.
Section 4: Projected End-of-Month Results
The top right section of the worksheet is where the meaty stuff happens! Here you'll find your projected end-of-month cushion (or shortfall), as well as the difference between all your INCOME and EXPENSE items.
As the formulas above show, your final yellow Summary box for Days 22-31 tells you what's left after all of your monthly income and spending, with your Starting Cushion figured in.
Put simply, for our hypothetical couple and their sample data (as of Day 17 of the month), their checking account will have started the month with a $240 balance (the Starting Cushion), but will end it with a balance of only $36.52. This assumes, of course, that the figures entered already (the estimated for Day 18 and later) don't change.
This isn't likely — but it's the best thing they've got for an estimate of where they'll stand!
The blue area next to that? Well, this shows you exactly what it says: Your total INCOME ITEMS minus total EXPENSE items. In the case of Bob and Jane's sample data, if their numbers don't change, they'll be spending $203.48 more than they brought in this month.
Hopefully this clears up the math a bit!
Hopefully the BoxBudget spreadsheet can help you get to a better place than Bob and Jane Bigspend seem to be headed!
Need a Bigger, Badder Budget?
Again, I created this BoxBudget spreadsheet to be really easy and user-friendly. If you're looking for something with a bit more detail, check out my Spending Plan spreadsheet.
You can see the list of all my spreadsheets — budgets and otherwise — at my Excel page.
Questions? Comments? Contact Me!
As always, if you've got a question or issue, don't hesitate to drop me a line. I'll get back to you as soon as possible!