Risk & Issue Register and risk maps

[duffenberg]

To all interested and involved in this area. I am looking for any
ideas on creating a risk map in excel, based on risk values scored in
a risk register. Presentation-wise, I would like to develop a 10 x 10
matrix, with the two axis named consequence/impact and likelihood.
Where a risk is scored within a particular value in the Risk register
worksheet, (or a summary of risks in a worksheet), I would like a link
to that risk to be posted into the appropriate cell of the risk map.
For example, if a risk scores 5 on impact x 9 on likeliood, (score
45), in the risk register worksheet I would like a link to that risk
to appear in the appropriate cell of the risk map, i.e. in the cell
intersect of 5 and 9 of the risk map. The reason I would like for a
link to be posted is to allow the user to navigate to the risk and
view the detail, by jsut clicking on the link within the appropriate
risk map cell.

Ideally, I would like the name of the link to be ranked in accordance
to the other risks in the risk register. For example, if the risk
that scores 45 is ranked 30th out of 80 risks that have been assessed,
then in the risk map I would like the link to be named 30, and when
selected to link the user to the risk register worksheet where further
details can be viewed.

All of the above to update as risks are added to the risk register
worksheet(s).

I appreciate that this complex, but would like to know if it is
feasible.

Regards,

NiallD

 

[Max]

One venture which delivers it ..

Illustrated in this sample construct at:
http://www.savefile.com/files/768019
Plotting risk map w hyperlink to risk register.xls

In a sheet: X (ie the Risk Register Reference)
Col A= Risk#s, Col B = Impact#, Col C = Likelihood#,
with data from row2 to say, row10

In D1:F1 are the labels: Score, Rank, Coord

Put in D2:
=IF(OR(B2="",C2=""),"",B2*C2)

Put in E2:
=IF(ISERROR(RANK(D2,$D$2:$D$10)),"",RANK(D2,$D$2:$D$10))

Put in F2:
=IF(OR(B2="",C2=""),"",VLOOKUP(B2,{1,"B";2,"C";3,"D";4,"E";5,"F";6,"G";7,"H";8,"I";9,"J";10,"K"},2,0)&C2+1)

Select D2:F2, copy down to D10

Then in a sheet: Risk Map,
Impact#s: 1,2, ... 10 are labelled in B1:K1,
while Likelihood#s: 1,2, .. 10 are labelled in A2:A11

Put in B2:
=IF(ISNA(MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0)),"",HYPERLINK("#"&CELL("address",INDIRECT("'X'!A"&MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0))),INDEX(X!$E:$E,MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0))))

Copy B2 across by 10 cols to K2, fill down to K11 to populate the 10x10
matrix. B2:K11 will yield the required results, ie it'll "plot" the rank
numbers from X's col E in the correct cells as hyperlinks. And clicking on
the hyperlinks in the risk map will jump directly to the appropriate line in
X (ie the Risk Register Reference) for more details, as desired.

---

 

[duffenberg]

One venture which delivers it ..

Illustrated in this sample construct at:http://www.savefile.com/files/768019
Plotting risk map w hyperlink to risk register.xls

In a sheet: X (ie the Risk Register Reference)
Col A= Risk#s, Col B = Impact#, Col C = Likelihood#,
with data from row2 to say, row10

In D1:F1 are the labels: Score, Rank, Coord

Put in D2:
=IF(OR(B2="",C2=""),"",B2*C2)

Put in E2:
=IF(ISERROR(RANK(D2,$D$2:$D$10)),"",RANK(D2,$D$2:$D$10))

Put in F2:
=IF(OR(B2="",C2=""),"",VLOOKUP(B2,{1,"B";2,"C";3,"D";4,"E";5,"F";6,"G";7,"H­";8,"I";9,"J";10,"K"},2,0)&C2+1)

Select D2:F2, copy down to D10

Then in a sheet: Risk Map,
Impact#s: 1,2, ... 10 are labelled in B1:K1,
while Likelihood#s: 1,2, .. 10 are labelled in A2:A11

Put in B2:
=IF(ISNA(MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0)),"",HYPERLINK("#"&CELL(­"address",INDIRECT("'X'!A"&MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0))),IND­EX(X!$E:$E,MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0))))

Copy B2 across by 10 cols to K2, fill down to K11 to populate the 10x10
matrix. B2:K11 will yield the required results, ie it'll "plot" the rank
numbers from X's col E in the correct cells as hyperlinks. And clicking on
the hyperlinks in the risk map will jump directly to the appropriate linein
X (ie the Risk Register Reference) for more details, as desired.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik








- Show quoted text -

Max,

Seldom do you get a response like yours that fully understands your
problem and gives you a workable solution.

Many thanks,

Niall

 

[duffenberg]

One venture which delivers it ..

Illustrated in this sample construct at:http://www.savefile.com/files/768019
Plotting risk map w hyperlink to risk register.xls

In a sheet: X (ie the Risk Register Reference)
Col A= Risk#s, Col B = Impact#, Col C = Likelihood#,
with data from row2 to say, row10

In D1:F1 are the labels: Score, Rank, Coord

