Overview of blog

This blog currently has blog posts on:

• Creating a waterfall chart (in Excel)
• Feedback on our Data Visualisation talk (Life Conference, November 2018)
• Visualising mortality improvements
• Visualising daily incidences (eg, claims)
• Displaying a large number of client recommendations
• Display a matrix of correlation assumptions

Upcoming blog posts will include:

• Visualising the results of stochastic modelling
• General insurance triangles
• Expanded hints-and-tips
• Visualising the parameters used by a model
• Feedback from DataViz talks delivered to Knowledge Sharing Scotland (KSS) forum in April
• Visualising higher-order datasets (eg, beyond two dimensions)

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.

Feedback from the Life Conference

Feedback

We talked about Data Visualisation, and our working party's objectives, at the 2018 Life Conference in Liverpool on November 23rd.  The presentation is linked below and was well received with some good audience engagement.

Presentation slides

Data Visualisation as a Powerful Means of Communication

(Download via GoogleDrive)

Feedback from audience

We received the following feedback from the audience Q&A:


Can we have some case studies which show how visualisations can be used to explore the hidden meaning, rather than present a message which the presenter is already aware of?

Can there be guidance on/reference to whether and how to cut down the amount of information which needs to be presented, rather than just presenting what is there (eg, the correlation matrix could have been cut down instead of/in addition to the presentational improvements).

Heatmaps could be used in the correlation matrix instead of the ellipses.

People need to consider those with colour blindness, especially using red and green for heatmaps.

Should the working party also consider wider presentational techniques, eg Powerpoint skills?

Simple rules of thumb would be very useful as a resource on the blog, eg for displaying daily data, use a grid like the one in the example.

We need to help actuaries minimising the time spent considering and creating ways of presenting data – that is the main constraint.  

Does WP have any ideas on how to promote a good data visualisation culture within a company?


So, watch out for developments in these areas soon...

Visualise Mortality Improvements

Click here to download the R-code (R Markdown notebook with text and code; to be opened in RStudio), and here to download the corresponding rendered html document with properly formatted text and all results from the code (includes interactive figures). Note that you will have to download the file (use download button in the top right corner) and open it in a browser (Chrome, Firefox, Edge, Internet Explorer, ...), as the Google Drive preview will show the file as text document. Be aware that your firewall may block the download of html/Rmd files.

For more information on R Markdown, see https://rmarkdown.rstudio.com/lesson-1.html.

1. Problem Statement

The visualisation of mortality rates and mortality improvements can be tricky due to the fairly high number of dimensions of interest (age, time, gender, country, etc.). Moreover, traditional visualisations usually illustrate either mortality rates, or mortality improvements, but rarely both together. We propose some approaches on how the various dimensions can be appropriately reflected in plots of mortality rates/improvements, and how it is possible to combine rates and improvements in a visually appealing way.

2. Suggested Approach

We suggest the use of different types of visualisations to deliver a full picture of mortality rates and developments. Figs. 2 and 4 are interactive – we suggest viewing them in the attached html file. 

• Heatmaps (Fig. 1): These are often used to depict mortality improvement rates, and provide an excellent overview of the age- and time-dependence of improvements. Alternatively, a 3d surface plot can be used (Fig. 2).
• "Trajectory plots” (Figs. 3 & 4): These are very common e.g. in physics, however they are rather unconventional in the actuarial field. The idea is to plot the development of a variable as a function of time in the form of a trajectory with the current value of the variable on the x-axis, and the rate of change on the y-axis (mechanics: space on the x-axis and velocity or momentum on the y-axis).
• Combined line/scatter/bar plots (Fig. 5) for a more conventional illustration. In combination with faceting and a well-designed plot arrangement, a lot of information can be packed into these plots. 

Fig. 1: Heatmap with annual mortality improvement values.

Fig. 2: (Interactive) two-dimensional surface representation of mortality improvements.

Fig. 3: Trajectories with mortality rates and improvements for females of four selected countries.

Fig. 4: (Interactive) trajectories for 10 developed countries (UK, Japan, France, Italy, Span, Sweden, Switzerland, Austria, Norway, USA) and both genders. The grey band covers approx. 80% of the data.

Fig. 5: Mortality rates, improvements, and population exposure for Japan.

3. Rationale and Commentary

Heatmaps

These are powerful visualisations and particularly handy in the context of mortality improvements as a function of time and age (gender and country fixed). Quite often, diagonal structures appear in these heatmaps, which can point to cohort effects or data errors (e.g. exposure miscounts). Both observations (cohorts / data issues) are highly relevant for actuarial applications.

The mortality (improvement) surface in time-age space typically forms the basis for projecting mortality rates into the future. In particular, the heatmaps are used to set the starting (i.e. current) improvement rates for these projections. For example, if improvements in the latest year appear too high or too low (which is often due to boundary effects in the underlying modelling), one may go back one or two years and start the projection at these older improvement rates.

