This is the fourth in my series of articles on personal finance with Numbers. In this chapter, we will learn how to use our spreadsheet to make predictions about our financial situation.
Predicting Your Cash Flow
The example in the last chapter ended when you started your Internet business as a TikTok star. You borrowed some money and bought your first piece of equipment, a phone.
Now you have created your first content and the number of followers is growing. Money from TikTok starts coming in. You can start dreaming about buying that Xbox you always wanted. But first you have to pay back your grandfather’s loan. How long will it take you to accomplish these two goals?
In this chapter, you will learn how to predict your future financial situation using our double-entry bookkeeping system.
Money Coming In
For the past few weeks, you have been busy producing a new video every day. Your follower count is slowly but surely growing, so TikTok has started paying you.
Your first payment for June has just arrived: $5!
When you created your business plan, you looked at how quickly other influencers’ incomes grew. In the first few months, it grew linearly.
So you predict that your income will grow by $5 every month. Next month it should be $10, then $15, and so on. You can write it down like this:
Now you have a good idea of what your income will be for the rest of the year.
Next, you want to know if you might buy an Xbox for Christmas. So you want to calculate the value of your cash account at the end of the year.
Therefore, you list all the other transactions that are likely to occur.
Your parents’ allowance is a fairly predictable cash flow:
On the other hand, you will have some expenses as well.
Every month you have to pay $10 for your cell phone plan. This is essential for your business.
And your sweet tooth has not gone away. For the past few months, you have been paying at least $5 a week for new candy, which comes to $20 a month. Knowing yourself, you include them in your projections.
And if you like, you can also sort these transactions by date, so you can see all the transactions that happened in a month together. You can do this using the context menu of the first column “A”.
Now you have all the data you need to look into the future. With the data prepared, the next step is to do the calculations.
Looking into the Future
How can we use Numbers to automatically calculate the value of the cash account today and at the end of the year? Or better yet, how will it look at the end of each month?
Let’s turn these questions into questions for Numbers. The questions are:
- Can you give me the sum of all transactions for the Cash account that have occurred to date?
- Can you give me the total of all transactions for the Cash account up to the end of the year?
- Can you give me the total of all transactions for the Cash account up to any given date, e.g. the end of a month in the future?
The answer, of course, lies in the “sumifs” formula. With it we can answer all these questions.
The trick is that “sumifs” can check multiple conditions. So far we have only used the condition if the debit or credit account has a certain value, e.g. “Cash”. Now we want to add another condition. This condition is: “Is the date of this transaction earlier than the date I am interested in?” This date could be today, the end of the year, or the end of a particular month.
We could write it like this:
We added two new parameters: First, a value we want to check, in this case the date of the transaction. Next, the condition we want that date to satisfy.
Unfortunately, Numbers does not like this notation. It cannot take another formula as a parameter of a formula.
But there is a solution, it just takes a little detour. The way to make it work is to add some extra columns to our list of transactions. In these columns we will perform the calculations we are interested in. Then we can use the result of these calculations in the “sumifs” formula.
Let’s add a column that calculates whether a transaction has occurred by today.
First, add a column to the right as follows
We call this column “Booked to date”.
Next, we will add the formula “Date earlier than today”. First click on the column with the transaction date, A3, then type <= and then TODAY.
We add this formula to all cells in this column by dragging it down.
It should return TRUE for all transactions in the past until today and FALSE for all transactions that will happen in the future.
Finally, we can use this column to calculate the balance of the cash account as of today. We put the new column as the last condition of our formula:
Take a look at the last two parameters. The first parameter is the new column “Booked to date”. This is the column whose condition we want to check. And the condition is simply that the column has the value “true”, so the second parameter is simply the value “true”.
Applying this formula to both the credit and debit sides of the cash account gives us the cash account balance as of today, July 1st, 2023:
With this new trick up our sleeves, it is not too difficult to calculate whether a transaction has occurred by the end of a given month. To do this, we need a new column and a new formula.
We add a new column and name it “Month”. Then we use the formula “Month” and apply it to the date of the transaction:
The result is a number representing the month in which the transaction occurred, 1 for January, 2 for February, and so on.
This column enables you to easily calculate the balance of the Cash account in any given month. Say you want to know the balance in June, you set the condition that the “Month” column is equal to 6.
To quickly display the balance for each month, we create another table, with each month having its own row.
We then apply the formulas to each month by dragging it down
Now you can see the cash at the end of each month in the last column.
But wait, there is a bug! The problem is that the formula now calculates all the transactions that have occurred on the Cash account during each month. But the balance of each month does not take into account the balance of the previous month. Only the two together give us the real value of the cash account.
This is easy to fix. Starting from the second month, we change the formula to add the balance from the previous month. Then we can drag the cell from June down to December.
Your calculations are now correct.
And as a last step, you can hide the columns. They are not helpful for you, only for your formulas, and they will work automatically from now on.
Planning Your Goals
In this table, you can now see how your cash will develop.
This will help you answer your first question: When will you be debt-free?
The numbers show that your cash is growing every month. In October, it exceeds $100.
This is the right time to pay back your grandpa.
And when will you be able to buy your Xbox? You have to take into account that paying off your debts will reduce your cash, so you can put this transaction in your list of future transactions. Let’s put it at the end of October:
With this additional transaction in place, take a look at your updated numbers.
By the end of December, you have just over a hundred dollars left.
This is a bit disapointing. It is not really enough for the new Xbox and a few games.
Now you know that this second goal is not achievable with your current plan. This is the hard truth, but there is no need to despair. You can see that your money is growing, and if you keep going, your Xbox will be yours just a few months later.
Conclusion
In this article, we have approached financial planning based on your numbers. By adapting this approach to your own finances, you will be able to plan your personal cash flow for the year. This will help you decide if you have room to invest or treat yourself to a vacation.
Once you have the data in your spreadsheet, the possibilities for calculations and projections are endless. From here, you can start asking yourself more and more sophisticated questions about your financial future.