Iklan

Estimasi dan Pemilihan Model dengan Excel (Seri 5 Peramalan)


Tulisan seri 5 peramalan ini akan memberikan tahapan estimasi model untuk metode peramalan sederhana, sekaligus cara memilih model terbaik dari model-model yang ada (sebagai pengantar pemahaman, silakan baca seri 1 – 4). Tetapi berbeda dengan tulisan pada seri 3, kali ini kita akan mencoba mengaplikasikannya pada fasilitas yang ada di Microsoft Office Excel 2003.

Sebagai latihan, misalnya kita punya data penjualan selama 10 tahun terakhir (1999 – 2008), secara berurut sebagai berikut: 2, 3, 6, 9, 10, 11, 14, 16, 18, 27 (angka-angkanya dibuat kecil hanya untuk menyederhanakan).

Buka program Excel, ketikkan angka tersebut pada worksheet Excel secara berurut pada satu kolom (untuk keseragaman, ketikan di range A2:A11). Angka tahun tidak perlu diinputkan.

Selanjutnya klik Insert > Chart. Akan muncul tampilan berikut:

16

Pada Chart type: pilih XY (Scatter), pada Chart sub-type klik gambar paling atas (yang berwarna hitam pada tampilan diatas). Kemudian klik Next, akan muncul tampilan berikut:

26

Pada Data range: isikan range dari data yang akan dianalisis. Misalnya dalam contoh data kita berada pada range A2:A11. Maka akan muncul tampilan berikut:

36

Klik Next, akan muncul tampilan berikut:

46

Isikan Chart title dengan judul grafik, Value (X) axis dengan Judul sumbu horizontal, Value (Y) axis dengan judul sumbu vertical. (Catatan: pilihan-pilihan Axes, Gridlines, Legend dan Data Labels bisa Sdr. coba utak-atik sesuai kebutuhan. Tidak dijelaskan disini untuk mempersingkat dan agar lebih focus pada pembahasan mengenai peramalan).

Selanjutnya klik Next, akan muncul tampilan berikut:

56

Klik Finish. Grafik akan muncul pada sheet Sdr. sebagai berikut:

66

Kemudian klik menu Chart yang ada menu bagian atas Excel, selanjutnya pilih Add Trendline. Akan muncul tampilan berikut:

76

Akan muncul enam pilihan trend/tipe regresi, yaitu Linear, Logaritmic, Polynomial, Power, Exponential, Moving Average. Jika anda memilih polynomial akan diminta memasukkan jumlah ordernya. Jika anda memilih moving average akan diminta memasukkan jumlah periodenya.

Persamaan model trend linear dan eksponensial sudah pernah kita bahas pada seri 2 tulisan ini.

Model trend logarithmic memiliki persamaan Yt = β0+β1lnT

Model trend power memiliki persamaan : Yt = β1T β0

Model Polynomial memiliki persamaan berikut: Yt = β0+β1T+β2T2….+β6T6

Jika kita memilih model polynomial, kita diminta memasukkan ordernya (pangkat tertinggi dari persamaan tersebut). Jika kita menginginkan trend kuadratik, masukkan angka 2, jika menginginkan trend kubik masukkan angka 3. Dalam Excel, order tertinggi yang bisa kita masukkan adalah order 6.

Pilihan Moving Average kita tinggalkan dulu, karena akan dibahas tersendiri dalam seri peramalan berikutnya.

Anda bisa mencoba-coba pilihan yang diinginkan.

Setelah memilih trend yang diinginkan, kemudian klik Options. Akan muncul tampilan berikut:

86

Isikan nama garis trendnya (default Excel adalah Series 1,2 dst. Jika ingin menggunakan nama sendiri sendiri, klik Custom dan ketik namanya). Conteng Set Intercept jika anda ingin menggunakan intercept dengan nilai tertentu atau nol. Abaikan ini, jika anda ingin menggunakan intercept sesuai perhitungan. Conteng Display equation on chart untuk menampilkan persamaan trend pada grafik. Conteng Display R-squared value on chart jika and ingin menampilkan nilai R-squared pada grafik.

Pada bagian forecast kita bisa isikan tahun/unit forecast trend kedepan (yang diforecast kedepan dimulai dari data tahun terakhir) atau forecast trend ke belakang (yang dimulai dari data tahun awal di tarik kebelakang).

Setelah itu, klik OK. Maka akan muncul tampilan grafik berikut:

96

Tampilan grafik di atas adalah contoh grafik ketika kita memilih model trend polynomial dengan order 2 (model trend kuadratik) dengan forecast kedepan untuk 20 tahun/unit.

Sayangnya, program Excel tidak memberikan perhitungan MAPE, MAD ataupun MSD seperti pada program Minitab yang telah kita bahas sebelumnya. Namun demikian, Program Excel memberikan perhitungan R2 untuk setiap model yang kita bangun. Karenanya kita bisa menggunakan ukuran R2 ini untuk memilih model trend yang terbaik. Secara umum, model trend terbaik adalah model dengan nilai R2 yang paling tinggi.

Selain itu, dalam output trendline Excel, forecast yang diberikan juga hanya dalam bentuk grafik dan tidak memunculkan nilai forecast. Oleh karenanya, kita harus menghitung sendiri dengan memanfaatkan persamaan model yang telah terbentuk.

Iklan

9 Tanggapan

  1. Pak, saya tanya terkait model hasil persamaan polynomial menggunakan metode estimasi apa? Koefisien persamaannya bisa dihitung dengan metode apa pak? Makasih.

  2. pak tolong dijelaskan apa maksud dari y=0,1553x^2+0,6917+1.8167
    dan R^2=0.956

  3. dari data-data yang saya dapatkan, setelah memilih trendline yang tepat ternyata didapatkan R yang paling besar adalah dengan trend polynomial. Uji statistik apa yang dilakukan pada polynomial ini, bagaimana caranya Pak. Tolong ya Pak….

    • lihat pada alamat web dibawah ini
      http://www.academia.edu/3724866/Peramalan_Trendline

  4. nice pak.. cmn sy test dgn uji statistik 7 nilai R dan model persamaannya beda pak.

    • jangan memakai scatter with only markers tapi pakai line with markers pada pembuatan grafik kamu…
      kalau memakai scatter with only markers persamaannya akan menjadi : y=o,…x^-….x+….
      kalau memakai line with markers persamaannya akan menjadi :
      y=0,…..x^+….x+…..

  5. Pak ,, saya mau tanya. apa mksud dari beberapa metode peramalan (misalnya single eksponensial smothing, dll ) kemudian bagaimana cara kita memilih metode yang tepat untuk peramalan? Mengapa harus memilih metode tersebut?
    yang terakhir saya ingin tahu pak contoh data apa yang menggunakan metode-metode tersebut??
    terima kasih ya pak sebelumnya,, saya sangat berharap bapak bisa membantu saya…

  6. pak tolong, apa sih hubungan peramalan dat dengan bentuk linear, kuadratik, dan ekponensial(pada data time series)

    Tidak ada hubungannya. Dalam peramalan, kita harus memilih salah satu bentuk, sesuai dengan kecenderungan data time series pada masa yang lalu

  7. Pak tolong dong dibahas mengenai uji kelinieran pada regresi, pake SPSS dan minitab ya Pak,

    seblumya
    thanks a lots
    Best regards

    Insya Allah, lain kali akan dibahas

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: