The Spreadsheet Queen Tutorial

Okay, I like my spreadsheets – that’s hardly a secret to those who know me, LOL. I figure out I have a need to track something, and when I can’t find an existing form or template on the net that gives me all the aesthetic or practical qualities I’m after, I design my own. Most of these are very basic on purpose. The less ‘fuss’, the less ink is used up when I go to print them out for my records later.

I have many, many spreadsheets on the go at any one time (*grin*) and when I mention these to others, especially my net buddies, I inevitably get questions about how (and more recently why, LOL) I go about using them. So I thought I’d write a tutorial to give you an idea of the types of templates I’ve designed, what I use them for, and how I do it all.

This tutorial will concentrate on Household Expenses.

Groceries

Let’s start with the basics. For most of us, this is one of the most variable expenses and most of us would like to save as much as possible in this area. One of the first steps is to first track what you are spending, and then work out where to cut back. A while ago I got curious as to how the individual categories stacked up over the course of the month and year. I felt the burning need to know how much I spent on meat, dairy, fruit and vegetables and so on. Some people have questioned my sanity on this one – why so many categories? I can’t explain this specifically but offer up this explanation: I’m just about one-fifteenth crazy, LOL.

I started with a Master Grocery List.

All of my most frequently-bought items are listed in categories. Each week I print one of these babies off, stick it on the fridge, and simply use a highlighter to mark off the things that we need. Doing it this way is helpful because as I’m wandering around the supermarket I’m also prompted about items we may not specifically need that week, but that we buy regularly – so I can stock up if I see them on sale, knowing we’ll use them eventually.

Next, I designed a Grocery Tracking Spreadsheet.


As you can see it is a very simple Excel document, spread out over two A4 landscape pages. I know what you’re thinking, and no, I’m not OCD…well not yet anyway, LOL. On the left are numbers 1-31 to represent the date. The lines after the 7th, 14th, 21st and 28th are a visual tool to break the document up into weeks. The position of these change with each new month. The darker line in the middle shows the page break. I’ve already started inputting amounts for March.

The headings at the top of each column match the categories on the Master Grocery List. I have 26 categories all up (trust me, the men in white coats are on speed dial) hence spanning two full pages, but it is worth it.


This shows the ‘totals’ columns – one at the bottom of each column for each category and another at the end of each row for the date. In this way I can run my finger along any row or column, and (for example) tell you how much we spent on Meat, or Beverages for the entire month (totals at bottom) or how much we spent across all the categories on any particular date. At the very bottom right hand corner is a ‘grand total’ cell showing the entire amount spent on everything for the whole month. The columns were formulated to automatically calculate the totals for me, so whenever I enter a new figure in any cell, the totals in those columns change automatically. Takes a very big chunk out of the workload (*grin*).


This is how the final document looks. It prints out on two pages. I tend to print these off at the end of every month and put them in my Home Management Binder under my Financial section – they don’t line up exactly (to put them in the Binder I have to stick them in ‘portrait’ orientation which alters how one reads them somewhat) but it works pretty well. In theory, at the end of the year I have twelve months (24 pages) of information, showing exactly where my grocery money went. Over time, you can usually find trends, such as an increase in spending during holiday periods, and you can use that information to better prepare you for next time.

Other Categories

Most of my other spreadsheets for household expenses follow the same basic layout as the one above, modified slightly to remove unnecessary columns and to change the headings. All of my spreadsheets – with the exception of the Grocery Tracking Spreadsheet, are single page documents. Here’s an example of a few of those:

Education


Leisure & Entertainment


Clothing & Shoes


(The names for this one have been removed – but there is room for six family members to each have a column for Clothing and one for Shoes).

And finally, there’s the Household Expenses Tally.


This is virtually identical to the layout of the Grocery Tracking Spreadsheet with just the headings changed to reflect the category names – Mortgage (or Rent), Groceries, Leisure & Entertainment, Education, Household Repairs & Maintenance and so on across all 26 categories. This will probably end up being the only paper record I keep, along with the details of the grocery expenditure, with everything else backed up to disc if I need to go back and look at something more deeply later. This ‘snapshot’ look at our expenses will go into the Financial section of the Binder as well. So an entire year’s worth of financial information can be contained in 48 pages (two each per month for the Groceries Tracking Spreadsheet and for the Household Expenses Tally). If it grows too large for the Financial section of the Binder, it’s a simple thing to switch to a stand-alone binder.

Household Expenses List

Use this list to help brainstorm your own list. Keep in mind that our family’s expenses probably won’t be the same as yours and just because I track so many, doesn’t mean you have to (*grin*). Remember: I’m crazy, LOL.

Major Expenses

- mortgage
- groceries
- electricity
- gas
- telephone
- council rates
- water
- mobile phones (we have two on the same account)
- internet

Education

- school fees
- other study fees (I study)
- uniforms
- textbooks
- workbooks (at home learning with the kids)
- excursions and camps
- sporting fees
- school performances
- canteen
- casual days etc (they usually ask for a gold coin donation)
- school incidentals (anything else I’ve forgotten)

Leisure & Entertainment

- takeaway (family)
- takeaway (work lunches)
- takeaway (days out – for example, when I’m out on errand day)
- respite (eating out)
- respite (worker) **We have a disabled son and are registered with a respite service who cares for him and our other two children in our home about once a month – so this basically forms Date Night.
- cinema
- DVD rental
- photos (development)
- lottery
- alcohol
- misc leisure

Clothing & Shoes (separate columns for Clothing and for Shoes, for each family member)

Gifts

- birthdays (family)
- birthdays (extended family)
- birthdays (school friends)
- easter gifts
- misc gifts (noticeably absent is Christmas Gifts…they come later)

Medical

- doctor visits (gap fee)
- prescriptions
- OTC medications
- dentist visits (gap fee)
- other specialists’ visits (gap fee)

Insurances

- comprehensive car/contents (combined premium)
- home (building)
- ambulance

Car & Transport

- registration
- petrol
- RAA roadside assistance
- servicing and maintenance
- parking
- licensing
- public transport

Banking

- transaction fees (we have none because we choose people-free banking almost exclusively)
- credit card annual fee
- monthly mortgage fee

Allowances

- one column each for the five family members (we each get a personal allowance)

Personal Grooming

- haircuts
- beauty treatments

Household Repairs & Maintenance

- pest control
- general repairs
- computer repairs and software

Travel & Holidays

- accommodation
- misc petrol (above and beyond our usual fuel costs for that time period)
- misc food (above and beyond our usual grocery bill for that time period)

Christmas

- gifts (we haven’t yet separated this into a column for each family member but we probably will at some point)
- food
- decorations

Savings

- adults (collective)
- column for each of the kids

Even If I’ve Made It Seem Complicated, It’s Really Simple…Honest!

The possibilities are really endless with this kind of tracking – I have around 26 major categories I track but within that, countless subcategories as represented by the many columns within each document. It paints a very accurate, very useful picture, with heaps of benefits. And it really only takes a few minutes each day to input figures, or around half an hour a week if you simply toss your receipts into a box to deal with later. And you don’t need complicated or expensive software programs to do it.

And to all my net buddies…now do you understand what I’m on about? LOL.

Cheers,
Lizzie