I have a friend who’s not very fond of numbers. I suspect they reside in the same part of her brain as potentially hostile dogs and IRS auditors. She’ll deal with them when she has to, but if there’s too many of them she might end up running away no matter what her original intentions were.
She tries to be responsible with her money, but doesn’t have the time or focus to track daily expenses. So at one point she came to me and asked me to help her figure it all out. She’d tried trimming things as much as she could, and the fixed bills (like mortgage) and common variable bills (like groceries and utilities) were well below their combined incomes, but the credit card balance just kept climbing. I told her to bring all her receipts, bills, and/or bank statements from the last three months to me, and we’d see what we could figure out. In the end, this is the chart we came up with.
Since she did her banking online I was able to get her to log in so that I could download the transaction record. That saved a lot of data entry time, so don’t forget that’s an option if you want to do this yourself! I saved it in text-delimited format, then opened it in Excel. All the data was neatly lined up, ready for me to manipulate. I then manually added in the few items that had been put on the credit card during this time period, and we were ready to go.
The first thing to do was to group items. Most items had a description like “Visa Check POS Debit — L340 DATE 10-06 — RUSH BRASSFIELD-CK865-288-1287”. The relevant information there is the date (10-6), and location the money was spent (RUSH is a gym they went to). It was therefore labeled “exercise”. The first thing I noticed was that they had a lot of eating out — even fast food can add up to a pretty chunk of change, after all. I was worried that it would end up being the largest optional section of the pie chart, but in the end I was wrong. That’s why graphs are valuable – they give you more to go on than just your first impression!
Partway through grouping the items I started to suspect a pattern, and so I went back and added more information to my categories. The gym membership was for the household, so it became “HH-exercise.” Most of the eating out as for solitary work lunches, so they became either “Mom-eating out“ or “Dad-eating out”. At this point I was avoiding any judgments as to whether something was absolutely necessary or not, I just tried to record factual information.
Once everything was given a group I added up everything in each group. For example, if there were three “Mom-eating out” entries for $3.13, $5.70, and $4.26, then I added them up and combined them into a single entry of $13.08.
Once there was only a single entry for each group I sorted it by value, which I’ll explain how to do later. If I hadn’t sorted the values then the chart would still have been useful, but perhaps not quite so easy to read at a glance. The last step was to make a pie chart, which is what you see above.
If she’d needed it to be simpler I could have grouped everything further, into “optional” and “required” groups. The mortgage and utility bills would have been required, while the gym membership, eating out, and general entertainment would have been optional. If I’d done that, the chart might have ended up something like this one.
Or, to simplify it even further, I could have focused solely on the optional expenses, since logically they’re where the most belt-tightening could be done. That would have produced a chart something like this.
So, if you had a similar project, here’s how to do it.
- Gather the financial information for at least one month, preferably more. In this case we downloaded the last 3 months of expenses from the bank, and then manually added everything that had been put on the credit card during the time period.
- Group values into categories. In this case we categorized things by who the expense was for (him, her, their child, or the entire household), and by what the money was spent on (groceries, mortgage, entertainment, etc.). We didn’t worry about the fine details…the “groceries” category included anything bought in a grocery store, even if it was toilet paper or birthday cards.
- Add up all the values in each category. Imagine the pie chart like an actual pie. Lots of people try to “diet” by taking just one little slice, then another…and another. It makes it seem like they’re taking less than the person that took a big slice on their first trip and never returned for a second. Likewise “Groceries” may take as much of the pie as “Mortgage”, but if it’s in a lot of little trips then it’s hard to tell.
- Sort the values. This isn’t a requirement, but it makes the chart a little more readable, and makes it easier to tell at a glance which category is bigger.
- Make a pie chart. You can follow the instructions here.
- Add labels and formatting. You can follow the instructions here.