- Problem Definition

After last week blog discussed about cost estimate calculation for offshore regasification facility project using historical data, and then this week according to Dr. Paul Giammalvo suggestion, price forecast method will be used to predict project cost within the next 5 years.

Still using the same indicative price last week, this week the indicative price for offshore regasification facility project with capacity 100 MMSCFD will be forecasting using MS Excel “Best Fit” Regression Analysis Curve.

This calculation can be using as an input value in financial economic model. With this method, we can calculate cash flow prediction project value within a certain period in the future.

- Identify the Possible Alternative

Using last week indicative price, then capex value for offshore regasification facility project, as follow:

Table 1. Indicative Price

From the table above, then to analyze price forecasts for next 5 years will use:

- MS Excel “Best Fit” Linear Regression Analysis Curve
- MS Excel “Best Fit” Polynomial Regression Analysis Curve
- MS Excel “Best Fit” Logarithmic Regression Analysis Curve

- Development of The Outcome for Alternative

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

Picture 1. Input Data

Using these input data (indicative price 2015-2017) and MS Excel “Best Fit” Linear Regression Analysis Curve, then trendline and trending them out to 5 years provide in picture (2) below. While the trendline use R^{2} = 0.9948.

Picture 2. Linear Trendline

Then still using data input in 2015-2017, now MS Excel “Best Fit” Polynomial Regression Analysis Curve with R^{2} = 1 will be used in the second analysis. The result of the polynominal regression analysis can be seen in the picture (3) below.

Picture 3. Polynominal Trendline

The latest, on the third data input in 2015-2017 analysis will use MS Excel “Best Fit” Logarithmic Regression Analysis Curve with R^{2} = 0.9941. The result of the logarithmic regression analysis can be seen in the picture (4) below.

Picture 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.

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

- Selection Criteria

Further, value of all treadline for the fifth year, which is 2021, 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”.

- Analysis & Comparison of Alternative

The following is data to be used for PERT calculation

Table 2. Trendline Forecasts of Offshore Regasification Facility Project

From the table above, we can see

a. Best case (optimistic) = $ 114.2

b. Most Likely case = $ 123.5

c. Worst case (pessimistic) = $ 124.2

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

Step 1 – PERT weighted Mean

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

= $ ((114.2) + (4 x 123.5) + (124.2))/6

= $ 732.4/6

= $ 122.1

Step 2 – Standard Deviation

= (Largest Value – Smallest Value)/6

= $ (124.2 – 114.2)/6

= $ 10/6

= $ 1.67

Step 3 – Variance

= Sigma/Standard Deviation^2

= $ 1.67^2

= $ 2.8

The following picture (6) below shows normal distribution curve:

Picture 6. Normal Distribution Curve

From the step 3, there is small variance means that the risk was small, so there is no need high contingency to cover the risk. Hence, P(75) will be considered to being calculate for the indicative price .

Picture7. P(75) Distribution Curve

The following above is P(75) cost estimate offshore regasification facility project in 2021 with value $ 123.36.

- Selection of the Preferred Alternative

This blog displays one of method in determining price forecast, on next week 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 offshore regasification facility project.

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

- Planning Planet (2017).
*Creating The Owners Cost Estimate (Top Down).*

Retrieved from http://www.planningplanet.com/guild/gpccar/creating-the-owners-cost-estimate

- Sullivan, G. W. (2014). Engineering Economy 16
^{th}Chapter 3 – Cost-Estimation Techniques, pp. 113-121. - (2017).
*W11.1_SJP_Forecasts Part 3*.

Retrieved from https://js-pag-cert-2017.com/w11-1_sjp_forecasts-part-3/

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

** **

AWESOME Bu Irene!!! WOW, now you’ve done a truly PROFESSIONAL job of cost estimating….

You’ve not only calculated the cost using FUTURE money, but you provided your management with a RANGE of options and you have produced a SINGLE FIGURE and with the SINGLE FIGURE you have attached a PROBABILITY.

It just doesn’t get any better than this, Bu……

NOW if you really want, for future blog postings, I would hope that you use the other methods Steve showed in his paper? See which of those models works best in your situation?

Too bad that you can’t get more data points but once you get the model set up then you can start to build more data points and REFINE the model…..

Keep up the great work but PLEASE try to catch up with your blogs and your paper!!!! You are now 3 weeks late on your second draft….

BR,

Dr. PDG, Jakarta, Indonesia