Track your expenses and get your finances and health in control

So you are trying to get fit and you have learned that to do so, your food habits are as important as the physical workout. This time we are going to look into a method for achieving control over your food habits as well as your expenditure. At the same time we will also map out your food expenses and with data illustration show how you actually spend money on food. As a bonus, we will include expenses for clothing, hygiene and household. This will give us a more complete overview of how we spend money for ourselves and our home.

This is not a be-all end-all solution, but a handy process to follow to get more insights into your food habits and food spending.

Track your expenses for 30 days – To start with

What you will do is to track your expenses over 90 days. To do this, you will need to save the receipts from your food purchases. To make it easier, we focus on the purchases in supermarkets and convenience stores for your home. That means that your lunches and dinners out are not in scope.

Maybe you’re thinking “why limit yourself to that? I spend a lot on food in restaurants and cafes..” That is a good point. However, the context here is the food that you eat at home as well as the expenses related to your home (widely interpreted, as we include hygiene products here).

• There are several goals to this experiment:
• We want to see the correlation between food spending and home food intake, which is usually where the bad habits are noticeable. Both from a personal finance and a food health perspective.
• We also want to see how often and how much we spend money on clothes, hygiene aritcles and the home.

Let’s get started!

The Excel file

I have provided an Excel file with this article, and you can download it here.

As you can see, we have columns for entering our purchases complete with the following information:

