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 / 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 selections will be used in the analysis in order to get a forecast spread to use the PERT analysis on:
- All 7 Data points
- First 4 Data points
- Last 4 Data points
- Middle 5 Data points
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 trendline facility and trend them out to week 26, provides the chart in figure 2. The trendline uses a formula of y = 2637.5x – 150 / R² = 0.9693
Figure 2 – Trendline using all 7 Data Points
To get additional trendlines the use of various data-points from the 7 available are required, these were noted in the criteria selection above. Using the first four data points and MS Excel’s trendline facility and trend them out to week 26, provides the chart in figure 3. The trendline uses a formula of y = 2847.5x – 525 / R² = 0.9739
Figure 3 – Trendline using first 4 Data Points
Using the last four data points and MS Excel’s trendline facility and trend them out to week 26, provides the chart in figure 4. The trendline uses a formula of y = 2075x + 2787.5 / R² = 0.9857
Figure 4 – Trendline using Last 4 Data Points
Using the middle five data points and MS Excel’s trendline facility and trend them out to week 26, provides the chart in figure 5. The trendline uses a formula of y = 2508.9x + 282.14 / R² = 0.9856
Figure 5 – Trendline using 5 Middle Data Points
Plotting all four trendlines on one chart (figure 6) reveals the range of the forecasts from the trendlines.
Figure 6 – All Trendlines on one Chart
This allows to visually see the “Best Case”, “Worst Case”, and by averaging the two middle trends the “Most Likely Case”. The forecast figures are shown in Table 2.
Table 2 – Trendline Forecasts
Therefore, the following values are assigned:
- “Best Case” (Optimistic) = $56,738
- “Worst Case” (Pessimistic) = $73,510
- “Most Likely Case” = $66,969
Using the PERT formula:
Step 1 – PERT weighted Mean
(Optimistic + (4 x Most Likely) + Pessimistic) / 6
= $ ((56,738 + (4 x 66,969) + 73,510) / 6)
= $ 398,124 / 6
= $ 66,354
Step 2 – Standard Deviation
(Largest value – Smallest value) / 6
= $(73,510 – 56,738) / 6
= $ 16,772 / 6
= $ 2,795.33
Step 3 – Variance
Variance = Sigma^2
Figure 7 shows the normal distribution curve with the ‘Mean’ value shown.
Figure 7 – Normal distribution curve showing ‘Mean’ value
With Sigma () = 2,795.33, the variance $7,813,888 divided by 2 = $3,906,944 which if divided by = +/- 1,398 . This means the shape of the curve is a lot lower than the curve on figure 7, 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 and P98, refer to figure 8.
Figure 8 – Normal distribution curve showing P75, P85, P90 & P98 values
So, for this forecasting exercise the following figures have been determined; P75 = $68,239 / P85 = $69,251 / P90 = $69,936 / P98 = $72,095.
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