Calculated Column Formula: Probability and impact analysis for risks #SharePoint #SP2010 #ProjectServer #PS2010 #EPM #MSProject #in
Many project managers like to do risk analysis in terms of probability and impact, ultimately creating a heat map when data is rolled up at the project or programme level.
Further information about the methodology can be found here: http://www.expertprogrammanagement.com/2010/06/project-risk-management/
To aid in the creation of these roll up dashboards, we need to evaluate each risk as it is entered (and updated) and assess the probability of the risk occurring against the impact it has against the project (normally in terms of Cost, Resource or Time).
The following chart details this analysis:
NB: Numeric values have been assigned for the formulas listed below
To implement this in SharePoint (either standalone or as an extension to the standard Risks list in Project Server) we need to translate what the Project Manager / Team Members would like to state in words regarding probability & impact into a numeric value for sorting and analysis.
The following Choice columns are added to the Risk form.
User Interface Columns:
Column | Type | Values |
Probability of Risk | Choice | Very Low, Low, Medium, High, Very High |
Impact of Risk | Choice | Very Low, Low, Medium, High, Very High |
Once we have the UI version of Probability and Impact, we need to use some hidden / calculated columns to convert these values in to numbers.
Once we have these values we then multiply them together:
Probability x Impact = Expected Outcome Value
Hidden Columns:
Column | Type | Values |
Risk Probability Value | Calculated Column | =IF([Probability of Risk]=”Very Low”,0.1,IF([Probability of Risk]=”Low”,0.3,IF([Probability of Risk]=”Medium”,0.5,IF([Probability of Risk]=”High”,0.7,IF([Probability of Risk]=”Very High”,0.9,0))))) |
Risk Impact Value | Calculated Column | =IF([Impact of Risk]=”Very Low”,0.05,IF([Impact of Risk]=”Low”,0.1,IF([Impact of Risk]=”Medium”,0.2,IF([Impact of Risk]=”High”,0.4,IF([Impact of Risk]=”Very High”,0.8,0))))) |
Expected Value Result | Calculated Column | =[Risk Probability Value]*[Risk Impact Value] |
Now that we have our Expected Outcome as a number, we can convert this back to a value the end user will understand, report and dashboard from.
RAG Outcome:
Column | Type | Values |
RAG | Calculated Column | =IF([Expected Value Result]<0.05,”Green”,IF([Expected Value Result]>0.14,”Red”,”Amber”)) |
For each risk in the list we will now have a RAG value.
Further customisations I end up doing in clients include adding a RAG indicator column showing a graphical representation of the risk using jQuery.
RAG Indicator:
Column | Type | Values |
RAG Indicator | Calculated Column | =”<div class=’convertToHTML’><img src=’/pwa/customisation/images/RAG” & [RAG] & “Sml.gif’ alt=”‘ & [RAG] & ‘” title=”‘ & [RAG] & ‘”></div>” |
The above formula expects three images:
- RAGGreenSml.gif
- RAGAmberSml.gif
- RAGRedSml.gif
Images in the formula are stored in a document library called:
- Customisation
with a folder inside called:
- images
Run the following jQuery function on the page with the list view on to convert the RAG Indicator calculated column string into rendered html:
Nice work, I liked this idea. And its pretty much an Out of the box solution. 🙂
Very useful.
Also worth noting that it is possible to update the out-of-box Impact, Probability and Exposure fields with the calculated values so that they are available for OLAP analysis. This is possible by creating a SharePoint workflow in SharePoint Designer to set the values on save or change – all good!