Excel'de Monte Carlo benzetimi giriş

Not:  En güncel yardım içeriklerini, mümkün olduğunca hızlı biçimde kendi dilinizde size sunmak için çalışıyoruz. Bu sayfanın çevirisi otomasyon aracılığıyla yapılmıştır ve bu nedenle hatalı veya yanlış dil bilgisi kullanımları içerebilir. Amacımız, bu içeriğin sizin için faydalı olabilmesini sağlamaktır. Buradaki bilgilerin faydalı olup olmadığını bu sayfanın sonunda bize bildirebilir misiniz? Kolayca başvurabilmek için İngilizce makaleye buradan ulaşabilirsiniz .

Bu makalede Microsoft Excel'de veri çözümlemesi ve iş modelleme Wayne l Winston tarafından uyarlanmıştır.

  • Kimler Monte Carlo benzetimi kullanır?

  • Bir hücrede =RAND() yazdığınızda ne olur?

  • Nasıl ayrı bir rasgele değişken değerlerini benzetimini yapabilirsiniz?

  • Nasıl normal rasgele değişken değerlerini benzetimini yapabilirsiniz?

  • Nasıl elde etmek için kaç kart Tebrik Kartı şirket saptayabilirsiniz?

Belirsiz olayların olasılıklar doğru tahmin isteriz. Örneğin, yeni bir ürünün nakit akışlarının bir pozitif net bugünkü değeri (NBD) olduğundan olasılık nedir? Risk faktörüne bizim yatırım Portföyünün nedir? Monte Carlo benzetimi bize belirsizlik sunma ve bir bilgisayarda kaç kez binler sonra bunları yürütmeyi modeli durumlar olanak tanır.

Not: Adı 1930s ve bir atom bomb detonate gerekli zinciri tepki başarıyla yarayacağını olasılık tahmin etmek için 1940s sırasında gerçekleştirilen bilgisayar benzetimleri Monte Carlo benzetimi gelir. Bunlar benzetimleri Monte Carlokod adı verdiğiniz böylece bu çalışması katılan physicists kumar, biri büyük fan bırakılan.

Sonraki beş bölümlerde Monte Carlo benzetimleri gerçekleştirmek için Excel'in nasıl kullanabileceğinizi örnekleri görürsünüz.

Birçok şirket Monte Carlo benzetimi kendi karar verme sürecini önemli bir parçası olarak kullanın. Burada, bazı örnekler verilmiştir.

  • Genel Motors, Proctor ve kumarsa, Pfizer, Bristol Etikan Squibb ve Eli Lilly benzetimi ortalama return hem de yeni ürünlerin risk faktörüne tahmin etmek için kullanın. GM pazarlamak için hangi ürünler gelen belirlemek için bu bilgileri CEO tarafından kullanılır.

  • GM benzetimi net gelir corporation için tahmin, yapısal ve satın alma maliyetlerini tahmin ve (örneğin, faiz oranı değişiklikleri ve exchange oranı bağlı) risk farklı türde için kendi susceptibility belirleme gibi etkinlikler için kullanır.

  • Lilly benzetim her ilaç için en iyi bitki kapasitesi belirlemek için kullanır.

  • Proctor ve kumarsa, simülasyon modeli ve en verimli şekilde yabancı exchange risk Çit için kullanır.

  • Sears kaç birim her ürün satır Üreticiler sipariş verilmesi belirlemek için benzetim kullanır; Örneğin, bu yıl sipariş verilmesi Dockers trousers çiftlerini sayısı.

  • Yağ ve ilaç şirketler benzetimi değere "gerçek seçenekleri," gibi genişletin, sözleşme veya bir proje ertelemek için bir seçenek değerini kullanın.

  • Finansal planlayıcıları Monte Carlo benzetimi kendi istemcileri emeklilik için en iyi yatırım stratejileri belirlemek için kullanın.

Hücredeki formül =RAND() yazdığınızda, 0 ile 1 arasında herhangi bir değer varsayılır eşit oranda büyük olasılıkla bir sayı alın. Böylece, zaman, yaklaşık yüzde 25, bir sayı 0,25 küçük veya eşit alacak; yaklaşık yüzde 10'da bir sayı alacak zaman en az 0.90 ve vb.. S_SAYI_ÜRET işlevinin nasıl çalıştığını göstermek için Şekil 60-1'deki Randdemo.xlsx, dosyaya göz atın.

