A well-designed cash flow forecasting template can dramatically improve the quality of reporting outputs. However, this is not a one-size-fits-all situation. For a template to be well designed it must be tailored to align with the business objectives of the cash forecasting process.
As one of the key stages in setting up a cash flow forecasting process, treasury teams often spend considerable time working out how their forecasting template should be designed. To assist, we have written this post which explores how a cashflow forecasting template should be designed.
In this post we will outline: what a cash forecasting template consists of, the key decisions that need to be made when in the design process, as well as providing some useful tips.
What is a cash forecasting template?
A forecasting template (also known as a cash forecasting model) is the reporting structure and associated logic that produces the required forecast output. A forecast template has two dimensions and typically collects two types of cash flow data.
The two dimensions of a forecasting template are:
- Reporting periods split on a daily, weekly or monthly basis to certain points in the future.
- Cashflow classifications which group cash flows typically on a “management reporting” level of detail.
The two types of cash flow data in the template are:
- Actual data. In the graphic above this is displayed in the column furthest left.
- Forecast data. In the graphic above this is displayed in the columns on the right.
What is the right level of granularity for the cash forecasting template?
The level of granularity chosen for both the reporting periods and cashflow classifications will be determined by the overall objectives of the forecasting process.
For example, if the business objective is short term liquidity planning, the template will need will need a reporting granularity of at least daily. If the reporting periods are any broader than this, for example if reporting periods are presented weekly, short-term liquidity shortfalls may be missed.
These are important considerations as reporting granularity that is too fine can muddy the waters and disguise important trends in the data. Conversely, cashflow classifications or reporting periods that are too broad for the objectives might mean that precise but important signals are missed.
In addition, to support decision making in liquidity planning, the forecast will also need to be as accurate as possible. As forecast accuracy generally decreases the further into the future the forecast stretches, the forecast horizon will need to be relatively short. In our experience forecasts used for short term liquidity planning do not usually exceed a horizon of 10 business days.
How to choose the right forecast horizon
To assist those currently making decisions on which is the appropriate time horizon for their objectives, we are in the process of releasing a short series of articles on the subject. The first reviews the practical uses for the 13-week cash flow forecast.
Model structure examples
The table below outlines some examples of illustrative business objectives. It includes which forecast horizon, reporting date granularity, cashflow classifications, and frequency of creation would be appropriate for each.
|Forecast Horizon||Reporting Date Granularity||Cashflow Classifications||Frequency of Creation|
|Short term liquidity planning||10 business days||Daily||High level flows and balances||Twice a week|
|Interest and debt reduction||13 weeks||Weekly||Management reporting categories and flows||Weekly|
|Covenant and key date visibility||To next significant reporting date (at least)||Weekly||Management reporting categories and flows||Weekly, more frequent approaching key date|
|Liquidity risk management||Six months||Weekly for 13 weeks, then monthly for 3 months||High level flows and balances||Monthly|
While the above examples are illustrative, ultimately, the structure of the template chosen should be able produce the range of reporting outputs needed to meet the business objectives.
How much actual data should be included in the cash flow forecasting template?
Capturing actual cash flow and balance data as part of the overall forecast reporting process is also an important consideration.
Similar to the granularity decisions, how much actual data should be included will be decided by the business objectives. Actual cash data allows the forecast model to produce both trend and variance analysis which are very useful pieces of supplementary analysis to the base forecast.
As business objectives help to define the scope of the various different elements of the cash forecasting template, having clearly defined business objectives is the first step in designing a forecasting template.
Setting up a cash forecasting process
This post is an extract from the guide we recently produced which covers all aspects involved in setting up a cash flow forecasting process. Please follow this link to the cashflow forecasting setup guide, which we welcome you to download. The guide discusses: what is involved in setting business objectives, how to set the process up, as well as what comes after go-live.