Excel 'de Monte Carlo benzetimine 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 makale, Microsoft Excel veri analizi ve Iş modelleyerek Wayne L. Winston tarafından uyarlanmıştır.

  • Monte Carlo simülasyonu kimler kullanıyor?

  • Bir hücreye = Rand () yazdığınızda ne olur?

  • Ayrı bir rastgele değişkenin değerlerini nasıl benzetebilirim?

  • Normal rastgele değişkenin değerlerinin benzetimini nasıl benzetebilirim?

  • Tebrik kartı şirketi kaç kart üretmelidir?

Belirsiz olayların olasılıklarına doğru şekilde tahmin etmek istiyoruz. Örneğin, yeni bir ürünün para akışlarının pozitif bir net değeri (NBD) olma olasılığı nedir? Yatırım portföyümüz risk faktörü nedir? Monte Carlo benzetimi, belirsizlik sunan durumları modellemenizi ve sonra da binlerce kez bir bilgisayarda yürütmeyi mümkün kılar.

Not: Monte Carlo benzetim adı, bir atom bomb'in Detonate zincirlik için gereken zincir iş eyleminin başarılı bir şekilde çalışmasını sağlayan Bu işle ilgili olan doktor büyük bir kumarı olan büyük fanlara sahip olduğundan, Monte Carlokod adı benzetimleri verdi.

Sonraki beş bölümde, Monte Carlo benzetimleri gerçekleştirmek için Excel 'i nasıl kullanabileceğinizi gösteren örnekler göreceksiniz.

Birçok şirket karar verme sürecinin önemli bir parçası olarak Monte Carlo benzetimini kullanır. Işte bazı örnekler.

  • Genel molar, Ector ve Gablesi, Pfizer, Bristol-Myers Stibb ve eli Galy, yeni ürünlerin ortalama dönüş ve risk faktörünü tahmin etmek için benzetim kullanır. GM ile bu bilgiler, pazarda hangi ürünlerin geliyor olduğunu saptamak amacıyla CEO tarafından kullanılır.

  • GM, şirketin şirket için net geliri tahmin etme, yapısal ve satın alma maliyetleri tahminleri ve kullanım maliyetini farklı risk türlerine (faiz oranı değişiklikleri ve döviz kuru dalgalanmaları gibi) belirlemede olduğu gibi etkinlikler için benzetim kullanır.

  • Bu, her uyuşturucu için en iyi tesis kapasitesini belirlemek amacıyla benzetim kullanır.

  • ProgID ve gamutun, yabancı Exchange riskini modellemek ve en iyi şekilde uzatmak için benzetim kullanır.

  • Sears, her ürün satırının tedarikçilerine kaç tane sipariş alınacağını belirlemek için benzetim kullanır; örneğin, bu yıl sipariş edilecek Doclatoner trousers çiftlerinin sayısı.

  • Yağ ve ilaç şirketleri, bir projeyi genişletme, anlaşma veya erteleme seçeneğinin değeri gibi "gerçek Seçenekler" değerini belirleme seçeneğini kullanır.

  • Finansal planlayıcılar, Monte Carlo benzetimini kullanarak, istemcilerinin emekli oldukları en iyi yatırım stratejilerini belirler.

Bir hücreye = S_SAYI_ÜRET () formülünü yazdığınızda, 0 ile 1 arasında herhangi bir değeri varsaymak büyük olasılıkla bir sayı alırsınız. Dolayısıyla, zamanın yüzde 25 ' i oranında, 0,25 değerinden küçük veya buna eşit bir sayı almanız gerekir; zamanın yüzde 10 ' u etrafında en az 0,90 ve bu şekilde. S_SAYI_ÜRET işlevinin nasıl çalıştığını göstermek Için, şekil 60-1 ' da gösterilen, Rasgeledemo. xlsx dosyasına bir bakın.

Kitap Resmi

Not:  Rasgeledemo. xlsx dosyasını açtığınızda, şekil 60-1 ' da gösterilen rasgele sayıları görmezsiniz. S_SAYI_ÜRET işlevi, çalışma sayfası açıldığında oluşturduğu sayıları daima otomatik olarak yeniden hesaplar veya çalışma sayfasına yeni bilgiler girildiğinde.

