Create a Waterfall Chart in Excel

1. Problem Statement

Waterfall charts are an extremely useful tool in an actuarial context, for example to visualise an analysis of change in insurance reserves over a year. In Excel 2013 and earlier, there is no inbuilt way to create a waterfall chart. This exists in Excel 2016, however the inbuilt graphing options currently lack much of the functionality that is desirable in a waterfall chart.

In this post we show how to achieve the same waterfall functionality that exists in Excel 2016, but using a stacked column chart and Excel’s regular charting functionality. This should allow waterfall charts to be created in all versions of Excel, and also allows for some additional functionality over that offered in Excel 2016’s in-built waterfall chart.

2. Suggested Approach

We show how a waterfall chart can be built as a clustered bar or column chart, which allows for additional functionality in the graphing (such as more flexibility over colouring, detailed analysis of largest changes, more control over end points) with only a little added complexity.

Firstly, some data manipulation is required. The table below shows the data for a typical analysis of change needed to use Excel 2016’s inbuilt waterfall chart, and the additional columns needed to create this as a stacked column chart.

To create the stacked column chart:

• The blank space column is the running total of all the values so far, PLUS where the next value is negative, that value should be added on. This allows us to record negative values as positive values starting at the correct height, otherwise Excel would show them below the axis.
• The col up and col down values are then just the absolute values of the change, split out into those that are positive and those that are negative. We will assign a different colour to the up and down values.
• The final column – col label – is not strictly necessary but useful for creating neat data labels. We can treat these in the same way as the blank space column and assign data labels with values from the amount column.

The two approaches are shown below, with the blank space column outlined to demonstrate how the approach works and then with outlining removed to match the Excel 2016 chart:

3. Rationale and Commentary

The advantages of creating a chart in this way are discussed below with examples.

3.1 More control over colours

The standard Excel Waterfall can only use different colours for ups and downs. Having now created separate columns for ups and downs, we can add further columns at will and change their colours. Here we highlight the large increase in reserves from writing New Business:

As a new series, we can now easily assign a new colour to the one value in this series:

3.2 More control over content of each bar

Having highlighted New Business as a particular area of strain for this company, it’s possible we’ll want to display a chart which breaks this down by product. Again, with Excel’s in-built waterfall functionality this wouldn’t be possible, whereas we can now split it into further columns and use the in-built Key to show this in more detail. As before, we are just creating several new series, each with one value, and assigning these a different colour:

3.3 Adding an end point

As well as the changes applicable to each factor, it is helpful to show the end point – in our case the final reserves – within the waterfall. Whilst Excel can do this, it will default to treating this as another change. It is very easily handled within the column chart:

3.4 Other sensible changes

As well as the above changes to enhance clarity, the best examples of waterfall charts we have seen often include the following:

• Sensible groupings of changes – e.g., in the above example demographic changes are mixed in with market changes. We would recommend grouping by type of change (e.g., using the components of the standard formula SCR as a guide) or even by changes to each product
• Neutral colours – since waterfall charts are often used to show changes in value, Excel defaults to non-neutral colours (blue for up, orange for down). We would recommend using neutral colours, with no connotations of “good” or “bad” when showing an analysis of change in reserves.

4. Applicability and Alternatives

4.1 Intermediate columns

If grouping by type of change, it can be helpful to add an intermediary column to show the total changes of, for example, assumptions changes (things within the control of the modeller) vs other changes. The below chart shows how this can be done:

4.2 Bar Chart

Some users may prefer a bar instead of a column. Whilst not possible using Excel’s inbuilt waterfall chart, this is a simple change having created the column chart, although it is important to note that when simply changing chart type Excel will default to a bottom-to-top approach for the values; we would recommend reversing all the values in the chart when plotting this (see below):

5. Implementation

The case study was produced using Excel and an example workbook is at available from the Working Party. Excel 2016 was used for the creation of both charts, however the column/bar charts will work in any version of Excel.

The implementation requires creating a few new columns of data which are easily calculated from the existing data.

6. Context

A common problem in actuarial science is showing how a value has changed over time, splitting it into component changes to help people visualise the relative importance of each change.