Loading...
Quick Links

Microsoft® Project Do’s and Don’ts: Creating a Risk Assessment Dashboard: Part 2

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:

dashboard_1

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…”

dashboard_2

4) The “More Tables” dialog appears. Click on the “New…” button.

dashboard_3

5) The “Table Definition” dialog appears. Enter data into this as is indicated in the figure below, then click on “OK”.

dashboard_4

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.

dashboard_5


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.

dashboard_6

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.

dashboard_7

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.

dashboard_8

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.

dashboard_9

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.

dashboard_10

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.

dashboard_11

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.

dashboard_12

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.

dashboard_13

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.

Share This Post
22 Comments
  1. 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?

    Reply
  2. Regina,
    Did you create the lookup table?

    Reply
  3. 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.

    Reply
  4. I kept getting a syntax error when trying to put in the formula for prob x impact. I tried 4 times. Please help.

    Reply
  5. Step 3 produces a syntax error. I tried 4 times, along with trying to send this comment 4 times.

    Reply
  6. Carol,
    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:
    sam.huffman@versatilecompany.com

    Sam Huffman

    Reply
  7. I had trouble on Step 3-6 above. Here’s the formula that worked for me:
    IIf([Text3]>14,”High”,IIf([Text3]>5,”Medium”,”Low”))

    Reply
  8. I keep getting the same issue as Carol. Wouldn’t it had made more since to publish the fix in the comments instead of through e-mail with only her. I tried Russell’s formula as well and keep getting the same thing.

    Reply
  9. The procedure worked perfectly for me. To avoid the syntax error is better to use the Insert Field option instead of typing the characters.

    Reply
  10. Hi

    use this instead
    IIf([Text5]>14,’High’,IIf([Text5]>5 And [Text5]<14,'Medium','Low'))

    replacing ‘Text5’ with the number you are using, worked for me in Project 2013

    Reply
  11. The only way that I made it was to replace the COMMA with a SEMI-COMMA right nect to the number “14”

    Reply
  12. Hello,
    At Graphical Indicators, i couldn’t see text of High, Low ,and Medium in Value Column. Please help how to get them.

    Reply
  13. Thanks Sam, For the great Tutorial… Really Appreciate the effort 🙂

    Just One Note to the readers:

    In MPP 2016 the formula does not accept double quote to the strings. Hence the formula is

    “IIf([Text3]>14,”High”,IIf([Text3]>5,”Medium”,”Low”))”

    (Without the Quotes.)

    Thanks
    Jigar Thakkar
    +91 9819280575
    jigar1859@gmail.com

    Reply
  14. Thank you Sam, with a little fooling around (errors on my part) it worked like a charm. I copied your formula straight off your page and for whatever reason Project didn’t like the copied double quotes. I backspaced them out, re-entered and on I went.

    Reply
  15. Oh, now I see everyone had a solution for the formula in one way or another….Anyway, GREAT information.

    Reply
  16. I had trouble with the formulas above also. Using 2013.
    The winner in the end was to use the original formula and change ” to ‘
    so:
    IIf([Text3]>14,’High’,IIf([Text3]>5 And [Text3]<14,'Medium','Low'))

    Reply
  17. Great little guide. Only problem I can see is if there are multiple risks per activity (per line in the plan). Can anyone think of a solution?

    Reply
  18. thanks. very useful

    Reply
  19. Thanks

    Reply
  20. Hello Sam and thank you for sharing your experiences. Can you explain about the capability of MS Project to support the tracking and management of risk projects in compare with MS Excel and P6, please?
    Can you share the potential of strength and limitations of MS Project for Project Risk Management, please?
    Thanks.

    Reply
  21. 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!

    Sam Huffman

    Reply
  22. Hi,

    Trying to get the step with “IIf([Text3]>14,”High”,IIf([Text3]>5 And [Text3]<14,”Medium”,”Low”))”. working, but get a Syntax error. Anyone have any tips?

    Reply

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Please complete this equation so we know you’re not a robot. *

Thanks for submitting your comment!
You must be logged in to comment.