Pengenalan 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 adalah Microsoft Excel Analisis Data dan pemodelan bisnis dengan Wayne L. Winston.

  • Siapa yang menggunakan simulasi Monte Carlo?

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

  • Bagaimana Anda bisa melakukan simulasi nilai variabel acak diskrit?

  • Bagaimana Anda bisa melakukan simulasi nilai variabel acak normal?

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

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

Catatan: Nama simulasi Monte Carlo berasal dari simulasi komputer selama tahun 1930an dan 1940an untuk memperkirakan probabilitas yang reaksi yang diperlukan untuk bom atom untuk meledakkan akan bekerja dengan sukses. 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.

Perusahaan banyak menggunakan 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 hasil rata-rata dan faktor risiko produk baru. Di GM, informasi ini digunakan oleh CEO untuk mengetahui produk mana datang ke pasar.

  • GM menggunakan simulasi untuk aktivitas seperti perkiraan pendapatan bersih untuk perusahaan, prediksi biaya struktur dan pembelian, dan menentukan yang kerentanan untuk berbagai jenis risiko (seperti perubahan suku bunga dan exchange fluktuasi).

  • Lilly menggunakan simulasi untuk menentukan kapasitas optimal pabrik untuk masing-masing obat.

  • Proctor dan berjudi menggunakan simulasi untuk model dan optimal membatasi devisa risiko.

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

  • Perusahaan oil dan obat gunakan simulasi nilai "riil pilihan," seperti nilai opsi untuk memperluas, kontrak atau menunda proyek.

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

Saat Anda mengetik rumus =RAND() di dalam sel, Anda mendapatkan angka yang sama kemungkinan menganggap setiap nilai antara 0 dan 1. Jadi, sekitar 25% waktu, Anda harus mendapatkan angka kurang dari atau sama dengan 0,25; sekitar 10 persen waktu Anda harus mendapatkan angka yang setidaknya 0.90, dan sebagainya. Untuk menunjukkan cara kerja fungsi RAND, lihat file Randdemo.xlsx, diperlihatkan dalam gambar 60-1.

Gambar Buku
Gambar 60-1 menunjukkan fungsi RAND

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

Pertama, salin dari sel C3 untuk C4:C402 rumus =RAND(). Lalu Anda nama rentang C3:C402 Data. Lalu, di kolom F, Anda bisa melacak rata-rata dari angka acak 400 (sel F2) dan menggunakan fungsi COUNTIF untuk menentukan pecahan yang antara 0 dan 0,25, 0,25 dan 0,50, 0,50 dan 0,75, dan 0,75 dan 1. Ketika Anda menekan tombol F9, angka acak tersebut dihitung kembali. Pemberitahuan bahwa rata-rata dari angka 400 adalah selalu kira-kira 0,5, dan bahwa sekitar 25 persen hasil 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. Sebagai contoh, jika bilangan acak yang dihasilkan dalam sel C3 sejumlah besar (misalnya, 0,99), hal itu menjelaskan apa pun tentang nilai angka acak lain yang dihasilkan.

Anggap permintaan kalender diatur oleh variabel acak diskrit berikut ini:

Permintaan

Probabilitas

10.000

0,10

20.000

0,35

40.000

0,3

60.000.000

0,25

Bagaimana kami bisa Excel bermain keluar, atau mensimulasikan, permintaan ini untuk kalender berkali-kali? Triknya adalah untuk mengaitkan setiap nilai kemungkinan fungsi RAND dengan permintaan yang memungkinkan 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

Lebih kecil dari 0,10

20.000

Lebih besar dari atau sama dengan 0,10, dan lebih kecil daripada 0,45

40.000

Lebih besar dari atau sama dengan 0,45, dan lebih kecil daripada 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
Gambar 60-2 simulasi variabel acak diskrit

Tombol untuk simulasi kami adalah untuk menggunakan angka acak untuk memulai pencarian dari tabel rentang F2:G5 (dinamai pencarian). Angka acak lebih besar dari atau sama dengan 0 dan lebih kecil daripada 0,10 akan menghasilkan permintaan 10.000; angka acak lebih besar dari atau sama dengan 0,10 dan lebih kecil daripada 0,45 akan menghasilkan permintaan 20.000; angka acak lebih besar dari atau sama dengan 0,45 dan lebih kecil daripada 0,75 akan menghasilkan permintaan 40.000; dan angka acak lebih besar dari atau sama dengan 0,75 akan menghasilkan permintaan 60.000. Anda membuat angka acak 400 dengan menyalin dari C3 C4:C402 rumus RAND(). Anda lalu menghasilkan uji 400, 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 demand masing-masing. Saat kami tekan F9 untuk menghitung ulang angka acak, probabilitas simulasi adalah mendekati probabilitas asumsi permintaan kami.

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

Gambar Buku
Gambar 60-3 simulasi variabel acak normal

Misalnya, kami ingin mensimulasikan uji 400, atau perulangan, untuk variabel acak normal dengan rata-rata 40.000 dan simpangan baku 10.000. (Anda bisa mengetikkan nilai ini di sel E1 dan E2, dan nama sel rata-rata dan sigma, ini.) 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 mendekati 40.000 dan simpangan baku mendekati 10.000.

Pada dasarnya, untuk sejumlah acak 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) 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 bisa digunakan sebagai alat keputusan. Anggap bahwa permintaan untuk hari Valentine kartu diatur oleh variabel acak diskrit berikut ini:

Permintaan

Probabilitas

10.000

0,10

20.000

0,35

40.000

0,3

60.000.000

0,25

Kartu ucapan seharga $4.00, dan biaya variabel menghasilkan masing-masing kartu sebesar $1,50. Sisa kartu harus dibuang biaya sebesar $0.20 per kartu. Berapa banyak kartu akan dicetak?