Put in D2:
=IF(OR(B2="",C2=""),"",B2*C2)

Put in E2:
=IF(ISERROR(RANK(D2,$D$2:$D$10)),"",RANK(D2,$D$2:$D$10))

Put in F2:
=IF(OR(B2="",C2=""),"",VLOOKUP(B2,{1,"B";2,"C";3,"D";4,"E";5,"F";6,"G";7,"H­";8,"I";9,"J";10,"K"},2,0)&C2+1)

Select D2:F2, copy down to D10

Then in a sheet: Risk Map,
Impact#s: 1,2, ... 10 are labelled in B1:K1,
while Likelihood#s: 1,2, .. 10 are labelled in A2:A11

Put in B2:
=IF(ISNA(MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0)),"",HYPERLINK("#"&CELL(­"address",INDIRECT("'X'!A"&MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0))),IND­EX(X!$E:$E,MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0))))

Copy B2 across by 10 cols to K2, fill down to K11 to populate the 10x10
matrix. B2:K11 will yield the required results, ie it'll "plot" the rank
numbers from X's col E in the correct cells as hyperlinks. And clicking on
the hyperlinks in the risk map will jump directly to the appropriate linein
X (ie the Risk Register Reference) for more details, as desired.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik








- Show quoted text -

Max,

One further query. Using the above structure, what if I have two
risks that are scored equally, i.e. they have a joint rank. Can the
risk map show two, (or more), hyperlinks back to the Risk Register?

Regards,

Niall

 

[Max]

Max,
Seldom do you get a response like yours that fully understands
your problem and gives you a workable solution.
Many thanks

Welcome, glad you liked it ..

One further query. Using the above structure, what if I have two
risks that are scored equally, i.e. they have a joint rank. Can the
risk map show two, (or more), hyperlinks back to the Risk Register?

I'm not sure that's possible. One way around it would be to use a tiebreaker
col in the risk register and then point the ranking to it.

In the sample file, in X,

Put in G2:
=IF(D2="","",D2-ROW()/10^10)
Copy down to G10. This is the tiebreaker col.

Then replace the rank formula in E2 with:
=IF(ISERROR(RANK(G2,$G$2:$G$10)),"",RANK(G2,$G$2:$G$10))
Copy down to E10

The above will ensure that all risks in X would be fully reflected in the
risk map, tied scores notwithstanding. In the event of tied scores, the risk
that is "higher up" in X would be ranked above the other risk located "lower
down" in X.

---

 

[duffenberg]

Welcome, glad you liked it ..


I'm not sure that's possible. One way around it would be to use a tiebreaker
col in the risk register and then point the ranking to it.

In the sample file, in X,

Put in G2:
=IF(D2="","",D2-ROW()/10^10)
Copy down to G10. This is the tiebreaker col.

Then replace the rank formula in E2 with:
=IF(ISERROR(RANK(G2,$G$2:$G$10)),"",RANK(G2,$G$2:$G$10))
Copy down to E10

The above will ensure that all risks in X would be fully reflected in the
risk map, tied scores notwithstanding. In the event of tied scores, the risk
that is "higher up" in X would be ranked above the other risk located "lower
down" in X.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---

Max,

This makes sense. I can let management decide which risk the wish to
prioritise as a joint risk by using their noodle. One last query, if
I may - the convention that the management team are used to for
presenting risk maps go in the reverse order to what you have done.
How can I have the highest possible risks, (i.e. Likelihood=10;
Consequence=10), in the top right hand corner of the risk map? This
would have the "Y" axis, (Likelihood) in reverse direction starting at
"1" in Cell A11. The coordinates resolved in the X worksheet would
have to adjust accordingly.

Best regards,

Niall

 

[Max]

How can I have the highest possible risks, (i.e. Likelihood=10;

Consequence=10), in the top right hand corner of the risk map?

In the sample's sheet: X,
Just replace the formula in F2 (Coord col) with:
=IF(OR(B2="",C2=""),"",VLOOKUP(B2,{10,"B";9,"C";8,"D";7,"E";6,"F";5,"G";4,"H";3,"I";2,"J";1,"K"},2,0)&C2+1)
Copy down to F10. That should do it.
Re-label accordingly the "y-axis" (Likelihood) in Risk Map

---

 

[Max]

Apologies, pl dismiss the earlier response for now.
It wasn't correct.

---

 

[Max]

Here's the corrected version ..

How can I have the highest possible risks, (i.e. Likelihood=10;
Consequence=10), in the top right hand corner of the risk map?

In the sample's sheet: X,
Replace the formula in F2 (Coord col) with:
=IF(OR(B2="",C2=""),"",VLOOKUP(B2,{1,"B";2,"C";3,"D";4,"E";5,"F";6,"G";7,"H";8,"I";9,"J";10,"K"},2,0)&12-C2)
Copy down to F10. That should do it. Re-label accordingly the "y-axis"
(Likelihood) in Risk Map

---

 

[Max]

Here's a link to the revised sample:
http://www.savefile.com/files/770124
Plotting risk map w hyperlink to risk register_v2.xls

---