One of the biggest struggles for people, both young and old, is managing their money correctly: 66% of millennials don’t feel on track when it comes to saving for retirement, according to a 2019 TD Ameritrade report, and before the pandemic, households struggled to save more than 8% of their income. Despite the growing wealth available to the population, it’s still not easy for most of us to make wise financial decisions, but some hacks shall help.

The aim is to propose a system that should help you understand where your money is flowing, stick easily to your financial goals, and not lose time with Excel spreadsheets. Let’s clarify from the beginning: the only way to start managing your finances is to collect data about it and process them correctly to have actionable advice. To quote for the umpteenth time the famous economist Peter Ducker, you can’t manage what you don’t measure.

Following the quote, by the time you have finished reading the article, you will have an extremely light cross-platform form to fill, an automated Excel spreadsheet that will process your answers, and an insightful Data Studio Dashboard that will guide you.

I also believe that it is straightforward to get lost in Excel and analysis: once you capture some data, there’s a zillion way to process it. My statement here is that your examination should reflect your budget goals: do you want to save for the holidays? Do you want to reduce the expenses in Margaritas? Perfectly fine. Just make sure that you adjust your system to that.

For instance, in this article, I will show you my budget around two goals: saving a good portion of the salary and reducing fun-related expenses. What if you do not have any purpose? What I would suggest here is to stick to a general rule of thumb, such as the 50-30-20 rule, that says to divide your monthly after-tax income into three spending categories: 50% for needs, 30% for wants, and 20% for savings, or paying off debt.

Capture

The first step of the process is generating a form to capture our transactions. To do that, I have chosen Google Forms since it’s elementary and easy to manage. The Form is composed of three questions:

1. Amount: here, you are going to specify, of course, the amount of money spent.
2. Category: Here’ the notation is basic: I’m specifying whether it is a revenue or a cost, adding two dots, and then the category’s name. Needless to say that the minor type you manage, the easier it will be.
3. Notes: here, we can add some additional notice regarding the transaction if we want not to forget one of them.

And the first step is done!

Process

The next step consists of creating a spreadsheet to store all the answers. Here I’ve prepared a template you can use, open it, click on File, and select “create a copy”. It’s not over yet! We need now to connect the google form to the spreadsheet to store the answers. To do so, go to the Form, click on the answer section, click the Google Sheet icon, and select the template you have just copied.

Once you’ve finished, the spreadsheet will be composed of three parts:

• the raw answers collection
• the processor where the answer is processed
• the elaboration in which some additional information

Now that the integration is ready, you need to make sure that raw data get captured in the processor. To do so, select A2 cell in the processor, then select A2 cell of the raw datasheet. You’ll get a formula such as =FormAnswers!A2. Select the cell again cell, like the little blue box in the bottom right of the cell, drag the selection till cell D100. You’ll see that also the other three columns will populate if you have data in the first four columns.

Once you have some answer stored, you will find the first insights about your financial performance: you will see the average money spent per day and the spending target: your balance divided by the day missing the salary (assuming it’s on the 10th of the month).

Visualisation

Of course, you are not getting any useful information yet, and this is why you may want to go to the second part of this article, where you will see how to build a dashboard like this: