Artikel ini disesuaikan dari Microsoft Excel Data Analysis and Business Modeling oleh Wayne L. Winston.

  • Siapa menggunakan simulasi Monte Monaco?

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

  • Bagaimana Anda bisa mensimulasikan nilai variabel acak diskret?

  • Bagaimana cara Anda mensimulasikan nilai variabel acak normal?

  • Bagaimana perusahaan kartu ucapan bisa menentukan berapa banyak kartu yang akan dihasilkan?

Kami ingin memperkirakan probabilitas kejadian yang tidak pasti secara akurat. Misalnya, berapa probabilitas bahwa arus kas produk baru akan memiliki nilai bersih saat ini (NPV) positif? Apa faktor risiko portofolio investasi kami? Simulasi Monte Monte memungkinkan kami untuk membuat model situasi yang menunjukkan ketidakpastian lalu memainkannya di komputer ribuan kali.

Catatan:  Simulasi nama Monte Host berasal dari simulasi komputer yang dilakukan selama tahun 1930 dan 1940 untuk memperkirakan probabilitas bahwa reaksi berantai yang diperlukan untuk bom atom untuk melakukan detonasi akan berhasil. Ahli fisika yang terlibat dalam pekerjaan ini merupakan penggemar besar kami, sehingga mereka memberikan simulasi nama kode Monte Monte.

Dalam lima bab berikutnya, Anda akan melihat contoh bagaimana Anda bisa menggunakan Excel untuk melakukan simulasi Monte Coi.

Banyak perusahaan menggunakan simulasi Monte Integral sebagai bagian penting dalam proses pembuatan keputusan mereka. Berikut ini beberapa contoh.

  • General Motors, Proctor dan Gamble, Pfizer, Bristol-Myers Squibb, dan Eli Lilly menggunakan simulasi untuk memperkirakan pengembalian rata-rata dan faktor risiko produk baru. Di GM, informasi ini digunakan oleh CEO untuk menentukan produk mana yang masuk ke pasar.

  • GM menggunakan simulasi untuk aktivitas seperti perkiraan pendapatan bersih untuk perusahaan, memperkirakan biaya struktural dan pembelian, dan menentukan kerentanannya terhadap berbagai jenis risiko (seperti perubahan suku bunga dan fluktuasi nilai tukar).

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

  • Proctor dan Gamble menggunakan simulasi untuk mencontoh dan mengoptimalkan risiko pertukaran asing.

  • Sears menggunakan simulasi untuk menentukan berapa banyak unit dari setiap baris produk yang harus dipesan dari pemasok—misalnya, jumlah pasangan Pengtambat yang harus di pesan tahun ini.

  • Perusahaan minyak dan terkemuka menggunakan simulasi untuk memberi nilai "opsi sebenarnya," seperti nilai opsi untuk memperluas, kontrak, atau menunda proyek.

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

Saat mengetikkan rumus =RAND() di sel, Anda akan mendapatkan angka yang kemungkinan sama dengan nilai antara 0 dan 1. Dengan demikian, sekitar 25 persen waktu, Anda akan mendapatkan angka kurang dari atau sama dengan 0,25; sekitar 10 persen dari waktu yang Anda akan mendapatkan angka yang setidaknya 0,90, dan sebagainya. Untuk mendemonstrasikan cara kerja fungsi RAND, lihat file yang Randdemo.xlsx, yang diperlihatkan dalam Gambar 60-1.

Gambar Buku

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

Pertama, salin dari sel C3 ke C4:C402 rumus =RAND(). Lalu Anda namai rentang Data C3:C402. Lalu, di kolom F, Anda bisa melacak rata-rata dari 400 angka acak (sel F2) dan menggunakan fungsi COUNTIF untuk menentukan pecahan yang berada antara 0 dan 0,25, 0,25 dan 0,50, 0,50 dan 0,75, dan 0,75 dan 1. Saat Anda menekan tombol F9, angka acak dihitung ulang. Perhatikan bahwa rata-rata dari 400 angka selalu sekitar 0,5, dan di sekitar 25 persen dari hasilnya berada dalam interval 0,25. Hasil ini konsisten dengan definisi angka acak. Perhatikan juga bahwa nilai yang dibuat oleh RAND di sel yang berbeda ber bekerja secara independen. Misalnya, jika angka acak yang dihasilkan di sel C3 adalah angka yang besar (misalnya, 0,99), angka tersebut tidak memberi tahu kami tentang nilai dari angka acak lain yang dihasilkan.

