12 Months

Rolling Cash Flow Forecast

With a rolling cash flow forecast, the number of periods in the forecast remain constant (e.g. 12 months, 18 months, etc). Whenever actual data is available for the most recent month, the forecast is rolled forward. Rolling forecast work best if key cash flow drivers are modeled explicitly and directly drive forecast cash flow outputs. 

Robust rolling forecast models in Excel

1) explicit model inputs based on key drivers

2) transparent model calculations and processing

3) summarized relevant model outputs

Robust and flexible cash flow forecast models typically have the following structure and are based around key driver inputs that can be easily changed and updated. 

Cash flow model inputs

- Key cash flow drivers should be modeled explicitly 


- Inputs should only need to be input once


- Inputs should be organized logically so they are easy to understand and update after that


- All model inputs should be the same color. most financial models use blue font or yellow shading for inputs


- Documents your sources for model inputs were possible 

Cash flow model processing

- Model calculations and processing should be transparent


- Hard-coded calculations should be avoided. all calculations should draw on explicit drivers


- Breakdown complex calculations into steps to make them easier to follow, audit, and update 


- Consider putting complicated calculations and processing on a separate worksheet so that only final figures go onto output worksheet 


- Documents how and why complicated calculations are structured

Cash flow model outputs

- Model outputs should be easy to find and understand


- Model outputs should be grouped logically


- Model outputs should be formula driven (with no hard-coding)


- Outputs should provide key results to aid decision-making


- Key model outputs should be summarized in one location 

Modeling best practices

1) Clarify the business problem

2) Think about inputs, process, and outputs

3) Plan your structure 

4) Put in data validation and integrity formatting controls 

5) Test the model using test/dummy data

Forecasting monthly operating cash flows as the following:

Modeling changes in working capital 

Review of working capital ratio

Forecasting monthly investing cash flows as the following:

Circular references and financing

Turning integration on or off

Conditional formatting

1- conditional formatting enables you to highlight cells with a certain color depending in the cell’s value


2- conditional formatting is accessed from home tab / styles

Steps to use conditional formatting

Data validation

You can use data validation to prevent users from entering data that is not valid. Data validation is accessed from the data tab / data tools / data validation, as the below PrtSc:

Steps to use the data validation

You can uses (circle invalid date) to quickly identify invalid date with red circles. the tools can be accessed from the data tab / data tools / data validation / circle invalid date.

Error checking

The importance of error checking:


1- error checks should be plentiful in order to quickly identify problems with the cash flow model


2- error checks also give other users confidence in the model’s integrity


3- at a minimum consider error checks that ensure the balance sheet balances and the cash flow statement matches the balance sheet

Protecting data 

The default setting in excel is for cells to be locked. although formula / output cells should remain locked input cells need to be unlocked before protecting a worksheet. you can unlock cells by going to the home tab / number and selecting the arrow in the bottom right hand corner. the excel shortcut is CTRL+1.


Once you have unlocked your input cells, you can protect your worksheet by going to review tab / changes / protect sheet.