# Resource 2

**Cash flow waterfall**

**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:

- 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.