Objective:
This method describes the steps to establish a “Planned %
Complete” metric using MS Project, to derive the Planned Value
(PV) calculation for purposes of basic Earned Value Management.
Background:
MS Project provides capabilities to calculate standard earned value
(EV) metrics, Budgeted Cost of Work Performed (BCWP or EV), Budgeted
Cost of Work Scheduled (BCWS or PV) and associated derived metrics,
however the method natively employed assumes a pure cost basis for
presenting related calculations (e.g. cost-loaded resources). While
traditional earned value management considers schedule and cost
performance in terms of budgeted dollars, this is not entirely necessary
as hours, days, units of work, or some other “budget” unit
may be preferred.
Additionally, MS Project’s cost-loaded method for calculating EV
and PV may present problems when performing earned value calculations
for fixed-price, deliverables-based projects where deliverable costs are
not derived from a pure “bottoms-up” rollup of resource
costs and planned effort (e.g. project deliverable costs may be
allocated across the project budget according to cash flow requirements.
A $100,000 project may consist of two deliverables priced at $50,000
each; the cost basis of resources and effort however may not rollup
proportionately to each of the deliverables).
To establish the BCWP (EV), MS Project employs a calculation of
“% Complete” (or “Physical % Complete” if
selected) multiplied by the “Baseline Cost” of a task.
Similarly, to establish the BCWS (PV), MS Project employs a calculation
of an internal “Planned % Complete” multiplied by the
Baseline Cost of a task. Unfortunately, the “Planned %
Complete” formula used by MS Project is not available for end use.
A workaround to derive “Planned % Complete” may entail
dividing BCWS by the Baseline Cost if desired. However, as mentioned
previously, this poses difficulty if the Baseline Cost is not derived by
a pure roll-up of costs loaded into MS Project. In our earlier example,
a $50,000 deliverable may be 50% complete, but this does not represent
$25,000 of planned effort.
To work around this issue and to provide a stable means for calculating
“Planned % Complete” with respect to the Planned Value
metric which serves as the applicable correlative for the “%
Complete” entry/calculation used to establish the Earned Value
metric, the method discussed herein is suggested for consideration.
Method:
Validating the % Complete Calculation
Industry practices often employ the “% Complete” metric to
establish Earned Value. For example, 50 of 100 software modules have
been developed in a baselined 1000 hour/125 day/$100,000 project,
representing 50% of the task being complete. The Earned Value of this
task may be expressed as 50 modules, 500 hours, 62.5 days, or $50,000,
or simply 50%.
MS Project allows a user to either enter the “% Complete”
against the task (e.g. as a matter of expert opinion on the completeness
of the task or following an earning method such as the “50/50
rule”, etc), or MS Project will calculate the value as the ratio
of Actual Duration/Duration (which would be based on the number of days
in which the task was actually worked on).
Regardless of whether the user enters the % Complete or Actual Duration
for non-summary tasks, MS Project in turn calculates the “%
Complete” and “Actual Duration” for summary tasks
which are rolled up according to a weighted average based on the
calculation:
Actual Duration = Duration * % Complete
However, % Complete for Summary Tasks is first calculated based on a
“hidden” formula within Microsoft Project as follows:
% Complete(Summary Task) = Σ Actual Duration(Subtasks) / Σ
Duration(Subtasks)
The proof of this formula is developed by 1) creating a custom duration
column (e.g. Duration2 below) with a formula to display Actual Duration
and then selecting the rolled-up sum of Actual Duration for subtasks as
follows:
2) Next create a custom duration column (e.g. Duration3 below) with a
formula to display Duration and then select the rolled-up sum of
Duration for subtasks as follows:
3) And finally, create a custom number column (e.g. Number4 below) to
divide the value in the Duration2 column by the value in the Duration3
column. This equals percent complete at both the subtask and summary
task level. (Note the F9 key is used to “refresh” MS project
such that the custom number column recalculates summary values, which
will equal the summary “% Complete” values.)
Calculating “Planned % Complete”
Using the premise established above to show how MS Project calculates %
Complete for summary tasks, we can follow a similar method to establish
a “Planned % Complete” value to show what portion of the
baselined task should be complete which enables the calculation of
Planned Value.
To arrive at a “Planned % Complete” calculation which
effectively rolls up at the summary task level with the weighted average
of subtasks in kind to the method used to roll up the “%
Complete” of summary tasks, we must employ several custom formulas
and summations similar to what has been demonstrated thus far.
1) Create a column (Number6) to establish a “Planned %
Complete” for subtasks. The formula used here will not calculate a
value for summary tasks. Use the following:
IIf([Baseline Start]>[Status Date],0,IIf([Baseline Finish]<=[Status
Date],100,ProjDateDiff([Status Date],[Baseline
Start],"Standard")/ProjDateDiff([Baseline Finish],[Baseline
Start],"Standard")*100))
2) Create a custom duration (e.g. Duration10 below) to calculate the
Planned Duration as the product of the “Planned % Complete”
just calculated and the Baseline Duration. Be sure to select Rollup Sum
for calculation of summary rows (this is similar to how we summed Actual
Duration to be the numerator in the custom “% Complete”
calculation.
3) Next create a custom duration (Duration1) to sum the Baseline
Duration similar to how we summed Duration to calculate a custom %
Complete.
4) Finally, create a custom number (Number7 below) with a formula to
divide Planned Duration by Baseline Duration (Duration10/Duration1
below). Select to use this formula for summary tasks. This results in a
“Planned % Complete” calculation that employs the same
method for calculating and rolling up planned percentages against the
baseline as MS Project uses to calculate and roll up actual “%
Complete” values.
Note that custom numbers can not be displayed as percentages in MS
Project, thus the value is multiplied by 100.
