calculating how far behind a task is

[Miles_Goodchild]

I use the project status date and progress lines a lot to visually inspect a plan to quickly identify tasks which are tracking behind or ahead of where they "should be" based on the expected level of completion at the status date.  This is a useful way of focusing the team's attention and challenging them with "based on your progress how are you going to recover the delay and hit the planned finish date".

I would like to be able to calculate in MSP what my eyes can see - i.e. that based on the current expected progress (is this "complete through"?) minus actual % complete we're 2 weeks behind schedule.  In this way I can then add this onto the finish using a custom field and indicate when tasks will complete using the current burn rate.

Many thanks

Miles

[#2]

@Miles_Goodchild -- I would recommend you approach your project status question a little differently.  Setting the Status date is good, and it should always represent the last day of the last reporting period.  For many of us, the Status date is last Friday, Saturday, or Sunday.

After setting the Status date, manually enter task progress using whatever method or process that you prefer.  For example, there was a task last week with a Duration of 5 days, starting on Monday and finishing on Friday, with a team member assigned to work full-time on the task.  The team member should have finished last week, but he reporting that he is only 50% complete.  You enter 50% in the % Complete column.

After entering task progress, you should RESCHEDULE incomplete work from the past into the current reporting period.  In the case of the example task, there are 2.5 days of incomplete work that are still scheduled in last week.  After rescheduling this incomplete work from last week into the current week, the Finish date of the task has now slipped 2.5 days.

To determine project and task slippage, apply the Tracking Gantt view to see the slippage visually.  This assumes you saved a Baseline, however.  You ARE saving a Baseline for each of your projects.  Right?  To see how many days of slippage you have in your project, apply the Variance table.  In this table, you will see 2.5 days in the Finish Variance column for the task in question.  If the task is a Predecessor, all Successor tasks will also show 2.5 days of Start Variance and Finish Variance.  If the task is on the Critical Path, the Summary Summary Task (Row 0 or Task 0) will also show 2.5 days in the Finish Variance column.

So, to summarize, I would recommend you follow the process above, and focus your efforts on whether the project is running late, and if so, how late.  Hope this helps.

[#3]

@Dale Howard Thank you for the suggestion which is a powerful approach which can be very useful.  One weakness that this has is that if the team feels that despite the progress to date they can pull the time back and protect the end date this method doesn't allow for this.   Obviously this doesn't apply in the example you gave; rather the team member would need to give a new finish date :)

Your comment on Baselines raises a smile :)

I have a nasty feeling that project will not be able to perform the calculation I want however I am hoping that someone can prove this incorrect.

Thanks

Miles

[#4]

@Miles_Goodchild -- There is no direct method in Microsoft Project to compare the expected % complete progress with the actual % complete progress.  The only way to do that is to use the EVA tables in the software.  However, I doubt your team would find the EVA information to be of any value to them.

Unless someone a whole lot smarter than me knows how to create the formula you seek, then I think you might want to reconsider the advice I gave in my previous post.  Sorry, but hope this helps.

[#5]

@Miles_Goodchild 

If I may "dip my toe in the water". First understand that I am NOT a "whole lot smarter" than Dale with respect to Project, matter-of-fact, I'm not even close. However, what you want to do with Project reminds me of Alice's restaurant (i.e. "you can get anything you want"). Thanks to VBA, you can get virtually anything you want but, that doesn't mean the result will always be meaningful.

You mentioned in your original post that you'd like to have a metric comparing the current expected progress and actual percent complete (calculate what your eyes can see) and questioned whether "complete through" represented the current expected progress. For a non-summary task the "complete through" is simply duration multiplied by the entered percent complete value. For summary lines the relationship is a little more involved (basically a weighted average). So no, "complete through" is not expected progress.

Keep in mind that percent complete is a highly subjective metric. It is based on duration and actual duration and nothing is accomplished by the passage of time (i.e. duration) other than curing concrete or paint drying, etc. Actual progress should be based on effort (actual work) compared to estimated work for the task.

As Dale noted, the whole concept of expected versus actual is embodied in classic earned value metrics, although I've always taken exception to the concept of schedule variance based on cost. Aside from available earned value, what is the user's definition of "expected progress"?

If, as Dale suggested, the Tracking Gantt does not quite give what you want visually to "tweak the troops" into action, exactly what metric and visual display would work better? As I noted, anything can be coded with VBA, but will it mean anything?

My thoughts.

[#6]

@John-project @Dale Howard 

Firstly thank you John and Dale for helping to clarify my though processes.  The particular situation which prompted this question is dealing with a situation were progress is against a pretty arbitrary assumption of how long a large number of documents will take to be modified by a number of teams.  The better teams have off plan excel trackers which give the % complete and in the weaker situations it is a bit “finger in the air”.  Many of the teams are behind which is “clear” to see on the progress line but it is harder to throw this slippage into the future.

As the tasks have no element of work to them in the way that we three would normally use the term all the normal metrics and approaches we’d use (for instance Dale’s excellent approach) don’t work and we're dealing with "duration".

I want to arm the PM with a graphic which illustrates to the senior team (who can then apply their pressure to the other sections of the organisation to get progress back on track) the impact of the current slippage.  The simplest version of this is a picture where the current slippage is added to the current assumed end points which will illustrate how badly this slippage could impact the delivery date.

John’s comment about % complete of duration and paint drying triggered a thought that this is exactly what the eye sees when looking at the progress line so with that in mind I can calculate the “expected duration done” by subtracting the start from status date:

Exp Duration Done = ProjDateDiff([Start],[Status Date],[Project Calendar])/480

Duration Done = ([duration]/480)*([% complete]/100)  (divided by 480 as duration is counted in minutes and 60*8h =1d)

Duration gap = Exp Duration done – Actual Duration

Thus I can ad duration gap to current finish to get the impact of the slippage and display this on the Gantt view as a thin line.

[Finish 10] Expected Finish = ProjDateAdd([Finish],[Number17]*480,[Project Calendar])

This only applies to tasks which is OK as that is what I am looking at. 

It also assumes that I have updated the plan to move incomplete items’ finish date into the future however if this wasn’t the case an new formula using ProDateDiff to add the remaining Duration to the status date could be used:

IIf([Status]=2 And [Finish]<[Status Date],ProjDateAdd([Status Date],(([Duration]/480)-[Number16])*480,[Project Calendar]),ProjDateAdd([Finish],[Number17]*480,[Project Calendar])).

Thanks to both of your for your help,  it is very useful to other minds to bounce things off :)

[#7]

@John-project -- Nice comments, John.  Thanks for sharing.  :)