Kitap Resmi
Şekil 60-1 gösteren S_SAYI_ÜRET işlevi

Not: Randdemo.xlsx dosyayı açtığınızda, Şekil 60-1'deki aynı rasgele sayılar görmezsiniz. S_SAYI_ÜRET işlevi her zaman otomatik olarak bir çalışma sayfası açıldığında veya çalışma sayfasına yeni bilgi girerken üretir sayıları yeniden hesaplar.

İlk olarak, C3 hücreden formül =RAND()için C4:C402 kopyalayın. Ardından C3:C402 veriaralığı adlandırın. Ardından, sütun F (hücre F2) 400 rastgele sayıların ortalamasını izleyebilir ve 0 ve 0,25, 0,25 ve 0,50, 0,50 ve 0,75 ve 0,75 ve 1 arasında kesirleri belirlemek için EĞERSAY işlevini kullanın. F9 tuşuna bastığınızda, rasgele sayılar yeniden hesaplanır. 400 sayıların ortalamasını her zaman yaklaşık 0,5 ve yaklaşık yüzde 25 sonuçların 0,25 aralıklarında durumda olduğuna dikkat edin. Bu sonuçları rastgele bir sayı tanımı tutarlı. Ayrıca farklı hücrelerdeki S_SAYI_ÜRET tarafından oluşturulan değerleri bağımsız olduğuna dikkat edin. Örneğin, rastgele bir sayı oluşturursa C3 hücresinin büyük bir sayı (örneğin, 0.99), bunu bize hiçbir şey oluşturulan diğer rasgele sayı değerleri hakkında bildirir.

İsteğe bağlı bir takvim için aşağıdaki kesikli rasgele değişkeniyle tabidir varsayalım:

İsteğe bağlı

Olasılık

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Nasıl biz kaybolma veya benzetmek, bu talep takvimler için birçok kez Excel olabilir mi? S_SAYI_ÜRET işlevinin her olası değeri takvimler için olası bir talep ilişkilendirmek için eli olur. Aşağıdaki atama 10.000 isteğe bağlı zaman yüzde 10 ortaya Sessize sağlar ve.

İsteğe bağlı

Atanan rastgele bir sayı

10.000

0,10 küçüktür

20.000

Büyüktür veya eşittir 0,10 ve 0.45 azını

40.000

Büyüktür veya eşittir 0.45 ve 0,75 azını

60.000

Büyüktür veya eşittir 0,75

İsteğe bağlı benzetimi göstermek için bir sonraki sayfada şekil 60-2'deki dosya Discretesim.xlsx, bakın.

Kitap Resmi
Şekil 60-2 ayrı bir rasgele değişken taklit etme

Bizim benzetimi anahtarına rastgele bir sayı ( Aramaolarak adlandırılır) F2:G5 tablo aralıktaki bir arama başlatmak için kullanmaktır. Büyüktür veya eşittir 0 ve 0,10'den küçük rastgele sayı 10.000 isteğe bağlı ödeme; büyüktür veya eşittir 0,10 ve 0.45'den küçük rastgele sayı 20.000 isteğe bağlı ödeme; büyüktür veya eşittir 0.45 ve 0,75'den küçük rastgele sayı 40.000, isteğe bağlı ödeme; ve isteğe bağlı olarak 60.000 büyüktür veya eşittir 0,75 rasgele sayılar ortaya çıkarır. 400 rasgele sayılar, C3 C4:C402 için formül RAND()kopyalayarak oluşturun. Ardından 400 deneme veya yinelemelerini VLOOKUP(C3,lookup,2)formülü B3 B4:B402 için kopyalayarak Takvim talep oluşturun. Bu formül 0,10'den küçük rastgele bir sayı 10.000 isteğe bağlı oluşturur, herhangi bir 0,10 ve 0.45 arasında rastgele bir sayı 20.000 ve benzeri bir talep üretir sağlar. Hücre aralığı F8:F11, her talep oluşturan bizim 400 yinelemeleri oranını saptamak için EĞERSAY işlevini kullanın. Benzetim yapılmış bir olasılıklar biz rasgele sayılar yeniden hesaplamak için F9 tuşuna bastığınızda, bizim tahvil talep olasılıklar yakın verilmiştir.

Herhangi bir hücreye formül NORMINV(rand(),mu,sigma)yazarsanız, normal bir rasgele değişken ortalama mu ve standart sapma sigmayaşıyorsunuz benzetimi yapılan bir değeri oluşturur. Şekil 60-3'teki dosyasında Normalsim.xlsx, bu yordamı gösteriliyor.

Kitap Resmi
Şekil 60-3 normal rasgele değişken taklit etme

Diyelim ki 400 deneme veya 40.000 ortası ve standart sapma 10.000 normal bir rasgele değişkenle için yinelemeleri benzetmek istediğinizi varsayalım. (E1 ve E2 hücrelerine şu değerleri yazın ve hücreleri anlama ve bu sigma, sırasıyla adlandırın.) Formül =RAND() C5:C403 için C4 kopyalama 400 farklı rasgele sayılar oluşturur. B4 B5:B403 için NORMINV(C4,mean,sigma) formülü kopyalama 40.000 ortası ve standart sapma 10.000 normal bir rasgele değişkenle 400 farklı deneme değeri üretir. Biz rasgele sayılar yeniden hesaplamak için F9 tuşuna bastığınızda, 40.000 ve standart sapma 10.000 yakın yakın ortalamadan kalır.

Aslında, rastgele bir sayı için x, formül NORMINV(p,mu,sigma)psırasındaki yüzde birlik mu bir ortalama ve standart sapma sigmaile normal bir rasgele değişkenin oluşturur. Örneğin, rastgele bir sayı 0.77 C4 hücresine (bkz: Şekil 60-3) B4 hücresinde yaklaşık 77th yüzde birlik normal rasgele değişken 40.000 ortası ve standart sapma 10.000 ile oluşturur.

Bu bölümde, Monte Carlo benzetimi karar verme aracı olarak nasıl kullanılabileceğini görürsünüz. Sevgililer Günü Kartı talep aşağıdaki kesikli rasgele değişkeniyle tabidir varsayalım:

İsteğe bağlı

Olasılık

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Tebrik Kartı satan $4.00 için ve her kartı üretme değişken maliyet $1.50. Kalan kartları 0,20 kartı başına ödemeyle elden gerekir. Kaç tane kartları yazdırılacak?

Temel olarak, biz her olası üretim miktarı (10.000, 20.000, 40.000 veya 60.000) birçok kez (örneğin, 1000 yinelemeleri) benzetmek. Sonra hangi sipariş miktarı 1000 yinelemeler üzerinden en yüksek ortalama kar toplamı saptama. Şekil 60-4'teki dosyasında Valentine.xlsx, bu bölüm için verileri bulabilirsiniz. Hücreleri B1:B11 aralığı adlarında için hücreleri C1:C11 atayın. Hücre aralığı G3:H6 ad Aramaatanır. Satış fiyatı ve maliyet parametreleri sayfamızı hücreleri C4:C6 içinde girilmiş olmalıdır.

Kitap Resmi
Sevgililer Günü Kartı benzetimi şekil 60-4

C1 hücresine bir deneme üretim miktarı (Bu örnekte 40.000) girebilirsiniz. Ardından, C2 hücresindeki formül =RAND()rastgele bir sayı oluşturun. Daha önce açıklandığı gibi C3 hücresindeki formül VLOOKUP(rand,lookup,2)kartı talebi benzetmek. (DÜŞEYARA formülde S_SAYI_ÜRET hücreye C3, RAND işlevi atanan hücre adıdır.)

Satılan birim sayısı bizim üretim miktarı ve isteğe bağlı küçüktür. Formülü içeren bizim gelir, C8 hücresine hesaplamak MIN (üretilen, isteğe bağlı) * unit_price. Size toplam ürün maliyeti formülü içeren hücreye C9, hesaplamak üretilen * unit_prod_cost.

İsteğe bağlı olarak sayısından daha fazla kart biz elde etmek isterseniz, eşittir üretim talep eksi üzerinden birim sayısı sol; Tersi durumda hiç birimleri kalmış. Biz formülü içeren hücreyi C10 bizim elden maliyetini hesaplamak unit_disp_cost * Eğer (üretilen > talep, üretilen – talep, 0). Son olarak, C11 hücresinde, biz bizim kar gelir – total_var_cost-total_disposing_costhesaplamak için.

Etkili bir yolu her üretim miktarı birçok kez (örneğin, 1000) F9 tuşuna basın ve her miktar için beklenen bizim kar kaydetmesini isteriz. Bu durum iki yönlü veri tablosu bizim kurtarıcınız birlikte gelen biridir. ("Duyarlılık çözümlemesi ile veri tabloları," Bölüm 15, veri tabloları hakkında ayrıntılı bilgi için bkz.) Bu örnekte kullanılan veri tablosu şekil 60-5'te gösterilmektedir.

Kitap Resmi
Tebrik Kartı benzetim için Şekil 60-5 iki yönlü veri tablosu

Hücre aralığındaki A16:A1015 1 – 1000 (bizim 1000 denemeler karşılık gelen) sayıları girin. Bu değerleri oluşturmak için kolay bir yol A16 hücresinde 1 girerek başlatmaktır. Hücreyi seçin ve Giriş sekmesinde düzenleme grubunda, Dolgutıklatın ve seri iletişim kutusunu görüntülemek için seriyi seçin. Şekil 60-6'daki serisi iletişim kutusunda, adım değeri 1 ve 1000 durdurma değerini girin. Seri içindeki alanında, sütunları seçeneğini belirleyin ve Tamam' ı tıklatın. 1 – 1000 numaraları olacaktır başlayarak A16 hücresinde sütununda girilen.

Kitap Resmi
Şekil 60-6 deneme 1'den sayılar 1000 doldurmak için seri iletişim kutusunu kullanma

Sonraki biz bizim olası üretim miktarları (10.000, 20.000, 40.000, 60.000) hücreleri B15:E15 girin. Her deneme numarasını (1-1000) kar hesaplamak istediğiniz ve her üretim miktarı. Biz bizim veri tablosu (A15) tablonun sol üst hücresini Kar (içinde hesaplanan hücre C11) formülünü C11 =girerek bakın.

Şimdi her üretim miktarı talebi 1000 yinelemelerini taklit içine püf Excel hazır çalışıyoruz. Tablo aralığı (A15:E1014) seçin ve veri sekmesindeki Veri Araçları grubunda neler Durum Çözümlemesi'i tıklatın ve sonra veri tablosunu seçin. Bir iki yönlü veri tablosu oluşturmak için satır giriş hücresi olarak bizim üretim miktarı (C1 hücresine) seçin ve sütun giriş hücresi (hücre I14 seçtik) herhangi bir boş hücreyi seçin. Tamam'ı tıklattıktan sonra Excel her sipariş miktarı için 1000 talep değerleri benzetimini yapar.

Neden çalıştığını anlamak için hücre aralığındaki C16:C1015 veri tablosu tarafından yerleştirilen değerleri göz önünde bulundurun. C1 hücresine bir değer 20.000 her bu hücreler için Excel kullanır. C16 içinde boş bir hücre ve yeniden hesaplama zamanını C2 hücresindeki rastgele bir sayı sütun giriş hücresi değeri 1 yerleştirilir. Karşılık gelen kar sonra hücreye C16 kaydedilir. 2 sütun hücre giriş değerinin boş bir hücreye sonra yerleştirilir ve rastgele bir sayı C2 yeniden yeniden hesaplar. Karşılık gelen kar C17 hücresine girilir.

Formülü AVERAGE(B16:B1015)C13:E13 için B13 hücreden kopyalayarak, biz her üretim miktarı için ortalama benzetimi yapılan kar hesaplamak için. Formülü STDEV(B16:B1015)C14:E14 için B14 hücreden kopyalayarak, biz bizim benzetimi yapılan kar her sipariş miktarı için standart sapmayı hesaplayın. Biz, F9 tuşuna her zaman her sipariş miktarı için isteğe bağlı 1000 yinelemelerini benzetimi yapılmış. Her zaman 40,000 kartları üretme en büyük beklenen kar verir. Bu nedenle, 40,000 kartları üretme düzgün karar olduğunu görünür.

