CelerisSystems

COMPARING PRESENT VALUES OF

%COMPLETE TO “WHERE I SHOULD
BE” USING MICROSOFT PROJECT

P REPARED BY

James E Aksel, PMP, PMI-SP, MVP

For Additional Information about Earned Value Management Systems and reporting,
please contact:
CelerisSystems, Inc.
3335 East Mira Loma Ave, Suite 143
Anaheim, CA 92806
United States

Telephone: +1 714-813-4025 or 800-951-E3V8M6S7

http://www.Celeris-Systems.com
CelerisSystems

Contents 
1 Introduction ......................................................................................................................... 4
2 Background ......................................................................................................................... 4
3 Percent Complete Calcuation Types......................................................................................... 4
3.1 Percent Complete ............................................................................................... 4
3.2 Percent Work Complete ....................................................................................... 5
3.3 Physical Percent Complete ................................................................................... 6
4 Analysis .............................................................................................................................. 7
4.1 Expected Percent Complete .................................................................................. 7
4.2 Duration Analysis (Detail Tasks) ........................................................................... 9
4.2.1 Business Rules for Consideration ...................................................................... 10
4.2.2 Calculation of Duration Expected Percent Complete – Summary Tasks ................... 16
4.2.3 Expected Duration Complete Calculation – Alternate Method ................................ 16
4.2.4 Duration Based Percent Complete Conclusion ..................................................... 16
4.3 Work Analysis (Detail Tasks) .............................................................................. 16
4.4 Cost Based Percent Complete (Physical % Complete) (Detail Tasks) ......................... 17
4.4.1 Using Physical Percent Complete for Calculations ................................................ 17
4.4.2 Changing Earned Value Methods ...................................................................... 17
4.4.3 Saving A Baseline .......................................................................................... 19
4.4.4 Switching Baselines Used for Earned Value Calculations ....................................... 19
4.4.5 Calculating Expected Physical % Complete ........................................................ 20
5 Stoplight Indicators (Red Amber Green – RAG)....................................................................... 21
5.1 Stoplight Business Rules .................................................................................... 21
5.1.1 Business Rules Based on Percentage................................................................. 21
5.1.2 Business Rules Based on Duration .................................................................... 22
5.2 Inserting Indicator Icons on the Schedule ............................................................ 23
5.2.1 Indicator Icons for %Complete and %Work Complete ......................................... 23
5.2.2 Indicator Icons for Physical % Complete............................................................ 25
5.2.3 Indicator Icons for Absolute Duration Variance ................................................... 25

Equations 
Equation 1 Duration Based Percent Complete......................................................... 4
Equation 2 Percent Work Complete ...................................................................... 6
Equation 3 Physical Percent Complete ................................................................... 7
Equation 4 Notional Equation for Expected Duration Percent Complete .................... 11
Equation 5 Notional Equation for Expected Work Percent Complete ......................... 17
Equation 6 Expected Physical % Complete........................................................... 17
Equation 7 Expected Physical % Complete Calculation for Baseline “i” ..................... 20
Equation 8 Calculating Schedule Variance Index ................................................... 22
Equation 9 Schedule Performance Index (SPI) Formula ......................................... 25
CelerisSystems
Figures 
Figure 1 Percent Complete Screen Shot ................................................................ 5
Figure 2 Resource Sheet ..................................................................................... 5
Figure 3 Resource Loaded Schedule Showing Total Work ......................................... 5
Figure 4 Work Profile (Task Usage View) ............................................................... 5
Figure 5 Percent Work Complete Screen Shot ........................................................ 6
Figure 6 Costed Resource Loading ........................................................................ 6
Figure 7 Physical Percent Complete Screen Shot .................................................... 7
Figure 8 Graphical Illustrations of Three Methods of Percent Complete....................... 9
Figure 9 Customizing Fields ............................................................................... 13
Figure 10 Customize Fields Dialog Box ................................................................ 13
Figure 11 Formula Definition for Custom Desired Duration Column .......................... 14
Figure 12 Custom Field Dialog Box for Expected Duration % Complete Calculation .... 15
Figure 13 Formula Dialog Box for Expected Duration % Complete ........................... 15
Figure 14 Showing All Tasks In The Schedule....................................................... 18
Figure 15 Multiple Task Information Dialog Box .................................................... 18
Figure 16 Saving a Baseline .............................................................................. 19
Figure 17 Accessing Microsoft Project Options ...................................................... 19
Figure 18 Microsoft Project Options Dialog Box..................................................... 20
Figure 19 Earned Value Parameters Selection Dialog Box ....................................... 20
Figure 20 Formula for Expected Physical Percent Complete .................................... 21
Figure 21 Duration Based Schedule Variance Calculation ....................................... 23
Figure 22 Setup Parameters for Schedule Variance Calculation ............................... 24
Figure 23 Establish Icon Settings ....................................................................... 25
Figure 24 Indicator Icons for Absolute Schedule Variance Durations ........................ 26

