Pengenalan ke simulasi Monte Carlo di Excel

Catatan: Kami ingin secepatnya menyediakan konten bantuan terbaru dalam bahasa Anda. Halaman ini diterjemahkan menggunakan mesin dan mungkin terdapat kesalahan tata bahasa atau masalah keakuratan. Kami bertujuan menyediakan konten yang bermanfaat untuk Anda. Dapatkah Anda memberi tahu kami apakah informasi ini bermanfaat untuk Anda di bagian bawah halaman ini? Berikut artikel dalam bahasa Inggris untuk referensi.

Artikel ini telah disesuaikan dari Microsoft Excel Analisis Data dan pemodelan bisnis dengan Wayne L. Winston.

  • Siapa yang menggunakan simulasi Monte Carlo?

  • Apa yang terjadi saat Anda mengetik =RAND() dalam sel?

  • Bagaimana Anda bisa melakukan simulasi nilai variabel acak diskret?

  • Bagaimana Anda bisa melakukan simulasi nilai variabel acak normal?

  • Bagaimana perusahaan kartu ucapan menentukan berapa banyak kartu untuk menghasilkan?

Kami ingin akurat memperkirakan probabilitas tidak pasti acara. Misalnya, apa itu probabilitas bahwa produk baru arus kas akan memiliki positif nilai bersih saat ini (NPV) Apa faktor risiko kami investasi portofolio? Simulasi Monte Carlo memungkinkan kami untuk model situasi yang menyajikan ketidakpastian dan lalu bermain mereka menggunakan komputer ribuan kali.

Catatan:  Nama simulasi Monte Carlo berasal dari simulasi komputer yang dilakukan selama 1930 dan tahun 1940 untuk memperkirakan probabilitas yang reaksi jaringan yang diperlukan untuk bom atom untuk meledakkan berhasil. Fisikawan terlibat dalam pekerjaan ini penggemar besar judi, sehingga mereka memberi simulasi nama kode Monte Carlo.

Di samping lima bab, Anda akan melihat contoh bagaimana Anda bisa menggunakan Excel untuk melakukan simulasi Monte Carlo.

Banyak perusahaan gunakan simulasi Monte Carlo sebagai bagian penting proses keputusan mereka. Berikut adalah beberapa contoh.

  • Umum motor, Proctor dan berjudi, Pfizer, Bristol-Myers Squibb, dan Eli Lilly menggunakan simulasi untuk memperkirakan return rata-rata dan faktor risiko produk baru. Di GM, informasi ini digunakan oleh CEO untuk menentukan produk mana datang untuk memasarkan.

  • GM menggunakan simulasi untuk aktivitas seperti perkiraan pendapatan bersih untuk corporation, prediksi biaya struktural dan pembelian, dan menentukan webnya kerentanan untuk berbagai jenis risiko (seperti perubahan suku bunga dan fluktuasi kurs mata uang).

  • Lilly menggunakan simulasi untuk menentukan kapasitas optimal pabrik untuk narkoba setiap.

  • Proctor dan berjudi menggunakan simulasi untuk model dan benar-benar hedge risiko asing exchange.

  • Sears menggunakan simulasi untuk menentukan berapa banyak satuan setiap produk baris harus memerintahkan dari pemasok — misalnya, jumlah pasangan celana Dockers yang harus disusun tahun ini.

  • Perusahaan oil dan narkoba menggunakan simulasi nilai "opsi riil," seperti nilai opsi untuk memperluas, kontrak atau menunda proyek.

  • Perencana keuangan gunakan simulasi Monte Carlo untuk menentukan strategi optimal investasi untuk pensiun klien mereka.

Saat Anda mengetik rumus =RAND() dalam sel, Anda mendapatkan nomor yang cenderung secara merata mengasumsikan nilai apa pun antara 0 dan 1. Dengan demikian, sekitar 25 persen waktu, Anda harus mendapatkan angka kurang dari atau sama dengan 0,25; sekitar 10 persen waktu Anda harus mendapatkan bilangan yang setidaknya 0.90, dan sebagainya. Untuk menunjukkan cara kerja fungsi RAND, Simak file Randdemo.xlsx, diperlihatkan dalam gambar 60-1.

