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.

You may also like:

FREE COURSE: Financial Markets This is a great opportunity for anyone who wants to improve their knowledge in Financial Markets. It's a FREE course created by Yale University and st...
FREE COURSE: Introduction to Programming with MATLAB Today we share another Coursera free course this time focusing on programming with MATLAB. For those unfamiliar, MATLAB is a high-performance language...
10 Essential Risk Management Videos – Part 2 In part 1 of this article I've included a series of relevant Risk Management videos, covering conceptual topics around investing with derivatives secu...
FREE COURSE: Mastering Data Analysis in Excel This is a free course delivered by Coursera, which is an education platform that partners with top universities and organisations worldwide, offering ...
How to Create a Risk Heatmap in Excel – Part 2 In the previous article (part 1), I've introduced the concept and possible applicability of a risk heat map, when capturing and managing operational r...

Antonio Caldas

Program/Project/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.

One thought on “How to Create a Risk Heatmap in Excel – Part 1

Leave a Reply

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