Expected % Complete: An early warning indicator for project work

Last fall (October 2015), I was working with a client who wanted to provide the typical red, yellow, green "stop light" indicator for tasks that were essentially behind schedule as a quick visual for Project Managers. This then would rollup to the project level to be factored into the overall project schedule "health" calculation. There were many discussions around what "behind schedule" meant for them because there are many ways and different factors that can determine if a task is considered behind schedule as well as what threshold did the business what to consider a task for preventative or corrective action. Out of those, we decided to focus on a metric that we called "Expected % Complete". Because they would be using Percent Complete to record progress on task, this consisted of calculating the percentage complete that the task should be at a given date if all work was progressing normally. For example, if you have a task that has a duration of ten (10) days, on day five (5) the task should be expected to be 50% complete. If the task is actually only 20% complete at this halfway mark then the indicator might show a red stoplight. If the task was actually 70% complete, then the indicator would likely show as a green stoplight. We also had quite a number of discussions debating the use of baseline Start and Finish dates (Baseline Start / Baseline Finish) over what are essentially the estimated Start and Finish dates, as well as whether to use the Project Status Date or the current date. Which is right for you and your company may be different, but for purpose of this post we will be targeting the estimated Start and Finish dates as well as the current system date. You should be able to use the content in the rest of this post to modify the formulas and values for what is right for your particular situation. Let's setup the scenario. First, we are assuming that % Complete is being used to record progress against tasks. Second, we will be assuming that all tasks are duration based tasks. If you are using another task type, for example fixed units or fixed work tasks, then you will need to adjust the formula accordingly. Third, we will be using the current system date to calculate the Expected % Complete and not the Project Status Date. Lastly, I'm assuming that you already know how to create either local custom fields within your project in Microsoft Project or know how to create Enterprise Custom Fields within Project Server or Project Online. Also, I used a text custom field to hold this formula because the client wanted the percent complete formatted as a percentage strictly for visual purposes. This actually made creating the indicator field harder because I had to convert it back to a number before evaluating it against the red, yellow and green thresholds.

Now the formula:

Format(IIf(([Duration]=0),0,(IIf(([Estimated]="Yes"),0,(IIf(([Finish]<Now()),1,(ProjDateDiff([Start],Now())/[Duration])))))),"0%")

Don't get freaked out here. It's not as bad as you might think. A lot of this is testing certain conditions that might occur for a task such as whether the duration is zero like for a milestone task ([Duration]=0), the duration is only estimated ([Estimated]="Yes"), and whether the task's finish date has already passed ([Finish]<Now()). Let's break it down, okay?

First, the heart of the calculation is this: (ProjDateDiff([Start],Now())/[Duration])). Basically this says subtract the current date from the task's Start date field and divide by the duration of the task. For this we use to functions, the Now() function to represent the current date and the ProjDateDiff function to subtract the dates. For those familiar with VBA, you might be wondering why we are not using the DateDiff function. The ProjDateDiff is a special Project only function. It's special because it takes into account the calendar settings and hours per day that might be set for a particular task. Why is that important? Remember that tasks can use either the calendar that is for the overall project or a specific task calendar. Additionally, if you have resources assigned to that task (and you absolutely should) then they can each have individual calendars associated with them as well. Those will define that resources work day, hours and such. So, let's take a simple example. Let's say that you have a task that has a calendar that defines a work day as 10 hours/day and another task that has a calendar that defines a work day as 8 hours /day. Rather than having to figure out how the day unit is define for that task, and add those consideration to your formula, you can use the ProjDateDiff function to do all the heavy lifting for you. Additionally, duration can be a particularly vexing field to use in calculations because the value that comes back from referencing a duration type field is represented in minutes, not hours or days. There is also a really great constant that is available that can be used called [MinutesPerDay] that takes into account the calendar et al. Here I'm not using it because both the result of the ProjDateDiff function and the Duration are in minutes already. I don't need to convert them to anything else to get my Expected % Complete.

The second thing that might be throwing you is the IIf parts of this formula. This is known as an Immediate IF function. Basically it says, if the first part is true do this, other wise to that. It's format is like this: IIf(expr, truepart, falsepart). Because I'm testing for different conditions that might exist in the task, I have several nested IIf functions in the formula where the false part of the IIf function is another IIf function. First, I test to see if the duration is zero. If it is then the percentage is zero. Next, if the duration is still set as an estimated value, either because it still has the ? or is set to Estimated, this can do weird things to the duration value so I want to nip those in the bud. Lastly, I check to see if the Finish date is before the current date because if it is no matter how long ago this task should have been finish it should be 100%. Wrap all this in a Format function to format the result as a percentage and you are done.

So let's see this in action. Below is a project schedule with the Expected % Complete value custom field with a system date is 01/24/2016. I've filled in several percentage completes and also exposed several additional fields for you to peek at. Now all that's left is to create an indicator field based on my Expected % Complete value and the red, yellow and green thresholds.

Links: Project functions for Custom Fields: http://bit.ly/25dFPAd

email Christine: christine.flora@symnoian.com

Tying Your Project Metrics to your Strategic Business Drivers

Strategy and Execution. Pick up any business magazine or book and that's about all you'll be reading about it seems. Company XYZ is a top performer because they not only come up with great ideas (innovation / strategy), but then they can make it happen (execution). I recently read two books about Strategy and Execution, "Clever" by Rob Goffee and Gareth Jones and "Making Ideas Happen: Overcoming the Obstacles between Vision and Reality" by Scott Belsky. Even the cover story of my July-August 2010 Havard Business Review is about Strategy and Execution, "The Effective Organization: Turn Great Strategy into Great Results". Project Managers are all about execution, but how do our projects align with our company or organization's business strategy? Do we even know? Or care? We should. Sure, we spend time planning, defining scope and requirements, then putting together a team, but the majority of our time we spend actual doing things, aka executing. Plan the work. Work the plan. Do the projects that we spend time executing align with our core business strategies? If not, we shouldn't be doing them.

And not only should the projects that we are managing be aligned with our organization's strategic objectives, but so should the measurements that we track and use to manage them. I'm often asked by PM's or during the course of implementing an EPM (Enterprise Project Management) system what metrics should they be using. Most talk about Earned Value, Burn rate, Budget vs Actual and the like. And while these are all good and provide valuable information about how a project is peforming, I typically ask a question instead of giving a specific answer, at least right away.

What are your organization's core strategic drivers or objectives?

This is usually met with silence. Let's take Earned Value. Earned Value is a set of measurements that combine scope, schedule and cost into a set of integrated and hopefully objective metrics. Originated during the 1960s by the United States DOD to manage and provide insight into government projects, it has gained in popularity in the commercial space in recent years. I like it because of its high accuracy in predicting how a project is going to run (for cost and schedule) within the first 20% of work on the project. But it is a reactive, corrective action metric and it only covers part of what you should be managing as a project manager.

Take the following list of Project Management Knowledge areas as outlined in PMI's PMBOK (Project Management Book of Knowledge):

• Project Management Integration
• Project Scope Management
• Project Time Management
• Project Cost Management
• Project Quality Management
• Project HR Management
• Project Communication Management
• Project Risk Management
• Project Procurement Management

Only the three areas that are highlighted in bold are covered by Earned Value. What about the rest of the areas? Couldn't quality of the deliverables of your project effect its completion or the stakeholders satisfaction? Having to go back and rework parts that were delivered certainly could effect your project's timeline in a negative way. What about not having enough or the right team members to do the work on your project? Do they have the tools or training they need to do tasks they've been assigned? Or not getting timely delivery of materials to create those parts, could that negatively impact your project? What values in Earned Value do you use to measure these areas? The short answer is there aren't any.

Tracking costs and delivering on schedule might be part of your organization's strategic objectives, typically under Customer Satisfaction or decreasing cost to market, but they are only part of the picture. And if they aren't part of your organizations core strategic objectives, why are you putting so much emphasis on them by tracking them? I know, I know. It's almost considered heresy to suggest not tracking costs and schedule for projects, especially in today's economic climate, but I'm a "why" kind of gal.

I like Earned Value. I really do. In fact, I use it for almost every project that I manage. In my next post, I'll be talking about what you should and could be measuring in addition with Earned Value to get a better picture of how your project is executing.

Hint: It involves a little thing called the Balanced Scorecard.