Gambar Buku

Catatan:  Ketika Anda membuka file Randdemo.xlsx, Anda tidak akan melihat nomor acak yang sama yang diperlihatkan dalam gambar 60-1. Fungsi RAND selalu secara otomatis menghitung ulang nomor yang menghasilkan saat lembar kerja dibuka atau jika informasi baru yang dimasukkan ke dalam lembar kerja.

Pertama, salin dari sel C3 untuk C4:C402 rumus =RAND(). Lalu Anda memberi nama rentang C3:C402 Data. Lalu, di kolom F, Anda dapat melacak rata-rata angka acak 400 (sel F2) dan menggunakan fungsi COUNTIF untuk menentukan pecahan yang antara 0 dan 0,25, 0,25 dan bagian 0,50, 0,50 dan 0,75, dan 0,75 dan 1. Saat Anda menekan tombol F9, angka acak dihitung kembali. Pemberitahuan bahwa rata-rata angka 400 adalah selalu kira-kira 0,5, bahwa sekitar 25 persen hasil berada dalam interval 0,25. Hasil ini konsisten dengan definisi angka acak. Perhatian juga bahwa nilai-nilai yang dihasilkan oleh RAND dalam sel yang berbeda independen. Misalnya, jika angka acak dibuat di sel C3 angka besar (misalnya, 0,99), ini memberi tahu kami apa pun tentang nilai angka acak lain yang dihasilkan.

Anggap permintaan untuk kalender diatur oleh variabel acak diskret berikut ini:

Permintaan

Probabilitas

10.000

0,10

20.000

0,35

40.000

0,3

60.000.000

0,25

Bagaimana kami dapat memiliki Excel memutar, atau mensimulasikan, permintaan ini untuk kalender banyak waktu? Triknya adalah untuk mengaitkan setiap nilai kemungkinan fungsi RAND dengan permintaan kemungkinan untuk kalender. Tugas berikut ini memastikan bahwa permintaan 10.000 akan terjadi 10 persen waktu, dan lain sebagainya.

Permintaan

Angka acak yang ditetapkan

10.000

Kurang dari 0.10

20.000

Lebih besar dari atau sama dengan 0.10, dan kurang dari 0,45

40.000

Lebih besar dari atau sama dengan 0,45, dan kurang dari 0,75

60.000.000

Lebih besar dari atau sama dengan 0,75

Untuk menunjukkan simulasi permintaan, lihat file Discretesim.xlsx, diperlihatkan dalam gambar 60-2 di halaman berikutnya.

Gambar Buku

Tombol untuk simulasi kami adalah menggunakan angka acak untuk memulai pencarian dari tabel rentang F2:G5 (bernama pencarian). Angka acak lebih besar dari atau sama dengan 0 dan lebih kecil dari 0.10 akan menghasilkan permintaan 10.000; angka acak lebih besar dari atau sama dengan 0.10 dan kurang dari 0,45 akan menghasilkan permintaan 20.000; angka acak lebih besar dari atau sama dengan 0,45 dan kurang dari 0,75 akan menghasilkan permintaan 40.000; dan angka acak lebih besar dari atau sama dengan 0,75 akan menghasilkan permintaan 60.000. Anda menghasilkan angka acak 400 dengan menyalin dari C3 untuk C4:C402 rumus RAND(). Anda lalu menghasilkan 400 trials, atau perulangan, permintaan kalender dengan menyalin dari B3 untuk B4:B402 rumus VLOOKUP(C3,lookup,2). Rumus ini memastikan bahwa setiap angka acak kurang 0.10 menghasilkan permintaan 10.000, menghasilkan angka acak antara 0.10 dan 0,45 permintaan 20.000, dan seterusnya. Dalam rentang sel F8:F11, gunakan fungsi COUNTIF untuk menentukan pecahan perulangan kami 400 menghasilkan setiap permintaan. Saat kami tekan F9 untuk menghitung ulang angka acak, probabilitas simulasi adalah dekat probabilitas diasumsikan permintaan kami.

Jika Anda ketik dalam sel mana pun rumus NORMINV(rand(),mu,sigma), Anda akan menghasilkan nilai simulasi variabel acak normal mengalami rata-rata mu dan simpangan baku sigma. Prosedur ini adalah seperti yang ditunjukkan dalam file Normalsim.xlsx, diperlihatkan dalam gambar 60-3.

Gambar Buku

Misalnya, kami ingin mensimulasikan 400 trials, atau perulangan, untuk variabel acak normal dengan rata-rata 40.000 dan simpangan baku 10.000. (Anda dapat mengetikkan nilai ini di sel E1 dan E2, dan beri nama sel berarti dan sigma, ini secara berurutan.) Menyalin rumus =RAND() dari C4 ke C5:C403 menghasilkan angka acak 400 berbeda. Menyalin dari B4 ke B5:B403 rumus NORMINV(C4,mean,sigma) menghasilkan 400 nilai percobaan yang berbeda dari variabel acak normal dengan rata-rata 40.000 dan simpangan baku 10.000. Ketika kami tekan tombol F9 untuk menghitung ulang angka acak, rata-rata tetap dekat 40.000 dan simpangan baku dekat 10.000.

Pada dasarnya, untuk acak angka x, rumus NORMINV(p,mu,sigma) menghasilkan persentil th pvariabel acak normal dengan rata-rata mu dan simpangan baku sigma. Misalnya, angka acak 0.77 di sel C4 (Lihat gambar 60-3) akan menghasilkan di sel B4 kira-kira persentil ke-77 dari variabel acak normal dengan rata-rata 40.000 dan simpangan baku 10.000.

Di bagian ini, Anda akan melihat bagaimana penjaga simulasi dapat digunakan sebagai alat keputusan. Anggap bahwa permintaan untuk hari Valentine kartu diatur oleh variabel acak diskret berikut ini:

Permintaan

Probabilitas

10.000

0,10

20.000

0,35

40.000

0,3

60.000.000

0,25

Kartu ucapan menjual untuk $4.00, dan biaya variabel memproduksi setiap kartu adalah $1,50. Sisa kartu harus dibuang biaya $0,20 per kartu. Berapa banyak kartu harus dicetak?

Pada dasarnya, kami mensimulasikan kuantitas kemungkinan produksi setiap (10.000, 20.000, 40.000 atau 60.000) banyak kali (misalnya, perulangan 1000). Lalu kami menentukan kuantitas pesanan mana mengembalikan rata-rata keuntungan maksimum melalui perulangan 1000. Anda bisa menemukan data untuk bagian ini di file Valentine.xlsx, diperlihatkan dalam gambar 60-4. Anda menetapkan nama rentang dalam B1:B11 sel untuk sel C1:C11. Rentang sel G3:H6 ditetapkan nama pencarian. Penjualan harga dan biaya parameter kami dimasukkan di sel C4:C6.

Gambar Buku

Anda bisa memasukkan jumlah percobaan produksi (40.000 dalam contoh ini) di sel C1. Berikutnya, membuat angka acak di sel C2 dengan rumus =RAND(). Seperti yang dijelaskan sebelumnya, Anda melakukan simulasi permintaan untuk kartu di sel C3 dengan rumus VLOOKUP(rand,lookup,2). (Dalam rumus VLOOKUP, rand adalah nama sel yang ditetapkan ke sel C3, fungsi RAND not.)

Jumlah unit yang terjual lebih kecil produksi kuantitas dan permintaan kami. Dalam sel C8, Anda menghitung kami pendapatan dengan rumus MIN (diproduksi, permintaan) * unit_price. Di sel C9, yang menghitung total biaya produksi dengan rumus diproduksi * unit_prod_cost.

Jika kami menghasilkan selengkapnya kartu dari dalam permintaan, jumlah unit kiri atas sama dengan produksi minus permintaan; Jika tidak ada unit kiri. Kami menghitung biaya pembuangan kami di sel C10 dengan rumus unit_disp_cost*IF(produced>demand,produced–demand,0). Terakhir, dalam sel C11, kita menghitung laba kami sebagai pendapatan – total_var_cost-total_disposing_cost.

Kami ingin cara yang efisien untuk tekan F9 banyak kali (misalnya, 1000) untuk setiap produksi kuantitas dan menghitung laba kami diharapkan untuk setiap kuantitas. Situasi ini adalah satu di mana tabel data dua arah muncul untuk Excel kami. (Lihat Bab 15, "Sensitivitas analisis dengan tabel Data," untuk detail tentang tabel data.) Tabel data yang digunakan dalam contoh ini diperlihatkan dalam gambar 60-5.

Gambar Buku

Dalam rentang sel A16:A1015, masukkan angka 1 – 1000 (terkait dengan percobaan 1000 kami). Satu cara mudah untuk membuat nilai ini adalah untuk memulai dengan memasukkan 1 di sel A16. Pilih sel, lalu pada tab Beranda dalam grup pengeditan , klik isian, dan pilih seri untuk menampilkan kotak dialog seri . Dalam kotak dialog seri , diperlihatkan dalam gambar 60-6, masukkan nilai langkah 1 dan nilai berhenti 1000. Di area Seri di , pilih opsi kolom , dan lalu klik OK. Angka 1 – 1000 akan dimasukkan dalam kolom dimulai di sel A16.

Gambar Buku

Berikutnya kami masukkan kuantitas kemungkinan produksi kami (10.000 20.000 40.000, 60.000) di sel B15:E15. Kami ingin menghitung laba untuk setiap nomor percobaan (1 hingga 1000) dan kuantitas produksi setiap. Kita lihat rumus untuk laba (sel terhitung dalam C11) di sel kiri atas tabel data kami (A15) dengan memasukkan = C11.

Kami kini siap untuk Trik Excel ke dalam simulasi 1000 perulangan permintaan untuk setiap kuantitas produksi. Pilih rentang tabel (A15:E1014), dan lalu di grup alat Data pada Data tab, klik analisis apa jika, dan lalu pilih tabel Data. Untuk menyiapkan tabel data dua arah, pilih jumlah produksi kami (sel C1) sebagai sel Input baris dan pilih sel kosong mana saja (kami memilih sel I14) sebagai sel Input kolom. Setelah mengklik OK, Excel mensimulasikan 1000 permintaan nilai untuk setiap kuantitas pesanan.

Untuk memahami mengapa kerjanya, pertimbangkan nilai ditempatkan dengan tabel data dalam rentang sel C16:C1015. Untuk setiap sel-sel, Excel akan menggunakan nilai dari 20.000 di sel C1. Di C16, nilai sel input kolom 1 ditempatkan di sel kosong dan angka acak di sel C2 menghitung ulang. Laba terkait lalu direkam dalam sel C16. Lalu kolom sel input nilai 2 ditempatkan di sel kosong, dan angka acak di C2 lagi menghitung ulang. Laba terkait yang dimasukkan ke dalam sel C17.

Dengan menyalin dari sel B13 untuk C13:E13 rumus AVERAGE(B16:B1015), kita menghitung rata-rata laba simulasi untuk setiap kuantitas produksi. Dengan menyalin dari sel B14 untuk C14:E14 rumus STDEV(B16:B1015), kita menghitung simpangan baku laba kami simulasi untuk setiap kuantitas pesanan. Setiap kali kami tekan F9, 1000 perulangan permintaan disimulasikan untuk setiap kuantitas pesanan. Membuat kartu 40.000 selalu menghasilkan keuntungan terbesar yang diharapkan. Oleh karena itu, tampaknya bahwa memproduksi 40.000 kartu keputusan yang tepat.