Trajectories 

Visualising mortality rates or mortality improvements alone without combining the two leaves various interesting questions open. For example:

• Country X currently shows much higher improvements than country Y. Is this a “catch-up” effect, i.e. country X still has lower current mortality rates, and there is a lot of room for improvement? Or is it a genuinely different development patterns for two countries with a similar current mortality profile? 
• Can we identify a common trend across all (developed) countries, and infer a long-term improvement rate?

Trajectories help addressing such questions, and moreover give a strong visual impression. For example, it becomes evident how the 1970s and (even more pronounced) the 1990s saw detrimental changes to mortality rates and life expectancy in Russia. In the visualisation, the negative improvements make the trajectories go in the “wrong” x-direction, and one can easily see and understand how many years of positive improvements it takes to recover from such a crisis. Note that in our visualisation, the x-axis is reversed – this makes it more natural to understand the development of a country (movement from left to right, with decreasing mortality rates).

Plotting a multitude of countries (and genders) together may help dealing with the second question, which is whether we should assume a long-term improvement rate, and if yes, what value it should take. The plot above with a multitude of countries and both genders would suggest that there is an overall downward trend in improvements for those countries that are already well-advanced on their improvement journey. Yet, of course we do not know what will drive mortality improvements in the future, i.e. the next 30 years may be entirely different to the past.

Combined line/scatter/bar plots
These more conventional illustrations, when designed well, can convey a lot of information. One nice feature is shown in the third row: the visualisation of a multitude of densities on top of each other (here x- and y-axes are flipped, hence the curves are arranged horizontally). Such plots are sometimes called ridgeline plots.

For the example presented here, arranging mortality rates and improvements vertically helps in linking the two (steep curve in the top plot = high improvements in the bottom plot). In the top row, the combined plotting of raw data as crosses and the fitted values as lines adds a lot of transparency with respect to the fitting process. As a third row, we add the population exposure (how many people are alive in a certain year for a given age), which gives some additional information about the shift in the population structure.

