October 27, 2008
In January of 2008 I wrote about Mint.com.
I’ve had Mint quietly gathering data for the past 10 months and I’ve come up with a way to intelligently budget for 2009. The only work I’ve done is categorizing the transactions to make this whole exercise worth while.
Step 1: Export all Transactions from Mint into Excel. At the bottom of your “transactions” tab in Mint there’s a small link that says “export all N transactions”
Step 2: Make two additional worksheets. One labeled “Budget” and one labeled “Supplementary Information”. I also edited my initial worksheet to be called “transactions”.
The supplementary information was sort of a scratch pad that fed my “Budget” worksheet.
Step 3: Start calculating your average monthly expenses for relevant categories. For instance, what’s my average phone bill?
=AVERAGEIF(transactions!$F$2:$F$575, "*Phone*", transactions!$D$2:$D$575)
- You need to understand how excel’s averageif works.
- For once a month bills, this is easy… but say you want to know what your average monthly grocery expense. I visit the grocery store many times a month. This adds a layer of complexity.
=AVERAGEIF(transactions!F2:F575, "Groceries", transactions!D2:D575)
That’s my average grocery expense, but not my monthly. We’ll need to implement excel’s COUNTIF function as well.
The formula for monthly grocery expenses I used is as follows:
((Average Grocery Transaction * Number of Grocery Transactions) / Months of Data Collected)
I also used this method for Restaurants/Fast Food and Wal*Mart visits. I grocery shop at Kroger and do other shopping at Wal*Mart for tracking purposes.
Step 4: Look at your monthly averages, and make up your mind.
I was alarmed at how much money eating at restaurants really costs. It never really gelled for me. I took it a step further and said there are 90 meals a month (breakfast / lunch / dinner – 30 days in a month) and found out my per meal expenditures for groceries and for restaurants. This also showed me another startling number – an average of how many meals a month I was eating at a restaurant.
I made a goal – eat out 5 times a month. This has an added cost of saying, I’ll have to eat groceries more. At a savings of $5 per meal, it’s worth it.