Dampak risiko pada keputusan kami     Jika kami diproduksi 20.000 bukan 40.000 kartu, laba diharapkan kami menghilangkan kira-kira 22 persen, tetapi kami risiko (yang diukur oleh simpangan baku laba) menghilangkan hampir 73 persen. Oleh karena itu, jika kami sangat menolak untuk risiko, memproduksi 20.000 kartu mungkin keputusan yang tepat. Kebetulan, memproduksi 10.000 kartu selalu memiliki simpangan baku 0 kartu karena jika kami menghasilkan 10.000 kartu, kami akan selalu menjual semuanya tanpa sisa apa pun.

Catatan:  Di buku kerja ini, opsi perhitungan diatur ke Otomatis kecuali untuk tabel. (Gunakan perintah perhitungan dalam grup perhitungan pada tab rumus.) Pengaturan ini memastikan bahwa tabel data kami akan tidak dihitung ulang kecuali kami tekan F9, yang merupakan ide yang baik karena tabel besar data akan memperlambat pekerjaan Anda jika menghitung ulang setiap kali Anda mengetikkan sesuatu ke dalam lembar kerja Anda. Perhatikan bahwa dalam contoh ini, setiap kali Anda menekan F9, rata-rata keuntungan akan berubah. Ini terjadi karena setiap kali Anda menekan F9, urutan berbeda dari angka acak 1000 digunakan untuk menghasilkan tuntutan untuk setiap kuantitas pesanan.

Interval kepercayaan untuk rata-rata laba     Pertanyaan alami untuk meminta dalam situasi ini adalah, ke dalam interval apa kami persen 95 yakin rata-rata laba true akan berada? Interval ini disebut 95 persen interval kepercayaan untuk rata-rata laba. Persen 95 interval kepercayaan untuk rata-rata output simulasi dihitung dengan rumus berikut:

Gambar Buku

Dalam sel J11, Anda akan menghitung batas untuk interval kepercayaan 95 persen di rata-rata laba saat 40.000 kalender yang dihasilkan dengan rumus D13–1.96*D14/SQRT(1000). Dalam sel J12, Anda menghitung batas atas untuk interval kepercayaan 95 persen kami dengan rumus D13+1.96*D14/SQRT(1000). Perhitungan ini diperlihatkan dalam gambar 60-7.

Gambar Buku

Kami yang persen 95 yakin bahwa kami rata-rata laba saat kalender 40.000 mengurutkan antara $56,687 dan $62,589.

  1. GMC dealer percaya bahwa permintaan untuk 2005 utusan akan normalnya didistribusikan dengan rata-rata 200 dan simpangan baku 30. Biaya miliknya menerima Envoy adalah $25.000, dan ia menjual Envoy untuk $40.000. Setengah dari semua utusan tidak dijual harga penuh yang dapat dijual $30.000. Dia sedang mempertimbangkan pengurutan 200, 220, 240, 260, 280 atau utusan 300. Berapa banyak harus he urutan?

  2. Pasar swalayan kecil berusaha untuk menentukan berapa banyak salinan orang majalah mereka harus pesanan setiap minggu. Mereka percaya permintaan mereka untuk orang-orang diatur oleh variabel acak diskret berikut ini:

    Permintaan

    Probabilitas

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Supermarket membayar $1,00 untuk setiap salinan orang dan menjual untuk $1,95. Salin setiap terjual bisa dikembalikan untuk $0,50. Berapa banyak salinan orang harus store urutan?

Perlu bantuan lainnya?

Anda dapat bertanya kapan saja kepada para ahli di Komunitas Teknologi Excel, mendapatkan dukungan di Komunitas Answers, atau menyarankan peningkatan maupun fitur baru di User Voice Excel.

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×