Monday, October 8, 2012

W10_TRI_ Hot Rolled Coil Steel in Gold Equivalent Value

1.      Problem recognition, definition and evaluation

Steel is primarily used for major material in oil and gas projects. The volatility of steel price has therefore impacted actual projects cost overruns. Figure 1 shows the steel prices has shown significant volatility for the last 5 years benchmarked to Gold Price. Hence, the future cost estimation has to consider this positive trend of steel price and use a new alternative currency reference since US Dollar value has declined significantly in recent years as shown in Figure 2.
 
Figure 1. World Average Steel and Gold Price Jan 96 - Jul 12
Figure 2. US Dollar Index Chart: Jan 1973- Aug 2012

2.      Development of the feasible alternative

This will explore gold as alternative reference currency to be used in steel price forecast particularly in pipeline projects that install welded line pipe made from Hot Rolled Coil (HRC) and scrap steel. This chapter will develop model for HRC price.

3.      Development of the outcome

The objective is to convert and use HRC volume (tonne) into gold equivalent (ounce), so that HRC price will refer to gold price. The historical data of monthly average price of HRC steel in gold equivalent (tonne Billet/oz Gold) from Jan 1996 to July 2012 is presented in Figure 3.  
Figure 3. Monthly Average HRC Price in Gold Equivalent 

4.      Criteria of Selection

By applying a simple linear regression in Figure 2, the HRC-gold equivalent was steadily increasing as a function of time. However, S-squared of regression wasn’t high that means it didn’t closely fit to the data. Therefore, the other regression models are elaborated from January 2002 – July 2012 to jack up r-squared as presented in Table 1.
Table 1. HRC in Gold Equivalent (02-12)

5.      Analysis

In order to forecast the value for next 3 years (36 months), regression analysis is conducted by using trend line equations provided by Microsoft Excel as shown in Table 1. Figure 4 presents HRC-gold equivalent equation resulting polynomial 5th order formula with r-squared 0.8445.
 Figure 4. Regression Model for HRC-Gold Equivalent
As result of that, using observed data from January 2002 to July 2012 (127 data) the equation for forecasting HRC value in gold equivalent is displayed below, in which y is HRC-Gold Equivalent and x is monthly time unit 127 + n.
y = -9E-11x5 + 3E-08x4 - 5E-06x3 + 0.0007x2 - 0.0367x + 1.6116
Equation 1.  HRC-Gold Equivalent Forecasting Formula 2012-2016
As benchmark, Statictical Process Control (SPC) analysis is applied, since the data presented a steady condition  at 2009 onward.  Table 2 shows SPC paramaters for observed data. And then, the control chart is displayed in Figure 5. It shows that since 2009, HRC-gold equivalent has had statistically stability within the range  (UCL-LCL) using confidence level 95%.
Table 2. HRC-Gold Equivalent SPC Parameters

Figure 5. HRC-Gold Equivalent SPC Chart

6.      Selection of alternative

Compared to other models, the 5th order poly has the highest r-squared, and it is more conservative forecast model for 3 years onward that results relatively constant value in term of gold equivalent. However, the control chart is better in term of data matching and more comfortable to be used in forecast purpose since it allows plus/minus sigma as a value range. Therefore, the value of HRC steel for 3 years onward will be between 1.58 and 2.71 Tonne / gold ounce.

7.      Performance monitoring and post-evaluation of results

Relying US Dollar as solely reference currency in project estimation is not valid, since Purchasing Power of USD has deteriorated dramatically in recent years. Hence this creates a need to use an alternative currency with stable and reliable value as cost reference, namely Gold.  The control chart will be therefore used as reference chart in linking HRC price in gold equivalent. The updated chart shall be applied to check the chart validity and improve its reliability.

References:
·         Brassard, M and Ritter, D (2010), The Memory Jogger (2nd Edition),Canada, GOAL/QPC
·        Ming, T. (2001, Feb). An Introduction to SPC: SPC Tools - Control charts. Retrieved from: http://lorien.ncl.ac.uk/ming/spc/spc8.htm
·        Steel on the net. (2012, Sep 24). Historical Steel Price. Retrieved from: http://www.steelonthenet.com/pricing_history.php
·        US Federal Reserve. (2012, Sep 24). US Dollar Index. Retrieved from: http://www.federalreserve.gov/releases/h10/summary/indexn_m.htm
·         World Gold Council. (2012, Sep 24). Interactive gold price chart and downloads. Retrieved from: http://www.gold.org/download/value/stats/statistics/xls/gold_prices.xls

1 comment:

  1. Excellent, Pak Trian!!! Starting to show you are developing a very clear understanding of using gold equivalence to project into the future...

    Couple of comments. IF you want to get the R2 value up then you need to shorten the time frame.... DROP the data from months 0 to 50.

    Also, what you show in figure 5 is VERY good. Notice the very narrow band between the upper and lower control limits? Remember what a narrow band indicates? And there are no outliers, which is really good as well. Now according to Shewhart et al, yes, the process is out of control, but we know that already. Which is why we are writing this paper!!! Because the process is out of control, we can no longer use USD (or any other currency) as the basis for projecting costs into the future.

    Bottom line- you are closing in on the heart of your paper and once you complete the graphs, the rest is just telling the story....

    BR,
    Dr. PDG, Jakarta

    ReplyDelete