Creating Graphical Indicators Based on Formulas with MS Project

No Comments

This topic is one of the more common requests I hear in regards to MS 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 some kind of 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 we’ll use 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)

1

And the Resources:

2

One of the key areas of mapping variances in a schedule is to set a baseline to track our planned activities and have a reference point to where your actuals are as you progress your schedule through its lifecycle.

 

Now on to saving a Baseline:

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

3

 

The following “Set Baseline” dialogue box opens

4

 

Now let’s apply a cost table from the VIEW tab so we can see the new baseline values.

 

When we apply the Cost Table we get:

5

 

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.

6

After you click OK above you will see 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).

7

 

Now click the Formula button again, then Click the Field list pick; click Cost and then Actual Cost

 

8

 

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

9

 

The result should look like this. Click OK

10

An alert message will populate, click OK:

11

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

 

Setup the Graphical Indicator:

12

 

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.

13

From the drop down list choose the Green Light:

14

 

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

15

 

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

 

Right click on the ALL button to open up the Table View. Select More Tables

16

 

Make the following selections as noted:

17

 

For the new item, also set the Header Wrapping to “Yes” and the Text Wrapping to “No” then click OK, then Apply.

18

 

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

 

19

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

 

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

 

20

 

Since everything went fine with Task 1 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.

21

 

Enter the increased hours.

22

 

The following is displayed

 

23

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:

24

Task 3 has a

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 see costs (Baseline, Actual, and Variance) in Cost Table:

25

 

Hopefully this gets you on your way to creating strong visuals to help with managing your schedules and communicating effectively to your stakeholders. As always, we are here to help! Comment below with questions, give us a call at: 866.362.3847 or visit us at www.advisicon.com.

Project 2013 & Project Server 2013 at PMI Willamette Valley

No Comments

Today I will be starting the lecture circuit for PMI where I will be showcasing Microsoft Project, Project Server, and SharePoint Business Intelligence for both the current version and for [Project Online](http://go.microsoft.com/fwlink/p/?linkid=257733 “Try the Project Online Preview”) and [Project Server 2013](http://technet.microsoft.com/en-US/evalcenter/hh973403.aspx?wt.mc_id=TEC_120_1_33 “Download Microsoft Project Server 2013 Preview”).

I am super excited about helping people understand the key differences between what the Online presence of [Project Professional 2013](http://technet.microsoft.com/en-US/evalcenter/hh973401.aspx?wt.mc_id=TEC_119_1_33 “Download Microsoft Project Professional 2013 Preview”), [Office 2013](http://blog.advisicon.com/2012/07/20/project-13-and-office-13-out-for-review/ “Project Professional 2013 Preview and Office 13 Out for Review | Advisicon Blog”), [SharePoint 2013](http://sharepoint.microsoft.com/en-us/preview/sharepoint.aspx “Microsoft SharePoint 2013 Preview”), and Project Server 2013 will do.  With the ever increasing demand for better, faster and easier web accessibility to manage projects, programs and portfolios, Project 2013 provides a continued march to a more powerful extension of features in previous versions only in the desktop and now embedded online. Read More

Quickly Present Dashboards & Custom Views

No Comments

*[MS]: Microsoft
OK, we tend to get many questions around this and one of the amazing thing I find is that even super users of MS Project tend to approach working in Project like Excel.

Yes we know that in 2010 it has the “User Defined Scheduling” which acts more like Excel, however there should never be a reason for an end user who repeatedly has to insert, move, delete columns for a view. In many cases this “rinse and repeat” process is cumbersome and un-needed. Read More

Project Business Intelligence Reporting

1 Comment

It is amazing that for as long as I have been working in Project, Project Server and SharePoint (all in support of Project, Program and Portfolio management), one of the driving factors in doing the work is getting good reports and reporting from Project data.

However, this still continues to be one of the least understood elements. I am going to write a series of posts showcasing different reporting options with Project Professional as well a discussion points of Project Server/SharePoint BI so that people may have a better understanding of the overall mechanisms.
Read More