Anggaplah 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 Excel, atau mensimulasikan permintaan kalender ini berkali-kali? Triknya adalah, kaitkan setiap kemungkinan nilai fungsi RAND dengan kemungkinan permintaan untuk kalender. Penetapan berikut memastikan bahwa permintaan 10.000 akan terjadi 10 persen dari waktu, dan sebagainya.

Permintaan

Angka acak 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 atau sama dengan 0,45, dan kurang dari 0,75

60.000.000

Lebih besar dari atau sama dengan 0,75

Untuk mendemonstrasikan simulasi permintaan, lihat Discretesim.xlsx file, yang diperlihatkan dalam Gambar 60-2 pada halaman berikutnya.

Gambar Buku

Kunci dari simulasi kami adalah menggunakan angka acak untuk memulai pencarian dari rentang tabel F2:G5 (pencarian bernama). Angka acak yang lebih besar dari atau sama dengan 0 dan kurang dari 0,10 akan menghasilkan permintaan 10.000; angka acak yang lebih besar dari atau sama dengan 0,10 dan kurang dari 0,45 akan menghasilkan permintaan 20.000; angka acak yang lebih besar dari atau sama dengan 0,45 dan kurang dari 0,75 akan menghasilkan permintaan 40.000; dan angka acak yang lebih besar dari atau sama dengan 0,75 akan menghasilkan permintaan 60.000. Anda menghasilkan 400 angka acak dengan menyalin dari C3 ke C4:C402 rumus RAND(). Anda lalu menghasilkan 400 percobaan, atau per iterasi kalender dengan menyalin dari B3 ke B4:B402 rumus VLOOKUP(C3,lookup,2). Rumus ini memastikan bahwa angka acak yang lebih kecil dari 0,10 menghasilkan permintaan 10.000, angka acak apa pun antara 0,10 dan 0,45 menghasilkan permintaan 20.000, dan sebagainya. Dalam rentang sel F8:F11, gunakan fungsi COUNTIF untuk menentukan pecahan 400 per iterasi kami yang menghasilkan setiap permintaan. Ketika kita menekan F9 untuk menghitung ulang angka acak, simulasi probabilitas mendekati probabilitas permintaan yang diasumsikan.

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

Gambar Buku

Misalnya kami ingin mensimulasikan 400 percobaan, atau per iterasi, untuk variabel acak normal dengan rata-rata 40.000 dan simpangan baku 10.000. (Anda dapat mengetikkan nilai ini di sel E1 dan E2, lalu namai sel ini rata-rata dan sigma, secara berurutan.) Menyalin rumus =RAND() dari C4 ke C5:C403 menghasilkan 400 angka acak yang 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 menekan tombol F9 untuk menghitung ulang angka acak, rata-rata tetap mendekati 40.000 dan simpangan baku mendekati 10.000.

Pada dasarnya, untuk angka acak x,rumus NORMINV(p,mu,sigma) menghasilkan persentil ke-pvariabel acak normal dengan mu rata-rata dan sigma simpangan baku. Misalnya, angka acak 0,77 di sel C4 (lihat Gambar 60-3) menghasilkan dalam sel B4 sekitar persentil ke-77 dari variabel acak normal dengan rata-rata 40.000 dan simpangan baku 10.000.

Dalam bagian ini, Anda akan melihat bagaimana simulasi Monte Monte Monte dapat digunakan sebagai alat pembuatan keputusan. Anggaplah bahwa permintaan untuk kartu Hari Valentine 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 seharga $4,00, dan biaya variabel pembuatan setiap kartu adalah $1,50. Kartu leftover harus dibuang dengan biaya sejumlah $0,20 per kartu. Berapa banyak kartu yang akan dicetak?

