W9_HWB_Soybean in Gold Equivalent Value


1. Problem Definition

Indonesia is still importing 75% of the domestic soybean demand of 2.5 million tons. The high dependence on imported soybeans make the government was powerless when soybean prices rose due to the weakening of the rupiah against the U.S. dollar (U.S.).[1] As we know that soybean is the basic ingredients for tofu and tempe which are the most popular food here. Now I try to made 6 months future cost estimation of imported soybean price and use a new alternative currency reference with gold that consider as stable currency.

 2. Development of Feasible Alternatives

The predicted price will estimate by trendline from soybean price in oz gold value and by analyze SPC (Statistical Process Control).

3. Development Outcomes for Alternative

Below are the historical data of the average price of gold, exchange rate and soybean per month from Jan 2012 to Oct 2013:[2][3][4]


Here is a picture that shows the graph of data above.


4. Selection of Criteria

Convert the soybean price (Rp/gram) into gold equivalent (oz or troy-ounce), so that soybean price will refer to gold price. The historical data and its 6 months trendline of monthly average price of soybean in gold equivalent (gram soybean/oz Gold) from Jan 2012 to October 2013 (actual data) is presented in below.


From 6 trendline model which close to describe the forecast, below are the R-squared :


5. Analysis of the Alternatives

Using observed data from January 2012 to Oct 2013 (22 data) the equation for forecasting Soybean value in gold equivalent is displayed below, in which y is Soybean Equivalent and x is monthly time unit 22 + n.[6] Selection to the 4th order polynomial models based on the most moderate value among other models and also has quite large value of R-squared (0.8868).

y = 0.0587x4 – 2.7655x3 + 40.216x2 – 195.4x + 1750.8

Now, the SPC analysis show the result below :


So thus obtained control chart graph as below :

From control chart above, we can say that the data are out of control since the data are match one of the “out of control” criteria [5]. To make adjusted control chart, some data have to be taken out.

6. Selection of Preferred Alternative

But in this blog, 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 [6]. Therefore, the value of soybean for 6 months onward will be between 1214.65 and 1712.54 Gram / gold oz.

7. Performance Monitoring & Post Evaluation of Results

At this time, the value of the Rupiah continues to fall against the USD to make soybean import prices go up. If the price of imported soybean based on the price of gold, the price of soybeans will be relatively more stable. Control chart analysis could be made better by predict the price range in the coming months with daily data.

References:

[1] Pemerintah Tak Berdaya Hadapi Lonjakan Harga Kedelai. (n.d.). Retrieved from http://bisnis.liputan6.com/read/696045/pemerintah-tak-berdaya-hadapi-lonjakan-harga-kedelai

[2] London Fix Historical gold – result. (n.d.). Retrieved from http://www.kitco.com/scripts/hist_charts/yearly_graphs.plx

[3] default – Bank Sentral Republik Indonesia. (n.d.). Retrieved from http://www.bi.go.id/biweb/Templates/Moneter/Default_Kurs_ID.aspx?NRMODE=Published&NRNODEGUID=%7b3CE4C8F3-8793-458B-BC5B-A7DC189EF644%7d&NRORIGINALURL=%2fweb%2fid%2fMoneter%2fKurs%2bBank%2bIndonesia%2fKurs%2bTransaksi%2f&NRCACHEHINT=Guest

[4] Kementerian Perdagangan Republik Indonesia | Profil Ekonomi | Harga | Tabel Harga Kebutuhan Pokok Nasional. (n.d.). Retrieved from http://www.kemendag.go.id/id/economic-profile/prices/national-price-table?year=2013&month=10

[5] Brassard, M., Ritter, D., & GOAL/QPC (2010). The memory jogger 2: Tools for continuous improvement and effective planning (p. 62). Salem, N.H: Goal/QPC.

[6] MAHAKAM13: W10_TRI_ Hot Rolled Coil Steel in Gold Equivalent Value. (n.d.). Retrieved from http://aacemahakam.blogspot.com/2012/10/w10tri-hot-rolled-coil-steel-in-gold.html

Advertisements

3 Comments

Filed under Hari W, Week 09

3 responses to “W9_HWB_Soybean in Gold Equivalent Value

  1. Pak Hari, sorry but I am having a hard time understanding what you did here?

    Up until step 4, it looked like you are on the right track but why do I only see one projected curve and not three or even four of them? (See Trian’s paper, figure 13 on page 13)

    My suggestion would be to INCREASE your time frame to at least 2 years.

    And given your SPC analysis shows a PROCESS is out of control, why are you continuing to use this method if the data shows a process which is somehow broken? Again, MAYBE if you increased your sample size it would no longer show an out of control situation?

    Bottom line here, for your update, you need to start with the grams Soybeans/Ounces of Gold but extend the analysis to include at least 2 years of data. Then create three different scenarios, projected into the future 12 months, like Trian did in figure 13. Then based on the three scenarios, you can apply the PERT formula to determine what the P50, P75, P85 and P90 projected cost per gram of soybeans in in ounces of gold. THEN convert the ounces of gold BACK into dollars and rupiah for those same dates.

    Does this make sense to you? If you have any questions, I can only urge you to check with others who have done this successfully before you…. Let them HELP and GUIDE and MENTOR you…..

    BR,
    Dr. PDG, KL, Malaysia

    • Dear sir, i’m following what Trian did in his blog (i cite that), but however i will do longer my data as your advice.

      Rgds, hwb

      • Hi Hari,
        Something is not correct…….

        Better follow what Trian and Hari showed in their papers as published rather than their blog postings.

        There should be a minimum of THREE curves projecting costs into the future, not just one and to be candid, I think the 6th order polynomial curve is way too pessimistic to be real.

        By extending the period of historical data, you should be able to INCREASE the R squared value and find three curves which “look” to be more realistic given the data. Very important in using this tool that you can apply sound professional judgment in determining which projections are “rational” or justifiable if challenged by your management. 6th order polynomial does not look to me to be a realistic projection…… (Maybe a WORST CASE scenario but certainly not the only basis for making a cost projection.

        BR,
        Dr. PDG, KL, Malaysia

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s