Tuesday, January 19, 2010

Budget Planning with Excel: The Columns

What columns do you need in your Excel spreadsheet to track your finances? Well, the way we do it, you need one column for each

1. Source of income
2. Account that holds money (assets)
3. Account that you owe money (debts or liabilities)
4. Category of regular expenses

The first three should be easy to identify. I have one job, my wife has one job, so there are two income columns. We have a checking account, a savings account, and each of us carries cash, so there are four asset columns. We have a few credit cards and some student loans (our debt columns).

Deciding how many of the fourth kind of column (budget categories) you need takes some trial and error. We currently have about 20 budgeted columns. Just to give you some ideas, here they are:

Household supplies/Laundry
Life insurance
Health care
Car insurance
Car maintenance
Renters' insurance
Water/Gas/Garbage [all three of these appear on one bill]
My cell
My wife's cell
Social events

And the big catch-all:


We've set a certain amount of money that goes into each of these columns at the beginning of the month. Any leftover money should carry over to the next month, but the reality is that sometimes you have to steal from one column to make up unforeseen shortfall in another. This is where trial and error comes in. If one column is always short and another is always over, maybe you can change the allocations. Or maybe (if the Groceries column is always short, for example) you need to rein in your spending.

Finally, I should mention one other kind of column that is largely optional. I have columns for the deductions that come out of our paychecks before they're deposited: taxes, retirement, flexible spending accounts. I included them this year for the sake of completeness, but they're out of the picture for the basic monthly budget. They mostly don't interact with other accounts and budget categories, and my pay stub already shows me what the running total is.

In the next post, we'll start talking about the macros and forms I use to save time in entering income and expenses.

No comments: