Excel'de Monte Carlo benzetimine giriş

• Monte Carlo simülasyonu kim kullanıyor?

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

• Ayrık rastgele değişkenin değerlerinin simülasyonunu nasıl yapabilirsiniz?

• Normal bir rastgele değişkenin değerlerinin simülasyonunu nasıl yapabilirsiniz?

• Tebrik kartı şirketi kaç kart üretileceğ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ğere (NBD) sahip olma olasılığı nedir? Yatırım portföyümüzün risk faktörü nedir? Monte Carlo simülasyonu, belirsizliği ortaya koyan durumları modellememizi ve ardından bunları binlerce kez bilgisayarda oynamamızı sağlar.

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

Birçok şirket, karar alma sürecinin önemli bir parçası olarak Monte Carlo simülasyonu kullanmaktadır. Aşağıda bazı örnekler verilmiştir.

• General Motors, Proctor ve 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ır. GM'de, bu bilgiler CEO tarafından hangi ürünlerin pazara çıkarıldığına karar vermek için kullanılır.

• GM, şirketin net gelirini tahmin etme, yapısal ve satın alma maliyetlerini tahmin etme ve farklı risk türlerine karşı duyarlılığını belirleme (faiz oranı değişiklikleri ve döviz kuru dalgalanmaları gibi) gibi etkinlikler için simülasyon kullanır.

• Lilly, her ilaç için en uygun bitki kapasitesini belirlemek için simülasyon kullanır.

• Proctor ve Gamble, döviz riskini modellemek ve en uygun şekilde hedge etmek için simülasyon kullanır.

• Denizciler, her bir ürün hattının kaç adet biriminin tedarikçilerden sipariş edilmesi gerektiğini belirlemek için simülasyon kullanır; örneğin, bu yıl sipariş edilmesi gereken Docker pantolon çiftlerinin sayısı.

• Petrol ve ilaç şirketleri, projeyi genişletme, sözleşme yapma veya erteleme seçeneği gibi "gerçek seçeneklere" değer vermek için simülasyon kullanır.

• Finansal planlayıcılar, müşterilerinin kullanımdan kaldırılması için en uygun yatırım stratejilerini belirlemek için Monte Carlo simülasyonu kullanır.

Bir hücreye =SAYI_SAYI() 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ı almanız gerekir; yaklaşık yüzde 10 en az 0,90 gibi bir sayı almanız gerekir. S_SAYI_ÜRET işlevinin nasıl çalıştığını göstermek için Şekil 60-1'de gösterilen Randdemo.xlsx dosyasına göz atın.

İlk olarak, C3 hücresinden C4:C402'ye =RAND() formülünü kopyalayın. Ardından aralığı C3:C402 Verileri olarak adlandırın. Ardından F sütununda 400 rastgele sayının (F2 hücresi) ortalamasını izleyebilir ve EĞERSAY işlevini kullanarak 0 ile 0,25, 0,25 ve 0,50, 0,50 ve 0,75 ile 0,75 ve 0,75 ile 1 arasındaki kesirleri belirleyebilirsiniz. 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ğuna ve sonuçların yaklaşık yüzde 25'inin 0,25 aralıklarla olduğuna dikkat edin. Bu sonuçlar rastgele bir sayının tanımıyla tutarlıdır. Ayrıca, farklı hücrelerde S_SAYI_ÜRET tarafından oluşturulan değerlerin bağımsız olduğunu unutmayın. Örneğin, C3 hücresinde oluşturulan rastgele sayı büyük bir sayıysa (örneğin, 0,99), oluşturulan diğer rastgele sayıların değerleri hakkında hiçbir şey söylemez.

Takvim talebinin aşağıdaki ayrık rastgele değişkene tabi olduğunu varsayalım:

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

Excel'in takvimler için bu talebi birçok kez gerçekleştirmesini veya benzetimini nasıl gerçekleştirmesini sağlayabiliriz? İşin püf noktası, S_SAYI_ÜRET işlevinin her olası değerini takvimler için olası bir taleple ilişkilendirmektir. Aşağıdaki atama, 10.000 talebin yüzde 10 oranında gerçekleşmesini sağlar ve bu şekilde devam eder.

Talep	Rastgele sayı atandı
10.000	0,10'dan küçük
20.000	0,10'dan büyük veya buna eşit ve 0,45'ten küçük
40,000	0,45'ten büyük veya buna eşit ve 0,75'ten küçük
60.000	0,75'ten büyük veya buna eşit

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

Simülasyonumuzun anahtarı, F2:G5 (arama adlı) tablo aralığından arama başlatmak için rastgele bir sayı kullanmaktır. 0'a eşit veya 0,10'dan büyük ve 0,10'dan küçük rastgele sayılar 10.000 talep verir; 0,10'dan büyük veya buna eşit ve 0,45'ten küçük rastgele sayılar 20.000 talep verir; 0,45'ten büyük veya buna eşit ve 0,75'ten küçük rastgele sayılar 40.000 talep verir; ve 0,75'ten büyük veya buna eşit rastgele sayılar 60.000 talep verir. S_SAYI_ÜRET() formülünü C3'ten C4:C402'ye kopyalayarak 400 rastgele sayı oluşturursunuz. Ardından, DÜŞEYARA(C3,arama,2) formülünü B3'ten B4:B402'ye kopyalayarak takvim talebi için 400 deneme veya yineleme oluşturursunuz. Bu formül, 0,10'dan küçük herhangi bir rastgele sayının 10.000 talep oluşturmasını, 0,10 ile 0,45 arasındaki herhangi bir rastgele sayının 20.000 gibi bir talep oluşturmasını sağlar. F8:F11 hücre aralığında ÇOKEĞERSAY işlevini kullanarak 400 yinelememizin her talebin kesirini belirleyin. Rastgele sayıları yeniden hesaplamak için F9 tuşuna bastığımızda, simülasyon olasılıkları varsayılan talep olasılıklarımıza yakındır.

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

Ortalaması 40.000 ve standart sapması 10.000 olan normal bir rastgele değişken için 400 deneme veya yineleme simülasyonu yapmak istediğimizi düşünelim. (Bu değerleri E1 ve E2 hücrelerine yazabilir ve bu hücreleri sırasıyla mean ve sigma olarak adlandırabilirsiniz.) =RAND() formülünü C4'ten C5:C403'e kopyalamak 400 farklı rastgele sayı oluşturur. B4'ten B5:B403'e kopyalama NORMTERS(C4,ortalama,sigma) formülü, ortalama 40.000 ve standart sapma 10.000 olan normal bir rastgele değişkenden 400 farklı deneme değeri oluşturur. Rastgele sayıları yeniden hesaplamak için F9 tuşuna bastığımızda ortalama 40.000'e, standart sapma ise 10.000'e yakın kalır.

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

Bu bölümde Monte Carlo simülasyonlarının karar alma aracı olarak nasıl kullanılabileceğini göreceksiniz. Sevgililer Günü kartı talebinin aşağıdaki ayrık rastgele değişkene tabi olduğunu 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 ABD doları karşılığında satılır ve her kartı üretmenin değişken maliyeti 1,50 ABD dolarıdır. Kart başına 0,20 ABD doları ücret karşılığında artık kartların atılması gerekir. Kaç kart yazdırılmalıdır?

Temel olarak, her olası üretim miktarını (10.000, 20.000, 40.000 veya 60.000) birçok kez benzetiyoruz (örneğin, 1000 yineleme). Ardından hangi sipariş miktarının 1000 yineleme üzerinde en yüksek ortalama kârı verdiğini 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. G3:H6 hücre aralığına ad araması atanır. Satış fiyatımız ve maliyet parametrelerimiz C4:C6 hücrelerine girilir.

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

Satılan birim sayısı, üretim miktarımızın ve talebimizin küçük olmasıdır. C8 hücresinde gelirimizi MIN(üretilen,talep)*unit_price formülüyle hesaplarsınız. C9 hücresinde, üretilen*unit_prod_cost formülüyle toplam üretim maliyetini hesaplarsınız.

Talepten daha fazla kart üretirsek, kalan birim sayısı üretim eksi talebe eşit olur; aksi takdirde hiçbir birim bırakılmaz. Elden çıkarma maliyetimizi C10 hücresinde unit_disp_cost*EĞER (talep>üretilen,üretilen-talep,0) formülüyle hesaplıyoruz. Son olarak, C11 hücresinde kârımızı gelir olarak hesaplıyoruz : total_var_cost-total_disposing_cost.

F9'a her üretim miktarı için birçok kez (örneğin, 1000) basmak ve her miktar için beklenen kârı tahmin etmek için verimli bir yol istiyoruz. Bu durum, iki yönlü bir veri tablosunun kurtarmamıza geldiği durumdur. (Veri tabloları hakkındaki ayrıntılar için bkz. "Veri Tablolarıyla Duyarlılık Analizi" adlı 15. Bölüm.) Bu örnekte kullanılan veri tablosu Şekil 60-5'te gösterilmiştir.

