PM Tech Corner: Dynamic Dashboards in MS Project

Welcome back the PM Technical Corner!

I often hear about project schedules needing dashboards or key metrics for reporting.  In many cases executives or senior stakeholders roll their eyes at the sea of text that comes with scheduling tools or the massive amounts of information presented in a Gantt chart. What we want to do is to help anyone using a schedule to quickly find and present the issues through stop light reporting or other graphical indicator.

In this article, I am going to leverage MS Project 2013 for my screenshots and steps, but you can do this with any version of Project from 2003 or higher.

Creating Graphical Indicators Based on Formulas

To create a graphical indicator based on a formula consider the following scenario. We have a Project Baseline for a sample project that we are tracking the progress of.

  • If the Actual Task Costs are aligned with the Baseline Costs then we want to see a Green Light in the Gantt Chart View (Entry Table).
  • If the Actual Task Costs are exceeding the Baseline Costs up to $1,000, then we want to see a Yellow Light in the Gantt Chart View (Entry Table).
  • If the Actual Task Costs are exceeding the Baseline Costs by more than $1,000 then we want to see a Red Light in the Gantt Chart View (Entry Table).

image 

And the Resources:

image 

 

Saving a Baseline

First select your project tab and then choose the set baseline from the ribbon.

image

The following “Set Baseline” dialogue box opens.

image

Now let’s apply a cost table from the VIEW tab so we can see the new baseline values. (Select “Tables” from the Data section and select “Cost”).

When we apply the Cost Table we get:

image

From here we want to create a custom graphical indicator for showing when costs change (this can be applied to work or even the schedule slippage by following similar steps).

Let’s select the PROJECT tab and then select the CUSTOM FIELDS option from the ribbon.

Now, create Custom Field for Graphical indicator:

In the graphic below, we rename the field to make it easier to find and reference in future views, filters and formulas.

image

After you click OK above you will return to the Custom Fields dialogue box. Click on the Formula button and you will see a notice box open. Click OK.  (This is just warning you that you will lose any data that may have been typed into that field before).

Next click the Formula button again, then select the Field list pick; click Cost and then Actual Cost.

image

Now select the minus (-) sign then click the Field list pick again; click Cost and then Baseline Cost then Baseline Cost, as shown below:

image

The result should look like this. Click OK.

image

An alert message will populate, click OK.

image

 

Setting Up the Graphical Indicators

Now for the fun part, let’s create a graphical indicator to showcase the variances (both positive and negative).

 image

We need to make sure that we address the indicator at the Task or Row level so select Non-summary Rows.  Later if you want your summary tasks to also have graphical indicators you can choose the summary rows as well.

Here you will put in the conditions that will give you the range of values for the indicators to change.

image

From the drop down list pick choose the Green Light.

Finish the remaining two rows, then select OK, then select OK again.

image

Now we will put the Custom Field in the Entry table for easy reference and updating.

Add the ”Cost Overrun” field with the following selections:

Right click on the ALL button to open up the Table View (The upper left corner of Task entry). Select More Tables

image

Add the ”Cost Overrun” field with the following selections:

image 

Set the Header Wrapping to “Yes” and the Text Wrapping to “No” then click OK then Apply.

image

Now look at the Gantt Chart View and you will see your updates.

image

Since we did not enter any “Actuals” all tasks show a Green status.

 

Now set Task 1 as 100% Complete: Highlight the Row and select the 100% icon on the Task Ribbon.

image

Since everything went fine with Task1 the Graphical Indicator remains Green!

 

Now input 18 hours for Task 2.  We can do this many ways, but here is a great way to do actuals editing from the TASK ribbon.  Click on the Mark on Track option and select Update Tasks.

image

Enter the increased hours. 

image 

The following is displayed.

image

Task 2 has a Baseline Cost: 2 days * 8 hours per day * $200 per hour = $3,200.

Actual cost = 18 hours * $200 per hour = $3,600.  Since Actual Cost – Baseline cost = $3,600 – $ 3,200 = $400, and it is less or equal to $1,000, the Yellow indicator appears!

 

For Task 3, update the task for a duration of 5 days.

image

Task 3 has the following:

  • Baseline Cost: 3 days * 8 hours per day * $300 per hour = $7,200.
  • Actual cost = 5 days * 8 hours per day * $300 per hour = $12,600.

Since Actual Cost – Baseline cost = $12,000 – $ 7,200 = $4,800, and it is greater then $1,000, the Red indicator appears!

 

You can view costs (Baseline, Actual, and Variance) in the Cost Table:

image

 

Hopefully this gets you on your way to creating strong visuals to help with managing your schedules and communicating effectively to your stakeholders.

Reach out to me at Tim.Runcie@Advisicon,com for questions and happy PM’ing!

 

 

About the Author

Tim Runcie photo Tim Runcie is the president of Advisicon (a Gold PPM Company), a 20+ year project, program and portfolio expert and member of the Microsoft Advisory council.  Tim is also the author of over 36 books on technology and project management and a 12+ year nominated and awarded MVP at Microsoft. Tim and Advisicon offer webinars, classes and customized training for all your project management needs. book cover image 
mvp logo ms Partner logopac logo