Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Excel Risk Map

Posted on May 28th, 2013 in Automation , Project Management , VBA Macros - 25 comments

This is a guest post by Vijay, our in-house VBA Expert.

Hello Everyone,

We all have some projects to manage every now and then and there are needs of various trackers that help us in gauging the progress of the same. One of the most important things are heat maps that quickly help us in visually displaying the names of the projects that need special attention and resolve issues that are impacting them.

So go ahead and grab a cup of coffee and read this article that would help you in creating a Risk Heat Map in excel (will use some double shot espresso in the form of VBA code) to help us to the target.

Before we begin

First of all we will understand what we are trying to create here by looking at the image below.

risk-map-project-risks-in-excel

You would have seen a picture like this while managing project risks.

So today we will be learn how to create this in Excel to become more awesome in managing projects.

What is important here is how your data for the projects/entities being tracked laid out. We will use the Excel data tables [structured references] to help us here.

risk_map_data_table

There is a Setup sheet in the excel file where we can add the names of the projects that we will use on the data table, as well as the Probability and Impact have been defined as data tables. This helps us in using their contents as drop down options in the data table.

risk_map_setup_sheet

Adding Named Ranges

We need to use the Name Manager to create named ranges to be able to use the data table columns as drop down items, this is show below.

  1. 1.Type this in a blank cell and then copy “=tblProject[Project]”.
  2. 2.Bring up the Name Manager by pressing CTRL + F3, or by going to the Formula’s Tab and clicking on Name Manager.
  3. 3.Click on New
  4. 4.Type the name lstProject in the Name box
  5. 5.Paste “=tblProject[Project]” in the Refers To box and the click on OK.

Repeat this process for “=tblProbability[Probability]” and “tblImpact[Impact]”

Now you can go the actual risk data table and select the Project columns first blank cell and put in Data Validation List here, choose List and put the Source as lstProject. Repeat this for Probability and Impact cells. You will only need to this one time for the first row, new rows when added to the table will automatically contain these settings.

After we have created the above data table, we need to add 3 columns to the right side where we will setup the calculation that will be used to update the risk map.

a) First Column is named as “ProbabilityScore” Since probability has been marked as “A, B, C or D”, we would need to convert this into a number. This is done by using the below formula.
=IFERROR(CHOOSE(MATCH([@Probability],lstProbability,0),4,3,2,1),””)

b) Second Column is named as “SearchString”
=IF([@Status]=”Open”,CONCATENATE(“x”,[@ProbabilityScore]^4+[@Impact]),””)

c) Third column is named as “DisplayName”
=CONCATENATE([@ID],” “,LEFT([@Project],20),IF(LEN([@Project])>20,”…”,””))

Understanding the SearchString Table

When creating the SearchString we are raising the probability score to the power of 4, this is what I have chosen you may select any number that you need. Below is the resulting matrix of numbers that we obtain by doing this.

risk_map_score_table

The last columns in only used for trimming the name of the project to 20 characters of there is a big name, else the actual name of the project is used to display in the Risk Map.

Understanding the Code

So now we are ready to look into the VBA code that helps us in creating the Risk Map.


Public Function showRiskMap(inputRange As Range, searchString As String, dataRange As Range, separator As String)
Dim cntr As Long
Dim tempArray() As Variant
Dim tempDataArray() As Variant
Dim tempString As String

tempArray = inputRange.Value
tempDataArray = dataRange.Value

For cntr = LBound(tempArray) To UBound(tempArray)
If tempArray(cntr, 1) = searchString Then
tempString = tempString & tempDataArray(cntr, 1) & separator
End If
Next
showRiskMap = tempString
End Function

We are sending 4 parameters to this function which are

  1. inputRange – this is the SearchString columns data
  2. SearchString – this is a manual enrty such as “x257”
  3. dataRange – this is the Display Name column from where we will pick the name of the project to display
  4. separator – this is CHAR(10) which is a line break in case we have multiple projects falling in the same category

We are making use of Array’s here to pass the data from the Table column into the array and then a simple For loop to parse them and show us the results.

I hope you will enjoy this article and this assist in managing your projects in a much efficient way.

Download Excel Risk Map File

Click here to download the file & use it to understand this technique.

Do you use Excel for creating Risk Maps?

Do you also user Excel for creating Risk Maps? If yes please put in the comment below how do you use the same and what has been your experience. Leave a comment.

More on VBA & Macros

If you are new to VBA, Excel macros, go thru these links to learn more.

Join our VBA Classes

