Do More Than Manage
Gord Schmidt's Ideas for Doing More with Microsoft Project Server
Summary Task

Another Approach to Risk Matrix Calculation

I just read Alexander Burton's post on EPMSource.com entitled Encoding a Risk Matrix in Project Server. I saw where he was going with it, but I thought there might be an easier formula to produce the same output.

My thought was to use numbers and multiplication instead of words. By converting the Consequence and Likelihood to scores between 1 and 5 and multiplying the two values together, one gets a table like this:

Risk Matrix

Converting those numbers to a Residual Risk label merely becomes identifying which band the result falls into.


Switch(

     Val(Mid(Likelihood, 2, 1)) * Val(Mid(Consequence, 2, 1)) < 8, "Low",

     Val(Mid(Likelihood, 2, 1)) * Val(Mid(Consequence, 2, 1)) < 16, "Medium",

     Val(Mid(Likelihood, 2, 1)) * Val(Mid(Consequence, 2, 1)) < 21, "High",

     Val(Mid(Likelihood, 2, 1)) * Val(Mid(Consequence, 2, 1)) >= 21, "Extreme"

)

The result is a simpler formula (although I will grant you that the Val and Mid functions and their repetition is still a little ugly).

Posted by Gord Schmidt on Thursday, September 30, 2010 | Permalink | Configure | Extend

Milestone