Comments on the data and pre-processing
We use deaths and exposure data from the publicly available Human Mortality Database (https://www.mortality.org/). A key pre-processing step is smoothing – without it, almost nothing of interest would be visible in the plots above. We use a 2-dimensional (Year + Age) spline-smoothing approach, in the form of a general additive model (GAM) with “thin-plate” regression splines. We make use of the easy-to-use implementation of GAMs in the R-package “mgcv” (function mgcv::gam). Note that the results above can be quite sensitive to the assumptions (ages and years used, # degrees of freedom for the splines, …).

4. Applicability and Alternatives

The plots presented above can also be used in generic contexts. 

Heatmaps are commonly used whenever there is a scalar function of interest which depends on two variables (or more, but the rest of them are kept constant). As an alternative (or in addition) to colour coding, the z-coordinate can be represented by contour lines. Classical application areas are bivariate probability distributions, or fitness landscapes in optimisation problems. 

Trajectories are common whenever one studies dynamical systems (where a rate of change is interesting). One can also relax the assumption of a rate of change on the y-axis and use an independent variable as y-coordinate; these visualisations can be used as alternatives to plots with secondary axes (see https://blog.datawrapper.de/dualaxis/, Solution 4).

Scatter plots, line charts or bars form the backbone of data visualisation, and can be used in almost any context. Quite often it is worth thinking about particularly well-suited arrangements and combinations of such plots (vertical/horizontal arrangements, or using insets).

5. Implementation

See the attached R-Markdown file for the full code for this blog post, and the HTML-file for a rendered version including the interactive visualisations.

We recommend the use of R and the package ggplot2. In particular, the use of the following commands:

• geom_raster/geom_tile for heatmaps 
• geom_point, geom_path, geom_line for tractories and line/scatter plots 

We have used the ggplot2 theme from the package cowplot. Also, the arrangement of plots is facilitated by the use of cowplot (command cowplot::plot_grid). The arrangement of multiple densities/histograms in one plot (used for the population exposure above) is facilitated by the package ggridges.

For the interactive visualisations, we have used the package plotly (also available for python, e.g.). Plotly has quite a different syntax to ggplot2, and unfortunately, the two packages do not (yet) work well together.

6. Context

The context is the analysis of past mortality improvement rates, and the assumption setting for future improvement projections. The visualisations have been used in the context of country-specific analyses for pricing, and in more general background studies on the topic.

7. Tags

Trajectories
Trajectories illustrate the evolution of a variable and its rate of change, here mortality rates and annual improvement values.

Heatmap
2-dimensional representations, here used for mortality improvements as a function of age and year

Interactive
Interactive visualisations are used to facilitate the visualisation of a larger amount of data.

Visualise Daily Incidences

1 Problem Statement

A large number of incidences occurring over a long timeframe can make it challenging to spot trends.
The example shown is for daily incidence reporting over a year, but is adaptable to other timeframes.

2 Suggested Approach

We suggest a hybrid of the following techniques:

A	Listing Listing the number of incidences each day, with days arranged in a calendar format. Conditional formatting is used to identify at a glance relatively better/worse days
B.	A calendar is included alongside to enable quick references to specific dates
C.	Daily/weekly averages are shown above/beside the calendar listing
D.	Daily/weekly average charts are shown at the top

3. Rationale and Commentary

4. Applicability and Alternatives

5. Implementation

The case study was produced using Excel and an example workbook is at TODO LINK.

• The data consists of a date and number of incidences assigned to each date
• Sparklines are used to produce daily/weekly averages
• Conditional formatting is used to colour the number of incidences
• More complex conditional formatting is used to create the calendar so that other years or calendars starting on  other days can be quickly shown

6. Context

7. Tags

8. Document Control

Table

Display a Large Number of Client Recommendations

1         Problem Statement

A large number of recommendations (e.g., in a consultancy report) can immediately appear overwhelming. Ranking recommendations by impact and importance, as well as grouping by area to which the recommendation applies (e.g., validation function, finance function), allows for focus on the “quick wins”; recommendations that have maximum impact for minimum effort.

2         Suggested Approach

We suggest a hybrid of the following techniques – see example overleaf:

A.      An overall heatmap plotting numbered recommendations by impact and importance, split into High/Medium/Low sections

B.      A coloured background to ease identification of relative importance, particularly enabling a user to rapidly identify “quick wins” – recommendations that are low effort and high impact

C.       Pre-requisites plotted graphically to identify dependencies between recommendations

D.      Separately, a collection of bar charts show the number of recommendations corresponding to High/Medium/Low impact and effort, and grouped by area to which the recommendation applies, to enable a high level view on where to focus resources

3         Rationale and Commentary

The first chart is self-explanatory, and can be included up-front in a report with limited explanation. Key advantages of presenting information in this way are:

·       From an overwhelming 100 recommendations in a report, there is now clarity on which recommendations to prioritise

·       There is clarity, in this example, for why recommendations 8 and 9 have been included despite being relatively lower impact for higher effort. As pre-requisites they are essential pieces of the puzzle for implementing more valuable recommendations

·       The background shading creates uniformity across recommendations that are equally balanced in terms of impact and effort

The second chart requires more explanation. Here, the same recommendations have been grouped to show the number of recommendations, in each portion of the grid, which is assigned to one of six areas in the business – reserving, pricing, reporting, finance, validation, and project management. This allows a focus by area – in the example shown most project management recommendations are grouped in relatively high impact, low effort portions, suggesting prioritising the implementation of these. 

Several other points can be made, as discussed in the following table:

Criticism	Possible remedy
The heatmap is visually cluttered	This is fairly inevitable for 100 recommendations – in practice fewer recommendations can be shown in this way, with larger numbers being grouped into multiple heatmaps
Ranking of impact and effort is subjective	This is inevitably the case, and it is recognised that graphically showing a subjective ranking gives the appearance of objectivity. However the value is in identifying initial “quick wins” – candidates for early implementation rather than rigorously prescribing an order in which recommendations should be addressed

4         Applicability and Alternatives

The motivating example is based on a consultant’s report detailing recommendations to be made, but the technique applies to any data which can be ranked by 2 variables.

·       A possible extension would be to colour the recommendation numbers on the heatmap, in line with the colours showing recommendations by area.

5         Implementation

The case study was produced using Excel and an example workbook is at available from the Working Party.

For each recommendation, a user is required to enter a value for impact and effort ranging from 1 to 100, and list any pre-requisites.

The heatmap is produced as follows:

·       A 100x100 cell grid is conditionally formatted to provide the background colours

·       VBA is used to create a textbox containing the number of each recommendation, and place it in the appropriate location on the grid

·       VBA then adds arrows representing dependencies

The collected bar charts are basic Excel charts arranged on a worksheet, however VBA is used to ensure all charts have the same scale and are coloured consistently.

6         Context

A common result of a consultancy project is a report detailing recommendations for implementation. Listing such recommendations can quickly become overwhelming, and too much time is required up front digesting all recommendations before choosing which to implement.

These graphs were produced to add to the Executive Summary of a report with 100 recommendations, allowing senior management to absorb the broad context (how much effort is needed overall, what areas require the most focus) without necessarily reading every single recommendation.

7         Tags

Hybrid	The approach is a hybrid of several different elements, attuned to the context
Heatmap	The recommendations are plotted geographically on a heatmap depending on their relative impact and effort
Bar chart	Bar charts are used to show the number of recommendations by area

8         Document Control

Version	Effective Date	Author	Comments
0.1	29/05/2018	Lloyd Richards	First draft
0.2	28/08/2018	Lloyd Richards	Initial Upload to Blog