Dynamic plotting of Risks into a Quadrant View

Hi Team,
I have a conundrum. I cannot figure out how to plot risks in a good way on a graph/matrix.

For example, I have 10 risks that are each rated from 1-5 on both likelihood and probability to get a total number as follows:


Likelihood Probability Total

Risk 1 3 4 12
Risk 2 3 4 12
Risk 3 3 4 12
Risk 4 5 4 20
Risk 5 4 4 16
Risk 6 4 4 16
Risk 7 4 5 20
Risk 8 1 4 4
Risk 9 3 4 12
Risk 10 2 4 8

I want to plot these on a graph in a couple of ways and be able to hyperlink from the plotted graph back to the risk itself.

I attach what the risk framework looks like and 2 examples of how I would want the plot views to work.

It would be amazing if someone could help.

Thanks,

Shane.

Hi Shanemoore,

Interesting, nuanced, request.

First, I'll say the calculation where you're multiplying the Impact rating (1-5) by the Probability rating (1-5) is probably not necessary when you're planning on plotting them on the grid you provided. When each of the 5 rows/columns in your grid represent the rating for Impact or Probability, there's really no use for the calculation. For this reason, I have removed it from the workbook I have attached.

Second, the critical piece of your request is the 'click/hyperlink to the risk' functionality, and really throws a wrench in things. In order to set up and actual 'hyperlink' we'd have to take one of two routes;

Option 1: Set up a specific location for every combination of the ratings (i=1, p=2 or i=2, 3....and so on) in different locations. It can be done, and if you REALLY want this we can continue working.
Option 2: Write a considerable amount of vba code and ensure your workbook is macro enabled. This can be done, but honestly I wouldn't recommend it (due to the following below).


This leads me to the obvious way to fulfill this request, but it's not without it's nuances. The 'click/hyperlink' functionality, when you boil it down, is really just the default functionality of a pivot table. If you're willing to accept this piece of it, and the small nuances that come with it, we can get creative to fulfill this request.

Regarding the 'nuances' I speak of, for example:

A pivot table won't show axis values that don't exist. If you have no Risks with probability ratings of 0, 1, or 2, they won't show up in the pivot table. One way around this is to add 'dummy' entries to your table of data with all combinations of those values. This is the avenue I took in the attached workbook, and I think it works out quite well from a 'visually appealing & functionally sufficient' standpoint. Where you see '4' in the table on the 2nd worksheet, just double click to get the list of risks associated with those ratings. (Granted it will pull the 'dummy' rating too, but I think it's pretty easy to understand that's a placeholder and should be disregarded. There are other modifications we could make to further clarify.)


Now you also asked for a possible way to have it displayed as a sort-of scatter plot, if you will. I tried working on this, thinking I could overlay it in front of the pivot table, then push it to the back so when you double clicked it worked with the pivot table...etc. I just couldn't figure out a good way to make the scatter plot work, so I'm stopping efforts and presenting you with this option. Another reporting tool (IE Tableau) would be a great solution to this problem, since the 'click on a chart and have it do something' functionality is not native to excel without macros.


I hope this helps man. I'm new around here, and I know there's some sort of reputation thing, but I'm not sure how it works. If you found this helpful, and know how the rep works, please feel free to give me a thumbs up.

Hi dflak - thanks for the excel file in return. I have added to your reputation as a thank you.

The issue with adding the risk text in the cell as you have done probably wont work as the descriptions of risks in general can be long so would never be stated as "Risk5" or "Risk 6". For example, a risk may be worded as "There is a risk that the engine may overheat due to blah, blah, blah".

The comments about the same result factors is entirely true. The sum of the impact and likelihood items would have to be 'offset' slightly - perhaps by multiplying by a random number so that they would be shown to be in the same box but could be plotted in a different part of the box. So, if the risk was a '12', perhaps multiplying it by (randomly) 1.025 would still be 12.

Any other help is gratefully received. You are very clever.

Hi, thank you so much for this work. It is much appreciated. Cant believe you could turn this around and give me a solution so quickly ! Champ.

thanks dflak ! and greetings from Sydney !

Hi,
thanks for the notes
Ummm, I actually have excel 2016. I have not updated my profile. Sorry.
Per second note, yes, you are correct, any amount of risk is indeed possible but I understand the limitations.
Thank you for this great work.