Resource 2

Cash flow waterfall

35307105446_26c800e113_b.jpg

To download

To download the Excel file with the embedded PDF, press this button to access the Dropbox folder.


How to a cash flow waterfall

Paper description

This paper is about how to use a cash flow waterfall in Excel financial models.  

Objective

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.

Resource list

The following files are includes:

  • indexation_RESOURCE.docx
  • indexation_MODEL.xlsx

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

Topic discussion

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.

Worked example

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

base          100.00

1                102.50         2.50%

2                104.75         2.20%

3                106.45         1.62%

4                105.78        -0.63%

 

Note:

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

= 2.50%

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:

  1. Copy cell S28 and paste it into cell S29.  Then change the value to that published by the ONS.
  2. Change the value in cell S22 to 5, since you now have 5 months of actuals for that year.
  3. 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.