Ilk olarak, C3 hücresinden C4: C402 formülünü C4 olarak kopyalayın . Ardından C3: C402 veriaralığını adlandırın. Ardından, F sütununda, 400 rastgele sayıların (hücre F2) ortalamasını izleyebilir ve 0 ile 0,25, 0,25 ve 0,50, 0,50 ve 0,75 ve 0,75 ile 1 arasındaki kesirleri belirlemek için EĞERSAY işlevini kullanabilirsiniz. F9 tuşuna bastığınızda rastgele numaralar yeniden hesaplanır. 400 numarasının ortalaması her zaman yaklaşık 0,5 ve sonuçların yüzde 25 ' i yaklaşık olarak 0,25 Bu sonuçlar rastgele bir sayının tanımıyla tutarlıdır. Ayrıca, S_SAYI_ÜRET tarafından üretilen değerlerin farklı hücrelerde bağımsız olduğunu unutmayın. Örneğin, C3 hücresinde üretilen rastgele sayı büyük bir sayıysa (örneğin, 0,99), üretilen diğer rastgele sayıların değerleri hakkında bize bilgi verilir.

Bir takvimin talebinin aşağıdaki ayrı rastgele değişken tarafından yönetildiğini varsayalım:

Ten

Olasılık

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Excel 'in bu talebe birçok kez çalma veya benzetimini nasıl yapabilirim? Bu, S_SAYI_ÜRET işlevinin olası her değerini takvimler için olası bir talebe ilişkilendirmekte. Aşağıdaki ödev, zamanın yüzde 10 ' unu bir talebin yüzde 10 olmasını sağlar.

Ten

Atanan rastgele numara

10.000

0,10 'den az

20.000

0,10 değerinden büyük veya buna eşit ve 0,45 değerinden küçük

40.000

0,45 değerinden büyük veya buna eşit ve 0,75 değerinden küçük

60.000

0,75 değerinden büyük veya buna eşit

Talebin benzetimini göstermek Için, Discretesim. xlsx dosyasına bakarak, sonraki sayfanın şekil 60-2.

Kitap Resmi

Benzetim metnimizin anahtarı, F2: G5 (adlandırılmış arama) tablo aralığından arama başlatmak için rastgele bir sayı kullanmaktır. 0 ' dan büyük veya buna eşit olan ve 0,10 ' dan küçük olan rastgele sayılar, 10.000 talebinde olacaktır; 0,10 değerinden büyük veya buna eşit ve 0,45 'ten küçük olan rastgele sayılar, 20.000 için bir talep verecek; 0,45 değerinden büyük veya buna eşit ve 0,75 'ten küçük olan rastgele sayılar, 40.000 için bir talep verecek; 0,75 'den büyük veya buna eşit olan rasgele sayılar, 60.000 talebini sağlayacaktır. C3 'tan C4 'ye kopyalayarak 400 rastgele sayılar, S_sayı_üret: C402 formülünü ()formülünü kullanarak oluşturabilirsiniz. Ardından B3: B402 (C3, arama, 2)formülü kullanarak 400 deneme veya yineleme oluşturabilirsiniz. Bu formül, 0,10 'den az olan herhangi bir rastgele sayının 10.000 talebi oluşturmamasını sağlar, 0,10 ve 0,45 arasındaki herhangi bir rastgele sayı, F8: F11 hücre aralığında, her talebi oluşturan 400 yinelememiz kesirini belirlemek için EĞERSAY işlevini kullanın. Rastgele sayıları yeniden hesaplamak için F9 tuşuna bastığınızda, benzetim yapılmış olan olasılıklara en çok talep olasılıklarına yakın.

NORMTERS (S_SAYI_ÜRET (), mu, Sigma)formülünü yazarsanız, ortalama mu ve standart sapma Sigma'e sahip normal bir değişken değeri üreteceksiniz. Bu yordam, şekil 60-3 gösterilen Normalsim. xlsx dosyasında gösterilmektedir.

Kitap Resmi

400 'in ortalaması olan normal rastgele bir değişken için deneme veya yinelemelerini benzetmek istediğiz, standart sapması 10.000. (Bu değerleri E1 ve E2 hücrelerine yazabilir ve bu hücreleri, sırasıyla Ortalama ve Sigmaolarak yazabilirsiniz.) = Rand () formülünü C4 'den C5: C403 B4 ile B5 arasında kopyalama: B403 (C4, ortalama, Sigma) formülü, 400 ortalaması olan normal bir rasgele değişkende farklı deneme değerleri oluşturur ve standart sapması 10.000. Rastgele sayıları yeniden hesaplamak için F9 tuşuna bastığınızda, ortalama 40.000 'e yakın, standart sapma de 10.000 öğesine yakın olarak kalır.

Aslında, bir rastgele sayı xIçin, NORMTERS (p; mu, Sigma) formülü, ortalama mu , standart sapma Sigmaiçeren normal bir rastgele değişkenin p Örneğin, C4 hücresindeki 0,77 rastgele numarası (şekil 60-3), 18 ' in ortalaması olan normal bir rastgele değişkenin yaklaşık yüzde

