Cash flow forecast is an important component of project finance modeling. Properly done cash flow forecast enables the firm to manage its fund and search for external funding in case of shortage.

As the project progresses, we update the projections for the actuals up to the current time period and need to re-forecast for the balance time period.

Adjusting cash flow forecast for actuals and re-forecasting can be done by the following methods:

1. Using Excel Forecast Formula
2. Using Excel Trend Formula
3. Using S-Curve Method

In this post, we will explore these three methods of cash flow forecast with examples.

To understand the cash flow forecast adjustment and re-forecasting, we will consider a project of fifteen months duration with a capital expenditure of forty thousand USD.

Assume that initially the capital expenditure cash flow was forecasted as follows:

As the project progressed, we kept updating the projected cash flow with the actuals. Now assume, we are in June 2015. The cash flow forecast and actuals look as follows:

Now we need to forecast the cash flow for the balance time periods.

Using Excel Forecast Formula: Forecast formula calculates a future value by using existing values.

Syntax: =FORECAST(x, known_y's, known_x's)

The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression.

Using Excel Trend Formula: Excel Trend Function finds the linear trend by calculating the line of best fit for the equation; y = mx + b

It is a simple equation for straight line, where,

x is the independent variable

y is the dependent variable

m is the slope of the line

b is a constant

Syntax: =TREND(known_y's, [known_x's], [new_x's], [const])

Note that Const is an optional input, a logical value specifying whether to force the constant b to equal to 0. If const is TRUE or omitted, b is calculated normally and if const is FALSE, b is set equal to 0 (zero), and the m-values are adjusted so that y = mx. Also note that this is an array formula.

Note: first, select the range. Next, type the formula and then finish by pressing CTRL + SHIFT + ENTER. The formula bar indicates that this is an array formula by enclosing it in curly braces {}.

Difference between Forecast and Trend Formulas: You will notice that the Forecast and Trend formulas give the same results. However, Forecast computes a single new y value for a single new x value whereas TREND is more suited to data points in a series such as a time series, and is capable of computing multiple y values for multiple new x values. In our case Trend is a better formula to use.

If you notice both of these methods have a problem - the total amount does not match i.e. total value is not equal to original projections. This will be a big problem in case of a lump sum contract.

To overcome this issue we will use S-curve method.

S-Curve Method: S-curve is a graph displaying the cumulative costs plotted against time. The name derives from the S-like shape of the curve, flatter at the beginning and end and steeper in the middle, which is typical of most projects.

First find the cash flow percentage of the original projected cash flow. Then find out how much has been actually spent to date. The balance needs to be reworked on pro rata basis of the original projected cash flow percentages.

You will notice that the total amount remains unchanged and it appears to be a rather robust method for re-forecasting cash flows.

You can download the cash flow forecast adjustment model for FREE.

Hope you enjoyed this post on cash flow forecast adjustment and re-forecasting. Use the comment section below to let me know your thoughts.