There are numerous (online) tools available for categorizing, summarizing and analyzing risks in organizations. In this article I will illustrate the power of Excel regarding plotting of risk in one comprehensive Heatmap. This map is one control mechanism which can be used for monitoring key risks. These risks will eventually be reported to senior/ executive leadership or higher levels.
I was triggered to design this tool when I came across an article about the COSO-ERM Risk Assessment in Practice. The risk heatmap presented in that article is as follows:
The heatmap is build out of the following key elements:
- Risk ID’s;
- Impact scale;
- Vulnerability (Dot colour);
- Speed of onset.
Impact scale, likelihood, vulnerability and speed of onset should be defined by the risk team of a company before you start with the Excel sheet. The assessment of risks follows, which can be done in a qualitative and/ or quantitative way. Techniques like interviews, workshops, surveys, benchmarking and scenario analyses are qualitative assessments. Quantitative methods are benchmarking, scenario analyses etc.
After the assessment, the risk heatmap can be filled in (Excel). You can find the Excel file here in my dropbox account.
The Excel sheet consists of three worksheets: the heatmap, option menu and a hidden sheet. You only have to change the hidden sheet if you want to add more than 62 Risks (which I don’t recommend because the graph will become unreadable).
The hidden sheet is basically the input for the heatmap. The following formula is used in cells A3 to O6.
Breaking down the formula:
Als/ If is used to perform a logical-test. The outcome of the test can be true or false. In this case Excel tests if heatmap G8 (vulnerability) equals A1, A2, A3…(Int is used for rounding values) If the outcome is true, then Excel determines via Index and Match the values for L (likelihood), S (speed of onset) and I (impact). In case of Risk ID 1, vulnerability is 1. Excel searches for L, S and I in heatmap. For 2, 3,4 and 5 the outcome is #N/B (#N/A). In that case, the logical test is false. The advantage is of N/B is that only 4,8, 4 and 4 will be presented in the graph. N/B values are ignored.
The option worksheet is currently used for the bubble size. You can control the standard bubble size by making it higher or lower than 40. A higher value will result in smaller bubbles. You will have to make use of a higher value if you defined a lot of risks to make the graph better readable.
The information above is used to make the bubble graph. You can use “Select Data Source” in Excel in order to find out which values are used for the Y-axis, X-axis, size and names.
Excel result of the Coso heatmap:
If you have any recommendations or questions, please don’t hesitate to contact me.