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

Get FREE Advanced Excel Exercises with Solutions!

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.

Calculate Total Cash In to Forecast Cash Flow in Excel

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

calculate COGS to Forecast Cash Flow in Excel

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

calculate total cash in

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

get the total cash In

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

determine variances to Forecast Cash Flow in Excel

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

show variances

Read More: How to Calculate Annual Cash Flow in Excel


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.

Calculate Total Cash Out to Forecast Cash Flow in Excel

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

calculate Total Cash Out

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

show total cash out

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

get variances to Forecast Cash Flow in Excel

Read More: How to Calculate Incremental Cash Flow in Excel


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.

Calculate Net Change in Cash to Forecast Cash Flow in Excel

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

get variances

Read More: How to Calculate Discounted Cash Flow in Excel


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.


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo