Simulasi Monte Carlo di Excel: Panduan Lengkap

Mengapa Menggunakan Excel untuk Simulasi Monte Carlo?

Setelah Anda memilih untuk menerapkan simulasi Monte Carlo, ada banyak alat yang dapat membantu, seperti Excel, Python, R, SAS, dan MATLAB.

Faktor terpenting yang perlu dipertimbangkan, terutama saat menerapkan simulasi Monte Carlo untuk pertama kali, adalah tingkat keakraban Anda dengan alat tersebut. Excel adalah salah satu alat yang paling banyak digunakan di dunia bisnis, yang berarti banyak orang sudah familiar dengan operasi dasarnya. Ini mengurangi waktu pelatihan dan menghilangkan kebutuhan untuk mempelajari perangkat lunak baru dari nol.

Excel juga menyediakan alat yang mudah digunakan untuk membuat bagan dan grafik, yang berguna untuk memvisualisasikan hasil simulasi. Selain itu, tersedia beberapa add-in yang kuat untuk Excel, yang meningkatkan kemampuannya dalam melakukan simulasi Monte Carlo yang kompleks.

Namun, perlu dicatat bahwa untuk simulasi yang lebih maju, terutama yang memerlukan penanganan kumpulan data besar atau menjalankan jumlah simulasi yang sangat tinggi, alat yang lebih khusus selain Excel mungkin lebih tepat.

Fungsi Excel Utama untuk Monte Carlo

Selanjutnya, kita akan membahas dua fungsi Excel penting: RAND() dan NORM.INV(), mencakup sintaks, parameter, dan kasus penggunaan tipikalnya. Fungsi-fungsi ini membantu menghasilkan angka acak dan mendefinisikan distribusi probabilitas, yang merupakan aspek mendasar dari setiap simulasi.

Fungsi RAND()

RAND() menghasilkan angka acak lebih besar atau sama dengan 0 dan kurang dari 1. Angka-angka tersebut didistribusikan secara seragam, artinya setiap angka dalam rentang yang ditentukan memiliki kemungkinan yang sama untuk muncul.

Sintaks untuk RAND() adalah sebagai berikut:

RAND()

Fungsi RAND() tidak memerlukan argumen apa pun. Cukup digunakan sebagai RAND().

Dalam konteks Simulasi Monte Carlo, RAND() dapat digunakan untuk mensimulasikan terjadinya peristiwa acak atau untuk memvariasikan input ke dalam model Anda.

Fungsi NORM.INV()

Sementara RAND() menghasilkan angka acak seragam, NORM.INV() digunakan untuk menghasilkan angka acak dari distribusi normal, yang merupakan kebutuhan umum dalam Simulasi Monte Carlo. Fungsi ini mengembalikan invers dari distribusi kumulatif normal untuk mean dan simpangan baku yang ditentukan.

Sintaks untuk fungsi NORM.INV() adalah sebagai berikut:

NORM.INV(probability, mean, standard_deviation)

Parameternya adalah:

• probability: Probabilitas yang sesuai dengan distribusi normal, yang harus bernilai antara 0 dan 1. Ini biasanya dihasilkan oleh fungsi RAND().

• mean: Rata-rata aritmetika dari distribusi normal.

• standard_deviation: Simpangan baku dari distribusi normal, ukuran seberapa tersebar angka-angka di sekitar mean.

Fungsi NORM.INV() digunakan untuk mentransformasikan angka acak terdistribusi seragam dari fungsi RAND() menjadi angka yang mengikuti distribusi normal tertentu. Ini berguna untuk memodelkan variabel yang diharapkan menunjukkan variabilitas alami mengikuti kurva normal.

Sekarang setelah kita memiliki semua blok bangunan, fungsi, dan konsep di balik simulasi Monte Carlo, mari kita terapkan di Microsoft Excel.

Menerapkan Simulasi Monte Carlo di Microsoft Excel: Sebuah Contoh

