In Part One, I presented a simple method to identify, quantify, and evaluate risk in projects and tasks. In this article, I will create a “Risk Assessment Dashboard” in Microsoft Project that’s based on that method.
When completed it will look like this:
I will create the “Risk Assessment Dashboard” in four steps:
Step 1: Create the “Risk Assessment” table
1) Click on the “View” tab.
2) In the “Data” group, click on “Tables”.
3) At the bottom of the “Tables” list click on “More Tables…”
4) The “More Tables” dialog appears. Click on the “New…” button.
5) The “Table Definition” dialog appears. Enter data into this as is indicated in the figure below, then click on “OK”.
When a new table is created, Project places the table at the top of the table list so it is easy to apply. See the figure below.
Want more Do’s and Don’ts? Check out MPUG’s new book: Microsoft® Project Do’s and Don’ts!
Now on to the next step.
Step 2: Create the two custom field lookup tables
1) Click on the “Project” tab.
2) In the “Properties” group, click on “Custom Fields”.
3) The “Custom Fields” dialog appears. The “Text1” field needs a name more descriptive of its function. Select “Text1” from the field list in the dialog and then click on the “Rename” button just under the list.
4) The “Rename Field” dialog appears. Rename the field “Probability(1…5) as is shown in the figure below.
5) Click on the “OK” button in the “Rename Field” dialog to return to the “Custom Fields” dialog.
6) In the “Custom Attributes” section of the “Custom Fields” dialog, click on the “Lookup…” button.
7) The “Edit Lookup Table for Probability (1…5)” dialog will appear. This dialog will contain the levels of probability and what they mean. Enter the levels as shown in the figure below.
8) Click on the “Close” button in the lower right corner of the dialog. This returns you to the “Custom Fields” dialog.
9) Click on “OK” to close the dialog.
10) Note that the “Text1” field is now ”Probability (1…5)” and that every cell in the field contains a drop down listing of the levels of probability and what the number means. Project will still maintain the original field name as well as the new name.
11) Repeat 1 through 9 for the “Text2” field, but rename it “Impact (1…5)”. Once both fields are created, it’s on to the next step.
Step 3: Create two formula fields
The “Text3” and “Text4” fields do the math and quantify the risk for the graphical indicators. Each will have to be customized.
1) For the “Text3” field follow steps 1 – 5, but give the field the new name “Risk Level (Prob X Impact, Max = 25)”.
2) In the “Custom Fields” dialog, in the “Custom Attributes” section, click on “Formula…”.
3) The formula editor will appear for the new “Risk Level…” field. Enter this formula (without the quotes): “[Probability (1…5)] * [Impact (1..5)]”. Alternatively, the editor contains MS Project’s fields for selection and inclusion in the formula. It now should look like the figure below.
4) Click on “OK” to set the formula, and click on “OK” in the “Custom Fields” dialog to set the field customizations. If the field returns “#ERROR”, it is because there is no data to evaluate yet. Test the field and results by choosing the value “2” from the two custom field lookup table. The result should be “4”.
5) Repeat 1 – 2 above, but rename “Text4” to “Risk Severity”. This field will contain a formula that associates the risk to a High, Medium or Low threat. The field will evaluate per the rules in the table below.
6) Repeat 3 above with the formula “IIf([Text3]>14,”High”,IIf([Text3]>5 And [Text3]<14,”Medium”,”Low”))”. Note that I used the actual field name in this formula. I could have used the new names instead. Both are valid.
The final step will associate the graphical indicators with “Risk Severity”.
Step 4: Create graphical Indicators
1) Return to the “Risk Severity” fields’ “Custom Field” dialog.
2) Near the bottom of the dialog, in the “Values to display” section, click on “Graphical Indicators” as shown in the figure below.
3) The “Graphical Indicators” dialog appears. The goal is to have an indicator for “Low”, “Medium” and “High”. This will associate the correct color to each risk level. The figure below illustrates the syntax and selections you should make.
4) Click “OK” to set and close the “Graphical Indicators” dialog. Click on “OK” again to set and close the “Custom Field” dialog.
Test the new table out by entering a few tasks and using the probability and impact fields to associate different levels of risk. It should give you information similar to the figure below.
Once the work is done to create the “Risk Assessment Dashboard,” edit the graphical indicators to work in Summary tasks and in the Project Summary Task. This action will enable you to represent and manage risk at any level in your project!
In the next article, I will use the fields, the formula, and the graphical indicators from this article to report on risk. I will illustrate filtering and grouping risk and how to summarize the cost. work and schedule that is developed in the project so far.
Question: I completed the Part 2 execise however the data is not visable in the Proability column (the entire column is white with the exception of the column header). I can only see the data if I mouse over the cells or select a cell to see the data…..Any suggestions?
Did you create the lookup table?
I was not able to put in the formula in Step 3. I kept getting : the formula contains a syntax error or contains a reference to an unrecognized field or function name. To return to the Formula dialog box and highlight the error, click OK. I put the probability x impact formula exactly like you have in Step 3, 4 times.
I kept getting a syntax error when trying to put in the formula for prob x impact. I tried 4 times. Please help.
Step 3 produces a syntax error. I tried 4 times, along with trying to send this comment 4 times.
Let’s take this up as an email assist rather than comments. I am happy to help you get your dashboard up and running. Use:
Muhammad Farooq Khan
At Graphical Indicators, i couldn’t see text of High, Low ,and Medium in Value Column. Please help how to get them.
Abbas and all,
Thanks for trying the technique out. There are many ways to use this idea in a customized file. I applaud all the comments and assistance you gave each other!
As you know, MS Project has no specific risk management capabilities until you move into the enterprise level of Project. So if you are using Project in a non-enterprise environment, you will have to make your own. That is why it’s important to get at least a basic risk tool in place such as the Risk Assessment Dashboard.
Excel and P6 are both excellent tools, but I have not used either for risk management so I can’t comment on their use, accuracy or value.There are many other add-ins that are helpful if you are willing to spend a little money. I have evaluated no other risk management add-ins for Project, so again I can’t comment on their use, accuracy or value.
Thanks again for trying this technique!