Issue Identification and Appraisal
In blogs 9 and 10, we commenced looking at several forecasting methods that can be applied determining what the Estimate at Complete (EAC) would be for the sample project data we are using. Unfortunately, last week’s blog provided some erroneous information, which Dr. Paul Giammalvo rightly pointed out that regardless of the method chosen analysis will rely on the PERT curve and Z Table to determine the desired ‘P’ level. And as the example last week was based on the IEAC methods the results need to be attributed to this also. The Z-table ‘P’ figures are provided below in the ‘Develop Outcomes’ section. This week’s blog continues with the problem statement “Other than IEAC, what other forecasting method could have been used to provide the Estimate at Complete (EAC) range for the weekly report?”
The feasible alternatives are:
- 6 IEAC methods outlined in the Guild of Project Controls Compendium and Reference (GPC) Module 4.4 ‘Assess, Prioritize and Quantify Risks/Opportunities’
- MS Excel’s “Best Fit” regression analysis curve
- Monte Carlo Simulation
Each method is being evaluated in separate blogs, and then a conclusion blog will finish up the series. This week will evaluate MS Excel’s “Best Fit” curve.
Develop the Outcomes for each
6 IEAC methods – Evaluated in blogs 9 & 10, and will form part of the final analysis in the final blog of the series. P75 = $50,961 / P85 = $51,187 / P90 = $51,340 / P95 = $51,566 / P98 = $51,821
MS Excel “Best Fit” Curves – Uses data set of values (ACWP data points from week 1 thru’ week 7) then plots the best fit line or curve through the data points to week 26 completion point in the future.
Monte Carlo Simulation – Requires @Risk software to perform the exercise. This option will be reviewed in a future blog in this series when the software is received.
To evaluate the “Best Fit” curve, there is a need to provide some data points, so the ACWP data points from week 1 through week 7 from the weekly report is going to be used.
Table 1 – ACWP Data Points
MS Excel uses data-points to produce trend lines, as the data points are limited the following regression methods will be used in the analysis to get a forecast spread to use the PERT analysis on:
- • Linear Regression
- • Polynomial Regression (2nd)
- • Logarithmic Regression
From the above trend line proposals, the highest value will represent the “Worst Case”, the lowest value will be the “Best Case”, and the two mid values will be averaged to provide “Most Likely”.
Analysis and Comparison of the Alternatives
Plotting the 7 data points in Table 1 using a scatter chart provides the following, see figure 2.
Figure 1 – Weekly Report Data Points
Using these 7 data points and MS Excel’s “Linear regression” trendline facility and trending them out to week 26, provides the chart in figure 2. The trendline uses R² = 0.985
Figure 2 – Linear Trendline
Again, using these 7 data points, but this time using MS Excel’s “Polynomial regression” trendline facility and trending them out to week 26, provides the chart in figure 3. The trendline uses R² = 0.9883
Figure 3 – Polynomial (2nd) Trendline
Again, using these 7 data points, but this time using MS Excel’s “Logarithmic regression” trendline facility and trending them out to week 26, provides the chart in figure 4. The trendline uses R² = 0.9369
Figure 4 – Logarithmic Trendline
Plotting all three trendlines on one chart (figure 5) reveals the range of the trendline forecasts from.
Figure 5 – All Trendlines on one Chart
This allows to visually see the “Best Case”, “Most Likely Case” and “Worst Case”, refer to Table 2 below.
Table 2 – Trendline Forecasts
Therefore, the following values are assigned:
- “Best Case” (Optimistic) = $26,000
- “Worst Case” (Pessimistic) = $67,000
- “Most Likely Case” = $43,000
Using the PERT formula:
Step 1 – PERT weighted Mean
(Optimistic + (4 x Most Likely) + Pessimistic) / 6
= $ ((26,000 + (4 x 43,000) + 67,000) / 6)
= $ 265,000 / 6
= $ 44,167
Step 2 – Standard Deviation
(Largest value – Smallest value) / 6
= $(67,000 – 26,000) / 6
= $ 41,000 / 6
= $ 6,833.33
Step 3 – Variance
Variance = Sigma^2
Figure 6 shows the normal distribution curve with the ‘Mean’ value shown.
Figure 6 – Normal distribution curve showing ‘Mean’ value
With Sigma () = 6,833.33, the variance $46,694,444 divided by 2 = $23,347,222 which if divided by = +/- 3,417 . This means the shape of the curve is a lot lower than the curve on figure 6, similar to the version shown in Blog 10.
Again, the result from Step 3 reveals that the very large variance means that the number is risky, so a higher P number (P90, P95, P98?) needs to be considered when selecting one, however for this blog we will show the P75, P85, P90, P95 and P98, refer to figure 7.
Figure 7 – Normal distribution curve showing P75, P85, P90, P95 & P98
So, for this forecasting exercise the following figures have been determined; P75 = $48,776 / P85 = $51,249 / P90 = $52,923 / P95 = $55,407 / P98 = $58,201.
Selection of Preferred Alternative
This blog is the third in a series of five, the last blog will compare the three alternatives from each blog and then select a preferred method.
Monitoring Post Evaluation Performance
No matter which forecasting method is finally decided upon, projects are dynamic, and forecasting methods vary, it is recommended to review the feasible alternatives on a regular basis, determining any appearing trends.
This is a continual process of checking, reviewing, and monitoring to ensure the correct method is being used to provide the most accurate result for management level decisions to be effective.
- • Bergeron, E. (n.d.). Standard normal probabilities. Retrieved from http://www.stat.ufl.edu/~athienit/Tables/Ztable.pdf
- • Guild of Project Controls. (2015, October 3). GUILD OF PROJECT CONTROLS COMPENDIUM and REFERENCE (CaR) | Project Controls – planning, scheduling, cost management and forensic analysis (Planning Planet). Retrieved from http://www.planningplanet.com/guild/gpccar/assess-prioritize-and-quantify-risks-opportunities
- • Theyerl, P. (2013, February 28). Excel – Multiple and varied trendlines [Video file]. Retrieved from https://www.youtube.com/watch?v=dsJnsuoVfA8
- • ExtendOffice. (2016). How to add best fit line/curve and formula in Excel? Retrieved from https://www.extendoffice.com/documents/excel/2642-excel-best-fit-line-curve-function.html#excel2013