Pada dasarnya, kami mensimulasikan setiap kemungkinan produksi jumlah (10.000, 20.000, 40.000 atau 60.000) beberapa kali (misalnya, perulangan 1000). Lalu kita menentukan jumlah pesanan yang menghasilkan 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 diberi nama pencarian. Penjualan harga dan biaya parameter dimasukkan di sel C4:C6.

Gambar Buku
Gambar 60-4 hari Valentine kartu simulasi

Anda bisa memasukkan jumlah percobaan produksi (40.000 dalam contoh ini) di sel C1. Berikutnya, buat bilangan acak di sel C2 dengan rumus =RAND(). Seperti 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 terjual lebih kecil dari produksi kuantitas dan permintaan. Dalam sel C8, Anda menghitung kami pendapatan dengan rumus MIN (dihasilkan, permintaan) * unit_price. Dalam sel C9, Anda menghitung total biaya produksi dengan rumus dihasilkan * unit_prod_cost.

Jika kita menghasilkan kartu lebih dari dalam permintaan, jumlah unit tersisa sama dengan produksi minus permintaan; Jika tidak ada unit kiri. Kita menghitung biaya pembuangan kami di sel C10 dengan rumus unit_disp_cost * IF (dihasilkan > permintaan, dihasilkan – permintaan, 0). Akhirnya, dalam sel C11, kita menghitung laba kami sebagai pendapatan – total_var_cost-total_disposing_cost.

Kami ingin efisien untuk tekan F9 berkali-kali (misalnya, 1000) untuk setiap jumlah produksi dan menghitung laba kami diharapkan untuk setiap jumlah. Situasi ini adalah satu di mana tabel data dua arah muncul untuk Gunakan 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
Tabel data dua arah gambar 60-5 untuk kartu ucapan simulasi

Dalam rentang sel A16:A1015, masukkan angka 1 – 1000 (sesuai dengan percobaan 1000 kami). Salah satu cara yang mudah untuk membuat nilai ini adalah dengan mulai dengan memasukkan 1 di sel A16. Pilih sel, lalu pada tab Beranda di 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 ke dalam kolom dimulai di sel A16.

Gambar Buku
Kotak untuk mengisi percobaan angka 1 sampai 1000 dialog 60 gambar-6 menggunakan seri

Berikutnya kami masukkan jumlah 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. Kami merujuk ke rumus untuk laba (sel terhitung dalam C11) di sel kiri atas tabel data kami (A15) dengan memasukkan = C11.

Kami sekarang sudah siap untuk Triknya Excel ke dalam simulasi 1000 perulangan permintaan untuk setiap jumlah produksi. Pilih rentang tabel (A15:E1014), lalu di grup alat Data pada Data tab, klik analisis jika apa, dan lalu pilih tabel Data. Untuk membuat 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 jumlah pesanan.

Untuk memahami mengapa kerjanya, pertimbangkan nilai ditempatkan dengan tabel data dalam rentang sel C16:C1015. Untuk setiap sel, Excel akan menggunakan nilai 20.000 di sel C1. Di C16, nilai sel input kolom 1 ditempatkan di sel kosong dan angka acak dalam 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 dimasukkan di sel C17.

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

Dampak risiko pada kami keputusan     Jika kita menghasilkan 20.000 bukan 40.000 kartu, keuntungan kami diharapkan menghilangkan kira-kira 22 persen, tapi kami risiko (yang diukur oleh simpangan baku laba) menghilangkan hampir 73 persen. Oleh karena itu, jika kita sangat menghindari risiko, menghasilkan 20.000 kartu mungkin keputusan yang tepat. Kebetulan, menghasilkan 10.000 kartu selalu memiliki simpangan baku 0 kartu karena jika memproduksi 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 data yang besar akan memperlambat pekerjaan Anda jika menghitung ulang setiap kali Anda mengetikkan sesuatu ke lembar kerja Anda. Perhatikan bahwa dalam contoh ini, setiap kali Anda menekan F9, rata-rata laba akan berubah. Ini terjadi karena setiap kali Anda menekan F9, urutan berbeda dari 1000 angka acak digunakan untuk menghasilkan permintaan untuk setiap jumlah pesanan.

Interval kepercayaan untuk rata-rata laba     Pertanyaan natural untuk meminta dalam situasi ini adalah, ke dalam interval apa kami 95 persen 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 rata-rata keuntungan 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
Gambar 60-7 95 persen interval kepercayaan untuk rata-rata laba saat kalender 40.000 mengurutkan

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

  1. GMC dealer percaya bahwa permintaan 2005 utusan akan normalnya didistribusikan dengan rata-rata 200 dan simpangan baku 30. Dalam biaya menerima utusan sebesar $25.000, dan dia menjual utusan untuk $40,000. Setengah dari semua utusan tidak dijual harga penuh bisa dijual $30.000. Ia sedang mempertimbangkan pengurutan 200, 220, 240, 260, 280 atau utusan 300. Berapa banyak dia harus memesan?

  2. Pasar swalayan kecil berusaha untuk menentukan berapa banyak salinan orang majalah mereka harus memesan setiap minggu. Mereka percaya permintaan mereka untuk orang-orang ini diatur oleh variabel acak diskrit 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 seharga $1,95. Setiap salinan terjual bisa dikembalikan untuk $0,50. Berapa banyak salinan orang harus toko urutan?

Perlu bantuan lainnya?

Anda selalu dapat bertanya kepada pakar di Komunitas Teknologi Excel, mendapatkan dukungan di komunitas Jawaban, atau menyarankan fitur baru maupun penyempurnaan di Suara Pengguna 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.

×