## W11_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 / 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 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

and

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

= 2,795.33^2

= 7,813,888

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.

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