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