Excel'de Monte Carlo benzetimine giriş

Bu makale, Wayne L. Winston tarafından yazılan Microsoft Excel Data Analysis and Business Modeling kitabından uyarlanmıştır.

Genel bakış

• Monte Carlo simülasyonunu kimler kullanır?
• Bir hücreye =S_SAYI_ÜRET() yazarsanız ne olur?
• Ayrık bir rassal değişkenin değerlerini nasıl simüle edebilirsiniz?
• Normal bir rassal değişkenin değerlerini nasıl simüle edebilirsiniz?
• Bir tebrik kartı şirketi kaç kart üreteceğini nasıl belirleyebilir?

Belirsiz olayların olasılıklarını doğru bir şekilde tahmin etmek istiyoruz. Örneğin, yeni bir ürünün nakit akışlarının pozitif net bugünkü değeri (NBD) olma olasılığı nedir? Yatırım portföyümüzün risk faktörü nedir? Monte Carlo simülasyonu, belirsizlik sunan durumları modellememizi ve daha sonra bunları bir bilgisayarda binlerce kez oynatmamızı sağlar.

Sonraki beş bölümde, Monte Carlo benzetimlerini gerçekleştirmek için Excel'i nasıl kullanabileceğinize ilişkin örnekler göreceksiniz.

Monte Carlo simülasyonunu kimler kullanır?

Birçok şirket, Monte Carlo simülasyonunu karar verme süreçlerinin önemli bir parçası olarak kullanıyor. İşte birkaç örnek.

• General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb ve Eli Lilly, yeni ürünlerin hem ortalama getirisini hem de risk faktörünü tahmin etmek için simülasyon kullanıyor. GM'de bu bilgiler CEO tarafından hangi ürünlerin piyasaya sürüleceğini belirlemek için kullanılır.
• GM, şirket için net geliri tahmin etmek, yapısal ve satın alma maliyetlerini tahmin etmek ve farklı risk türlerine (faiz oranı değişiklikleri ve döviz kuru dalgalanmaları gibi) duyarlılığını belirlemek gibi faaliyetler için simülasyon kullanır.
• Lilly, her bir ilaç için en uygun tesis kapasitesini belirlemek için simülasyon kullanır.
• Proctor and Gamble, döviz riskini modellemek ve en iyi şekilde korumak için simülasyon kullanır.
• Sears, tedarikçilerden her bir ürün grubundan kaç adet sipariş edilmesi gerektiğini belirlemek için simülasyon kullanır (örneğin, bu yıl sipariş edilmesi gereken çift Dockers pantolon sayısı).
• Petrol ve ilaç şirketleri, bir projeyi genişletme, daraltma veya erteleme seçeneğinin değeri gibi "gerçek seçeneklere" değer vermek için simülasyon kullanır.
• Finansal planlamacılar, müşterilerinin emekliliği için en uygun yatırım stratejilerini belirlemek için Monte Carlo simülasyonunu kullanır.

Bir hücreye =S_SAYI_ÜRET() yazarsanız ne olur?

Bir hücreye =S_SAYI_ÜRET() formülünü yazdığınızda, 0 ile 1 arasında bir değer varsayma olasılığı eşit olan bir sayı elde edersiniz. Bu nedenle, zamanın yaklaşık yüzde 25'inde 0,25'ten küçük veya buna eşit bir sayı almalısınız; Zamanın yaklaşık yüzde 10'unda en az 0,90 olan bir sayı almanız gerekir ve bu böyle devam eder. S_SAYI_ÜRET işlevinin nasıl çalıştığını göstermek için, Şekil 60-1'de gösterilen dosya Randdemo.xlsx bir göz atın.

Önce, C3 hücresinden =S_SAYI_ÜRET() formülünü C4:C402'ye kopyalayın. Ardından aralığı C3:C402 Veri olarak adlandırırsınız. Ardından, F sütununda, rastgele 400 sayının (F2 hücresi) ortalamasını izleyebilir ve 0 ile 0,25, 0,25 ile 0,50, 0,50 ile 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 sayılar yeniden hesaplanır. 400 sayının ortalamasının her zaman yaklaşık 0,5 olduğunu ve sonuçların yaklaşık yüzde 25'inin 0,25'lik aralıklarla yapıldığını unutmayın. Bu sonuçlar rastgele bir sayının tanımıyla tutarlıdır. Ayrıca, farklı hücrelerde S_SAYI_ÜRET tarafından üretilen değerlerin bağımsız olduğunu da unutmayın. Örneğin, C3 hücresinde oluşturulan rasgele sayı çok büyük bir sayıysa (örneğin, 0,99), oluşturulan diğer rasgele sayıların değerleri hakkında bize hiçbir şey söylemez.

