Cash flow waterfall
To download the Excel file with the embedded PDF, press this button to access the Dropbox folder.
How to a cash flow waterfall
This paper is about how to use a cash flow waterfall in Excel financial models.
We offer pragmatic and practical solutions to model indexation. Firstly, this means that the modeller can properly understand the impact of indexation. Secondly, others can easily understand the workings. Thirdly, the solution is easily updateable as time passes and the actuals unfold. And fourthly, the solution is replicable if multiple indices are required.
The following files are includes:
Topic base information
The properties, behaviour and language of an macro-economic index.
An index has three properties (RPI used as example)
- Value, for example 280.7
- Date, for example May 2018
- Base Date, for example Jan 1987 (index = 100.00 at this date)
An index implies a rate of change between each index, and the rate of change of the index is normally talked of in annual terms, for example, “RPI is around 2.50% annually”, or “annual RPI is running at around 2.0%”
Topic useful websites
The UK Office Of national Statitics published monthly indices at https://www.ons.gov.uk/economy/inflationandpriceindices/timeseries
Macro-economic indexation is required to escalate prices over time from the effect of inflation.
There are various rates of inflation as measured by national governments. Each rate uses a basket of goods and/or services as the basis for the measure. The UK government publishes these indices on a monthly basis to one decimal place. Each index has a base year in which the value of the index is 100.
Sometimes long-lasting public or private contracts can be linked to a particular index or a weighted average of more than one index.
To make indexation practically useful it must have two characteristics:
- be updated with actual outturn values
- include sensible forecast rates into the future
The main issue with indexation is that the rate of change is generally understood as an annual measure.
Often the best way to understand something is to look at a worked example. Here is an example of five years of indexation and the associated rate of change each year.
year index rate
1 102.50 2.50%
2 104.75 2.20%
3 106.45 1.62%
4 105.78 -0.63%
To calculate the rate of change between year x and the year (x-1) the formula is:
= change in index / old index
= ( new index – old index ) / old index
= ( new index / old index ) -1
So, for the rate between the base year and year 1:
= ( 102.50/100.00 ) -1
How to use the Indexation_Module
Inside the Excel file you will find a sheet called Indexation_Module. This is the core calculation sheet and here are some use cases that will explain how it works.
Use case 1: Updating the Indexation_Module with actuals
Each month the ONS publishes the previous month’s index. The next one to update is the August 2018 index in cell S29. To update:
- Copy cell S28 and paste it into cell S29. Then change the value to that published by the ONS.
- Change the value in cell S22 to 5, since you now have 5 months of actuals for that year.
- Make sure you are happy with the RPI rate forecast in row 21.
[You might want to use the yellow sheet called “data_ONS” to aid your updating process.]
If calculations have been set to automatic, then all the formulae will update appropriately.
Use case 2: Updating the Indexation_Module with forecast inputs
To update your latest future RPI rate forecast use row 21.
Use case 3: Use the February RPI index in a calculation
To use the February RPI index in a calculation simply reference row 35 of this sheet. Row 35 contains the February RPI index in the form of actuals to February 2018 and forecast thereafter.
Use case 4: Use February RPI Year-on-Year Actual + Forecast Implied Rate
To use the “February RPI Year-on-Year Actual + Forecast Implied Rate” reference row 40. You will see from the calculation that this time series has values that are calculated rates rather than indices.
Use case 5: Use a weighted Aug + Feb RPI Index
To use a “Aug + Feb RPI Weighted Index” reference row 48. Cells F46 and F47 show the weighting inputs used.
Use case 6: Use an “Average RPI Index”
To use an avegare RPI index reference row 63. This index is an average of the financial year from 1 April to 31 March each year.
Use case 7: Use an “Average RPI Year-on-Year Rate”
To use an “Average RPI Year-on-Year Rate” reference row 64. This rate is an average of the financial year from 1 April to 31 March each year.