Household expenditure analysis: identifying savings

Obviously the size of your savings is clearly influenced by your current expenditure, perhaps not so much where compulsory superannuation is concerned in Australia, but definitely when it comes to making voluntary additional contributions to a pension of superannuation scheme.

And clearly, with the market the way it’s been over the last 12 months, many people’s pensions are now looking decidedly smaller, thereby increasing the amount that has to be saved.

So how in a practical sense can you identify what you’re really spending and more to the point, where you are spending it?

An obvious place to look is at the last 12 month’s bank statements but It is not that easy a task. For example, as an Australian resident with a household of 4, including two small children, we primarily use 2 funding sources to operate for household expenditure, a credit card and a bank account.

In Australia, as opposed to the UK, banks tend to impose more small transaction charges for things like direct debits and cash withdrawals so it makes sense to charge as much as possible to a credit card to reduce the number of bank account transactions provided you can pay your card off in full every month (and obviously it’s critical that you can pay it off).  The plus side of this credit-card based system is of course that Australian banks pay less derisory rates of interest on current accounts than do the UK banks.

Most accounts allow you to export transactions into something like Excel but on my accounts this is limited to the last 3 months, so you need to export every 3 months and save to build up a year’s worth.

Your next issue is quite a large number of transactions. For example, for our household of 4 we generated 367 transactions on our joint current account and 510 transactions on our joint credit card. Cash flowing into and out of other investments such as high interest accounts must of course be eliminated and then you’ve got the laborious task of categorising each expenditure item in Excel. To label all significant 850 odd transactions took me about 4 hours.

What you’ll end up with is something like this:

18/12/2007 Bigwonline             Abbotsford    Au 31.6 entertainment
18/01/2008 Simmone Logue Foods    Double Bay    Au1 55.7 groceries
12/02/2008 Harris Farm Mrkt         Edgecliff 52.63 groceries
25/03/2008 Sydney Ferries           Sydney 26 entertainment
26/03/2008 Sydney Aquarium          Darling Harbo 28.5 entertainment
27/03/2008 Peters Meats             Edgecliff 53.75 groceries
6/05/2008 The Bay Tree Pty Ltd     Woollahra 339.9 gift
3/06/2008 Deli Cucina              Edgecliff 54 groceries
21/08/2008 Orson & Blake P/L        Woollahra    Au 290 gift

Excel remembers your categorisations and will prompt you after you first type a the first letter of a existing category on a new row (see our category list at the end).

You’ll then have a huge jumbled list of categorisation which you can argue with your partner about for hours but changing things is no problem – you just use Excel’s condition sum feature.

For instance Excel will pick out all items categorised as ‘childcare’ from both our credit card expenditure sheet and our current account expenditure sheet wherever they are and add them using the formula:

=SUMIF(‘Credit-card’!E2:E514,”childcare”,’Credit-card’!C2:C514)+SUMIF(‘Cash-mgmt’!E2:E514,”childcare”,’Cash-mgmt’!C2:C514)

Incidentally for our household of 4 with two small children these are the categories we came up with and this is our summarized expenditure breakdown for the last 12 months (perhaps useful if you’re an expatriate returning to Australia):

Expediture Category A$ Amount Proportion
Childcare $27,554 18.1%
Groceries $24,084 15.8%
Tax $13,000 8.5%
Cash $12,200 8.0%
Travel $11,107 7.3%
Medical $8,697 5.7%
Cleaning $8,000 5.2%
Sundries $7,887 5.2%
Utilities $7,884 5.2%
Car $5,832 3.8%
Clubs $5,641 3.7%
Entertainment $5,230 3.4%
Restaurants $4,468 2.9%
Unknown $3,836 2.5%
Gifts $3,639 2.4%
Clothing $2,897 1.9%
Electricals $535 0.4%
     
TOTAL EXPENSES $152,491  

Probably bears no relationship to your own patterns (for example we have no mortgage costs) but that’s the whole point. Going through this exercise enabled us to identify about $13,000 in savings.

Posted under savings levels

This post was written by mike on January 7, 2009

Tags: , ,