Bayangkan Anda adalah analis data yang bekerja di perusahaan elektronik konsumen yang dinamis dan ditugaskan untuk menilai kelayakan finansial peluncuran pelacak kebugaran wearable baru.

Pasar untuk perangkat tersebut kompetitif dan permintaan konsumen bisa sangat bervariasi, dipengaruhi oleh tren musiman, efektivitas pemasaran, dan tindakan pesaing. Selain itu, biaya yang terkait dengan pembuatan perangkat ini dapat berfluktuasi akibat perubahan biaya material dan ketidakpastian rantai pasok.

Anda memutuskan menggunakan simulasi Monte Carlo di Excel untuk mengatasi tantangan ini. Anda yakin pendekatan ini akan membantu memperkirakan profitabilitas potensial di bawah berbagai skenario, sehingga perusahaan dapat membuat keputusan yang tepat tentang strategi harga, volume produksi, dan investasi pemasaran.

Anda juga telah menganalisis data masa lalu dari peluncuran produk serupa dan studi pasar dalam industri elektronik konsumen. Dari analisis ini, Anda menyimpulkan metrik tertentu yang akan menginformasikan simulasi Anda:

• Permintaan rata-rata 10.000 unit untuk perangkat baru pada tahun pertama peluncuran, dengan simpangan baku 2.000 unit, mencerminkan ketidakpastian dalam adopsi konsumen.
• Harga jual per unit biasanya berkisar antara $50 hingga $70, tergantung pada penetapan harga kompetitif dan kejenuhan pasar.
• Biaya per unit, yang dipengaruhi oleh harga material yang bergejolak dan efisiensi manufaktur, rata-rata $30 per unit dengan simpangan baku $5.

Data historis ini membentuk asumsi dasar parameter simulasi Anda, membantu membuat simulasi yang lebih akurat mencerminkan kondisi pasar saat ini.

Langkah-langkah yang dapat Anda ikuti untuk menerapkan simulasi Monte Carlo pada contoh ini adalah sebagai berikut:

Langkah 1: Siapkan lembar Excel Anda

Pertama, siapkan lembar kerja Excel untuk menyertakan kolom bagi setiap variabel dan satu kolom untuk laba yang dihitung.

Tampilan awalnya akan seperti ini:

Menyiapkan lembar Excel.

Langkah 2: Masukkan rumus untuk variabel

Di setiap baris, Anda akan memasukkan rumus untuk menghasilkan nilai acak bagi permintaan, harga jual, dan biaya berdasarkan distribusi yang telah Anda tentukan:

• Permintaan: Distribusi normal (mean = 10.000 unit, simpangan baku = 2.000 unit)
• Harga Jual: Distribusi seragam ($50 hingga $70)
• Biaya: Distribusi normal (mean = $30, simpangan baku = $5)

Untuk memasukkan rumus satu per satu, pilih sel A2 dan ketik berikut ini:

=NORM.INV(RAND(), 10000, 2000)

Persamaan di atas membuat distribusi normal dengan mean dan simpangan baku tertentu seperti di bawah ini:

Membuat distribusi untuk permintaan.

Selanjutnya, pilih sel B2 dan ketik berikut ini:

=50 + (70-50) * RAND()

Persamaan di atas membuat distribusi seragam antara $50 dan $70 untuk harga jual seperti di bawah ini:

Membuat distribusi untuk harga jual.

Pilih sel C2, dan ketik berikut ini:

=NORM.INV(RAND(), 30, 5)

Persamaan di atas, mirip dengan persamaan permintaan, membuat distribusi normal dengan mean dan simpangan baku tertentu seperti di bawah ini:

Membuat distribusi untuk biaya.

Langkah 3: Hitung variabel terikat

Sekarang, hitung laba, yaitu variabel terikat, untuk setiap simulasi menggunakan rumus di kolom D:

=(B2 - C2) * A2

Menghitung laba.

Langkah 4: Isi ke bawah untuk mensimulasikan banyak skenario