Tables 
Table 1 Expected Values of Percent Complete ........................................................ 8
Table 2 Variables in Play ................................................................................... 10
Table 3 Formulas for Expected Duration Complete for Detail Tasks.......................... 12
Table 4 Schedule Variance Thresholds ................................................................ 21
Table 5 Interpreting Schedule Variance ............................................................... 22
Table 6 Schedule Variance Indicator Icons........................................................... 22
Table 7 Second Type of Schedule Variance Indicators ........................................... 22
CelerisSystems
1 INTRODUCTION
This paper discusses the concept of expected %Complete for project tasks compared
to an actually claimed percent complete. This paper provides additional information
on how to answer the question: “If I am on schedule, what %Complete should I be
at this point in time?”

The key to understanding percent complete is to recognize the value is a fraction. To

obtain a fraction, divide two numbers. This implies the user knows which two
numbers to divide: Bricks laid divided by total bricks required, wages paid divided by
total budget, work hours spent divided by total work hours estimated. Without
knowing which two numbers to divide, a percent complete is just a number for
misinterpretation by others. The real questions here are: “If I am on schedule, how
many bricks should I have laid? How many dollars in wages should I have paid? How
many work hours should have been expended? How much cost should I have
incurred?

2 BACKGROUND
Microsoft Project contains three measures of “%Complete.” Many users of the
software want to know: If they are on schedule, what should be the values
calculated by each of the measures of percent complete. The three measures are:
Percent Complete (%Complete), Percent Work Complete (%Work Complete), and
Physical % Complete. Additional measures are available if created by the user.

3 PERCENT COMPLETE CALCUATION TYPES

3.1 Percent Complete
The default method of measuring “%Complete” by Microsoft Project is based on
activity duration, a 10 day activity is 40% Complete at the close of business on the
4th work day from the start date of the activity. Microsoft Project will indicate this
task is “late” if the value for %Complete is less than the expected value as of the
status date. This information is viewable in either the status indicator or status
columns.

The formula to roll up %Complete for summary level tasks, and eventually the entire
project is given by:

∑
i =tasks
i =1
%Completei * Durationi
i = task

∑ Duration
i =1
i

Equation 1 Duration Based Percent Complete

Using Figure 1 below, Microsoft Project will calculate the percent complete as
follows:

Page 4 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems

Figure 1 Percent Complete Screen Shot

