Issue Identification and Appraisal
This is the final part in the series of Blogs regarding forecasting methods that can be applied to determine what the Estimate at Complete (EAC). This week’s blog poses the problem statement “Which forecasting method provides the best Estimate at Complete (EAC) accuracy?”
The feasible alternatives that were reviewed were:
- (Blogs 9 & 10) – IEAC methods outlined in the Guild of Project Controls Compendium and Reference (GPC) Module 4.4 ‘Assess, Prioritize and Quantify Risks/Opportunities’ & NDIA’s Guide to Managing Programs and Projects Using Predictive Measures.
- (Blog 11.1) MS Excel’s “Best Fit” regression analysis curve
- (Blog 12) Monte Carlo Simulation using @Risk
- (Blog 13) Monte Carlo Simulation using regular MS Excel (this was a late addition as research was showing options to perform same simulation without @Risk)
Each method was evaluated and Probability numbers developed for each method.
Develop the Outcomes for each
As mentioned above each of the feasible alternatives were evaluated and probability ‘P’ numbers were determined for each, let’s quickly recap each process used.
- IEAC used the ACWP, CPI and SPI data from the Week 7 report and provided a range of forecasts, which were then used to determine best, most likely and worst cases which allowed the determination of the mean, std. deviation and variance – all the information required to utilize the PERT formulas. The information was then input to a Normal Distribution curve and the probability figures determined.
- MS Excel’s best fit used the 7 data points from all the weekly reports and used all the available regression options to provide a forecast range, which again determined the best, most likely and worst cases, before following the same process as above (PERT results, Normal distribution and probability numbers).
- @Risk used the 7 data points and used the ARMA distribution to provide a range of forecasts, which allowed determination of the probability figures.
- MS Excel’s Monte Carlo simulation used the PMB figure of $49,795 and using a model assessed the mean and std. deviation, which was then input to the PERT formulas, normal distribution curve and provided the resultant probability numbers.
The criteria selection was from weekly report 7, and the following information was utilized; ACWP, BAC, CPI, SPI, weekly ACWP in the form of 7 data points.
Analysis and Comparison of the Alternatives
The results from each alternative are presented in from the evaluation of each methodology.
Table 1 – Results from Forecast Alternatives
For information purposes, table 2 provides the same tabulation but shows how each forecast has performed against the mean for each probability figure.
Table 2 – Results as a % above/below Mean
As noted in each of the blogs, except for the @Risk method, the variances were large and therefore a high “P” number should be considered, for the purposes of this exercise the selection of the P90 and P95 figures are suggested as the focal points for the accuracy of the forecasts, table 3 shows the figures.
Table 3 – Probability Figures used to determine most accurate forecast
To compare how the developed forecast figures, look against the current ACWP figures as of week 13, taking the W07 data point and placing the forecast on week 26 and connecting a straight line between the two points allows a visual comparison of which forecast is trending close to the data points to date. The first set of data points for the P90 probability is plotted on a chart with the week 13 information.
Figure 1 – P90 Forecasts vs Week 13 ACWP (Full version)
To better see the comparison, a zoom-in view from week 7 through week 15 is shown on figure 2.
Figure 2 – P90 Forecasts vs Week 13 ACWP (Zoom-in)
For the P90 comparison it appears that the MS Excel Monte Carlo method is tracking close to the new data points from week 8 to 13. Let’s look at the P95 comparison figures.
Figure 3 – P95 Forecasts vs Week 13 ACWP (Full version)
And zooming in to better see the comparison.
Figure 4 – P95 Forecasts vs Week 13 ACWP (Zoom-in)
Again, the comparison appears to be closely following the new data points and it’s the same methodology, MS Excel Monte Carlo method.
Selection of Preferred Alternative
Now that the analysis of the 4 alternatives has been completed, and the results have been tabulated and plotted the P90 and P95 probability figures against the latest information from week 13 to provide a live comparison. The final selection will use Multi-Attribute Decision Making technique to provide the most accurate method at this stage. Remember, this is a forecast and unforeseen risks can occur that can change the current perspective, but at present this there are no signs that such an issue will occur.
The Feasible alternatives are already identified, all that is required is to identify the attributes; i) Ease of use of the forecasting method, ii) Average of the forecasts vs the PMB as a %, and iii) forecast performance against new data points. Table 4 shows the results of the MADM, along with the ranking of each method.
Table 4 – MADM results for the Forecasting methods
For this exercise, it appears that MS Excel Monte Carlo simulation method has provided the most accurate results, although the IEAC formulas were very close also.
Monitoring Post Evaluation Performance
As previously mentioned in the earlier blogs, there is no right or wrong forecasting method, and although one feasible alternative out ranked the others this time, there is no guarantee that will be the same the next time. Projects are dynamic, and forecasting methods vary, it is recommended to review all feasible alternatives on a regular basis, determining any appearing trends, 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.
- 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
- Guild of Project Controls. (2015, October 3). 08.9.3.3 Monte Carlo Simulation – Guild of project controls compendium and reference (CaR) | Project Controls – planning, scheduling, cost management and forensic analysis (Planning Planet). Retrieved August 13, 2017, from http://www.planningplanet.com/guild/gpccar/conducting-a-cost-risk-analysis
- 5 Project performance forecasting – Guild of project controls compendium and reference (CaR) | Project Controls – planning, scheduling, cost management and forensic analysis (Planning Planet). (2015, October 3). Retrieved July 20, 2017, from http://www.planningplanet.com/guild/gpccar/project-performance-forecasting
- 3.3.04 Force Field or SWOT Analysis – Guild of project controls compendium and reference (CaR) | Project Controls – planning, scheduling, cost management and forensic analysis (Planning Planet). (2015, October 3). Retrieved July 10, 2017 from http://www.planningplanet.com/guild/gpccar/identify-risks-opportunities
- 3.3.7 Multi-Attribute Decision Making – Guild of project controls compendium and reference (CaR) | Project Controls – planning, scheduling, cost management and forensic analysis (Planning Planet). (2015, October 3). Retrieved August 17, 2017 from http://www.planningplanet.com/guild/gpccar/managing-change-the-owners-perspective
- National Defense Industrial Association. (2014). A Guide to managing programs using predictive measures. Author.
- Verschuuren, G. (2013, December 19). Monte Carlo Simulations in Excel[Video file]. Retrieved from https://www.youtube.com/watch?v=UeGncSFijUM&t=8s
- @Risk [Computer program] v7.5. 798 Cascadilla St, Ithaca, NY 14850, USA: Palisade Corporation (2016).
- Bergeron, E. (n.d.). Standard normal probabilities. Retrieved from http://www.stat.ufl.edu/~athienit/Tables/Ztable.pdf
- Theyerl, P. (2013, February 28). Excel – Multiple and varied trendlines [Video file]. Retrieved from https://www.youtube.com/watch?v=dsJnsuoVfA8
- (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
- Kyd, C. (2014). How to create normal curves with shaded areas in new excel. Retrieved from http://www.exceluser.com/excel_dashboards/normal-curve-new-excel.htm