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

Display a Matrix of Correlation Assumptions

1. Problem Statement

Correlation matrices are often large, complex and visually off-putting. The objectives of the visualisation are to:

• Present a correlation matrix in a way which is straightforward to engage with
• Make it easy to locate the material assumptions
• Make it easy to identify possible inconsistencies between correlation assumptions

2. Suggested Approach

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

1. Bar charts to illustrate the materiality of individual risks, measured by undiversified capital requirements. Colour is used to collate risks into categories.
2. Shading of alternate rows and columns to lead the eye to the row and column headings, and borders around correlations within each category that align to the bar charts.
3. A table of values to show the correlation assumptions – this can be triangular because the matrix is symmetric, and the values of 1.0 on the diagonal are omitted. The typography is designed to emphasise visual differences between zero, positive and negative values.
4. Ellipses to visualise the sign and magnitude of each correlation, in the space created by restricting the numerical assumptions to a triangle. These help with seeing patterns.

3. Rationale and Commentary

The illustrative matrix in section 2 parameterises an economic capital model for a hypothetical mid-size insurance group writing life and non-life business. (There is nothing specific to insurance groups here, and other kinds of financial institution could make use of the same ideas.) There are 25 risk factors, categorised into market, credit, life, non-life and operational risk, leading to 300 separate correlation assumptions once symmetry of the matrix is taken into account.
Other than the ellipses, this is a hybrid of techniques that are likely to be familiar, so we start by discussing the ellipses. The convention is that:

• Zero correlations are white circles (appropriately enough).
• Positive correlations are non-circular ellipses slanting diagonally upwards and are red. Larger correlations are darker coloured and more cigar-shaped, converging to a diagonal line for correlations of +1.0.
• Negative correlations are like positive but blue, point downwards, and become darker coloured and more cigar-shaped towards -1.0.
• Use of red and blue (rather than red and green) is intentional, for the benefit of those with red-green colour blindness

Admittedly this needs explanation when seen for the first time, but the key principles are easily grasped. The benefit of the ellipses is that it is easier to see patterns and relationships within the correlations, for example:

• There are lots of zero correlations (which are significant in their own right).
• The non-zero correlations seem to have been set primarily within each category, with comparatively few non-zero correlations between risks in different categories.
• Non-life risk is moderately correlated in itself, but life risk isn’t.
• Risks within the operational risk category are fairly heavily correlated with each other (lots of bold), but operational risk isn’t financially significant.
• There is ‘visual continuity’ in the ellipses, with values that are numerically similar being visually similar.

We arrived at the other elements of the presentation by critiquing the very basic approach of just putting all the correlations into a table, as below:



The immediate criticism is that this is visually off-putting. Several other points can be made, as discussed in the following table:

Criticism	Possible remedy
There is no indication of the financial significance of the risks.	Show pre-diversification capital requirements beside row/column headings.
All values are formatted to 2 decimal places, which makes them all look very similar to each other.	Show zeros without decimal places and all other values to 2 decimal places, to make the zeros and non-zero values stand out from each other.
It’s hard to see which values are negative, because minus signs don’t use much ink/many pixels, and so don’t stand out.	Use () rather than minus signs to present negative values.
The numerically large correlations (0.5/0.75 and negatives) don’t really stand out.	Use bold formatting to highlight any correlations with absolute values ≥ 0.5.
The symmetry of the matrix leads to repetition, in that the upper-right triangle has the same values as the lower-left triangle.	Remove the upper-right triangle of values and use the space created to represent the correlations as ellipses.
The values on the leading diagonal are always going to be 1.0, and don’t carry any information as such, but look important.	Remove them.
If we look at a correlation towards the middle of the matrix, it’s hard to look up the row and column headings.	Shade alternate rows and columns.
It isn’t particularly easy to identify which correlations are within a category, and which are between categories	Use coloured borders to identify blocks of correlations within a category.

Addressing each criticism leads to the approach in section 2.

4. Applicability and Alternatives

The motivating example is a matrix of assumptions, but the ellipse technique can also be applied to empirical data, and to the correlations between simulated results for risk factors, or the financial impact of risk factors (e.g. P&L or changes in own funds).

There is no requirement to show ellipses in the top-right triangle:

• Some users may prefer to see numbers (despite the repetition).
• Others may prefer to dispense with ellipses and show the correlations as a heatmap:

• Or it may be preferable to see an indication of the financial significance of each correlation, for example a (different) heat-map showing the ranked impact on overall economic capital of changing each assumption by 1%.
• It is also common to set key correlations using detailed analysis and use completion rules (also called imputation rules) to derive the remaining correlations. The top-right triangle could be used to visualise the distinction between analytical and imputed correlations, possibly with an overlay to indicate the degree of statistical confidence in analytical correlations. For example, the cells for imputed correlations could be left blank, with analytical correlations shown using circles coloured to indicate confidence.

The method is restricted to matrices that fit on a page. For larger matrices (large insurance groups may have hundreds of risk factors and tens of thousands of correlations), we suggest a combination of the following techniques:

• Present sub-matrices, for example by legal entity, geography or risk category
• Filter out or coalesce financially insignificant assumptions

5. Implementation

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

The bars in each row are produced using Excel conditional formatting data bars, and the bars in each column use Excel sparklines. At the time of writing, Excel does not have a built-in feature to produce the ellipses, but it’s reasonably straightforward to produce them using VBA.

The typography was implemented using the custom numeric format "0.00;(0.00);0" (without the surrounding quotation marks).

The bold formatting and row/column shading uses conditional formatting.

The R packages ellipse and corrplot produce correlation matrix ellipses. Corrplot implements several other approaches to correlation matrix visualisation, and there is a walkthrough of using it here.

6. PDF

Download as a PDF here.