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.

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.

What Is 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.

Step-by-Step Procedure to Forecast Cash Flow in Excel

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: Calculate Total Cash In

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.

• 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.

• 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.

• 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.

• 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.

Read More: How to Calculate Operating Cash Flow in Excel (2 Easy Ways)

Similar Readings

• How to Calculate Payback Period in Excel (With Easy Steps)
• Cash Flow Statement Format in Excel for Construction Company
• How to Calculate IRR in Excel for Monthly Cash Flow (4 Ways)
• Create Monthly Cash Flow Statement Format in Excel
• How to Calculate Future Value of Uneven Cash Flows in Excel

Step 2: Calculate Total Cash Out

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.

• 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.

• 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.

• 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.

Read More: How to Create Cash Flow Projection Format in Excel

Step 3: Calculate Net Change in Cash

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.

• Then, press Enter.
• Therefore, you will get the 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.

• 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.

Read More: How to Calculate Net Cash Flow in Excel (3 Suitable Examples)

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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!

Related Articles

• Create Weekly Cash Flow Statement Format in Excel
• How to Calculate Payback Period with Uneven Cash Flows
• Calculate Incremental Cash Flow in Excel (2 Examples)
• How to Calculate Present Value of Future Cash Flows in Excel
• Prepare Daily Cash Flow Statement Format in Excel
• How to Apply Discounted Cash Flow Formula in Excel
• Create Cash Flow Statement Format Using Direct Method in Excel