Member
Hope anyone here can let me know the best of calculating PV in MS project 2007. I have created a project containing some custom number fields but am not able to calculate PV. Looking for a good formula to do the same. We calculate EV differently that project, but was hoping for some suggestions. Somebody please help. Many thanks.
Member
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.
Member
This is really interesting Bill!
Your post is exellent but I have a question regarding a related topic.
What if I need the planned % work complete instead of planned % complete. I need this to calculate Planned hours. Now i do this in Excel and paste it back in a text field in MSP. It is a very easy formula I need, but I cant seem to manage to do it in MSP. The formula is:
Planned hours = the sum of planned hours from project start to the status date.
Really hope there is someone who can answer
Posting Permissions
Reply With Quote
Bookmarks