W11.1_SJP_Forecasts Part 3

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?”

Feasible Alternatives

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.

Selection Criteria

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

and

  • “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

= $6,833.33^2

= $46,694,444

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.

References

  • • 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
 

One Reply to “W11.1_SJP_Forecasts Part 3”

  1. NICE WORK, Steve!!!! Now you seem to have it pretty well understood.

    All you to do now is generate the same analysis using simulation, compare the results and then draw your conclusions.

    I am truly looking forward to see what the outcomes are and what your analysis and conclusions will be…

    Keep up the really great work!!!

    BR,
    Dr. PDG, Jakarta

     

Leave a Reply

Your email address will not be published. Required fields are marked *