Friday, September 30, 2011

Tracking Expenses in Excel (food expenses 1.0)

For a period of time, I have been budgeting my food expenses using a cash only approach.  Every two weeks, I take out enough money for the next two weeks and rely on only that cash for my food.  (I haven't always done so well with that approach.)  There have been some good things about it, but I'm opting for a change in how I track my food expenditures.

I have made up a spreadsheet in Excel that has three tabs on it (download here):
  1. Summary.  This is what makes the spreadsheet really useful.  It keeps a running total and average daily expense going and you can modify a few cells to get it to report a different number of days or months of history.  You can specify a start and end date in two of the sections.  The last section gives a summary of costs for the last 12 months (as time goes on, the "queries" automatically update to reflect the most recent past 12 months).  If you want to know how many days (after today) that you need to avoid buying things in order to get your daily expenses down to a desired level, adjust the purchase free days after today: cell to see the effect.
  2. rawData.  Enter all of your food expenses here.  Be sure to include the Date and the Amount - the Categories column is optional and the current version of this spreadsheet does not base it's summaries on anything in this column.  But it is included for completeness - and you can look for yourself how much you've spent on Junk.  It is up to you to decide how finely you want to break down your purchases for entry in this tab.  I normally just enter my receipts at the end of the day (plus any food costs I don't have a receipt for) and categorize broadly.
  3. CategoriesList.  You can change or add up to a total of 9 categories in this tab.  The number 9 is arbitrary and if you look under the Data tab of the ribbon, you can click on Data Validation in the Data Tools panel and figure out how to change it.  Be brave!  Do a little poking and prodding and you'll likely figure out how to do it.  (Or don't and just use it the way it is.)
It should go without saying that you can use this spreadsheet to track whatever expenses you want. 

The current edition does not support selecting on just certain categories of expenses to produce a summary of only those.  However, if you "unhide" some rows in the summary tab, you may discover that it isn't difficult to make such modifications as are necessary to choose only one category or more.  If you want to learn how to modify these queries, look up the function DSUM in the Excel help and peruse what you find.  

Another minor limitation of the spreadsheet is that it only references 499,999 rows of data in the rawData tab, which is easily changed.  A quick calculation will reveal why such a change will not be necessary.

I've just made a vanilla expense tracker that should be easy for anybody to use without the need for making any modifications, but there's nothing stopping you from making it really cool.