• —Day number (Excel enumerates the weekdays from 1 to 7. We then convert this to a weekday name. This is columns Day and Weekday, respectively.
• — The date of the purchase. This is column Date.
• — The total of the purchase. When we enter our purchases, I suggest that the entire sum is written out as an equation. That way you can easily go back and track individual expenses later. This is column Total.
• — The store. This will be vital for our geo-habit mapping in the analysis phase. This is column Store.
• — Category. We define several categories of purchases using capital characters. That way we can illustrate where our money is spent. Again, that is for the analysis phase later on. This is category Category.
• — Finally, we use one column as an increment of the day from the day when we began our data sampling. So day one will simply be noted as 1. Several purchases on the same data sampling day will have the same number. This is column Day increment.

If you look at the righthand side of the screenshot, you can see there is a column with our predefined categories. These are the categories of purchases. You can naturally add and remove categories as you see fit! But try to think about how to organize your categories before you begin the data sampling.

I found that the simplest was to simply buy items for one category in one shop at a time. That way you don’t mix the purchases with different categories (for instance buying food, hygiene and household items in one go). This approach made my data entry easier, plus if I have to go back and look at old purchase receipts I will know that one purchase receipt will only contain purchases for one category.

Below the categories, there are three placement areas for 30 day intervals. Here we will calculate the sums for the purchases in the respective categories. This gives us the opportunity to track our spending and analyse data in 30 day intervals. You could of course choose to analyze your data whenever you like. I have picked 30 day intervals since it gives me a monthly data set each to work with.

The first 30 days – Let’s focus on the food

The first 30 day period will define your routine. Make sure that you are strict with saving receipts and entering them into your Excel sheet. I often found myself saving up receipts for a few days and then entering them all at once, batch-style. It’s really up to you how you want to do it. But I really recommend that you start with making a reminder for yourself so that when you go shopping, you always save the purchase receipt. After a short while it will become a routine.

In fact, even after I completed this experiment, I retain the habit of saving my shopping receipts. That in itself is a big win.

Analysis of the first 30 days

Congratulations! You made it through the first 30 days! It’s time to analyze the data, which is a fun exercise. Here is my data for the first 30 days:

Category	Sum/category
Food and household	€ 37.84
Food	€ 264.74
Household	€ 94.44
Clothes	€ 33.07
Hygiene	€ 79.34
All	€ 509.43

In total, I spend 509.43 Euros on food, household, clothes and hygiene. Okay, so what can we deduce here? Well, the first figure that stands out is the food category, in total 264.74 Euro for the first 30 days.

Where do I spend on food?

By using the store column, I want to map out where I do my food purchases. This is to understand which area and which stores I go to. Maybe it’s possible to do cheaper shopping elsewhere, for instance? Let’s take a look.

Yeah, that makes sense, three supermarkets close to each other (the larger triangle with the food stores at its vertices), and close to home. Nothing that really stands out here.

But.. How often do I go to each store? This is an interesting one.. There are three supermarkets, let’s call them SupermarketA, SupermarketB, and SupermarketC:

SupermarketA 3

SupermarketB 26

SupermarketC 7

Now, this is interesting! I primarily go to one shop, a whopping 26 times during the 30 day period. That’s 7.7% of all my food purchases. When you think about it, it makes sense. We all live somewhere and work somewhere. Chances are that there will be a supermarket between both places that is convenient for the daily grocery shopping.

Is another store cheaper to buy food in?

This calls for an indepth analysis of the price differences between the supermarkets. But keep in mind that the data set is still only for 30 days. So you will want to do the same supermarket frequency check later again.

HYPOTHESIS (Try to deduce your own hypothesis for your spending!)

It’s time to pick a few items that I buy often. Let’s say food items FoodA, FoodB, FoodC. Then I go to the shops and compare prices. They might have different producers and offer volume discounts?

As you can see, we are already deducing facts that can prove to save you money in the future.

Which weekdays do I spend on food purchases?

With the COUNTIFS function in Excel, we can count the number of food purchases for different weekdays.

Weekday	Nr of food purchases
Monday	8
Tuesday	5
Wednesday	5
Thursday	5
Friday	3
Saturday	6
Sunday	4

As we can see, usually the food purchases occurs on Mondays and Saturdays, closely followed by Tuesdays, Wednesdays and Thursdays. It’s rare that food shopping is done on Fridays or Sundays.

HYPOTHESIS (Try to deduce your own hypothesis for your spending!)

The food shopping on working days is most likely smaller purchases, for daily usage. If I concentrate food shopping in the weekend, I could go further away to supermarkets with volume discounts, and possibly decrease the spontaneous food purchases?

Where do you spend on clothes?

Let’s do the same exercise for clothes. Only one store in my case. So not much to say about this so far. We will have to gather data before any phenomenon can be observed and forming a hypothesis.

Where do you spend on hygiene and household items?

This is an interesting one! After I added all the locations for the hygiene and household shopping, I use Google Maps to create polygons around the locations. It quickly becomes apparent that there are two main areas where I do my shopping for hygiene and household items.

HYPOTHESIS (Try to deduce your own hypothesis for your spending!)

I can easily say that these are areas where I often pass through, so obviously I choose the stores that are close at hand. Maybe there are options for my hygiene and household shopping further away though?

This is an hypothesis I can test by simply finding alternative stores or stores in the same chains.

Data analysis is only as useful as the quality of your data

We have now completed the first 30 days of the expense tracking experiment. During this time we have accumulated a data set for 30 days. We have made some observations and formulated several hypothesises. With these we can already beging tinkering with the routines for food, hygiene and household product shopping.

But the observations are based on 30 days of data. To make more accurate observations and spot other hidden trends, it’s really necessary to continue this experiment for a longer time. That is why I have developed the course to track expenses for 90 days! In that course you will see how hypothesises develop over time, as well as how to illustrate the gathered data using MS Excel with graphs.

The next 30 days

In the next 30 days, you simply continue with the data entry. Save the purchase receipts and continue entering the values as new rows in the Excel sheet.

At the same time, remember the hypothesises that you established in the last section. What’s more interesting now is to simultaneously think of your theories and possible improvements while still going about your daily life and shopping.

Analysis of the next 30 days – Comparing with the last period

We now have more data, in total for 60 days. This means that we can compare the current 30 day period with the first 30 day period, and thereby see any fluctuations or trends. This is highly interesting as we can begin to notice any trends, for instance, did you spend more or less on food shopping this month?

You will now be able to calculate your first meaningful monthly average for the various categories.

Take food for instance:

(€ 264.74 + € 306.81) / 2 = € 285.775

This is my monthly average for food shopping for the first two 30 day periods, so roughly two months. This is another reason to use 30 days or a month as the period, because that enables you to understand your spending in cycles of income (monthly salaries).

To continue, I have the following summaries from the first two months:

First 30 Days	Sum/category
Food and household	€ 37.84
Food	€ 264.74
Household	€ 94.44
Clothes	€ 33.07
Hygiene	€ 79.34
All	€ 509.43
Next 30 Days	Sum/category	Current – Previous
Food and household	€ 0.00	-€ 37.84
Food	€ 306.81	€ 42.07
Household	€ 117.14	€ 22.70
Clothes	€ 59.69	€ 26.62
Hygiene	€ 12.98	-€ 66.36
All	€ 496.62

Here you can see that I compare the expenses per category for this 30 day period, with that of the first 30 day period. Thereby I can see if I have increased or decreased the spending in any category.

To inspire you for your own analysis, here is an interesting observation: I increased the spending for food, household as well as clothes. To dig into this, I could go through the purchase receipts for the particular time periods.

The last 30 days

Okay, it’s time to take a look at the last 30 day period.

Third 30 Days	Sum/category	Current-Previous
Food and household	€ 21.81	€ 21.81
Food	€ 293.07	-€ 13.74
Household	€ 49.72	-€ 67.42
Clothes	€ 164.06	€ 104.37
Hygiene	€ 16.80	€ 3.82
All	€ 545.46

After a quick look at the table above, we can see that the monthly total went up with about 50 Euros. It’s also apparent that the Clothes category was the major increase (~100 Euros more spent in the Clothes category in this period than the last period).

Analysis of the last 30 days

Let’s take our data analysis further. Let’s look at our Excel sheet again. As you can see we have saved the week days of our purchaes. This means that we can calculate statistics of which weekdays we usually go shopping!

Look at the Analysis tab to see how I investigated this. In the right-hand side you can see data with the title Food purchases/weekday, underneath which I have used COUNTIFS statements. These make it possible to count values in one column if and only if another column contains another value.

=COUNTIFS(Data!D4:D54,”1″,Data!I4:I54,”B”)

“B” stands for our Food category. The “1” thru “7” values are looked up from the Day column in our first sheet. This is the numeric representation of the weekday.

The results of these calculations are shown in the column Nr of food purchases (Analysis sheet). I have highlighted the 2-3 most frequent food shopping days in red.

Interesting. It turns out that for the 90 days, Mondays are in the top 2-3 food shopping das for periods 1 and 3. Furthermore Wednesdays are in the top 2-3 food shopping days for periods 2 and 3.

So, we can generalize and say that it is most probable that I will go food shopping on Mondays or Wednesdays.

The food shopping average for three 30 day periods

Like before, let’s calculate a monthly average for the food shopping.

(€ 264.74 + € 306.81 + € 293.07) / 2 = € 288,21

There you go, an average value for the food shopping per 30 day period.

Summary and your next steps

So far we have gathered data for 90 days in total. I have only touched upon a few metrics, but here are some other questions you can ask yourself:

Interesting questions to ask yourself..

– Which days of the week do you spend the most?

– How much do you spend weekly on candy?

– How much do you spend weekly on food (excluding candy)?

– How much do you spend on an average day?

– How much do you spend on an average week?

– Which stores do you go to the most?

– When do you go to certain stores (week days)?

– How much do you spend on clothes monthly?

– How much do you spend on hygiene monthly?

– How much do you spend on household monthly?

– How much do you spend on food monthly?

– How does your spending correlate in general to your salary payments? (requires noting down Day X for salary payments)