Skip to main content
Risk Heat Map

How to Create a Risk Heatmap in Excel – Part 1

Risk heat maps are commonly used in operational risk management and are specially useful to represent a firm’s risks in a visual manner, highlighting the ones that need to be managed more closely. When assessing operational risk, the risk manager will typically use a spreadsheet to record its firm’s key risks and rate the impact and likelihood (or probability) assessment scores for each risk.

Although many firms have specific risk management systems which offer this functionality, some firms still use spreadsheets to manage wider risks and display heat maps, which are usually included in management information reports for senior management or other senior executives.

The overall risk score corresponds to the product of the likelihood (or probability) rating scores and the impact rating scores. The simple formula to calculate risk score is:

Risk Score = Likelihood Score x Impact Score

When the list of risks is extensive, most of the times crossing several departments or business areas, there is a big challenge for the risk manager to plot these risks in a heat map, assuring all relevant risks are correctly displayed. The risks will be plotted on a heat map according to its score. The risks in the heat map will range from red, amber and green (RAG), according to their individual score.

The first example below shows how a complete range of risks can be plotted intelligibly into a chart using Excel (the data sheet feeding into this chart has more than 100 risks).

The second example shows a summarised heat map in a form of a table, also using Excel.

Risk Heat Map
 

Risk Heat Map Table
 

In the next article I will explain how to create each of these heat maps, with a special focus on the first example which requires some custom VBA code in order to achieve the aligned risks layout. I will also include the source files for the examples displayed above. Please keep tuned for the next article – now available here.


Antonio Caldas

Program/Project/HR and Risk manager with 15+ years mix-industry, with a particular emphasis in Banking & Financial Services. Active in risk management, market risk control, front office risk management, product control, change and transformation management, business analysis and business process improvement for global capital markets and investment banking, covering a multiple range of asset classes.

5 thoughts on “How to Create a Risk Heatmap in Excel – Part 1

  1. Hi,

    I found this chart very useful. However, in my company we also use Qualitative measures such as client impact, market impact, regulatory impact etc. Please can you advise how to incorporate Qualitative measures in this chart? with an example?

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox GDPR is required

*

I agree