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

Step 1: Forecasting Total Cash Inflows

Calculate Total Cash In to Forecast Cash Flow in Excel

We will calculate each particular given’s value in the following dataset. There was a 20% understatement in revenues for the projected value in the following dataset.

Enter the following formula to calculate the actual revenue in 2020:

=C7*1.2

Press Enter.

You will get actual revenue value in 2020.

We will use a similar formula for other years. Consequently, we will get the following revenue row, as shown below. In this dataset, the costs of goods sold were overstated by 20% compared to the projected value.

Enter the following formula to calculate the actual costs of goods sold in 2020:

=C8*0.8

Press Enter.

You will get the actual costs of goods value in 2020.

We will use a similar formula for other years. We will get the following costs of goods sold as shown below.

Enter the following formula to calculate the total cash for 2020:

=SUM(C7:C9)

Press Enter.

You will get the total cash in for 2020.

Drag the Fill Handle icon to the right to fill other cells with the formula.

You will get the following total cash in a row.

We will calculate the variance between each particular’s actual and projected values for each year.

Enter the following formula to calculate the variance between each individual’s actual and projected values for 2020:

=D10-C10

Press Enter.

You will get the variance for 2020.

Drag the Fill Handle icon to the right to fill other cells with the formula.

You will get the following total variance row. The positive variance indicates that cash is on hand more than was originally forecast.

Step 2: Forecasting the Total Cash Outflows

We will 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.

Enter the following formula to calculate the actual salaries and benefits in 2020:

=C13*1.1

Press Enter.

You will get actual salaries and benefits in 2020.

Drag the Fill Handle icon to fill other cells with the formula.

You will get the following actual cash-out particulars for 2020.

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.

You will get the following dataset.

We will calculate the total cash out for each year of the projected and its actual value.

Enter the following formula to calculate the total cash out for 2020:

=SUM(C13:C18)

Press Enter.

You will get the total cash out for 2020.

Drag the Fill Handle icon to the right to fill other cells with the formula.

You will get the following total cash out of a row.

Wwe will calculate the variance between the actual and projected values of each particular for each year.

Enter the following formula to calculate the variance between each individual’s actual and projected values for 2020:

=D19-C19

Press Enter.

You will get the variance for 2020.

Drag the Fill Handle icon to the right to fill other cells with the formula.

You will get the following total variances row. As a result of the positive variance, it appears that cash is on hand more than originally forecasted.

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

We will calculate the net change in cash in the following dataset.

Enter the following formula to calculate the net change in cash in 2020:

=C10-C19

Press Enter.

You will get a net change in cash in 2020.

Drag the Fill Handle icon to the right to fill other cells with the formula.

You will get the following net change in the cash row.

We will calculate the variance between the actual and projected values of each particular for each year.

Enter the following formula to calculate the variance between each individual’s actual and projected values for 2020:

=D21-C21

Press Enter.

You will get the variance for 2020.

Drag the Fill Handle icon to the right to fill other cells with the formula.

You will get the following variance row. The positive variance indicates that cash is on hand more than originally forecasted.

Download the 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