Bu bölümde, Monte Carlo benzetiminin karar verme aracı olarak nasıl kullanılabileceğini göreceksiniz. Sevgililer günü kartının talebinin aşağıdaki ayrı rastgele değişken tarafından yönetildiğini varsayalım:

Ten

Olasılık

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Tebrik kartı $4,00 için, her kartı üretmenin değişken maliyeti $1,50. Soltover kartları kart başına $0,20 maliyetinde elden çıkarılmalıdır. Kaç kart yazdırılsın?

Temel olarak, her olası üretim miktarını (10.000, 20.000, 40.000 veya 60.000) birçok kez benzediyoruz (örneğin, 1000 yinelemeleri). Ardından, hangi sipariş miktarının 1000 yinelemelerinin maksimum ortalama karı olduğunu saptadık. Bu bölümün verilerini, şekil 60-4 gösterilen Sevgililer. xlsx dosyasında bulabilirsiniz. B1: B11 hücrelerindeki Aralık adlarını C1: C11 hücrelerine atarsınız. G3: H6 hücre aralığına ad aramasıatanır. Satış fiyatlarımız ve maliyet parametrelerimiz C4: C6 hücresine girilir.

Kitap Resmi

C1 hücresine bir deneme üretim miktarı (Bu örnekte 40.000) girebilirsiniz. Ardından, C2 hücresinde = S_SAYI_ÜRET ()formülü ile rastgele bir sayı oluşturun. Daha önce açıklandığı gibi, C3 hücresindeki kartın talebini DÜŞEYARA (S_SAYı_ÜRET, arama, 2)ile simüle edebilirsiniz. (DÜŞEYARA formülünde S_SAYI_ÜRET , S_SAYI_ÜRET işlevinin değil, C3 hücresine atanmış olan hücre adıdır.)

Satılan birimlerin sayısı, üretim miktarımız ve Taleplerimizden daha küçük olur. C8 hücresinde, gelirlerimizi en az (üretilmiş, talep) * unit_price C9 hücresinde toplam üretim maliyetini üretilen * unit_prod_cost.

İsteğe bağlı olarak daha fazla kart üreyoruz, en fazla üretimin eksi talep eksi Aksi takdirde hiçbir birim kalmadı. C10 hücresindeki unit_disp_cost * Eğer (produced>demand, üretilmiş – talep, 0)formülünü hesapladık. Son olarak, C11 hücresinde kâyoruz- total_var_cost-total_disposing_costolarak hesaplanır.

Her bir üretim miktarı için F9 'a birçok kez (örneğin, 1000) ve her bir miktar için beklenen karı kaç kez basmanız gerektiğini istiyoruz. Bu durum, iki yönlü veri tablosunun kurtarmamıza geldiği bir yoldur. (Veri tabloları hakkında ayrıntılar için, "veri tablolarıyla duyarlılık çözümlemesi" bölümüne bakın.) Bu örnekte kullanılan veri tablosu şekil 60-5.

Kitap Resmi

A16: A1015 hücre aralığına, 1 – 1000 sayısını girin (1000 denemelerimiz ile ilgili). Bu değerleri oluşturmanın kolay bir yolu, A16 hücresine 1 girerek başlamaktır. Hücreyi seçin ve düzenleme grubundaki giriş sekmesinde doldur'a tıklayın ve seri iletişim kutusunu görüntülemek için seriler 'i seçin. Seri iletişim kutusunda, şekil 60-6 gösterilen bir adım değeri 1 ve bir durma değeri 1000 girin. Seriler alanında, sütunlar seçeneğini belirleyip Tamam'ı tıklatın. 1 – 1000 sayıları, A16 hücresinin başlangıç sütununa girilir.

Kitap Resmi