Sejauh ini kita baru membuat satu simulasi. Mari kita perbanyak, katakanlah menjadi seribu simulasi.

Pilih sel A2 hingga D2 dan seret gagang isian (kotak kecil di kanan bawah seleksi) ke bawah untuk mengisi rumus sebanyak baris yang ingin Anda simulasikan (misalnya, 1000 baris untuk 1000 simulasi).

Hasilnya akan terlihat seperti ini:

Membuat simulasi.

Langkah 5: Analisis hasil

Setelah menjalankan simulasi, Anda dapat menganalisis hasil menggunakan fungsi statistik seperti min, maks, rata-rata, dan simpangan baku. Jangan ragu untuk merujuk cepat ke lembar contekan Excel untuk penyegaran fungsi bawaan Excel yang akan kita gunakan selanjutnya.

Untuk mencari laba rata-rata yang diharapkan setiap bulan, ketik berikut ini di sebuah sel, misalnya G6:

=AVERAGE(D2:D1001)

Untuk mencari laba minimum yang diharapkan setiap bulan, ketik berikut ini di sebuah sel, misalnya G7:

=MIN(D2:D1001)

Untuk mencari laba maksimum yang diharapkan setiap bulan, ketik berikut ini di sebuah sel, misalnya G8:

=MAX(D2:D1001)

Untuk mencari simpangan baku laba, ketik berikut ini di sebuah sel, misalnya G9:

=STDEV.P(D2:D1001)

Setelah dieksekusi, lembar Excel akan terlihat seperti ini:

Menganalisis hasil simulasi.

Kita dapat menafsirkan estimasi hasil dan implikasinya untuk peluncuran produk sebagai berikut:

• Laba rata-rata merepresentasikan laba yang diharapkan dari peluncuran pelacak kebugaran baru. Ini menunjukkan bahwa, rata-rata, setiap kali simulasi berjalan kita dapat mengharapkan laba sekitar $298.278,67. Nilai ini berguna sebagai estimasi sentral profitabilitas dengan asumsi yang diberikan.
• Laba minimum sebesar $67.598,78 adalah laba terendah yang diamati di seluruh simulasi kita. Ini menunjukkan skenario terburuk menurut asumsi model Anda, yang masih menguntungkan namun jauh lebih kecil daripada rata-rata. Hal ini bisa terjadi karena permintaan yang sangat rendah atau kondisi biaya yang tidak menguntungkan pada simulasi tertentu.
• Laba maksimum sebesar $641.955,42 merepresentasikan skenario terbaik, di mana permintaan dan harga kemungkinan berada pada level tertinggi dan biaya pada level terendah di seluruh simulasi. Ini menunjukkan potensi sisi atas jika kondisi sangat mendukung.

Mengingat rentang yang lebar antara laba minimum dan maksimum serta simpangan baku yang besar, terdapat risiko finansial yang cukup besar terkait peluncuran produk baru.

Pengambil keputusan harus mempertimbangkan apakah perusahaan nyaman dengan tingkat ketidakpastian ini dan potensi laba yang lebih rendah dari rata-rata.

Selain itu, meskipun opsional, kami menyarankan Anda membuat visualisasi seperti Histogram untuk mendapatkan pemahaman visual atas hasil simulasi.

Teknik untuk Meningkatkan Simulasi Monte Carlo di Excel

Saat Anda menjalankan ulang simulasi yang sama seperti di atas, Anda dapat mengamati sedikit perbedaan pada perhitungan, seperti yang ditunjukkan di bawah:

Hasil simulasi yang bervariasi.

Ini karena nilai-nilai dari simulasi awal dapat berubah antar iterasi, sehingga memengaruhi estimasi yang dihasilkan. Meskipun variasinya kecil, ketika nilai estimasi berubah, kekhawatiran tentang akurasi dan keandalan simulasi bisa muncul di benak para pengambil keputusan.

Mari kita jelajahi beberapa teknik lanjutan yang dapat kita gunakan untuk meningkatkan akurasi dan keandalan simulasi.

