Jan 3 2015
The first step in unleashing debt jihad is to understand where your money is going. This is best accomplished by creating a Monthly Summary. This is the first weapon in the arsenal of financial freedom.
We began a monthly summary in 2007 shortly after moving back from Japan. We were amazed at how poorly we understood where our money was going until we started capturing it and looking at it through the lense of a holy warrior. Suddenly our enemy’s finger prints could be seen…and of course, the enemy was us! When we looked at the numbers we were actually adding about $65.00/mth onto the credit card debt. Knowledge is power…and applying that knowledge to our monthly finances became very powerful indeed. During the course of debt jihad we went from going $65 further into the hole to paying off $1000 of debt each month.
Step 1. Get a Spreadsheet Program
The funny thing is, that while we were swimming in debt I thought we’d be savvy and buy Quicken so we could track our expenditures because we could hook it direct into our bank account. Did you catch what I said in the previous sentence? I said “we’d be savvy and BUY Quicken”…just so you know…the answer to reducing costs is not by increasing them. What I found out was that Quicken was not going to be flexible enough for the granularity we wanted. So we ended up ditching Quicken and using good old Microsoft Excel which we already owned on our home computer. Of course there are lots more options available now such as Google Docs, Star Office but suffice it to say that some sort of spreadsheet software will be required.
Next you want to set up your spreadsheet with four tabs:
- Roll Up (Tab1) – Used for calculating the data from Month 1 (Tab2)
- Month 1 (Tab2) – Used for capturing account transactions and categorizing (using lists from Income and Expenses) them
- Income (Tab3) – Used for Income Categories
- Expense (Tab4) – Used for Expense Categories
Step 2. Get the Data
Once you’ve sourced a spreadsheet program and setup the tabs you will need to get the data. Almost all Canadian banks allow you to download your account activity in what is called “comma separated value” format or .csv file that contains all of your debits and credits. After you have downloaded the data you can copy and paste it into the Month 1 tab. NOTE: It’s best to wait a few days after the end of the month before downloading the data as there can be a few adjustments especially if the end of the month is on a weekend.
You should have something like this (I’ve removed the account number and changed the actual amounts to a generic fives):
Step 3. Set up Categories
Ok so now you have a spreadsheet with your income and expense data. Next you need to categorize the data so you can roll up the amounts and eventually create your budget. We have continually updated our categories over time (which is why using a flexible tool like a spreadsheet is so helpful and also why Quicken didn’t work so well for us) so for our example here I am going to use generic categories from finiki (http://www.finiki.org/wiki/Budgeting) but you can customize to fit your needs. You will need two sets of categories; income and expenses.
Sample Income Categories (Tab 2)
Canada Child Tax Benefit (CCTB)
Sample Expense Categories (Tab 3)
Rent or mortgage payments
Property taxes/condo fees
Utilities (such as electricity, water, cable or telephone)
Repairs and maintenance
Car loan payments
Car repairs, gas, etc.
Car insurance/registration, etc
Medical and dental
Outstanding loan payments
Life, disability and medical insurance
Step 4. Define Names
You should now have 3 tabs in your spreadsheet and now you need to make the categories from tabs 2 and 3 available for use on tab 1. In Excel this can be done via a drop down list (http://office.microsoft.com/en-ca/excel-help/create-or-remove-a-drop-down-list-HP005202215.aspx). Go to tab 2 and highlight the three entries and then go to the FORMULAS section of the ribbon and in the Defined Names section click on “Define Name”.
Step 5. Categorize the Data
Now that you have the categories available we need to categorize the data we downloaded from the bank in step 2 and added into the Month 1 tab.
Step 6. Roll Up the Data
We’ve got the ability to capture and categorize our data but we still need to be able to roll it up so we can get the full picture. We do this by building a roll up sheet that can scan the Month 1 tab and sum its contents based on its category.
Canadian Child Tax Benefit
=SUMIF(‘Month 1’!F2:F250,”=Canada Child Tax Benefit (CCTB)”, ‘Month 1’!D2:D250)
=SUMIF(‘Month 1’!F2:F250,”=Other”, ‘Month 1’!D2:D250)
The expenses column is E on the Month 1 tab so we need to modify our queries accordingly. Enter these queries into their corresponding cell numbers.
Rent or mortgage payments (Cell B9)
=SUMIF(‘Month 1’!E2:E250,”=Rent or mortgage payments”, ‘Month 1’!D2:D250)
Property taxes/condo fees (Cell B10)
=SUMIF(‘Month 1’!E2:E250,”=Property taxes/condo fees”, ‘Month 1’!D2:D250)
Home insurance (Cell B11)
=SUMIF(‘Month 1’!E2:E250,”=Home insurance”, ‘Month 1’!D2:D250)
Utilities (such as electricity, water, cable or telephone) (Cell B12)
=SUMIF(‘Month 1’!E2:E250,”=Utilities (such as electricity, water, cable or telephone)”, ‘Month 1’!D2:D250)
Repairs and maintenance (Cell B13)
=SUMIF(‘Month 1’!E2:E250,”=Repairs and maintenance”, ‘Month 1’!D2:D250)
7. Monthly Summary Complete
Are you still with us? If yes, you’ve just completed the building blocks for a monthly summary. You can use these building blocks to build out a custom spreadsheet to your own personal preferences including one tab for each month. Once your spreadsheet is setup and you are downloading data from your bank you can then move onto Phase 2 which is to setup budgets for the categories you created in Phase 1.
REMEMBER: the categories are not set in stone…you can update them anytime depending on your preferences but be sure to update anything that is linked in your spreadsheet (or do what we do and implement changes at the beginning of the new year so you don’t have to go back and remediate the current year’s data).
Attached below is the example monthly summary we built above (Excel 2013 format):