Risk Management Heat Map Excel Template
In the, I’ve introduced the concept and possible applicability of a risk heat map, when capturing and managing. This article explains how to achieve the two heat maps described in part 1, including the data setup and necessary adjustments in Excel in order to plot all the risks (roughly 100) into an ineligible chart.The idea is that you can reuse the example heat map table, populate it and score your relevant risks and be able to see the result in the heat map chart. Step 1 – Risk Data SetupThe first step is to create a spreadsheet to record the relevant risks. The sheet I use has the following column headings:. Risk ID: unique for each risk. Dept Ref: short reference to distinguish each department. Risk Type: description of the risk type e.g. An applicable generic risk.
Risk Management Heat Map Excel Template Pdf
Business Unit: this may or not be the department name (in this example it is assumed so). Risk Description: self explanatory, this the goal is to record the risk description. Probability: ranges from 10 to 40. See the risk ratings table below. Impact: ranges from 10 to 40. See the risk ratings table below. Risk Score: corresponds to the product of probability rating scores and the impact rating scores.
Concat: used for the charts, it’s simply a concatenation of the “Probability” with “Impact” columns. RiskID: used for the charts, same as “Risk ID” but without the leading “R” i.e. “R1” becomes “1”.
Probability (%): used for the charts, macthes the value in column probability with a corresponding% which is in sheet “Risk Ratings”, using a vlookup functionOnce you are done setting up the necessary columns, make sure you save the file as a macro – File Save As Save as Type “Excel Macro-Enabled Workbook (.xlsm). Step 2 – Understanding Sheet “Risk Ratings”Sheet “Risk Ratings” contains the different scores, descriptions and criterias used for the “Probability” and “Impact” dimensions. Note that in this example sheet, I am using a 4 score rating system (10, 20, 30, 40), which correspond in the risk matrices to “Low, Medium, High, Very High”. Some firms use a 5 score rating system, for example Low, Medium, Medium-High, High, Very High.Step 3 – Fill in sheet “Risk Assessment Data”The next step is to fill in your data. The spreadsheet is pre-filled with dummy example data that you should replace with your own. My advice is that you replace (overwrite) the existing risk data instead of deleting all entries and creating new ones – this is the best option to make sure the heat map displays correctly.
Also note that the dummy risk entries have different impact and probability scores. This will induce some level of risk dispersion in the risk heat map which is useful to understand the example.Step 4 – Understanding sheet “Heatmap Table”The heat map table below displays the same risk data only a more summarised way, yet also allowing a graphical representation of risks in a RAG scale. The heat map table was created following two distinct steps:. Populate the table: using function countif, the table is filled crossing all possible combinations of row versus column (e.g. 10×10) which origin in the “Risk Assessment Data” sheet. Applying colour scales to the heat map: using Excel native function “ Conditional Formatting Color Scales“.
The standard function will apply predetermined colours but you can adapt and use your custom coloursStep 5 – Update Chart Data and LabelsAfter filling in your risk assessment data as explained in step 3, go to sheet “Risk Factor Graph” and click on button “ Update chart data and labels“. If everything is correctly input in sheet “Risk Assessment Data”, your heat map should plot correctly and display your risks in your Red, Amber and Green (RAG) chart.ConclusionEven though Excel includes several pre-made charts, when you have a considerable amount of data (e.g. 100 risks) to plot in a chart, you might face difficulties and issues displaying them. And Part 2 in this article explain how to achieve a simple yet populated risk heat map using Excel.Please comment below, we look forward to get your feedback on this solution and if you were able to apply it to your real life challenges.Download this Example Risk Heat Map. Note: you must enable macros in Excel in order to run this file. Dear Virgina, thank you for your feedback and for flagging indeed an important point. The reason why I intentionally left the chart with a 5 score grade in both probability (or likelihood) and impact is that, fundamentally, some firms will follow a 5 rating approach, while others will use 4 score system. I understand your point of making the high risks looking less risky (even though plotted towards the correspondent “MITIGATE” quadrant).
However, this template was built with the goal of being a basis for each person to adapt for their specific needs. When building it, I found that it would be more cumbersome to include a chart with 4 scale rating approach, and then someone wanting to adapt it to 5 rating system, than the other way around.Thank you again for your feedback and good luck with your Risk Management work.Best regards,Antonio Caldas. Hi Rebecca, thanks for your feedback and sorry for the late reply.
Yes, you can change these labels by doing the following:1 – open the excel template provided in the article2 – in sheet “Risk Factor Graph”, locate cell AT3 to MG3. If you right-click on this range and select option “Format Cells Custom” you will notice that these cells are defined to appear as “R” followed by a numeric. You can change this accordingly as you require3 – also, the numbers which are concatenated to the letter “R” are set from range AT50 to QD50You will be able to change R1, R2 etc by changing cells (including format) in range AT3 to MG3.
However, please note that this chart has been optimised to display small text labels such as R1, R2, etc. If you decide to include larger labels, it might turn your chart unreadable.Best,Antonio.