If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. It is a step-by-step program designed to teach you all concepts of VBA so that you can automate & simplify your work.

Click here to learn more about VBA Classes & join us.

About Vijay

Vijay (many of you know him from VBA Classes), joined chandoo.org full-time this February. He will be writing more often on using VBA, data analysis on our blog. Also, Vijay will be helping us with consulting & training programs. You can email Vijay at sharma.vijay1 @ gmail.com. If you like this post, say thanks to Vijay.

Your email address is safe with us. Our policies

Written by Vijay Sharma
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

25 Responses to “Excel Risk Map”

  1. SirJB7 says:

    Hi, Chandoo, Vijay!
    I think there’s a major mistake or missing data in the design and survey phases. Projects Coffee, Latte, …? No way! There’s only one valid attribute for those: Project Carlsberg.
    Regards!
    If b(ut)ob(ut)hc had read this…

  2. Patrick says:

    Chandoo, As always, great stuff – thanks!

    As a new student to Project Management, I really like the use of Excel for tools, and the creation of this Risk Register is an appealing idea. However, when I glances at the chart, I thought, what does “2 – Project Strong Coffee” mean? What is “3 – Project Strong Coffee” or “8 Project Big” and “9 Project Big”? How are they different?

    One suggestion that I would make here is to use the risk register to portray risks for a SINGLE PROJECT. Specifically, instead of consolidating project risks and listing projects multiple times, the register could be reserved for a specific project, and the specific risk is put in place of the project name (where you have it in the table – this requires removal of the Project Name validation). By making this change, the chart then will display the RISK in the applicable quadrant, so viewers can see at a glance what the risks are, and their respective impact and likelihood. For example: “System Overload” or “Labor Strike”, etc.

    If the project manager must portray multiple projects and their risks in a single chart, I would suggest abbreviating, lettering, or numbering projects, and concatenating project letter with an abbreviation of the risk, such as “A100 Labor Strike” or “A100 Mechanical Failure”, etc., so readers would know that the risks are and to which project they relate.

  3. zurman says:

    GOOD WORK

  4. Jacques says:

    Hi Vijay, Chandoo,

    Once again a very helpful and useful post!

    I work in the area of risk management and frequently make use of risk maps like these. This post is great by the way!

    In addition to the 2 dimensions of impact and probability I sometimes also want to monitor a 3rd dimension, lets call it the Velocity of a risk, i.e. how much/little time do you have to act before it is too late. This can also be measured for simplicity on a scale of 1 to 5 with 1 being “lots of time” and 5 being “very little time”. This is usually displayed by clever risk tools as a bubble of which the size depends on the rating (1-5). So instead of simply displaying a risk name in the riskmap the risk names are also accompanied by bubbles of varying size depicting the Velocity of a risk.

    Can this be done in excel? I believe so, but I think some clever tricks with charts is required. Or can this be done in a cell on the worksheet?

    Has anyone done this before?

    Regards

  5. martin says:

    Thanks Vijay!!

  6. Tim Reeks says:

    Am a tad confused -

    Whilst I like the 4 by 4 grid, since it stops someone fence sitting, I do not understand the Likelihood scale. Suggest swap the top two status, since ‘Almost Certain’ must be less than ‘Certain’ [or 'Is Certain'].

    In fact something that is Certain, should instead be considered an ‘Issue’, this thus suggests that perhaps that ‘Certain’ should be changed to ‘More than Likely’ [or 60% to 80%] and ‘Almost Certain’ changed to ‘Very likely’ as in 80% to 95%];

    I wonder if the very top right hand cell should have a Black background, as in BRAG [Black, Red, Amber, Green] or GRAB [Green, Amber, Red, Black];

    Whilst it may be useful to have the Project Name shown, I am not sure of its value other than to the Programme Manager, since the Project Manager is more than likely to be interested in how many BRAG risks their project has.

    Also I assume that the actual complete version would contain columns in which to enter the risk definition, plus the risk type another source of mappable data?

  7. Mike says:

    interesting

  8. Aaron Pace says:

    This was a great primer to get me thinking about a project I need to accomplish in Excel. I’ve stumbled upon Chandoo.org a few times in the past when looking for Excel/VBA solutions but I’ll be back more often now.

    Thanks Vijay and Chandoo for the great insights.

  9. vinod says:

    Hi Chandoo,

    I have quick question, can we highlight a particular word in an excel cell. for example “Sachin is a Batsman”. i need to highlight only Batsman with a different background. I am not talking about the font color. This is one of our requirement can this be achieved in excel with in a single cell.

  10. Norbert says:

    Hi Vijay!

    Great excel file for risk analysis, but …
    Could you – or other excel expert – create this excel without VBA code?
    This is a good tool, but for example at our company, we do not use VBA code for security reason. (this regulation comes from IT HQ, do not ask me why)

    Best regards,

    N.

  11. Thank You.

    Regards,

    Megha Nagaraja….

  12. Tony McG says:

    Nice maps! Useful code, too!

    Reference a text :”The failure of risk management, and how to fix it” and, by the same author, “How to measure almost anything”.

    The problem with any risk maps which have probabilities of ‘low’, ‘medium’, high’, or consequences which are qualitative, is that they are not calibrated and the same cell can mean different things to different people.

    Always best to have distinct probabilities in percent ranges, and consequences in a common value with a range – say dollars, or incidences, or whatever – but something countable and quantifiable. As indicated in the texts.

    So be careful, all, in developing a tool for risk which may be misleading.

    For your consideration,

    T.

    • Tim Reeks says:

      Tony

      Agree with what you say, however the consequences could be time [which delays the Project or Programme], cost [more resources, equipment, skills, delay in benefits / profits], reputation etc.

      Yes, it might mean different things to different people, but the value of a risk register is it allows the Programme and/ or Project Management team to quantify the risks and then present the risks using a number of maps, images etc.

      What is vital is having the risks recorded initially, then for the organisation to learn what value it can extract.

      • Tony McG says:

        Exactly: “…quantify the risks …”

        Need numbers which can be compared – high, medium and low are too fluffy!

        • Tim Reeks says:

          Tony

          I have used in the past a combination of % for likelihood and numbers to indicate the severity, plus both % and numbers for likelihood, as in 5 = 80%to 95%. Anything that increases the audience’s understanding of what is being presented must be good.

  13. Patrick says:

    Hi Chandoo
    I love this template.
    I have been tinkering changing names and stuff only to find when I saved it and then re-opened it i got the following error – “Error in loading DLL”.
    Any thoughts on what may have caused it and how I can fix it??

  14. Jorge says:

    Hi Vijay!!

    It’s a great post. Thanks so much! As soon I read it, I began to study about Arrays and Function in VBA. I would like to learn more about both this topics!

  15. Mike says:

    This is great but there are a couple of changes I am struggling to incorporate. Firstly the grid needs to be 5 x 5 to support likelyhood values of:

    Almost certain
    Likely
    Possible
    Unlikeley
    Rare

    And Impacts of:

    Insignificant
    Minor
    Moderate
    Major
    Severe

    I have added these to the Setup tab and altered column F in Risk Register Tab to:

    =IFERROR(CHOOSE(MATCH([@Probability],lstProbability,0),5,4,3,2,1),””)

    It’s not clear to me if I need to alter the formula for column G and I have almost no knowledge of VDA to know it that need adjusting at all.

    The other aspect I would like to see changed is to make this project centric, not programme, to highlight different tasks with risks as opposed to whole projects, which would then see make this spreadsheet awesome.

  16. Tim Reeks says:

    Some interesting scale measures. I wonder if it would help if each risk could be in addition typed or flagged as in Finance, Time, Resources, Benefit, Reputation, Objective. I also wonder if useful to provide the option of recording a risk, but being able to set the likelihood and / or impact to say TBC, so that the risk is not forgotten plus there would be something to remind the Project Manager to complete the risk’s assessment.

    Being able to present a Programme perspective would be useful as well.

    • Hui... says:

      @Tim

      It is the concept that is important here.

      You can customise the axis to suit your businesses/industries needs

      I have seen many variations including having the Important numbers at the Top Left or having the important numbers with the lowest score not highest.

  17. WJL says:

    A small suggested correction to the terminology in the Risk Map.
    In the Risk Map there is a label applied to the B Likelihood value, namely, “Certain”. This should be changed to a different label. A risk even that has a “Certainty” has a 100% probability and is no longer a risk but an Issue.

    WJL
    20 years aligning project results to corporate strategy

  18. Mattiu says:

    THANK you, guys… although not a coffee person but you’ve made my day with this great article.

  19. Mattiu says:

    I would like to have to only risk numbers separated with Comma. I can do it with inserting “,” but unfortunately the formula inserts a comma after only one risk number also. Any suggestions?

    =CONCATENATE([@ID],” “,LEFT([@Project],20),IF(LEN([@Project])>20,”…”,””))

    Thank you,
    Mattiu

Leave a Reply