Thursday, July 30, 2009

How to Get Started with the Budget Allocations Checkbook

The Budget Allocations Checkbook is a Microsoft Excel spreadsheet that has been developed over several years of use for tracking my personal expenses and managing my budget. During that time, it has gone through many iterations, and has been enhanced with macros that take care of very repetitive and mundane tasks involved with the chore of updating one's budget. It has become an invaluable tool for me in the management and tracking of our household spending.

It's designed around the idea of "allocations". Most of the budgeting strategies out there are centered around the idea of categorizing money you've spent. That's all well and good. It's good information, and it's a good habit, because it gives you an idea of how much of your income is being used for different types of things. But life doesn't demand the exact same spending habits for each paycheck. So, if you were to allocate each paycheck to different categories of spending, what are you supposed to do when you don't spend it all? Go blow what's left on take-out and DVDs? If you do that, then the category that had something left over will undoubtedly go over budget in the near future, and you'll be in a pinch.

Instead of treating what's left over as extra, it should just stay in that category. A perfect example is the "Auto & Home" allocation. I put money into that "allocation" every week, but thankfully, I don't need to buy things for my house and car every week. So, it builds up over time, which is a good thing, because when I do need to spend money, it's because something is broken, or wipers need replacing, or I need to run to the hardware store, or something. And the money I need to spend will be much more than what I put aside in a single week.

So, the only way to come close to having the money for things when you need it is to put all of your income into allocations, and leave it there until you need it. Figuring out the correct amount for you is a bit of an art, and it takes time to find the right balance, but in the long run, it makes life a lot simpler (financially, anyway).

It is meant to be used for a single account. I recommend simplifying things by doing all of your spending from a single account, as well. But, I'm sure that may not be possible for some people. In that case, one could use a copy of this spreadsheet for each account in use for normal spending.

Quick Start

Here is the easy way to get started tracking the spending for an account.

  1. Download it the empty spreadsheet: CBook Allocations.xls

  2. Get your transactions
    1. Download transactions from your bank in Excel format, or "comma-separated" (CSV)
    2. Copy and paste the transactions from the downloaded statement into the "statement" worksheet.
      1. If the columns used by your bank are not the same, then you can paste each column individually.
      2. The "transaction ID" and "pending" columns are optional

        Figure 1 - The "statement" worksheet

    3. Add a "starting balance" transaction in the "transactions" worksheet .
      1. The date should precede the date of the first transaction in the downloaded statement
      2. The amount should be the amount before the first transaction in the downloaded statement
      3. Put it in the "Buffer" category
      4. (optional) split the starting balance transaction (with the "Split" button) until you've allocated your money the way you want
    4. (first time only) Add a "starting balance" transaction in the "statements" worksheet

  3. Click the "Import" button on either the "statement" or "transactions" worksheet
    1. This will automatically import your activity into your transactions table.

  4. Pick a spending category for every transaction.

  5. Allocate your income
    1. For the transaction(s) in the "Salary" allocation, use the "Split Transaction" button to allocate that money.
    2. Split the salary transaction until it has been entirely allocated.

  6. Allocate the imported transactions. The allocation column for each transaction should be red if it has not been chosen yet.

  7. Select the "Allocations" worksheet. Click the "Update and Copy" button to copy the current allocation amounts and update the allocations table.

The hardest part of the whole process is copying and pasting your transactions into the statement worksheet. But once you've done that a couple times, it's a snap. The rest is already a snap. :)

If you want to try it out without going through the trouble of downloading your own transactions, try out this version with sample data in it.

Follow-up articles:
  • Automatically rename and categorize regular payees
  • Split your transactions automatically
  • Customizing your allocations
  • Moving money between allocations