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.

Multiple Timeline Views in MS Project

1 Comment

One of the key features of MS Project is a new feature called the timeline view. The idea behind this is that the Gantt chart can be too weighty or busy or in some cases, just too detailed for some audiences. For years project managers would take and build a separate Visio or PowerPoint view to communicate a high level roll-up to different stakeholder audiences.

 

This topic isn’t how to build a timeline, but how to have multiple timelines all within a single schedule. The idea being that you may have different audiences that want to see different milestones. What we often do is completely rebuild the timeline view each time to showcase different values to each audience.

 

1

 

 

 

Now, if you are spending more than 30 minutes once a week managing your schedule(s), then you are spending too much time and there are some knowledge gaps which you can fill with a little training.

 

I’m going to show you how to have multiple timeline views (named) for different audiences so that you can, at will, change, export, print, .pdf or copy and paste out to different audiences who are not going to have or use MS Project. All without having to replace or rebuild your views.

 

First of all, let’s help you build your timeline (the steps will be the same for building each new view).

 

Step 1:  Build a Project Schedule

 

Step 2:  Right Click on any task or milestone you want to put on the timeline and choose from the sort cut menu Add to Timeline, or select the task by clicking on it and then use the Add to Timeline button on the ribbon (as seen here on the Task Tab).  Another method is to simply highlight the row and drag the task object to the timeline view.

 

Once the timeline view is built, you can click on any task in the timeline and format it from the Format Ribbon tab.

2

3

Whichever way you like, you can quickly pull tasks, milestones, or summary tasks to a view and help create a snapshot of key activities based on your schedule.

 

Now here is where many project managers stop. When a different audience needs to see different information, some managers or schedulers will delete the existing tasks from the view and rebuild it each time. Ugh! Here is where a little information goes a long way.

 

You can actually save and name different Timeline views so all you have to do is switch between them quickly and easily.

 

The steps for building a timeline don’t change – the key is to save and name a timeline view.

 

Step 1:  Build a Timeline view

 

Step 2:  Click inside the timeline view to activate the Timeline Ribbon’s context sensitive Timeline Tools as seen here.

4

You will notice on the ribbon that there are options to copy the timeline to any external source, but to the far right, there is an option to switch the Timeline view.

 

Step 3:  Click on the Timeline View Drop down as seen here and choose the More Views option.

5

When you choose this option you will be able to choose a new view.

 

Step 4:  Click the New button.

6

 

Step 5Choose the Single View option and then click the OK button

 

7

 

Step 6:  Now from the View Definition dialogue box, give the view a new name, choose the Timeline view type from the screen and make sure you select Show in Menu to easily find and select your new view. Of course click the OK button when you are done.

 

In this case I will name my view “Finance Steering Committee”, which you will see in the next screen of created views.

8

 

Step 7Click the Apply button in the More Views dialogue box, or close and try this from the ribbon.  Your new View awaits you.

 

9

 

Here is the view from the Ribbon.

 

10

 

Either way, you now have a blank timeline view you can add your tasks and milestones that a different audience will want to see. Format this new timeline view and you can switch between different Timeline views by simply clicking the drop down and going between Timeline (the default) and any named views.

 

It is these little shortcuts that save countless key strokes, rebuilding, reformatting and allow project managers the ability to get in, get out and get back to work.

 

 

Microsoft Project Online – Where did my reports go?!

No Comments
microsoft-project-2013-logo

Understanding the latest update to Project Online reporting structure

In recent discussions with our clients, we understand that the immediacy that software-as-a-service (SaaS) provides is both a benefit and a challenge. It gives you the opportunity to benefit from the latest and greatest features but it imposes a challenge in that you have little or no time to prepare for the delivery of new features.

 

This is the case with the new library-based reporting structure Microsoft is deploying to Project Online. The new structure addresses one of the most common support questions but it comes at a cost in that your PWA’s BI Center sub-site is no longer the default for reports. The links on your homepage have been updated to point to new reporting libraries, a location that’s more easily discoverable by new and inexperienced users.

 

You have two choices to fix your reporting experience:

1.  Continue to use the same location (simplest solution): You will need to revert the changes made to the homepage’s reporting links to point them to the old location. To do this, follow these instructions under “Continue using your existing BI center site” section
2.  Use the new reporting libraries (recommended future proof solution): This is the best approach as it will ensure that you get the latest features and changes from Microsoft. To do this, follow these instructions under “Use the new Reports library” section.
Unless you have a large number of reports and many users and documents linking to them directly, we recommend that you move your reports and use the new reporting libraries. This is the future-proof solution because, all updates and changes to the reporting solution will assume your reports are in the libraries.

 

We are here to help so comment below with questions, give us a call at: 866.362.3847 or visit us at www.advisicon.com

Growing a Project Management Culture with Project Server & Team Foundation Server

4 Comments

As many of you know I just recently had a baby girl.  With this bundle of joy, I not only get little to no sleep, but every day it is a new adventure.  Oh the highs and lows of parenthood.  One thing that I am thrilled about is the fact that each day she is learning new things and whether she wants to or not, she is in a constant flux of learning and changing.  Watching her start to see, visualize her curiosity and comprehend that an object is something that she can grasp, then grabbing (and of course putting them right in her mouth), is quite comical.

So how does this have anything to do with Project Management, Project Server or Team Foundation Server you might ask?  Well it struck me, as I was putting Rachel into her car seat for the first time, that not every new experience, tool, safety device or contraption she is coming across is met with excitement or eager anticipation. Read More

After the MBA: 3 Skills to Learn and Utilize Following Graduation (Including The Skill of Project Management)

3 Comments

Manage Different Personalities Differently

In business and educational circles, an MBA is the pinnacle of college degrees and the first crucial step to financial, professional and societal success. Students invest hundreds of thousands of dollars to attend top level universities while earning an MBA and after the 2 to 4 year program has been completed, many students begin to feel inadequately prepared for the real world job market. While the content of MBA courses teaches future leaders of American business numerous core and essential skills there are three skills (Project management, networking, and leadership,) that an MBA must learn and utilize following graduation if they truly wish to succeed. Read More