Ardından, B15: E15 hücrelerindeki olası üretim miktarlarımızı (10.000, 20.000, 40.000, 60.000) girdik. Her bir deneme numarası (1-1000) ve her üretim miktarı için karı hesaplamak istiyoruz. $ C11girerek, veri tablonuzun (A15) sol üst hücresindeki kar (hücre C11 ' de hesaplanan) öğesine başvurduğumuz.

Artık Excel 'i, her üretim miktarı için 1000 talebinin benzetimini yapmaya hazırlıyoruz. Tablo aralığını (A15: E1014) seçin ve ardından Veri sekmesindeki Veri Araçları grubunda çözümleme 'ye tıklayın ve veri tablosu 'nu seçin. İki yönlü veri tablosu ayarlamak Için, satır giriş hücresi olarak üretim miktarımızı (A1 hücresi) seçin ve sütun girişi hücresi olarak boş bir hücre seçin (I14 hücresini seçtik). Tamam 'ı tıklattıktan sonra, Excel her sipariş miktarı için 1000 talep değerlerini taklit eder.

Bunun neden çalıştığını anlamak Için, C16: C1015 hücre aralığındaki veri tablosu tarafından yerleştirilen değerleri dikkate alın. Excel, C1 hücresinde 20.000 değerini kullanacaktır. C16 'Da, 1 ' in sütun girişi hücre değeri boş bir hücreye yerleştirilir ve C2 hücresindeki rastgele numara. İlgili kâr, C16 hücresine kaydedilir. Ardından, 2 ' nin sütun hücresi giriş değeri boş bir hücreye yerleştirilir ve C2 'daki rastgele numara yeniden hesaplanır. Karşılık gelen kar C17 hücresine girilir.

B13 hücresinden C13 'e kopyalayarak, Ortalama (B16: B1015)formülü, her üretim miktarı için Ortalama benzetimli karı hesapladık. B14 hücresinden C14 'e kopyalayarak, STDSAPMA (B16: B1015)formülünü E14, her sipariş miktarı için benzetimli Karımızın standart sapmasını hesapladık. F9 tuşuna her basdığımdan, 1000 talep 40.000 kartları üretmek her zaman beklenen en büyük karı verir. Bu nedenle, 40.000 kartları üreten bir karardır.

Kararımız riskin etkisi     40.000 kartları yerine 20.000 üretiyoruz, beklenen kar yaklaşık yüzde 22 oranında, ancak risk (kar amacı standart sapması ile ölçülür) yaklaşık 73 yüzdesini bırakır. Bu nedenle, risk son derece averse, 20.000 kartı üretmemiz doğru karardır. Incidentally, 10.000 kartlarının her zaman standart bir sapma olur, çünkü 10.000 kartları oluşturmamız nedeniyle her

Not:  Bu çalışma kitabında, tablo dışındaHesaplama seçeneği otomatik olarak ayarlanır. (Formüller sekmesinin hesaplama grubundaki hesaplama komutunu kullanın.) Bu ayar, çalışma sayfanıza her şey yazdığınızda, büyük bir veri tablosu yeniden hesaplandıklarında işinizi yavaşlatacak bir fikir olduğu için, Bu örnekte, F9 tuşuna bastığınızda ortalama kar değişikliğinin değiştirileceği unutmayın. Bu, her sipariş miktarı için talepler oluşturmak üzere F9 tuşuna her bastığınızda, bunun nedeni bir dizi 1000 rastgele sayı kullanılır.

Ortalama kar için güvenirlik aralığı     Bu durumda sorduğunuz bir soru, doğru ortalamaların düştüğünü yüzde 95 Bu aralığa, ortalama kar için 95 Güvenirlik güvenilirliği aralığıbelirtilir. Herhangi bir benzetim çıkışının ortalaması için yüzde 95 Güvenirlik aralığı aşağıdaki formülle hesaplanır:

Kitap Resmi

J11 hücresinde, 40.000 takvimleri D13 – 1.96 * D14/karekök (1000)formülü ile üretildiğinde, ortalama kar durumunda, yüzde 95 güvenilirlik aralığının alt sınırını hesaplar. J12 hücresinde, 95 + 1.96 * D14/karekök (1000)formülü ile yüzde güven aralığı için üst sınırı hesaplamalısınız. Bu hesaplamalar şekil 60-7.

Kitap Resmi

40.000 takvimleri sıralı olarak yüzde 95, $56.687 ve $62.589 arasındadır.

  1. GMC dağıtıcı, 2005-Voys talebinin normalde 200 ortalaması ve standart sapması 30 ile dağıtılabileceği anlamına gelir. Bir Envoy alma maliyeti $25.000 ve $40.000 için bir Envoy satmaktadır. Tam fiyata satılmayan tüm Envoys 'in yarısı $30.000 için satılabilir. 200, 220, 240, 260, 280 veya 300 Ne kadar sıralı?

  2. Küçük bir süper Pazar, her hafta bir sipariş etmesi gereken insanların sayısını belirlemeye çalışıyor. Kişilerin talebine, aşağıdaki ayrı rasgele değişkenle tabidir.

    Ten

    Olasılık

    15

    0,10

    20

    0,20

    56

    0,30

    30

    0,25

    35

    0,15

  3. Süper Pazar, kişilerin her kopyası için $1,00 ve bu uygulamayı $1,95 için satanlar. Satılmayan her kopya $0,50 için geri döndürülebilir. Kaç kişinin mağaza siparişi olması gerekir?

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.

×