We'll call you and arrange a demo




Blog

Cash flow forecasting template

Cash flow forecasting template
Posted by John Champion August 30, 2018

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 outlines how a cashflow forecasting template should be designed. In this post we will outline: what a cash forecasting template consists of, 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.

Cash forecasting model templates

The two dimensions of a forecasting template are:

  1. Reporting periods split on a daily, weekly or monthly basis to certain points in the future.
  2. 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:

  1. Actual data. In the graphic above this is displayed in the column furthest left.
  2. 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.

Business Objective

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.