Ayrık bir rassal değişkenin değerlerini nasıl simüle edebilirsiniz?

Bir takvime olan talebin aşağıdaki ayrık rastgele değişken tarafından yönetildiğini varsayalım:

Talep	Olasılık
10.000	0,10
20.000	0.35
40,000	0,3
60.000	0,25

Excel'in takvimlere yönelik bu talebi birçok kez gerçekleştirmesini veya benzetimini yapmasını nasıl sağlayabiliriz? Bu işin püf noktası, S_SAYI_ÜRET işlevinin her olası değerini olası bir takvim talebiyle ilişkilendirmektir. Aşağıdaki atama, 10.000'lik bir talebin zamanın yüzde 10'unda gerçekleşmesini sağlar ve bu şekilde devam eder.

Talep	Rastgele numara atanmış
10.000	0,10'dan az
20.000	0,10'a eşit veya daha büyük ve 0,45'ten küçük
40,000	0,45'e eşit veya daha büyük ve 0,75'ten küçük
60.000	0,75'ten büyük veya buna eşit

Talebin simülasyonunu göstermek için, bir sonraki sayfada Şekil 60-2'de gösterilen dosya Discretesim.xlsx bakın.

Simülasyonumuzun anahtarı, F2:G5 (adlandırılmış arama) tablo aralığından bir arama başlatmak için rastgele bir sayı kullanmaktır. 0'a eşit veya 0'dan büyük ve 0,10'dan küçük rastgele sayılar 10.000'lik bir talep verecektir; 0,10'a eşit veya daha büyük ve 0,45'ten küçük rastgele sayılar 20.000'lik bir talep verecektir; 0,45'e eşit veya daha büyük ve 0,75'ten küçük rastgele sayılar 40.000'lik bir talep verecektir; ve 0,75'e eşit veya daha büyük rastgele sayılar 60.000'lik bir talep verecektir. S_SAYI_ÜRET() formülünü C3'ten C4:C402'ye kopyalayarak 400 rastgele sayı üretirsiniz. Daha sonra, DÜŞEYARA(C3,bak,2) formülünü B3'ten B4:B402'ye kopyalayarak, takvim talebinin 400 denemesini veya yinelemesini oluşturursunuz. Bu formül, 0,10'dan küçük herhangi bir rastgele sayının 10.000'lik bir talep oluşturmasını, 0,10 ile 0,45 arasındaki herhangi bir rastgele sayının 20.000'lik bir talep oluşturmasını vb. sağlar. F8:F11 hücre aralığında, 400 yinelememizin her talebi veren kesirini belirlemek için EĞERSAY işlevini kullanın. Rastgele sayıları yeniden hesaplamak için F9'a bastığımızda, simüle edilmiş olasılıklar, varsayılan talep olasılıklarımıza yakındır.

Normal bir rassal değişkenin değerlerini nasıl simüle edebilirsiniz?

Herhangi bir hücreye NORMTERS(rand(),mu,sigma) formülünü yazarsanız, ortalama mu ve standart sapma sigmasına sahip normal bir rastgele değişkenin simüle edilmiş bir değerini oluşturursunuz. Bu prosedür, Şekil 60-3'te gösterilen dosya Normalsim.xlsx'nde gösterilmiştir.

Ortalaması 40.000 ve standart sapması 10.000 olan normal bir rastgele değişken için 400 denemenin veya yinelemenin benzetimini yapmak istediğimizi varsayalım. (Bu değerleri E1 ve E2 hücrelerine yazabilir ve bu hücreleri sırasıyla ortalama ve sigma olarak adlandırabilirsiniz.) =S_ÜRETE() formülünü C4'ten C5:C403'e kopyalamak 400 farklı rastgele sayı üretir. B4'ten B5:B403'e kopyalandığında, NORMTERS(C4,ortalama,sigma) formülü, ortalaması 40.000 ve standart sapması 10.000 olan normal bir rastgele değişkenden 400 farklı deneme değeri üretir. Rastgele sayıları yeniden hesaplamak için F9 tuşuna bastığımızda, ortalama 40.000'e ve standart sapma 10.000'e yakın kalır.

Esasen, rastgele bir x sayısı için, NORMTERS(p,mu,sigma) formülü, ortalama mu ve standart sapma sigma ile normal bir rastgele değişkenin pinci yüzdelik dilimini üretir. Örneğin, C4 hücresindeki rastgele sayı 0,77 (bkz. Şekil 60-3), B4 hücresinde, ortalaması 40.000 ve standart sapması 10.000 olan normal bir rasgele değişkenin yaklaşık 77. yüzdebirliğini oluşturur.

Bir tebrik kartı şirketi kaç kart üreteceğini nasıl belirleyebilir?

Bu bölümde, Monte Carlo simülasyonunun bir karar verme aracı olarak nasıl kullanılabileceğini göreceksiniz. Bir Sevgililer Günü kartına olan talebin aşağıdaki ayrık rastgele değişken tarafından yönetildiğini varsayalım:

Talep	Olasılık
10.000	0,10
20.000	0.35
40,000	0,3
60.000	0,25

Tebrik kartı 4,00 dolara satılıyor ve her kartı üretmenin değişken maliyeti 1,50 dolar. Kalan kartlar, kart başına 0,20 ABD doları maliyetle imha edilmelidir. Kaç kart basılmalıdır?

Temel olarak, olası her üretim miktarını (10.000, 20.000, 40.000 veya 60.000) birçok kez (örneğin, 1000 yineleme) simüle ediyoruz. Ardından, 1000 yineleme boyunca hangi sipariş miktarının maksimum ortalama kâr sağladığını belirleriz. Bu bölümün verilerini Şekil 60-4'te gösterilen dosya Valentine.xlsx bulabilirsiniz. B1:B11 hücrelerindeki aralık adlarını C1:C11 hücrelerine atarsınız. Aramada G3:H6 hücre aralığına atanır. Satış, fiyat ve maliyet parametrelerimiz C4:C6 hücrelerine girilir.

C1 hücresine deneme üretim miktarını (bu örnekte 40.000) girebilirsiniz. Ardından, C2 hücresinde =S_S_SAYI_ÜRET() formülüyle rastgele bir sayı oluşturun. Daha önce açıklandığı gibi, DÜŞEYARA(rand,bak,2) formülüyle C3 hücresindeki karta olan talebin benzetimini yaparsınız. (DÜŞEYARA formülünde S_SAYI_ÜRET işlevi değil RAND C3 hücresine atanmış hücre adıdır.)

Satılan birim sayısı, üretim miktarımızdan ve talebimizden daha küçüktür. C8 hücresinde, MİN(üretilen,talep)*unit_price formülündeki gelirimizi hesaplarsınız. C9 hücresinde, üretilen formülle toplam üretim maliyetini hesaplarsınız*unit_prod_cost.

Talep edilenden daha fazla kart üretirsek, kalan birim sayısı üretim eksi talebe eşittir; aksi takdirde hiçbir birim kalmaz. C10 hücresindeki elden çıkarma maliyetimizi unit_disp_cost*EĞER(üretilen>talep, üretilen talep-talep,0)" formülüyle hesaplıyoruz. Son olarak, C11 hücresinde kârımızı total_var_cost-total_disposing_cost gelir olarak hesaplıyoruz.

Her üretim miktarı için F9'a birçok kez (örneğin, 1000) basmanın ve her miktar için beklenen kârımızı hesaplamanın verimli bir yolunu istiyoruz. Bu durum iki yönlü bir veri tablosunun imdadımıza yetiştiği bir durumdur. (Veri tabloları hakkında ayrıntılı bilgi için Bölüm 15, "Veri Tablolarıyla Duyarlılık Çözümlemesi"ne bakın.) Bu örnekte kullanılan veri tablosu Şekil 60-5'te gösterilmektedir.

A16:A1015 hücre aralığına 1 ile 1000 arasındaki sayıları girin (bizim 1000 denememize karşılık gelir). Bu değerleri oluşturmanın kolay bir yolu, A16 hücresine 1 girerek başlamaktır. Hücreyi seçin, Giriş sekmesininDüzenleme grubunda Doldur'u tıklatın ve Serileriletişim kutusunu görüntülemek için Seriler'i seçin. Şekil 60-6'da gösterilen Seri iletişim kutusunda, 1 Adım Değeri ve 1000 Durdurma Değeri girin. Seri Girişi alanında, Sütunlar seçeneğini seçin ve Tamam'ı tıklatın. 1-1000 sayıları A16 hücresinden başlayarak A sütununa girilir.

Daha sonra olası üretim miktarlarımızı (10.000, 20.000, 40.000, 60.000) B15:E15 hücrelerine girelim. Her deneme numarası (1 ile 1000 arasında) ve her üretim miktarı için karı hesaplamak istiyoruz. Veri tablomuzun sol üst hücresindeki (A15) kar formülüne (C11 hücresinde hesaplanan) =C11 girerek başvuruda bulunuruz.

Artık her üretim miktarı için talebin 1000 yinelemesini simüle etmesi için Excel'i kandırmaya hazırız. Tablo aralığını (A15:E1014) seçin, ardından Veri sekmesindeki Veri Araçları grubunda What If Çözümleme'ye tıklayın ve sonra Veri Tablosu'nu seçin. İki yönlü bir veri tablosu oluşturmak için, Satır Giriş Hücresi olarak üretim miktarımızı (hücre C1) seçin ve Sütun Giriş Hücresi olarak boş bir hücre (I14 hücresini seçtik) seçin. Tamam'a tıkladıktan sonra, Excel her sipariş miktarı için 1000 talep değerinin benzetimini yapar.

Bunun neden işe yaradığını anlamak için, veri tablosunun C16:C1015 hücre aralığına yerleştirdiği değerleri göz önünde bulundurun. Bu hücrelerin her biri için Excel, C1 hücresindeki 20.000 değerini kullanır. C16'da, 1 sütun giriş hücresi değeri boş bir hücreye yerleştirilir ve C2 hücresindeki rastgele sayı yeniden hesaplanır. Buna karşılık gelen kâr daha sonra C16 hücresine kaydedilir. Daha sonra 2 sütun hücresi giriş değeri boş bir hücreye yerleştirilir ve C2 hücresindeki rastgele sayı yeniden hesaplanır. İlgili kar C17 hücresine girilir.

B13 hücresinden ORTALAMA(B16:B1015) formülünü C13:E13'e kopyalayarak, her üretim miktarı için ortalama simüle edilmiş karı hesaplıyoruz. STDSAPMA(B16:B1015) formülünü B14 hücresinden C14:E14'e kopyalayarak, her sipariş miktarı için simüle edilmiş karlarımızın standart sapmasını hesaplıyoruz. F9'a her bastığımızda, her sipariş miktarı için 1000 talep yinelemesi simüle edilir. 40.000 kart üretmek her zaman beklenen en büyük kârı sağlar. Bu nedenle, 40.000 kart üretmenin doğru karar olduğu görülüyor.

Riskin Kararımız Üzerindeki Etkisi 40.000 kart yerine 20.000 kart üretirsek, beklenen kârımız yaklaşık yüzde 22 düşer, ancak riskimiz (kârın standart sapmasıyla ölçüldüğünde) neredeyse yüzde 73 düşer. Bu nedenle, riske karşı son derece isteksizsek, 20.000 kart üretmek doğru karar olabilir. Bu arada, 10.000 kart üretmenin standart sapması her zaman 0 karttır çünkü 10.000 kart üretirsek, hepsini her zaman kalıntı olmadan satacağız.

Ortalama kar için güvenirlik aralığı Bu durumda sorulması gereken doğal bir soru, gerçek ortalama kârın hangi aralığa düşeceğinden yüzde 95 eminiz? Bu aralığa, ortalama kâr için yüzde 95 güven aralığı denir. Herhangi bir simülasyon çıktısının ortalaması için yüzde 95 güven aralığı aşağıdaki formülle hesaplanır:

J11 hücresinde, D13–1,96*D14/KAREKÖK(1000) formülüyle 40.000 takvim üretildiğinde, ortalama kârın yüzde 95 güven aralığının alt sınırını hesaplarsınız. J12 hücresinde, yüzde 95 güvenirlik aralığımızın üst sınırını D13+1,96*D14/KAREKÖK(1000) formülünü kullanarak hesaplarsınız. Bu hesaplamalar Şekil 60-7'de gösterilmiştir.

40.000 takvim sipariş edildiğinde ortalama kârımızın 56.687 ila 62.589 dolar arasında olduğundan yüzde 95 eminiz.

Sorunlar

1. Bir GMC bayisi, 2005 Elçilerine olan talebin ortalama 200 ve standart sapma 30 ile normal olarak dağıtılacağına inanıyor. Bir Elçi almanın maliyeti 25.000 dolar ve bir Elçiyi 40.000 dolara satıyor. Tam fiyattan satılmayan tüm Elçilerin yarısı 30.000 dolara satılabilir. 200, 220, 240, 260, 280 veya 300 Elçi sipariş etmeyi düşünüyor. Kaç tane sipariş etmeli?

2. Küçük bir süpermarket, her hafta People dergiden kaç kopya sipariş etmeleri gerektiğini belirlemeye çalışıyor. People taleplerinin aşağıdaki ayrık rastgele değişken tarafından yönetildiğine inanıyorlar:

   Talep	Olasılık
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0,15

3. Süpermarket, People her kopyası için 1,00 dolar ödüyor ve 1,95 dolara satıyor. Satılmayan her kopya 0,50 ABD doları karşılığında iade edilebilir. Mağaza People kaç kopya sipariş etmeli?

Daha fazla yardım mı gerekiyor?

Dilediğiniz zaman Excel Teknoloji Topluluğundaki uzmanlara sorabilir veya Topluluklar'dan destek alabilirsiniz.