Author Topic: Excel Tracking Template for Fewaopi  (Read 1787 times)

BurquenaAbroad

  • 5 O'Clock Shadow
  • *
  • Posts: 30
  • Location: Australia
Excel Tracking Template for Fewaopi
« on: November 14, 2014, 03:34:32 AM »
Fewaopi asked me to share a template of the Excel spreadsheet my partner and I created to track our spending, but I since I can't attach a file to a PM, I've decided to make a post (and maybe others will find it interesting).

First, my partner and I have merged finances, and we keep this Excel spreadsheet in a Dropbox so we can both access it throughout the day from our work computers. Typically we enter spending as soon as we get back to a computer, which is at least daily. We have a different column for each of us, just to help us remember who bought what.

Second, we have major categories with subcategories, for better tracking. Our system is this:
major category -->subcategories
Food -->meat, supermarket misc, fresh produce, work lunches
Needs -->rent, utilities, phones, medical, household requirements [eg, toilet paper]
Transport -->car, bikes, public transit
Fun -->food out, booze [incl coffee], events, clothes, grooming, gifts, posting letters and packages
Travel -->plane tickets, ground transport, accommodation, food, fun

Each major category gets it's own tab, and the subcategories are organized horizontally. We keep the monthly totals at the top of the table, and freeze the panes below, so we can see the monthly history and the most recent spending entries at the same time. The monthly totals (both for subcategories and the whole category) are a simple "=sum()" equation, which I alter by hand. Our Food tab also gets weekly sums (hence all those underlines) because it's easier for me when I do the shopping, but it's not strictly necessary.

The neat bit is that we use an equation that can automatically organize an entry into the correct subcategory. The equation is, for example:
=IF(COUNTIF(C10:E10, "*meat*")>0,D10+F10,"")

This equation is copied down the entire "meat" subcategory column. Then, when I buy a chicken frame, I enter "chicken frame meat", and it automatically puts the cost into the correct subcategory. We don't do this for every subcategory, because we developed this just in the past year, but I think we'll use it more effectively in the next generation spreadsheet. The remaining subcategories we tally by hand

The best bit is that each major category monthly total is linked to a box in the tab Monthly Totals, which lets me make that neat bargraph to compare our spending month to month. I can also easily calculate the average, etc in this page, which is helpful to see whether we're hitting above average or below average in a given month.

Lastly, we also have tabs to keep track of our Mustaches (a beautiful excel page) and our Income (an ugly excel page), but I've run out of time to post about those at the moment. Let me know if you're super keen and I'll give it another go.

I hope all that wasn't too confusing; my partner and I have developed this system over the past 1.5 years, and it's a bit of a Frankenstein as a result. However, the bonus is that I am dramatically better at using excel now than I was when we first started. I HIGHLY recommend this as tool to both improve your Excel skills and for tracking your spending because it's highly customizable and keeps you responsible - the computer isn't going to do any tracking for you, so you've got to do it yourself.

Ask me if you have any questions.

DrCadmium

  • 5 O'Clock Shadow
  • *
  • Posts: 6
Re: Excel Tracking Template for Fewaopi
« Reply #1 on: November 14, 2014, 05:19:27 AM »
I added some formulas in the food tab in grey to save you from manually summing everything up if you fancy using it. Some costs weren't even included in the table before *GASP*!!
:)