Handling A Risk Rating Matrix In SharePoint Lists

I had to implement a Risk Rating matrix in a SharePoint custom list being used as a Risk Register. The matrix was somewhat askew as shown below:

Likeli- hood	Certain	Low	Medium	High	Extreme	Extreme
	Likely	Low	Medium	Medium	High	Extreme
	Possible	Low	Low	Medium	High	High
	Unlikely	Low	Low	Low	Medium	High
	Rare	Low	Low	Low	Medium	Medium
		Insignificant	Minor	Moderate	Major	Severe
Rating		Consequence

In SharePoint 2007 this was a challenge but in SharePoint 2010 there was a significantly easier albeit not entirely straight forward way to do this.

The trick here is the askew nature of the matrix and the larger number of combinations. If it was more symetrical then a trivial mathematical formula could have handled this. If there were fewer ratings of likelhood and consequence then a simple logical expression could have done.

SharePoint 2010 Example

So in SharePoint 2010 I created a custom list called Risk Rating that had:

• A choice column called [Likelhood] with the 5 choices "Certain" to "Rare"
• A choice column called [Consequence] with the 5 choices of "Severe" to "Insignificant"
• A calculated column called [Likelihood - Consequence] with a formula = [Likelihood] & " - " & [Consequence]
• A choice column called [Risk Rating] with the 4 choices "Extereme" to "Low"
• A calculated column called [Rating] with the formula = [Risk Rating]
• The [Title] column is changed to optional and hidden 

I then add items to Risk Rating to cover the 25 possible combinations of Likelihood and Consequence.

Now in my Risks list, I add a column called [Initial Likelihood - Consequence] which is a lookup column that "links" to the [Likelihood - Consequence] column of the Risk Rating list, and I pull the [Rating] column across as an additional column.

This is an approach that you can use to "link" SharePoint 2010 lists via a composite key. It is not as pretty as I would like but it works.

Note that the resulting columns are called:

• [Initial Likelihood - Consequence]
• [Initial Likelihood - Consequence:Rating]

I used this name to make the example clearer, but a column name called "Initial Profile" would result in more readable linked column names.

You can also use this appraoch to have multiple ratings for each risk. For example you may also have a mitigated, reviewed or current risk rating. This simply means having a different column that has the same lookup properties as the first.

Note that the choice columns in the Risk Rating list can not be linked to with a lookup column, nor brought over as additional columns, even though the hidden Title column can be. This is why I had an additional calculated column for Rating, though instead of the convenience of a drop down, I could have just had a single text column for Rating that would have done the trick.

SharePoint 2007 Example

For those of you still on 2007, this approach could be used.

First I added two hidden calculated columns to the Risks list:

Likelihood Rating:
=IF(ISBLANK([Likelihood]),-1,CHOOSE(((FIND([Likelihood],"Rare....,Unlikely,Possible,Likely..,Certain.")-1)/8)+1,3,4,5,7,11))

Consequence Rating:
=IF(ISBLANK([Consequence]),-1,CHOOSE(((FIND([Consequence],"Insignificant,Minor........,Moderate.....,Major........,Severe.......")-1)/13)+1,0,3,4,8,11))

And then I added the final resulting risk as another calculated column:

Risk Rating:
=IF(OR([Likelihood Rating]=-1,[Consequence Rating]=-1),"",CHOOSE(MIN(4,(([Likelihood Rating]*[Consequence Rating])/19)+1),"Low","Medium","High","Extreme"))

Again, not pretty but it works. The real challenge was coming up with the weightings and divisor.

This approach should work equally well in 2010, but the previous option provides easier control.