Using Pivot Tables to Analyze Expenses

Back in February, I bought Caitlin Flander’s Mindful Budgeting planner. I hadn’t done any analysis or looked at how much I was spending yet.  I spent the first 3 months, simply collecting data, but decided to use my long weekend to do some analysis as Q1 comes to a close. This afternoon, I punch all the data points into an Excel spreadsheet and tried my hand at pivot tables and charts.

I actually have no experience with pivot charts or pivot tables, so I worked my way through a tutorial by Excel Campus off of YouTube. I found it fairly straightforward and produced two bar graphs.

Graph 1 – Personal Expenses by Category: This chart summarizes my total spending, including shared expenses for both J. and I. The Daily category includes mainly day-to-day expenses, but is comprised mainly of groceries. The Fun category includes shopping, alcohol and eating out. Transportation covers car expenses, including my financing and car insurance, but also travel. Unfortunately, I did not split the shared expenses, so the total value is slightly inflated. Here are a few observations:

  • Transport was high in Feburary as I paid for some tune-ups at the Honda service centre ($709), as well as a plane ticket to go to the job interview ($1077).
  • Health expenses are mainly Cody app plans that I purchase. There is a negative value from a refund that I received.
  • The professional category was higher in February because I had to pay my annual membership to the Ontario College of Teachers ($130) and I bought a lot of food from my own pocket to cover a few classroom parties.

expenses

Graph 2 – Shared Expenses by Subcategory: The data source is actually the same as the first graph, but the information is filtered to show only the expenses that I share with my boyfriend. Unfortunately, there are still some discrepancies from the numbers we calculated on Sunday, specifically under groceries, but ultimately, it`s not about the details, it`s seeing the trends that is most important. Some more observations about this data:

  • We had a party in March and went out a few times, therefore spending more. We also wandered in and out of the grocery store out of boredom after work.
  • We don`t eat out very often, but I know there is at least one meal out that I forgot to mark down because J. paid in cash … I shall be more diligent next time.
  • J. pushed me to walk more to work, so we use a lot less gas now. Mind you, we only use one tank of gas in a month. It`s about $65 for a full tank up north, and you only have one gas station to go to, so you can`t be fussy about prices!

 

expenses

All in all, this was a fun exercise. I still don’t really care to create a budget, but like observing my data. The only area that really might need to be reeled in a bit are groceries, but we cut back in the other areas that I feel like we need a bit of give there.

Anyway, I’m happy to have done this. It’ll be interesting to see how my spending will change when I go south in a couple of months!

Advertisements

One thought on “Using Pivot Tables to Analyze Expenses

  1. Pingback: Monthly Goals – April | Stumbling Through My Thirties

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s