|
MS Project Tips and Tricks
What should be my Percent Complete if I am on schedule?
This is probably the most common question people ask and I am amazed
why they do not know the answer. The answer is really a question:
"What %complete did you plan to be?" A percentage
is dividing two numbers. What is your numerator and denominator? The
denominator is the key and the units have to be consistent.
In MS Project, %Complete is based on duration of the activity. If a 10 day task
is 30% complete, you are telling MS Project there has been 3 days duration
completed and there are 7 days remaining. I am not sure I understand what that
really means.... The real measure here would be the value associated with the
task such as work or some other measure (such as the number of bricks laid). For
sure, it would not be duration; that implies I can earn percent complete just
through the passage of time.
If you have a costed resource loaded schedule with a baseline for
your project, and a status date, you have a Budgeted Cost of Work Scheduled
curve (BCWS) also known as the Planned Value (PV). That number, as of the status date, divided by your total budget gives you the %Complete
you should be at a specific point in time. Project does the work for you, it calculates Schedule
Performance Index (SPI) and Schedule Variance (SV). An SPI of 1.0 (100%)
is on schedule, less than 1.0 is behind schedule and greater than 1.0 is ahead
of schedule. Similarly, a negative SV is behind schedule. Insert the SPI and SV
columns into your view.
If you are not using Earned Value for your scheduling, I suggest you start
immediately. There are a number of good books on the topic (see my resources
page). These are the standard measures of progress used in the industry.
For those who insist, below is a formula that will calculate the %Complete
(based on duration) of where a project should be as of a status date (or even
time now). The formula can be placed into a spare text column and works
nicely for the discrete tasks.
We need to consider three types of tasks: Those with Finish no later than the status
date should be 100%, those with a start date greater than the status date should
be 0%. So, the only real tasks to calculate become those that bridge the
status date. To calculate the %Complete the same way Project does,
determine the duration between the task start date and the status date.
Divide that by the duration of the task and that's it. There are a few
wrinkles --- first the desired result for display is an integer (28% not 0.28).
Then there is a rounding issue so the formula needs to be adjusted slightly,
then tack on the percent sign. Here is the final formula:
IIf([Finish]<=[Status Date],"100",IIf([Start]>[Status Date],0,int(0.5+100*(ProjDateDiff([Start],[Status
Date])/[Duration])))) & "%"
The above formula will calculate a %complete as related to the current forecast
[Start] and [Finish] dates.
If you want to be mean, you can also project a %complete based on baseline
information:
IIf([Baseline Finish]<=[Status Date],"100",IIf([Baseline Start]>[Status Date],0,int(0.5+100*(ProjDateDiff([Baseline Start],[Status
Date])/[Baseline Duration])))) & "%"
The formula requires a proper status date (Project/Project Information....)
These formulas will only apply to discrete tasks (non-summary tasks). Microsoft Project will weight the %Complete of summary level tasks by the
durations and %complete of the tasks beneath the summary. Using the formula provided, it will be difficult to correctly summarize the weighted "should be %Complete" as part of the roll ups.
If you wish to modify the formula, change [Status Date] to Now() and then
you do not have to concern yourself with status dates. My opinion: This is
the absolute worst way to determine percent complete.
Something else to consider. MS Project will calculate schedule performance
parameters against the baseline of your choice. So, if the customer is tracking
you to a baseline from last October, and you have a different forecast, you can
still use the MS Project calculations of SPI, SV, etc. Simply save a baseline
for your current schedule in one of the 10 spare baselines provided by the
software. Then, use
Tools/Options/Calculation (tab) and click the Earned Value button. Change
the basis for calculations to the baseline of your choice. Now, you can analyze
progress against your current forecast as opposed to your approved baseline.
Schedule Status
Marching through a schedule plugging in a %Complete will not accurately status
your schedule. As a matter of fact, it will more than likely damage the schedule
logic. Think about the status date in relation to the task time line. You
need to concern yourself with the Actual Start date, remaining duration and
remaining work. They are the important parameters.
The four shalls:
- There shall be no task with a start date left of status date with 0% Complete,
establish a new start date if necessary.
- There shall be no task with a finish date left of the status date that is not
100% complete, establish a new finish date if necessary.
- There shall be no task with a %Complete>0 with a start date to the right of the
status date, you did not do the work in the future.
- There shall be no task claiming 100% Complete with a finish date to the right of
the status date, the latest this task can finish is the status date.
Risk Register and Schedule/Budget Implications
The attached white paper, here (Adobe Acrobat
document), discusses bi-directional tracing of risks between the Inegrqated Master Schedule
and the program Risk Register. There are plenty of items to consider, it is more than placing an ID from a spread sheet into the
schedule. There are implications to how the schedule costs, and the schedule work packages reflect the risks.
My COM Add Ins do not work
Many times items such as the Analyze Time Scaled Data in Excel just do not
seem to work properly. This is common with the Project Compare utility as
well. The attached document shows you how to re-add (or jump start) these
feature in MS Project. Link
here (Adobe Acrobat
document).
|
