How to Forecast Cash Flow in Excel (with Detailed Steps)

The cash flow statement shows how the balance sheet and income statement impact cash and cash equivalents. Basically, this refers to how much cash is coming in and going out of a given company. If you are looking for some special tricks to forecast cash flow in Excel, you’ve come to the right place. There is one way to forecast cash flow in Excel. This article will discuss every step of this method to forecast cash flow in Excel. Let’s follow the complete guide to learn all of this.

What Is a Cash Flow Forecast Model?

In addition to tracking operational performance in actual time, companies can compare anticipated cash flows with actual cash flows using the yearly cash flow forecast model. Despite the fact that yearly prediction models aim to predict the future, a variance analysis that demonstrates how accurate (or inaccurate) management estimates were can provide valuable information.

In the following section, we will use one effective and tricky method to forecast cash flow in Excel. To create a more understandable template, first of all, calculate total cash in, then determine total cash out and finally evaluate the net change in cash. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

Step 1: Forecasting Total Cash Inflows

Here we will demonstrate how to calculate total cash in. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. In this dataset, we will list the financials for each year in two columns: projected and actual. There was a 20% understatement of revenues and a 20% overstatement of costs of goods sold for the projected value in this dataset. Here, we will use the SUM function to calculate the total cash in.

Here, we can see the projected value of each particular given in the following dataset.
We are going to calculate the actual value of each particular given in the following dataset.
There was a 20% understatement in revenues for the projected value in the following dataset.
Therefore, to calculate the actual revenue in 2020, we will use the following formula.

=C7*1.2

Then, press Enter.
Therefore, you will get actual revenue value in 2020.

Next, we will use a similar formula for other years. Consequently, we will get the following revenue row as shown below.
There was a 20% overstatement of costs of goods sold for the projected value in this dataset.
Therefore, to calculate the actual costs of goods sold in 2020, we will use the following formula.

=C8*0.8

Then, press Enter.
Therefore, you will get the actual costs of goods value in 2020.

Next, we will use a similar formula for other years. Consequently, we will get the following costs of goods sold as shown below.

Now, we are going to calculate the total cash in for each year of the projected and its actual value.
Next, to calculate the total cash in for 2020, we will use the following formula.

=SUM(C7:C9)

Then, press Enter.
Therefore, you will get the total cash in for 2020.

Next, drag the Fill Handle icon to the right to fill other cells with the formula.
Consequently, you will get the following total cash in a row.

Now, we are going to calculate the variance between the actual and projected values of each particular for each year.
Next, to calculate the variance between the actual and projected values of each particular for 2020, we will use the following formula.

=D10-C10

Then, press Enter.
Therefore, you will get the variance for 2020.

Next, drag the Fill Handle icon to the right to fill other cells with the formula.
Consequently, you will get the following total variances row. As a result of the positive variance, it appears that cash is on hand in a larger amount than was originally forecast.

Step 2: Forecasting the Total Cash Outflows

Here we will demonstrate how to calculate total cash out. In this dataset, there was a 10% understatement of all the particulars of the projected value in this dataset. Here, we will use the SUM function to calculate the total cash out.

We are going to calculate the actual value of each particular given in the following dataset.
There was a 10% understatement of all the particulars of the projected value in this dataset.
Therefore, to calculate the actual salaries and benefits in 2020, we will use the following formula.

=C13*1.1

Then, press Enter.
Therefore, you will get actual salaries and benefits in 2020.

Next, drag the Fill Handle icon to fill other cells with the formula.
Consequently, you will get the following actual cash-out particulars for 2020.

Next, you have to follow the same formula to find out the out year’s actual cash out particulars because there was a 10% understatement of all the particulars of the projected value in this dataset.
Therefore, you will get the following dataset.

Now, we are going to calculate the total cash out for each year of the projected and its actual value.
Next, to calculate the total cash out for 2020, we will use the following formula.

=SUM(C13:C18)

Then, press Enter.
Therefore, you will get the total cash out for 2020.

Next, drag the Fill Handle icon to the right to fill other cells with the formula.
Consequently, you will get the following total cash out of a row.

Now, we are going to calculate the variance between the actual and projected values of each particular for each year.
Next, to calculate the variance between the actual and projected values of each particular for 2020, we will use the following formula.

=D19-C19

Then, press Enter.
Therefore, you will get the variance for 2020.

Next, drag the Fill Handle icon to the right to fill other cells with the formula.
Consequently, you will get the following total variances row. As a result of the positive variance, it appears that cash is on hand in a larger amount than was originally forecast.

Step 3: Obtaining the Net Change in Cash to Forecast Cash Flow in Excel

Here we will demonstrate how to calculate the net change in cash.

We are going to calculate the net change in cash in the following dataset.
Therefore, to calculate the net change in cash in 2020, we will use the following formula.

=C10-C19

Then, press Enter.
Therefore, you will get a net change in cash in 2020.

Next, drag the Fill Handle icon to the right to fill other cells with the formula.
Consequently, you will get the following net change in the cash row.
Now, we are going to calculate the variance between the actual and projected values of each particular for each year.
Next, to calculate the variance between the actual and projected values of each particular for 2020, we will use the following formula.

=D21-C21

Then, press Enter.
Therefore, you will get the variance for 2020.

Next, drag the Fill Handle icon to the right to fill other cells with the formula.
Consequently, you will get the following variances row. As a result of the positive variance, it appears that cash is on hand in a larger amount than was originally forecast.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.

Forecast Cash Flow.xlsx

Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to forecast cash flow in Excel. If you have any queries or recommendations, please share them in the comments section below.

<< Go Back to Excel Cash Flow Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!