W7_MFO_ Price Forecasts using Best Fit Curves On Pipeline Project

1. Problem Definition

In W6 blog posting comment, Dr Paul asked the author to take same case study for W7 blog posting using “Best Fit” curves to predict the 20 km pipeline project cost. So, the author want try to predict the cost of Polyethylene (PE) pipeline project in this W7 blog posting.

2. Identify the Possible Alternative

Using last week indicative price, then the cost for PE Pipeline project, as follow:

Table 1. Indicative Price of PE Pipeline

From the table above, then to analyze price forecasts for 20 km pipeline will use:

1. MS Excel “Best Fit” Linear Regression Analysis Curve
2. MS Excel “Best Fit” Polynomial Regression Analysis Curve
3. MS Excel “Best Fit” Logarithmic Regression Analysis Curve

3. Development of The Outcome for Alternative

These are the following an initial data plotting in determining price forecast:

Figure 1. Input Data

Using these input data and MS Excel “Best Fit” Linear Regression Analysis Curve, then trendline and trending them out to 20 Km provide in figure 2 below. While the trendline use R² = 0.9901.

Figure 2. Linear Trendline

Then still using data in table 1, now MS Excel “Best Fit” Polynomial Regression Analysis Curve with R² = 0.9945 will be used in the second analysis. The result of the polynominal regression analysis can be seen in the figure 3 below.

Figure 3. Polynominal Trendline

The latest, on the third data input in table 1 analysis will use MS Excel “Best Fit” Logarithmic Regression Analysis Curve with R² = 0.9063. The result of the logarithmic regression analysis can be seen in the figure 4 below.

Figure 4. Logarithmic Trendline

With the purpose to make it simple to see the results of the analysis, then bellow will be displayed plotting all three trendline in one chart.

Figure 5. All Trendline (Linear, Polynominal, Logarithmic)

4. Selection Criteria

Further, value of all treadline for PE Pipeline 20 km length, will be used, ranked and analyzed using PERT calculation. As for the smallest value represents “best case”, middle value represents “most likely” and the highest value represents “worst case”.

5. Analysis & Comparison of Alternative

The following is data to be used for PERT calculation

Table 2. Trendline Forecasts of PE Pipeline Project

From the table above, we can see

1. Best case (optimistic) = $ 1,029.13
2. Most Likely case = $ 1,417.30
3. Worst case (pessimistic) = $ 2,069.59

Using PERT calculation, then the Mean, Sd, and variance:

Step 1 – PERT weighted Mean

= ((Optimistic)+(4 x Most Likely)+(pessimistic))/6

= $ ((1,029.13) + (4 x 1,417.30) + (2,069.59))/6

= $ 1,461.32

Step 2 – Standard Deviation

= (Largest Value – Smallest Value)/6

= $ (2,069.59 – 1,029.13)/6

= $ 173.41

Step 3 – Variance

= Sigma/Standard Deviation^2

= $ 173.41^2

= $ 30,070.65

The following figure 6 below shows normal distribution curve:

Figure 6. Normal Distribution Curve

The result from the step 3 reveals that the very large variance means that the number is risky, so a higher P number needs to be considered when selecting one, hence for this blog, author use P90 refer to figure 7.

Figure 7. P(90) Distribution Curve

The following above is P(90) cost estimate 20 Km PE Pipeline project with value $ 1,683.29

6. Selection of the Preferred Alternative

This blog displays one of method in determining price forecast, on next blog another price forecast method will be applied. So in the last price forecast series, the best and optimum forecast method will be chosen to be applied in part of financial economic model for pipeline project.

7. Performance Monitoring and The Post Evaluation of Result

Forecasting method very dependent on the amount of data used, so it will be better and optimal if forecasting calculations using updated and valid data. Therefore project character are dynamic and unique, preferably input data for price forecast is updated periodically as a continual process of checking, reviewing and monitoring.

Reference:

1. Planning Planet (2017). Creating The Owners Cost Estimate (Top Down). Retrieved from http://www.planningplanet.com/guild/gpccar/creating-the-owners-cost-estimate
2. Sullivan, G. W. (2014). Engineering Economy 16^th Chapter 3 – Cost-Estimation Techniques, pp. 113-121.
3. (2017). W11.1_SJP_Forecasts Part 3. Retrieved from https://js-pag-cert-2017.com/w11-1_sjp_forecasts-part-3/
4. (2009). Excel Dynamic Chart #11: Dynamic Area Chart with IF Functioin – Normal Distribution Chart Statistics. Retrieved from https://www.youtube.com/watch?v=Fp1JV-ZVDZw
5. (2017). W6_AI_Price Forecact for Offshore Regasification Facility Project. Retrieved from http://emeraldaace2017.com/2017/09/10/w6_ai_price-forecasts-for-offshore-regasification-facility-project/
6. (2013). Normal curve using excel 2010. Retrieved from https://www.youtube.com/watch?v=hQHiG_cQiUE