%Complete = [(50%*(5 days) + 30%*(7 days) + 65%*(3 days)]/(5 days + 7 days + 3 days)

%Complete = 43.6667%

Microsoft Project will round to the nearest whole integer, 44%.

3.2 Percent Work Complete

Percent complete based on the number of work hours completed is another measure
calculated by Microsoft Project. For this purpose, two resources will be loaded to the
tasks with work as shown.

Figure 2 Resource Sheet

Figure 3 Resource Loaded Schedule Showing Total Work

The man-hour profile for work-hours is loaded with the majority of the work in the
first three days:

Figure 4 Work Profile (Task Usage View)

Percent work complete is calculated in accordance with an effort based weighting:

Page 5 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems

i =tasks

∑ %WorkComplete *Work i i
%WorkComplete = i =1
i =tasks

∑Work
i =1
i

Equation 2 Percent Work Complete

Using the same percentages as the original example, the calculation becomes:

%Work Complete= [50%*(40) + 30%*(14) + 65%*(12)]/[40+14+12]

%Work Complete=48.48%
Microsoft Project confirms the calculation:

Figure 5 Percent Work Complete Screen Shot

3.3 Physical Percent Complete

When the desired unit of measure is Physical Percent Complete, a baseline is
required and the calculation is based on the dollar costs involved. In this case, the
cost of the project is spread as shown below:

Figure 6 Costed Resource Loading

For the moment, the Project Status Date will be set far into the future, past the last
day of the project. In this case, although any measure of the three measures of
percent complete should indicate a behind schedule condition, the Physical %
Complete appears in the figure below:

Page 6 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems

Figure 7 Physical Percent Complete Screen Shot

The equation used by Microsoft Project depends on the dollar value of the work
budgeted and is shown below:

∑
i =tasks
Physical %Completei * BACi
Physical %Complete = i =1
i =tasks

∑ BAC
i =1
i

Equation 3 Physical Percent Complete

Note: BAC is Budget At Complete, Total Cost, or Baseline Cost.

The details of the calculation are:

Physical Percent Complete = [50%($3,440)+30%($1204)+65%(228)]/[$4872]

Physical Percent Complete = 46%

Further analysis regarding calculation of Physical % Complete is in a later section of

this paper. Specifically, what happens when the status date is before or during the
period of performance of tasks?

4 ANALYSIS
The previous section describes three methods of determining percent complete. In
each case, the value claimed for percent complete was numerically identical for the
three discrete tasks (50%, 30% and 65%, respectively). The weighted value of the
calculation produced a different result in each case at the summary level. In the
example, the values are close. However, consider what might happen if there was a
large discrepancy in the amount of work assigned to the tasks, or, if the costs of the
resources were vastly different from each other.

Further, the measures of percent complete (Duration, Work, and Physical) all
represent different things. Although the possibility does exist that the measures
could theoretically be equal under certain circumstances, the general case is the
measures will not be numerically equal.

4.1 Expected Percent Complete

The idea behind expected percent complete is that workers will produce value of 1
hour of work at the same rate assumed when the project was first bid. That is, a
brick layer can lay 80 bricks an hour, or, a software coder can write 80 lines of

Page 7 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems
perfect code per day, a technical writer will complete 10% of the document each day
for 10 days, etc. Assuming the work equals the bid rate as fact, then the percent
complete profile for each method described above can be calculated by dividing the
amount of production (in dollars, hours, or duration) by the appropriate unit for
total. Total work is 66 hours, total duration is 7 days, and total cost is $4,872.
These measures will produce the percent completes in the table below:

Table 1 Expected Values of Percent Complete

Physical
Method %Work
%Complete %
vs. Day Complete
Complete

1 20 21 19

2 40 42 39

3 60 64 58

4 73 79 75

5 87 94 93

6 93 97 96

7 100 100 100

Graphically the results are different for each method of percent complete as a
function of time:

Page 8 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems
120

100

80

%Complete
60
%Work Complete
Physical % Complete
40

20

0
1 2 3 4 5 6 7

Figure 8 Graphical Illustrations of Three Methods of Percent Complete

4.2 Duration Analysis (Detail Tasks)

By definition, the duration percent complete of a task grows only with the passage of
time and is always on schedule. Since it is duration based measurement, unless the
task has not started and needs to be delayed, the user should always claim the tasks
to be “as scheduled” when updating the duration based percent complete. Where the
calculation will vary is when the user makes updates to the remaining work and
remaining duration fields. This author has no understanding of the concept a user
can claim any duration based %complete and then provide remaining duration
estimates that are inconsistent with the percent complete provided. In the case of
duration based percent complete, it is best to adjust remaining duration (or
remaining work) and let the software calculate the percent complete. If the user
provides remaining work or duration greater than calculated by the program, the
%Complete value will regress. If the user revises the remaining duration/work to be
less than calculated by the program, the %Complete increases. Depending on the
reporting rules in place on the program, it may be a violation of reporting rules to
allow the percent complete to decrease from a prior reporting period.

If an estimator believes it will take three days to paint 24 feet of fence, the planned
profile will be 33% per day. If at the end of the first day the painter claims four
additional days are needed, then the job is 1/5 = 20% duration complete (1 day of
actual duration, 4 days of remaining duration).

What the user really wants to know is how the 20% complete calculation compares
to the 33% complete that is expected (the baseline). Indeed, what duration percent
complete is expected for the purpose of this comparison as of the status date (end of
day 1)? The answer is 33% and the painter met 20/33=61% of expectation (39%
behind schedule).

Page 9 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems
What can be done in a case such as this? If the task is originally assigned as fixed
units with one resource, then management needs to decide how to recover from the
given situation. There appears to be several choices:

• Clearly the painter has re-estimated the job, perhaps because of

increased intelligence gained during the performance of Day1. The
painter provides a revised estimate to complete so the cost of the job
increases (from 24 hours to 40 hours). Management must decide if the
additional cost will be absorbed or passed on to the customer, and, if
the additional duration is acceptable to all the stakeholders.

• Substitute a resource and assign the task to someone who can

complete the job within the remaining baseline duration (2 remaining
days). This may change costs.

• Change technology, perhaps the painter can use spray equipment as

opposed to a brush and complete on the baseline finish day (Day 3).
This may also impact cost.

• Man-load the task with additional workers (perhaps at increased cost)

to save the baseline duration, or at least compress duration. It is a
matter for management to reconcile estimating practices that 24 man-
hours were estimated for the job when the performing organization
provided a higher estimate once the task was underway and more
information was known. Of course this scenario that sooner or later
the maximum amount of productive resources will have already been
added to the job and adding more resources will decrease productivity.

It is necessary to understand what the expected percent complete represents and

how that should be compared to the percent complete column. The variables for
consideration are:

Table 2 Variables in Play

Forecast Data Baseline Data

Start Baseline Start

Duration Baseline Finish

Finish Baseline Duration

4.2.1 Business Rules for Consideration

To calculate an expected percent complete, rules must be in place to determine
when the duration will start and finish as an expectation; generally these are
baseline data. This will establish the “expected total duration” which becomes the
denominator in Equation 4. Additionally, the expected start date becomes the

Page 10 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems
defining item to anchor the expected total duration. Objectively, these dates are the
baseline start and baseline duration; this is not always the case.
Desired DurationToDate
Expected %Comeple = 100 *
ExpectedTotalDuration
Equation 4 Notional Equation for Expected Duration Percent Complete

It is reasonable to assume the task will start no later than [Baseline Start] as long as
the schedule logic yields [Start]≤ [Baseline Start]. If the task starts on the baseline
start date, the expected and desired duration to date will match. As a practical
matter, a task performer is accountable to start and finish on the baseline or forecast
dates. It is somewhat unreasonable to hold the task performer accountable for a
forecast start and a baseline finish; indeed the forecast start may already be later
than the baseline finish date due to other circumstances.

The business rules to determine expected percent complete must fairly address the
situation where schedule logic precludes starting a task on the baseline start date. If
expected percent complete accrues prior to any possible start date, the task
manager is being unfairly penalized.

In a similar matter, a forecast start may be well in advance of the baseline start;
holding the task performer to the baseline finish date will unnecessarily consume
available slack where the performer should be performing to the baseline duration
with a corresponding early finish compared to baseline finish.

To properly establish expectations, business rules must state if task performers are
accountable to baseline or current forecast dates. It is unreasonable to calculate an
expected % complete greater than 0% when incomplete predecessors preclude
starting the task when expected by the baseline. To do so will cause expected
percentage calculations to accrue for future tasks compounding the effect. This paper
will assume task performance is accountable to baseline dates or forecast dates, not
a mixture of the two types.

The table below establishes criteria for Desired Duration and Expected Total Duration
that can be substituted into Equation 1 to establish an expected duration complete
for detail level tasks. The table assumes measurement is in reference to the [Status
Date]. If reference is needed to the current date, replace [Status Date] with Now().

Page 11 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems
Table 3 Formulas for Expected Duration Complete for Detail Tasks

Desired Duration to Status Date for Expected Total

Detail Tasks Duration
IIf([Baseline Start]<=[Status Date],IIf([Status
Date]>=[Baseline Finish],[Baseline Duration],
Baseline Dates [Baseline Duration]
ProjDateDiff([Baseline Start],[Status Date])),0)/[Minutes
Per Day]
IIf([Start]<=[Status Date],IIf([Status Date]>= [Finish],
Forecast Dates [Duration],ProjDateDiff([Start],[Status Date])),0)/ [Duration]
[Minutes Per Day]

The formulas for desired duration must address:

4.2.1.1 Expected Start Date After Status Date

If the task expected start, either baseline start or forecast start, is to the right of the
status date, the desired duration complete is 0.
4.2.1.2 Expected Start Date Prior to Status Date

If the expected start date is no later than the status date, some measure of desired
duration exists. The formula must address that the desired duration can not be
greater than the total expected duration, otherwise the calculation in Equation 4 will
produce a value greater than 100%.
4.2.1.3 Calculation of Expected Duration Percent Complete – Detail Tasks

4.2.1.3.1 Expected Duration Calculation

Insert a column (custom number, or duration, text will not work) into the MS Project
file and rename the field “Desired Duration.” The formula is entered from the
Tools/Customize/Fields dialog box, see Figure 9.

Page 12 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems

Figure 9 Customizing Fields

Select the desired field and then Formula, see Figure 10:

Figure 10 Customize Fields Dialog Box

The formula for this column is from the left side of Table 3 consistent with the
desired baseline or forecast values. It is extremely important the summary task rows

Page 13 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems
are calculated using the “sum” as shown in Figure 10. The formula is entered by
clicking the Formula command button in Figure 10, see Figure 11 below:

Figure 11 Formula Definition for Custom Desired Duration Column

4.2.1.3.2 Expected Total Duration Calculation

Repeat the same steps for Desired Duration (immediately above), with the exception
being the formula. The formula will come from the right side of Table 3 and will be
either [Baseline Duration] or [Duration] to be consistent with the Desired Duration
column.
4.2.1.3.3 Calculation of Expected Duration Percent Complete

Add a third custom, this time use a custom number column, Number1 through
Number10. This column will provide the desired calculation and will address issues
created by summary tasks. Since the summary tasks for the components of this
calculation have already been addressed with the “sum” function, it will be possible
to use the formula for summary tasks as well.
It will be necessary to consider 0 duration tasks, tasks after the status date and
formatting of the calculation to contain no decimal places. This can be accomplished
with:
Format(100*(IIf([Start]>[Status Date],0,IIf([Duration2]=0,1,[Duration1]/[Duration2]))),0)
Note: substitute [Baseline Start] if measuring against baseline dates

The formula dialog for the custom column is shown in Figure 12 and Figure 13.

Page 14 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems

Figure 12 Custom Field Dialog Box for Expected Duration % Complete Calculation

Figure 13 Formula Dialog Box for Expected Duration % Complete

Page 15 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems
4.2.2 Calculation of Duration Expected Percent Complete – Summary Tasks
The summary task calculations must be duration weighted in accordance with
Equation 1. The formula requires summation of individual data elements for both the
numerator and denominator in the equation. This is easily accomplished using
custom text fields (or number fields) and formulas described above.

4.2.3 Expected Duration Complete Calculation – Alternate Method

If the schedule is properly resource loaded, and the resources contain reasonable
cost figures, use the Physical % Complete method of section 4.4 and its
subparagraphs.

If there are no resources or costs in the schedule, an alternative to calculate

expected duration complete is available by creating a resource (“Worker1”) with a
work rate of $1/hr and assign that resource to every detail task on the project. Then
set a baseline. At this point, use the Physical % Complete method of 4.4 since the
one resource assigned to each task will be level loaded across the task duration.

4.2.4 Duration Based Percent Complete Conclusion

Again, I am not certain any of these calculations truly make sense. The duration %
complete progresses with time and can never be insufficient or overstated. The value
can logically only be the measure of duration into the total task duration to the
status date divided by the task total duration (either baseline or forecast). When
Microsoft Office Project calculates %Complete, it uses the forecast dates for the
measurement. The %Complete value should not be entered by the user, it should be
calculated by the program after the user enters remaining duration or remaining
work (if the task is not fixed duration). If a user believes they are any (duration) %
complete other than as calculated by the program, they are not providing a duration
based % complete; more than likely, the user is providing an estimate of % Work
complete.

4.3 Work Analysis (Detail Tasks)

*** This section is in work ***

The percent work complete is a measure of how many hours are spent on a task in
comparison to what was bid to complete the task. However this is not necessarily
the case because it assumes 1 hour of effort will produce the same work product that
the person estimating the task believes the effort should take. If an estimator
believes it should take 24 hours to paint 24 feet of fence, then at the end of one day
there should be eight feet of fence painted. A problem occurs if the painter reports
at the end of the day that only six feet of fence were painted and it will take at least
32 more hours (4 days) to complete the job. At this point, the job is now 8/40=
20% Work Complete based on the latest revised estimate.

Page 16 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems
However, this is a measure of work hours to complete the job. There may be
additional detail available. For example, the painter may have painted 16 feet of
fence the first day and only have 8 feet of fence remaining. If the remaining 8 feet
requires substantial preparation work, or, might be located in rough terrain, then the
job may be physically 66% complete but still require more man-hours that what
might be originally estimated.

To establish an expected Work%Complete, the notional formula of Equation 5

applies:

ExpectedWorkToDate
ExpectedWork %Complete = 100 *
ExpectedTotalWork

Equation 5 Notional Equation for Expected Work Percent Complete

As before, business rules will dictate of the task manager is accountable for baseline
work to the status date, or, forecast work to the status date. In either case, the
resultant test addresses comparison of the date fields against a status date.

4.4 Cost Based Percent Complete (Physical % Complete) (Detail Tasks)

Once a resource loaded schedule (with resource costs) is established with a baseline,
the expected Physical %Complete at any status date is given by:
BudgetedCostOfWorkScheduled
ExpectedPhysical %Complete = 100 *
BaselineCost
Equation 6 Expected Physical % Complete

In the event a user desires to use forecast dates and costs as opposed to baseline
dates, the formula can be adjusted by saving data to one of the spare baseline fields
(Basline1 through Baseline10) and using that field as the basis for Earned Value
calculations.

4.4.1 Using Physical Percent Complete for Calculations

Progress calculations and variances are calculated by Microsoft Project using one of
two methods for Earned Value. Microsoft Project allows Earned Value calculations
based on Percent Complete or Physical Percent Complete. The default method for
Earned Value calculations is Percent Complete.

4.4.2 Changing Earned Value Methods

Although the exact details of properly displaying Physical%Complete are beyond the
scope of this paper, the essential parameters are addressed.

4.4.2.1 Change Earned Value Method for Tasks

Display all tasks in the schedule, for a single file this is normally accomplished from
the Formatting Toolbar, see Figure 14.

Page 17 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems

Figure 14 Showing All Tasks In The Schedule

Select the multiple task information icon from the Standard Toolbar, the Multiple
Task Information dialog box appears, see Figure 15. Once the dialog box is open,
select the advanced tab, and change the Earned Value Method to Physical Percent
Complete:

Figure 15 Multiple Task Information Dialog Box

Select OK to complete the change.

Page 18 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems
4.4.3 Saving A Baseline
From the main menu, select Tools/Tracking/Save Baseline (this is “Set Baseline” in
Project 2007), see Figure 16.

Figure 16 Saving a Baseline

4.4.4 Switching Baselines Used for Earned Value Calculations

From the main menu, select Tools/Options, see Figure 18.

Figure 17 Accessing Microsoft Project Options

Select the Calculations Tab, and then select Earned Value (see Figure 18):

Page 19 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems

Figure 18 Microsoft Project Options Dialog Box

When the Earned Value Dialog Box appears, select the appropriate baseline for
calculation of current forecast Earned Value parameters; in this example, Baseline1
is selected. Also verify Physical % Complete is selected as the default Earned Value
method. See Figure 19.

Figure 19 Earned Value Parameters Selection Dialog Box

4.4.5 Calculating Expected Physical % Complete

The expected Physical % Complete for any Baselinei is given by Equation 7; the
formula will also calculate correctly at summary task levels.
BCWS i
ExpectedPhysical %Completei = 100 *
BaselineCost i
Format([BCWS]/[Baselinei Cost],0)
Equation 7 Expected Physical % Complete Calculation for Baseline “i”

Place the formula in a spare number column, an example is shown in Figure 20

assuming use of Baseline1.

Page 20 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems

Figure 20 Formula for Expected Physical Percent Complete

5 STOPLIGHT INDICATORS (RED AMBER GREEN – RAG)

Once an expected and calculated percent complete are known, the next step in the
analysis is to determine if the variance between the expected and calculated percent
complete is cause for concern.

5.1 Stoplight Business Rules

The management team must decide how large a variance from expectations is cause
for monitoring (yellow), and how much additional variance is cause for more concern
(red). If tasks are reasonably on schedule and cost (green) there is no need for
action or reporting. Additionally, sometimes tasks are far ahead of schedule or far
below cost which is occasionally given a status of “blue.” Generally, the discussion on
cost is beyond the scope of this paper, but will be included as appropriate.

5.1.1 Business Rules Based on Percentage

It is generally accepted that there is no cause for concern if a project is progressing
with no more than a 10% schedule variance and a cost threshold of a specific dollar
amount; the figure varies widely even within the same industry. For purposes of
illustration assume:
Table 4 Schedule Variance Thresholds

Schedule Variance Condition

0-10% Green
10%-20% Yellow
More than 20% Red

Page 21 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems
Calculate the schedule variance by either subtracting the expected and calculated
values, or dividing them. For reasons that will become apparent later, the preference
is to divide the calculated and expected values in accordance with Equation 8.
Equation 8 Calculating Schedule Variance Index

CalculatedPercentComplete
ScheduleVarianceIndex =
ExpectedPercentComplete
Given the ratio of Equation 8, the following becomes apparent in Table 5:
Table 5 Interpreting Schedule Variance

Schedule Variance Condition Percent Ahead/Behind

Index
<1.0 Behind Schedule 100(1-Schedule Variance Index)
1.0 On Schedule 0%
>1.0 Ahead of Schedule 100(Schedule Variance Index – 1)

Combining the information in Table 4 and Table 5, each color coded icon indicates
the schedule variance conditions in Table 6:
Table 6 Schedule Variance Indicator Icons

Schedule Indicator
Variance Index Icon
>=0.90
0.8 to 0.90
<0.8

5.1.2 Business Rules Based on Duration

Percentage ahead/behind schedule is the most typical measure; it is also possible to
use absolute values. Use of absolute values no discouraged. A sample of duration
based schedule variances is illustrated in Table 7
Table 7 Second Type of Schedule Variance Indicators

Schedule Indicator
Variance Icon
1 day or less
2 to 5 days
More than 5
days
The duration based schedule variance can be in reference to either the start or finish
date and can be calculated with the date difference formula, such as Figure 21:

Page 22 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems
ProjDateDiff([Baseline Start],[Start])/[Minutes Per Day]

Figure 21 Duration Based Schedule Variance Calculation

The summary task formulas should be the same as for the individual tasks.

5.2 Inserting Indicator Icons on the Schedule

5.2.1 Indicator Icons for %Complete and %Work Complete
Assuming the calculated percent complete is in %Complete or %Work Complete, and
the expected percent complete is in Number1, then an indicator column must be
calculated and the icon set. Assuming the schedule variance index will be in
Number2, apply the formulas as needed for Number2 and the formula settings per
Figure 22:

%Complete/[Number1]
%Work Complete/[Number1]

Page 23 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems

Figure 22 Setup Parameters for Schedule Variance Calculation

The icon colors are set with guidelines in Figure 23. Note the order of the tests is
significant.

It is important to use a custom number field for this calculation, the text fields
produce errors on the border points (example: exactly 0.8 or 0.9).

Clear the check boxes for the summary rows and project summary row by selecting
each radio button and then clearing the check box.

If a test is necessary to establish a “blue” condition, such as at least 15% ahead of

schedule, this would be the first test and would be greater than or equal to 1.15.

Page 24 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems

Figure 23 Establish Icon Settings

5.2.2 Indicator Icons for Physical % Complete

As it turns out, Microsoft Project has a readily available calculation when using
Physical % Complete or properly applying Earned Value in Project 2003 or Project
2007; the field is the Schedule Performance Index [SPI]. The field is very similar to
the Schedule Variance Index that was created earlier. Note that the SPI is a real and
defined term in Earned Value calculation and is given by
EarnedValue BudgetedCostOfWorkPerformed ( BCWP)
[ SPI ] = =
PlannedValue BudgetedCostOfWorkScheduled ( BCWS )
Equation 9 Schedule Performance Index (SPI) Formula

The term “schedule variance index” is only used within the context of this paper for
clarity. The similarity between the schedule variance index and the Schedule
Performance Index (SPI) is quite obvious which is why the approach was taken to
produce an index rather than subtracting two values.

To color code the [SPI] apply the same criteria shown in Figure 23. Note the [SPI]
will calculate properly based on the specific baseline used for calculations as set in
Figure 19.

5.2.3 Indicator Icons for Absolute Duration Variance

Assuming the duration variance (in days) from Figure 21 is calculated and contained
in the Number1 field, the indicator Icons would be per Figure 24:

Page 25 of 26
©2008 CelerisSystems Telephone +1 714 813 4025
CelerisSystems

Figure 24 Indicator Icons for Absolute Schedule Variance Durations

Page 26 of 26
©2008 CelerisSystems Telephone +1 714 813 4025