Pada dasarnya, kami mensimulasikan setiap kemungkinan kuantitas produksi (10.000, 20.000, 40.000, atau 60.000) berkali-kali (misalnya, 1000 per iterasi). Lalu kami menentukan kuantitas pesanan mana yang menghasilkan laba rata-rata maksimum atas 1000 per iterasi. Anda dapat menemukan data untuk bagian ini dalam file Valentine.xlsx, yang diperlihatkan dalam Gambar 60-4. Anda menetapkan nama rentang di sel B1:B11 ke sel C1:C11. Rentang sel G3:H6 ditetapkan pencarian nama. Parameter harga dan biaya penjualan kami dimasukkan di sel C4:C6.

Gambar Buku

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

Jumlah unit yang terjual adalah jumlah dan permintaan produksi yang lebih kecil. Di sel C8, hitung pendapatan dengan rumus MIN(produksi,permintaan)*unit_price. Di sel C9, Anda menghitung biaya produksi total dengan rumus yang dihasilkan*unit_prod_cost.

Jika kami menghasilkan lebih banyak kartu dari yang diminta, jumlah unit tersisa sama dengan produksi dikurangi permintaan; jika tidak, tidak ada unit yang tersisa. Kami menghitung biaya pembuangan kami dalam sel C10 dengan rumus unit_disp_cost*IF(produksi>,produksi–permintaan,0). Akhirnya, di sel C11, kami menghitung laba sebagai pendapatan– total_var_cost-total_disposing_cost.