Meningkatkan jumlah simulasi

Menjalankan lebih banyak simulasi membantu merata-ratakan fluktuasi acak dan memberikan estimasi hasil yang lebih stabil dan akurat.

Untuk contoh di atas, kita dapat meningkatkan jumlah putaran simulasi (misalnya, dari 1.000 menjadi 10.000 atau lebih), terutama saat berurusan dengan parameter yang sangat bervariasi.

Menentukan jumlah simulasi yang “tepat” bergantung pada beberapa faktor.

Semakin kompleks modelnya (yakni semakin banyak variabel dan semakin luas rentang interaksinya), semakin banyak simulasi yang biasanya dibutuhkan untuk menangkap semua kemungkinan hasil dan memastikan hasilnya bukan karena kebetulan.

Jika input memiliki variabilitas tinggi atau sangat menyimpang, lebih banyak simulasi diperlukan untuk mengestimasi ekor (nilai ekstrem) distribusi hasil secara akurat.

Untuk analisis yang lebih mendetail, khususnya dalam keuangan atau manajemen risiko, tidak jarang menjalankan 10.000 hingga 100.000 simulasi. Rentang ini biasanya digunakan untuk memastikan hasil yang andal di berbagai skenario dan input. Tentu, seperti disebutkan sebelumnya, untuk analisis berskala besar seperti ini, Excel bukan selalu pilihan alat terbaik, melainkan R atau Python.

Memperbaiki distribusi input

Akurasi simulasi sangat bergantung pada seberapa baik distribusi probabilitas input mencerminkan ketidakpastian dan perilaku sebenarnya dari variabel dasar. Pada contoh di atas, kita mengasumsikan distribusi normal untuk permintaan dan biaya serta distribusi seragam untuk harga jual.

Selain itu, kita dapat menganalisis data historis yang lebih komprehensif untuk memparametrisasi distribusi dengan lebih baik. Kita dapat lebih memahami perilaku biaya, harga jual, dan permintaan terhadap faktor eksternal berdasarkan masukan pakar domain. Kita juga dapat mempertimbangkan penggunaan distribusi seperti log-normal, beta, atau gamma, atau membuat distribusi kustom berdasarkan data empiris.

Melakukan analisis sensitivitas

Analisis ini dilakukan untuk memahami variabel input mana yang memiliki dampak paling signifikan pada output dengan memvariasikan setiap input secara sistematis sambil menahan yang lain tetap konstan.

Dalam contoh di atas, kita dapat menahan dua variabel konstan dan mengubah distribusi satu variabel untuk memahami perubahan estimasi. Lalu, ulangi proses yang sama pada dua variabel lainnya satu per satu. Pada akhirnya, teknik ini membantu memahami variabel mana yang perlu difokuskan upayanya untuk meningkatkan akurasi.

Menerapkan teknik-teknik di atas secara iteratif dan menganalisis hasilnya dapat menghasilkan hasil yang lebih akurat dan andal.

Kesimpulan

Tutorial ini memperkenalkan Anda pada Simulasi Monte Carlo dan konsep statistik yang relevan. Setelah memperkenalkan fungsi Excel yang relevan, tutorial ini memberikan panduan langkah demi langkah untuk menerapkan Simulasi Monte Carlo di Excel menggunakan contoh dunia nyata.

Terakhir, Anda mempelajari beberapa praktik terbaik dan teknik lanjutan untuk memastikan hasil Anda lebih akurat dan andal.

Jika Anda tertarik menerapkan Simulasi Monte Carlo di atas menggunakan alat lain seperti Python atau R, dua sumber berikut akan berguna:

• Simulasi Monte Carlo di R
• Simulasi Monte Carlo di Python

Atau, jika Anda ingin tetap dengan Microsoft Excel yang sudah familiar, dan ingin menguasai keterampilan menggunakan alat yang banyak diadopsi ini, Anda dapat melihat jalur Excel Fundamentals kami.