Risk bizim karar üzerindeki etkisi     Biz 20.000 40,000 kartları yerine üretilen, bizim beklenen kar yaklaşık 22 yüzde düşünceye, ancak neredeyse 73 yüzde (kar göre standart sapmasını cinsinden gibi) bizim risk bırakır. Riski aşırı averse çalışıyoruz, bu nedenle, 20.000 kartları üretme sağ karar olabilir. Arada 10.000 kartlar her zaman üretme biz 10.000 kartları elde etmek istiyorsanız, biz her zaman hepsini herhangi bir leftovers olmadan satan olduğundan standart sapması 0 kartları bulunur.

Not: Bu çalışma kitabında Hesaplama seçeneğini Tabloları dışında otomatikolarak ayarlanır. (Formüller sekmesinde hesaplama grubunda hesaplama komutunu kullanın.) Bu ayar, biz çalışma sayfanıza yazın işlem tablosunda, büyük veri tablosu çalışmanızı düşürür çünkü bunlar iyi bir fikirdir, F9'a basın sürece bizim veri tablosunu yeniden hesaplar değil, sağlar. Bu örnekte, F9 tuşuna her ortalama kar değişeceğini unutmayın. Bu, F9 tuşuna her zaman her sipariş miktarı gereksinimlerini karşılamak üretmek için farklı bir dizi 1000 rastgele sayı kullanıldığından olur.

Kar için güvenirlik aralığını anlama     Bu durumda istemek için doğal soru yüzde 95 doğru ortalama kar düşme emin çalışıyoruz hangi aralığı mi? Bu zaman aralığından yüzde 95 güvenirlik aralığını ortalama kârdenir. Herhangi bir benzetim çıktı ortalaması için bir yüzde 95 güvenirlik aralığını aşağıdaki formülle hesaplanır:

Kitap Resmi

40,000 takvimleri ile D13–1.96*D14/SQRT(1000)formül üretildiği olduğunda J11 hücresinde, yüzde 95 güvenirlik aralığını ortalama kar üzerinde alt sınırını hesaplayın. J12 hücresinde, bizim yüzde 95 güvenirlik aralığını formülle D13+1.96*D14/SQRT(1000)için üst sınırı hesaplayın. Bu hesaplamalar şekil 60-7'de gösterilir.

Kitap Resmi
Şekil 60-7 yüzde 95 güvenirlik aralığını 40,000 takvimleri sipariş ettiğinizde ortalama kâr

Yüzde 95 40,000 takvimleri sipariş ettiğinizde bizim ortalama kar $56,687 ve $62,589 arasında olduğundan emin çalışıyoruz.

  1. GMC dağıtıcı 2005 Envoys talebi normalde 200 bir ortalama ve standart sapma 30 ile dağıtılacak olduğunu düşündüğü. Bir haberci alma bildir maliyeti 25000 ve 40.000 için bir haberci satışını yaptığı. Tam fiyatla satılan değil tüm Envoys yarısını satılan $ 30. 000 için. He 200, 220, 240, 260, 280 veya 300 Envoys sıralamayı düşünüyor. Kaç tane he sipariş?

  2. Küçük bir süpermarket her haftanın sipariş Kişiler dergisi kaç kopya belirlemeye çalışıyor. Bunlar, bunların talep Kişiler için aşağıdaki kesikli rasgele değişkeniyle tabidir düşünen:

    İsteğe bağlı

    Olasılık

    15

    0,10

    20

    0,20

    25

    0.30

    30

    0,25

    35

    0,15

  3. Süpermarket $1,00 kişilerin her kopyası için ödenen ve 1.95 için satıyor. Her satılmamış olanlar Kopyala 0,50 için döndürülür. Kişilerin kaç kopya mağaza gereken sırada?

Daha fazla yardım mı gerekiyor?

Sorularınızı Excel Teknoloji Topluluğu’ndaki uzmanlara sorabilir, Yanıt topluluğu’ndan destek alabilir ya da Excel için UserVoice platformuna yeni bir özellik veya iyileştirme önerisi sunabilirsiniz.

Office yeteneklerinizi geliştirin
Eğitimleri keşfedin
Yeni özellikleri ilk olarak siz edinin
Office Insider Programına Katılın

Bu bilgi yararlı oldu mu?

Görüşleriniz için teşekkür ederiz!

Geri bildiriminiz için teşekkürler! Office destek temsilcilerimizden biriyle görüşmeniz yararlı olabilir.

×