A16:A1015 hücre aralığına 1-1000 (1000 denememize karşılık gelen) sayıları girin. Bu değerleri oluşturmanın kolay yollarından biri, A16 hücresine 1 girerek başlamaktır. Hücreyi seçin ve ardından Düzenleme grubundaki Giriş sekmesinde Doldur'a tıklayın ve Seriler iletişim kutusunu görüntülemek için Seriler'i seçin. Şekil 60-6'da gösterilen Seriler iletişim kutusunda, 1 Adım Değeri ve 1000 Durdurma Değeri girin. Seri Yeri alanında Sütunlar seçeneğini belirleyin ve ardından Tamam'a tıklayın. A16 hücresinde başlayan A sütununa 1-1000 sayıları girilir.

Ardından B15:E15 hücrelerine olası üretim miktarlarımızı (10.000, 20.000, 40.000, 60.000) giriyoruz. Her deneme numarası (1 ile 1000 arasında) ve her üretim miktarı için kar hesaplamak istiyoruz. =C11 girerek veri tablomuzun (A15) sol üst hücresindeki kar formülüne (C11 hücresinde hesaplanır) başvuruyoruz.

Artık Excel'i her üretim miktarı için 1000 talep yinelemesi simülasyonu yapmak üzere kandırmaya hazırız. Tablo aralığını seçin (A15:E1014) ve ardından Veri sekmesinin Veri Araçları grubunda Durum Analizi'ne tıklayın ve ardından Veri Tablosu'nu seçin. İki yönlü bir veri tablosu ayarlamak için Üretim miktarımızı (C1 hücresi) Satır Giriş Hücresi olarak 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 simülasyonunu oluşturur.

Bunun neden çalıştığını anlamak için C16:C1015 hücre aralığındaki veri tablosu tarafından yerleştirilen değerleri göz önünde bulundurun. Bu hücrelerin her biri için Excel, C1 hücresinde 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. Karşılık gelen kar daha sonra C16 hücresine kaydedilir. Ardından 2 sütun hücresi giriş değeri boş bir hücreye yerleştirilir ve C2'deki rastgele sayı yeniden hesaplanır. karşılık gelen kar C17 hücresine girilir.

ORTALAMA (B16:B1015) formülünü B13 hücresinden C13:E13 hücresine kopyalayarak, her üretim miktarı için ortalama simülasyon kârını hesaplarız. STDSAPMA(B16:B1015) formülünü B14 hücresinden C14:E14 hücresine kopyalayarak, her sipariş miktarı için simülasyon kârlarımızın standart sapması hesaplanır. F9 tuşuna her bastığımızda, her sipariş miktarı için 1000 talep yinelemesi simülasyonu yapılmıştır. 40.000 kart üretmek her zaman beklenen en büyük kârı verir. Bu nedenle, doğru kararın 40.000 kart üretilmesi olduğu anlaşılıyor.

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

Ortalama Kar için Güvenilirlik Aralığı      Bu durumda sormamız gereken doğal bir soru, gerçek ortalama kârın hangi aralıkta düşeceğinden yüzde 95 eminiz? Bu aralık, ortalama kar için yüzde 95 olasılık aralığı olarak adlandırılır. Simülasyon çıkışlarının ortalaması için yüzde 95 güvenilirlik aralığı aşağıdaki formülle hesaplanır:

J11 hücresinde, D13–1,96*D14/SQRT(1000) formülüyle 40.000 takvim üretildiğinde ortalama kar üzerindeki yüzde 95 olasılık aralığı için alt sınırı hesaplarsınız. J12 hücresinde, yüzde 95 olasılık aralığımızın üst sınırını D13+1,96*D14/SQRT(1000) formülüyle hesaplarsınız. Bu hesaplamalar Şekil 60-7'de gösterilir.

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

1. GMC bayisi, 2005 Elçilerine olan talebin normalde ortalama 200 ve standart sapma 30 ile dağıtılacağına inanıyor. Elçi alma maliyeti 25.000 dolar ve 40.000 dolara bir Elçi satıyor. Tam fiyata satılmayan 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 Kişiler derginin kaç kopyasını sipariş etmesi gerektiğini belirlemeye çalışıyor. Kişiler talebinin aşağıdaki ayrık rastgele değişkene tabi olduğuna inanıyorlar:

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

3. Süpermarket her bir Kişiler 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 döndürülebilir. Mağaza sipariş Kişiler kaç kopya gerekir?