Tracking Qualitative Risk in Microsoft Project

An improved version of this article is included in my free e-book, MS Project Hacks. Download the book and a sample MS Project file demonstrating the techniques shown here.

I use Microsoft Project to schedule the tasks in my work breakdown structure. Then I flesh out the task list with things like external events, define milestones and summary roll-ups, identify dependencies, and assign resources. During execution, I use it to track progress on completion of tasks, in support of everything from communicating and managing team activities to status reporting and change management. I use several different views, flag fields, and filters to highlight the information I want to extract, and then print to a PDF file for distribution. It’s not perfect, but it’s effective.

One of the challenges in project management is preventing the gradual disconnect between the project schedule and risk management. When we’re in execution mode, we tend to focus on the list of tasks and the dates in the plan, and only occasionally on the results of that qualitative risk analysis we prepared in the planning stage. In order to see the gradual reduction in total project risk, I wanted a way to incorporate risk attributes into tasks on the schedule. I also wanted to factor in the work we’ve already accomplished, in order to see how much residual risk remains when a risk has been retired. I implemented this in Microsoft Project, using calculated fields.

Qualitative Risk DemoIn the simple example above, we have decided to track execution risks on three project tasks. Our qualitative analysis classified the probability of occurrence and the impact as Low, Medium, or High, scored 1, 2 and 3 respectively. In all three cases, the risks will be retired gradually, as the tasks are completed. We want to monitor this process of risk reduction graphically, using the formula Probability * Impact * Work Remaining to Retire the Risk, defined as 1 – (% Complete / 100). In this example, a score greater than 3.0 is considered high risk (red); as the score falls below the threshold, the visible risk indicator changes to yellow, and when the task is completed and the score drops to zero, the indicator changes to green.

Custom Fields - ProbabilityI implemented the Probability and Impact fields using Lookup values in number fields, and implemented the Risk field using a formula, with appropriate graphical indicators. At a glance, you can see that task 4, Data Conversion Mapping, was determined to have a performance risk with high probability and low impact; now that the task is 100% complete, it has been retired. Task 7, Code and Unit Test, was determined to be medium probability, medium impact. At the current 20% Complete, it shows as a high risk [2*2*(1-0.2)=3.2], but once it gets to 25% complete, it will drop to a medium risk [2*2*(1-0.25)=3.0]. Task 14 reflects low probability, high impact, and medium risk. As each task is worked, the associated risk is reduced in linear fashion. If you wanted to use a quadratic calculation, you could adjust the formula accordingly. To adjust the High risk threshold, change the value associated with the graphical indicator.

Edit Lookup Table - ProbabilityThe Lookup function was used to define four values: None, Low, Medium, and High.  I associated the None value with a blank graphical indicator, and the other values with appropriate graphics.  As a result, only rows with actual Probability and Impact values have indicators.  The Risk value is implemented using a Formula. To see how I’ve previously used a formula to implement a Status field calculation, click here. To download the MS Project 2007 file that was used to prepare this sample, click here.[wpdm_file id=4]

I’ve previously described the four common risk response strategies: avoid, transfer, mitigate, and accept. In this example, the risks were accepted and they will be retired through execution of the associated task. When a risk is avoided, there should be no need to track it as a task on the project schedule. Depending on the situation, you might want to track performance and retirement of a transferred risk as a task, or you might not. In a mitigation strategy, there might be one or more tasks to track separately from the execution tasks. Consider how you want to tell the story to your sponsor, team members, and stakeholders, as they are the primary consumers of this information.

As long as you associate the Probability and Impact score with a detail task to calculate the risk, it will work correctly. Note that this technique will not work with summary tasks, because MS Project doesn’t calculate percent complete for summary tasks in a way that is usable for calculated fields. If you have any questions, comments, or ideas for improving this design, please add a comment below.

This entry was posted in Risk Management and tagged , , , , by Dave Gordon. Bookmark the permalink.

About Dave Gordon

Dave Gordon is a project manager with over twenty five years of experience in implementing human capital management and payroll systems, including SaaS solutions like Workday and premises-based ERP solutions like PeopleSoft and ADP Enterprise. He has an MS in IT with a concentration in project management, and a BS in Business. In addition to his articles and blog posts, he curates a weekly roundup of articles on project management, and he has authored or contributed to several books on project management.

2 thoughts on “Tracking Qualitative Risk in Microsoft Project

  1. Hi Gordon, nice thing that you made. But what is the meaning of collom STAT?
    How does it change?
    gr Ferdi

  2. Hi Ferdi,

    The Stat column calculates the status of the task (Red, Amber, Green, complete, or future, with flags for milestones) based on the start and end dates and the % Complete column. I previously showed how to customize a numeric field for this purpose. Click here to read it. The sample file in this post has a slightly different version.

Comments are closed.