Kami ingin cara yang efisien untuk menekan F9 berkali-kali (misalnya, 1000) untuk setiap kuantitas produksi dan meningkakan laba yang diharapkan untuk setiap kuantitas. Situasi ini adalah salah satu di mana tabel data dua arah dapat menolong kami. (Lihat Bab 15, "Analisis Sensitivitas 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). Salah satu cara mudah untuk membuat nilai ini adalah dengan memasukkan 1 dalam 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, yang diperlihatkan dalam Gambar 60-6, masukkan Nilai Langkah 1 dan Nilai Hentian 1000. Dalam area Seri Dalam, pilih opsi Kolom, lalu klik OK. Angka 1-1000 akan dimasukkan di kolom A yang dimulai di sel A16.

Gambar Buku

Berikutnya kita masukkan jumlah produksi yang mungkin 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 setiap kuantitas produksi. Kami merujuk ke rumus untuk laba (dihitung di sel C11) di sel kiri atas tabel data kami (A15) dengan memasukkan =C11.

Sekarang kami siap untuk Excel agar bisa menyederhanakan 1000 pertuhan permintaan untuk setiap kuantitas produksi. Pilih rentang tabel (A15:E1014), lalu dalam grup Alat Data pada tab Data, klik Analisis Bagaimana-Jika, lalu pilih Tabel Data. Untuk menyiapkan tabel data dua arah, pilih kuantitas produksi kami (sel C1) sebagai Sel Input Baris, lalu pilih sel kosong (kami memilih sel I14) sebagai Sel Input Kolom. Setelah mengklik OK, Excel mensimulasikan 1000 nilai permintaan untuk setiap kuantitas pesanan.

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

Dengan menyalin dari sel B13 ke C13:E13 rumus AVERAGE(B16:B1015),kami menghitung rata-rata simulasi laba untuk setiap kuantitas produksi. Dengan menyalin dari sel B14 ke C14:E14 rumus STDEV(B16:B1015),kami menghitung simpangan baku laba dari simulasi laba untuk setiap kuantitas pesanan. Setiap kali kami menekan F9, 1000 per iterasi permintaan disimulasikan untuk setiap kuantitas pesanan. Menghasilkan 40.000 kartu akan menghasilkan laba terbesar yang diharapkan. Oleh karena itu, muncul bahwa menghasilkan 40.000 kartu adalah keputusan yang tepat.

Dampak Risiko pada Keputusan Kami      Jika kami menghasilkan 20.000 bukan 40.000 kartu, perkiraan laba kami turun sekitar 22 persen, tetapi risiko kami (yang diukur oleh simpangan baku laba) turun hampir 73 persen. Oleh karena itu, jika kita sangat bbalikan ke risiko, menghasilkan 20.000 kartu mungkin merupakan keputusan yang tepat. Insidennya, menghasilkan 10.000 kartu selalu memiliki simpangan baku 0 kartu karena jika kami menghasilkan 10.000 kartu, kami akan selalu menjual semuanya tanpa sisa.

Catatan:  Dalam buku kerja ini, opsi Penghitungan diatur ke Otomatis Kecuali Untuk Tabel. (Gunakan perintah Penghitungan dalam grup Penghitungan pada tab Rumus.) Pengaturan ini memastikan bahwa tabel data kami tidak akan dihitung ulang kecuali kami menekan F9, yang merupakan ide bagus karena tabel data yang besar akan memperlambat pekerjaan Anda jika tabel tersebut menghitung ulang setiap kali Anda mengetikkan sesuatu ke dalam lembar kerja Anda. Perhatikan bahwa dalam contoh ini, setiap kali Anda menekan F9, laba rata-rata akan berubah. Ini terjadi karena setiap kali Anda menekan F9, urutan berbeda dari 1000 angka acak digunakan untuk menghasilkan permintaan untuk setiap kuantitas pesanan.

Interval Kepercayaan untuk Laba Rata-rata      Pertanyaan alami untuk mengajukan situasi ini adalah, ke dalam interval berapa kita 95 persen yakin laba rata-rata yang sebenarnya akan turun? Interval ini disebut interval kepercayaan 95 persen untuk laba rata-rata. Interval kepercayaan 95 persen untuk rata-rata dari output simulasi apa pun dihitung oleh rumus berikut:

Gambar Buku

Di sel J11, Anda menghitung batas bawah untuk interval kepercayaan 95 persen berdasarkan laba rata-rata ketika 40.000 kalender dihasilkan dengan rumus D13-1.96*D14/SQRT(1000). Di sel J12, Anda menghitung batas atas untuk interval kepercayaan 95 persen dengan rumus D13+1.96*D14/SQRT(1000). Perhitungan ini diperlihatkan dalam Gambar 60-7.

Gambar Buku

Kami adalah 95 persen yakin bahwa laba rata-rata kami saat 40.000 kalender di pesan adalah antara $56.687 dan $62.589.

  1. GmC gmc yakin bahwa permintaan untuk Envoys 2005 akan didistribusikan secara normal dengan rata-rata 200 dan simpangan baku 30. Biaya penerimaan Envoy adalah $25.000, dan ia menjual Envoy seharga $40.000. Setengah dari semua Envoy yang tidak dijual dengan harga penuh dapat dijual seharga $30.000. Dia sedang mempertimbangkan untuk memesan 200, 220, 240, 260, 280, atau 300 Suara. Berapa banyak yang harus ia pesan?

  2. Small magazine mencoba menentukan berapa banyak salinan majalah Orang yang harus mereka pesan setiap minggu. Mereka yakin bahwa permintaan mereka untuk 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. Thelin pays $1,00 for each copy of People and sells it for $1,95. Setiap salinan yang tidak dijual dapat dikembalikan seharga $0,50. Berapa banyak salinan Orang yang harus dipesan toko?

Perlu bantuan lainnya?

Anda dapat bertanya kapan saja kepada pakar di Komunitas Teknologi Excel atau mendapatkan dukungan di Komunitas Jawaban.

Perlu bantuan lainnya?

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

Apakah informasi ini bermanfaat?

Seberapa puaskah Anda dengan kualitas terjemahannya?
Apa yang memengaruhi pengalaman Anda?

Terima kasih atas umpan balik Anda!

×