Encoding a Risk Matrix in Project Server

Out of the box Project Server provides the ability to store a project’s risks and issues within an associated project workspace. As these risks and issues are stored in SharePoint lists, there is a tremendous amount of flexibility to customize these lists. As a consultant, a common request I hear again and again is the need to extend the out of the box risks lists to encode some form of risk matrix, such as the one outlined below:

Risk Matrix

Implementing such a matrix is relatively simple thanks to SharePoint calculated columns, but can be fiddly to get correct. In this post I will run through the process of setting up the risk list to encode the matrix above and hopefully provide you with some insights on how it works that you can leverage elsewhere.

Setting up the columns

Before setting up the matrix, it is necessary to set up the three columns required to support the matrix:

· Consequence – used to store the risk consequence

· Likelihood – used to store the risk likelihood

· Residual Risk – used to store the calculated risk from the matrix

Each of these columns will be added to our Risk list in the Project Template site.

Remember: If you intend for this customization to be available in all new project sites moving forward, it is essential you make the changes in a template site not linked to a project and then register that template into Project Server.

Consequence & Likelihood

In the Project Template site Risk list, create two new choice columns called Consequence and Likelihood. In each column set of the choice values to be as follows:

Consequence Choice Value Likelihood Choice Values
(1) Insignificant (1) Rare
(2) Minor (2) Unlikely
(3) Moderate (3) Possible
(4) Major (4) Likely
(5) Extreme (5) Almost Certain

Residual Risk

The last column that needs to be set up is the Residual Risk, unlike the other two columns, this is a calculated column and uses a formula to reference the Consequence and Likelihood columns to determine the Residual Risk. The formula itself consists of only three operators, IF OR & AND and is relatively simple to implement, however as you can see in the matrix above, there are 25 different conditions that need to be encoded, so there is a lot of it Smile

=IF(OR((Likelihood="(1) Rare"),AND(Likelihood="(2) Unlikely",OR(Consequence="(1) Insignificant",Consequence="(2) Minor",Consequence="(3) Moderate")) ,AND(Likelihood="(3) Possible",OR(Consequence="(1) Insignificant",Consequence="(2) Minor")),AND(Likelihood="(4) Likely",Consequence="(1) Insignificant"),AND(Likelihood="(5) Almost Certain",Consequence="(1) Insignificant")),"Low",IF(OR(AND(Likelihood="(2) Unlikely",OR(Consequence="(4) Major",Consequence="(5) Extreme")),AND(Likelihood="(3) Possible",OR(Consequence="(3) Moderate",Consequence="(4) Major",Consequence="(5) Extreme")),AND(Likelihood="(4) Likely",OR(Consequence="(2) Minor",Consequence="(3) Moderate")),AND(Likelihood="(5) Almost Certain",OR(Consequence="(2) Minor",Consequence="(3) Moderate"))),"Medium",IF(OR(AND(Likelihood="(4) Likely",OR(Consequence="(4) Major",Consequence="(5) Extreme")),AND(Likelihood="(5) Almost Certain",Consequence="(4) Major")),"High",IF(AND(Likelihood="(5) Almost Certain",Consequence="(5) Extreme"),"Extreme"))))

Deciphering the above can be a bit daunting, but in essence the formula is broken down into four IF statements that are nested, each used to determine the residual risk condition (Low, Medium, High or Extreme). Within each IF statement the relevant Likelihood and Consequence values are evaluated. In the case of the ‘Low’ residual risk, there are five different evaluations as outlined below:

Matrix Marked Up

Evaluation Formula
1 (Likelihood=”(1) Rare”),
2 AND(Likelihood=”(2) Unlikely”,OR(Consequence=”(1) Insignificant”,Consequence=”(2) Minor”,Consequence=”(3) Moderate”)) ,
3 AND(Likelihood=”(3) Possible”,OR(Consequence=”(1) Insignificant”,Consequence=”(2) Minor”)),
4 AND(Likelihood=”(4) Likely”,Consequence=”(1) Insignificant”),
5 AND(Likelihood=”(5) Almost Certain”,Consequence=”(1) Insignificant”))

If any of the above evaluations are true, then the IF statement will return the text ‘Low’ into the calculated column.

Debugging

With a formula as complex as the above, it’s pretty easy to forget a comma or bracket. Unfortunately SharePoint is a little unforgiving and if you get it wrong and you will see an (un)helpful error message like this:

clip_image006

Unfortunately if you get an error all you can really do is review the formula to try to work out where the problem is.

Once the formula is accepted test each Residual Risk calculation manually by creating a risk and setting the relevant values for the Likelihood and Consequence and saving the risk.

If all goes well you will now have a new column called Residual Risk that is automatically populated depending on the values entered in the Likelihood and Consequence columns as outlined in the Risk Matrix.

List

Hopefully this has given you an overview of how simple it is to extend the risk list to encode a risk matrix. Of course you are not limited to just risks, the technique outlined above can also be applied to the issues list, or any other list within any SharePoint workspace where you want to encode an automated matrix type calculation.

Update: Gord Schmidt has posted a more elegant number based approach to the matrix here.

Advertisements

4 thoughts on “Encoding a Risk Matrix in Project Server

    • Hi,
      It shouldn’t be that much more than above. You effectively need to add a sixth IF statement for the likelihood and additional AND statements for the additional consequence. Failing that, perhaps look at the number based approach from Gord above.

      Alex.

  1. How long have I been looking for something like this??? YEARS! I have come up with similar statements, but this one makes me feel silly for not using the nested if that well!!! Genius, pure genius!

    Thanks so much!

    T

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s