![]() The standard function will apply predetermined colours but you can adapt and use your custom coloursĪfter 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.Įven though Excel includes several pre-made charts, when you have a considerable amount of data (e.g. Applying colour scales to the heat map: using Excel native function “ Conditional Formatting > Color Scales“.10×10) which origin in the “Risk Assessment Data” sheet Populate the table: using function countif(), the table is filled crossing all possible combinations of row versus column (e.g.The heat map table was created following two distinct steps: 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. Step 4 – Understanding sheet “Heatmap Table” This will induce some level of risk dispersion in the risk heat map which is useful to understand the example. Also note that the dummy risk entries have different impact and probability scores. The next step is to fill in your risk assessment 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. Step 3 – Fill in sheet “Risk Assessment Data” Some firms use a 5 score rating system, for example Low, Medium, Medium-High, High, Very High. 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”. Sheet “Risk Ratings” contains the different scores, descriptions and criterias used for the “Probability” and “Impact” dimensions. Step 2 – Understanding Sheet “Risk Ratings” Once 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). Probability (%): used for the charts, macthes the value in column probability with a corresponding % which is in sheet “Risk Ratings”, using a vlookup function.RiskID: used for the charts, same as “Risk ID” but without the leading “R” i.e.Concat: used for the charts, it’s simply a concatenation of the “Probability” with “Impact” columns.Risk Score: corresponds to the product of probability rating scores and the impact rating scores.Risk Description: self explanatory, this the goal is to record the risk description.Business Unit: this may or not be the department name (in this example it is assumed so). ![]() Risk Type: description of the risk type e.g.Dept Ref: short reference to distinguish each department.The first step is to create a spreadsheet to record the relevant risks. The sheet I use has the following column headings: 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. In the previous article (part 1), I’ve introduced the concept and possible applicability of a risk heat map, when capturing and managing operational risk. 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. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |