Kurs
Case Study: Net Revenue Management in Excel
4 sa
4.8K
Aslen Monako'daki Monte Carlo Kumarhanesi'nden adını alan Monte Carlo yöntemleri, finans, mühendislik, tedarik zinciri ve bilim gibi alanlarda, girdilerinde önemli belirsizlik barındıran olguları modellemek için yaygın olarak kullanılır.
Peki Monte Carlo simülasyonu nedir? Nasıl çalışır? Simülasyonu nasıl uygular ve sonuçları nasıl analiz ederim?
Bu eğitim, sizi Monte Carlo simülasyonuna ve tekniğin arkasındaki ilgili istatistiksel kavramlara giriş niteliğinde tanıştıracak. Ayrıca Monte Carlo simülasyonunu Excel'de uygulayarak ilgili Excel yerleşik işlevlerine de aşina olacağız.
Son olarak, bu eğitim size en iyi uygulamalar, ileri teknikler ve ek kaynaklar bırakacak; böylece Microsoft Excel'de Monte Carlo simülasyonuna dair her şeyi öğrenmek için tek durak noktanız olacak.
Monte Carlo Simülasyonu Nedir?
Monte Carlo Simülasyonu, rastgele değişkenlerin müdahalesi nedeniyle kolayca tahmin edilemeyen bir süreçte farklı sonuçların olasılığını modellemek için kullanılan matematiksel bir tekniktir.
Çeşitli alanlarda risk ve belirsizliğin etkisini anlamak için güçlü bir araçtır. Yöntem, karmaşık sistem ve süreçlerin davranışını simüle etmek için tekrarlanan rastgele örneklemeye dayanır.
Önce, doğasında belirsizlik taşıyan her bir değişken için bir olasılık dağılımıyla problem modellenir. Daha sonra bu olasılık dağılımlarından çok sayıda rastgele örnek çekilir ve bu örnekler sonuçları hesaplamak için kullanılır. Bu süreç çok kez tekrarlanarak mümkün sonuçların bir dağılımı oluşturulur; bu dağılım istatistiksel olarak analiz edilerek sistemin nasıl davranacağına ilişkin öngörüler sağlanır.
Kısaca, Monte Carlo simülasyonu, karmaşık sistemlerin davranışını, rastgele değerler kullanarak sonuçlarını defalarca simüle edip öngören bir tekniktir. Şu adımları kullanır:
- Belirsizliği modelleyin: Her değişkenin nasıl değişebileceğini olasılık dağılımlarıyla tanımlayın.
- Rastgele örnekleme: Bu değişkenler için dağılımlarına göre rastgele değerler seçin.
- Sonuçları simüle edin: Bu değerleri kullanarak sistemin davranışını simüle edin.
- Sonuçları analiz edin: Süreci çok kez tekrarlayarak olası sonuçların bir aralığını elde edin ve ardından en olası senaryoları öngörmek için bunları analiz edin.
Sırada, bazı ilgili istatistiksel kavramlara dalarak Monte Carlo simülasyonuna dair temel anlayışımızı geliştireceğiz.
Monte Carlo Rastgele Değişkenleri ve Dağılımları
Rastgele değişkenler ve bunlara ilişkin olasılık dağılımları, karmaşık sistemlerdeki rastgeleliği ve değişkenliği modellemek ve simüle etmek için matematiksel çerçeveyi sağladıklarından, Monte Carlo simülasyonunun temelini oluşturur.
Rastgele değişkenler
Rastgele değişken, değerleri rastgele bir olgunun sonuçları olan değişkendir.
Rastgele değişkenler iki türe ayrılır:
- Ayrık rastgele değişkenler: Sayılabilir sayıda farklı değer alırlar. Simülasyonlarda ayrık değişkenler, bir partideki hatalı ürün sayısı, saatte gelen müşteri sayısı veya diğer sayılabilir olaylar gibi senaryoları modelleyebilir.
- Sürekli rastgele değişkenler: Sürekli bir aralıkta herhangi bir değer alabilirler. Sürekli değişkenler, fiziksel ölçümler veya zaman süreleriyle ilgili simülasyonlarda kullanılır.
Rastgele değişkenler simülasyonlarda kullanılır çünkü Monte Carlo tekniklerinin keşfetmek ve ölçmek üzere tasarlandığı belirsizliği barındırırlar.
Olasılık dağılımları
Olasılık dağılımları, olasılıkların bir rastgele değişkenin değerleri üzerinde nasıl dağıldığını açıklar.
Olasılık dağılımları, Monte Carlo simülasyonunda farklı girdilerin veya senaryoların nasıl davranmasının beklendiğini tanımlamak için kullanılır; bu da doğru modelleme ve karar verme açısından kritiktir.
Normal dağılım, Merkezi Limit Teoremi nedeniyle, istatistik ve simülasyonlarda en yaygın kullanılan dağılımdır; çünkü pek çok doğal ve insan kaynaklı olgu bu dağılımı izleme eğilimindedir.
Normal dağılım (Kaynak)
Normal dağılım, ölçüm hataları veya hisse senedi getirileri gibi birçok küçük ve bağımsız etkinin etkilediği değişkenleri modellemek için kullanılır.
Diğer bazı olasılık dağılımları arasında, belirtilen bir aralıktaki her sonucun eşit olasılığa sahip olduğu durumlarda kullanılan uniform dağılımlar bulunur — ön veri olmadığında simülasyonlarda yaygın bir varsayımdır — ve bir dizi deney boyunca iki olası sonucun (başarı/başarısızlık) modellendiği durumlarda kullanılan binom dağılımlar yer alır; örneğin geçme/kalma testleri veya kalite kontrol kontrolleri gibi.
Artık Monte Carlo simülasyonlarının ardındaki kavram ve teoriyi anladığımıza göre, işin uygulama tarafına geçelim.
Monte Carlo Simülasyonu için Neden Excel?
Bir Monte Carlo simülasyonu uygulamaya karar verdiğinizde, simülasyonlarda size yardımcı olacak Excel, Python, R, SAS ve MATLAB gibi birden fazla araca sahipsiniz.
Özellikle Monte Carlo simülasyonunu ilk kez uygularken dikkate alınması gereken en önemli faktör, araca genel aşinalığınızdır. Excel, iş dünyasında en yaygın kullanılan araçlardan biridir; bu da pek çok kişinin temel işlemlerine halihazırda aşina olduğu anlamına gelir. Bu durum eğitim süresini kısaltır ve sıfırdan yeni bir yazılım öğrenme ihtiyacını ortadan kaldırır.
Excel ayrıca simülasyon sonuçlarını görselleştirmek için yararlı olabilecek grafik ve çizelge oluşturma araçlarını kolayca sunar. Buna ek olarak, Excel için mevcut olan çeşitli güçlü eklentiler, karmaşık Monte Carlo simülasyonlarını gerçekleştirme yeteneğini artırır.
Bununla birlikte, özellikle büyük veri kümelerinin işlenmesi veya çok yüksek sayıda simülasyon çalıştırılması gereken daha gelişmiş simülasyonlar için Excel dışındaki daha özel araçların daha uygun olabileceğini de unutmamak gerekir.
Monte Carlo için Temel Excel İşlevleri
Sırada, iki temel Excel işlevini inceleyeceğiz: RAND() ve NORM.INV(); sözdizimlerini, parametrelerini ve tipik kullanım durumlarını ele alacağız. Bu işlevler, her simülasyonun temel unsurları olan rastgele sayı üretimi ve olasılık dağılımlarının tanımlanmasına yardımcı olur.
RAND() işlevi
RAND(), 0'a eşit veya daha büyük ve 1'den küçük rastgele bir sayı üretir. Sayılar uniform dağılımlıdır; yani belirtilen aralık içindeki her sayı eşit olasılıkla ortaya çıkar.
RAND() için sözdizimi şöyledir:
RAND()RAND() işlevi herhangi bir argüman gerektirmez. Basitçe RAND() olarak kullanılır.
Monte Carlo Simülasyonu bağlamında RAND(), rastgele olayların gerçekleşmesini simüle etmek veya modelinizdeki girdileri çeşitlendirmek için kullanılabilir.
NORM.INV() işlevi
RAND() uniform rastgele sayılar üretirken, NORM.INV() normal dağılımdan rastgele sayılar üretmek için kullanılır; bu, Monte Carlo Simülasyonunda yaygın bir gereksinimdir. Bu işlev, belirtilen ortalama ve standart sapma için normal kümülatif dağılımın tersini döndürür.
NORM.INV() işlevinin sözdizimi şöyledir:
NORM.INV(probability, mean, standard_deviation)Parametreler şunlardır:
-
probability: Normal dağılıma karşılık gelen ve 0 ile 1 arasında olması gereken bir olasılık. Bu genellikleRAND()işlevi tarafından üretilir. -
mean: Aritmetik ortalama (normal dağılımın ortalaması). -
standard_deviation: Normal dağılımın standart sapması; sayıların ortalama etrafında ne kadar yayıldığının bir ölçüsü.
NORM.INV(), RAND() işlevinden gelen uniform dağılmış rastgele sayıları, belirli bir normal dağılımı izleyen sayılara dönüştürmek için kullanılır. Bu, doğal olarak normal eğriyi izleyen değişkenlik göstermesi beklenen değişkenleri modellemek açısından kullanışlıdır.
Artık bir Monte Carlo simülasyonunun tüm yapı taşlarına, işlevlerine ve kavramlarına sahibiz; şimdi bunu Microsoft Excel'de uygulayalım.
Microsoft Excel'de Monte Carlo Simülasyonunun Uygulanması: Bir Örnek
Diyelim ki dinamik bir tüketici elektroniği şirketinde çalışan bir veri analistisiniz ve yeni bir giyilebilir fitness takipçisinin piyasaya sürülmesinin finansal fizibilitesini değerlendirmeniz istendi.
Bu tür cihazlara yönelik pazar rekabetçidir ve mevsimsel trendler, pazarlama etkinliği ve rakiplerin hamlelerinden etkilenen tüketici talebi oldukça değişken olabilir. Ayrıca bu cihazların üretimiyle ilgili maliyetler, malzeme fiyatlarındaki değişiklikler ve tedarik zinciri belirsizlikleri nedeniyle dalgalanmalara tabidir.
Bu zorlukları ele almak için Excel'de Monte Carlo simülasyonunu kullanmaya karar verdiniz. Bu yaklaşımın, şirketin fiyatlandırma stratejileri, üretim hacimleri ve pazarlama yatırımları hakkında daha bilinçli kararlar almasını sağlayacak şekilde, farklı senaryolarda potansiyel kârlılığı tahmin etmenize yardımcı olacağını düşünüyorsunuz.
Ayrıca benzer ürün lansmanlarından ve tüketici elektroniği sektöründeki pazar çalışmalarından geçmiş verileri analiz ettiniz. Bu analizden, simülasyonunuzu besleyecek bazı metriklere ulaştınız:
- Lansmanın ilk yılında yeni cihazlar için ortalama 10.000 birim talep ve tüketici benimsemesindeki belirsizliği yansıtan 2.000 birim standart sapma.
- Birim satış fiyatı, rekabetçi fiyatlandırma ve pazar doygunluğuna bağlı olarak genellikle 50 ABD Doları ile 70 ABD Doları arasında değişir.
- Dalgalı malzeme fiyatları ve üretim verimliliğinden etkilenen birim maliyeti, birim başına ortalama 30 ABD Dolarıdır ve standart sapması 5'tir.
Bu geçmiş veriler, simülasyon parametrelerinizin temel varsayımlarını oluşturur ve simülasyonun mevcut pazar koşullarını daha doğru yansıtmasına yardımcı olur.
Bu özel örnek için Monte Carlo simülasyonunu uygularken izleyebileceğiniz adımlar aşağıdaki gibidir:
Adım 1: Excel sayfanızı kurun
Önce, her değişken için sütunlar ve hesaplanan kâr için bir sütun içerecek şekilde Excel çalışma sayfanızı hazırlayın.
İlk haliyle şöyle görünecektir:
Excel sayfasını kurma.
Adım 2: Değişkenler için formülleri girin
Her satırda, belirlediğiniz dağılımlara göre talep, satış fiyatı ve maliyet için rastgele değerler üreten formüller gireceksiniz:
- Talep: Normal dağılım (ortalama = 10.000 birim, standart sapma = 2.000 birim)
- Satış Fiyatı: Uniform dağılım (50 $ ile 70 $ arası)
- Maliyet: Normal dağılım (ortalama = 30 $, standart sapma = 5)
Bu formülleri teker teker girmek için A2 hücresini seçin ve şunu yazın:
=NORM.INV(RAND(), 10000, 2000)Yukarıdaki denklem, aşağıdaki gibi verilen bir ortalama ve standart sapma ile normal bir dağılım oluşturur:
Talep için dağılımı oluşturma.
Ardından B2 hücresini seçin ve şunu yazın:
=50 + (70-50) * RAND()Yukarıdaki denklem, satış fiyatı için 50 $ ile 70 $ arasında uniform bir dağılım oluşturur:
Satış fiyatı için dağılımı oluşturma.
C2 hücresini seçin ve şunu yazın:
=NORM.INV(RAND(), 30, 5)Yukarıdaki denklem, talep denklemiyle benzer şekilde, aşağıdaki gibi verilen bir ortalama ve standart sapma ile normal bir dağılım oluşturur:
Maliyet için dağılımı oluşturma.
Adım 3: Bağımlı değişkeni hesaplayın
Şimdi, D sütunundaki formülle her simülasyon için bağımlı değişken olan kârı hesaplayın:
=(B2 - C2) * A2
Kârın hesaplanması.
Adım 4: Birden çok senaryoyu simüle etmek için aşağı doldurun
Şimdiye kadar yaptığımız tek bir simülasyon oluşturmaktı. Bunu çoğaltalım; örneğin bin simülasyon yapalım.
A2'den D2'ye kadar olan hücreleri seçin ve dolgu tutamacını (seçimin sağ alt köşesindeki küçük kare) aşağı doğru sürükleyerek simüle etmek istediğiniz kadar satıra formülleri doldurun (ör. 1000 simülasyon için 1000 satır).
Şuna benzer görünecektir:
Simülasyonların oluşturulması.
Adım 5: Sonuçları analiz edin
Simülasyonları çalıştırdıktan sonra, min, maks, ortalama ve standart sapma gibi istatistiksel işlevleri kullanarak sonuçları analiz edebilirsiniz. Bir sonraki adımda kullanacağımız yerleşik Excel işlevlerine hızlı bir hatırlatma için Excel kopya kağıdına göz atmaktan çekinmeyin.
Her ay beklenen ortalama kârı bulmak için G6 hücresine şunu yazın:
=AVERAGE(D2:D1001)Her ay beklenen minimum kârı bulmak için G7 hücresine şunu yazın:
=MIN(D2:D1001)Her ay beklenen maksimum kârı bulmak için G8 hücresine şunu yazın:
=MAX(D2:D1001)Kârın standart sapmasını bulmak için G9 hücresine şunu yazın:
=STDEV.P(D2:D1001)Çalıştırdıktan sonra Excel sayfası şöyle görünebilir:
Simülasyon sonuçlarının analizi.
Tahmini sonuçları ve ürün lansmanına etkilerini şöyle yorumlayabiliriz:
- Ortalama kâr rakamı, yeni fitness takipçisinin lansmanından beklenen kârı temsil eder. Ortalama olarak her simülasyon çalıştırmasının yaklaşık 298.278,67 $ kâr elde edebileceğimizi öngördüğünü gösterir. Bu değer, verilen varsayımlar altında kârlılığın merkezi bir tahmini olarak faydalıdır.
- Minimum kâr olan 67.598,78 $, tüm simülasyonlarımız arasında gözlemlenen en düşük kârdır. Modelinizin varsayımları altında en kötü senaryoyu gösterir; hâlâ kârlıdır ancak ortalamadan belirgin şekilde düşüktür. Bu, belirli simülasyonda özellikle düşük talep veya elverişsiz maliyet koşullarından kaynaklanmış olabilir.
- Maksimum kâr olan 641.955,42 $, talep ve fiyatın muhtemelen en yüksek, maliyetlerin ise en düşük olduğu en iyi senaryoyu temsil eder. Koşullar son derece elverişli gerçekleşirse potansiyel yukarı yönü gösterir.
Minimum ve maksimum kârlar arasındaki geniş aralık ve kayda değer standart sapma göz önünde bulundurulduğunda, yeni ürünün piyasaya sürülmesiyle ilişkili önemli bir finansal risk vardır.
Karar vericiler, şirketin bu düzeydeki belirsizlikle ve ortalamanın altında kalabilecek kâr olasılığıyla ne kadar rahat olduğuna karar vermelidir.
Ayrıca, isteğe bağlı olsa da, simülasyon sonuçlarını görsel olarak anlamak için Histogramlar gibi görselleştirmeler oluşturmanızı öneririz.
Excel Monte Carlo Simülasyonlarını İyileştirme Teknikleri
Yukarıdakiyle aynı simülasyonu yeniden çalıştırdığınızda, aşağıda gösterildiği gibi hesaplamalarda küçük bir fark gözlemleyebilirsiniz:
Değişen simülasyon sonuçları.
Bunun nedeni, özgün simülasyonun değerlerinin yinelemeler arasında değişebilmesi ve ortaya çıkan tahminleri etkilemesidir. Değişim küçük olsa da tahmini değer değiştiğinde, karar vericilerin zihninde simülasyonun doğruluğu ve güvenilirliğine dair soru işaretleri oluşabilir.
Simülasyonların doğruluğunu ve güvenilirliğini artırmak için kullanabileceğimiz bazı ileri teknikleri inceleyelim.
Simülasyon sayısını artırma
Daha fazla sayıda simülasyon çalıştırmak, rastgele dalgalanmaları ortalamaya alarak sonuçların daha istikrarlı ve doğru tahmin edilmesine yardımcı olur.
Yukarıdaki örnek için, özellikle yüksek değişkenlik gösteren parametrelerle uğraşırken, simülasyon koşusu sayısını artırabiliriz (ör. 1.000'den 10.000'e veya daha fazlasına).
“Doğru” simülasyon sayısını belirlemek birkaç faktöre bağlıdır.
Model ne kadar karmaşıksa (yani değişken sayısı ve etkileşimlerinin aralığı ne kadar genişse), tüm olası sonuçları yakalamak ve sonuçların şansa bağlı olmamasını sağlamak için genellikle o kadar fazla simülasyon gerekir.
Girdiler yüksek değişkenliğe sahipse veya güçlü şekilde çarpıksa, sonuç dağılımlarının kuyruklarını (aşırı değerleri) doğru tahmin etmek için daha fazla simülasyon gerekli olacaktır.
Daha ayrıntılı analizler için, özellikle finans veya risk yönetiminde, 10.000 ila 100.000 simülasyon çalıştırmak yaygındır. Bu aralık, çeşitli senaryo ve girdilerde sağlam sonuçlar elde etmek için tipik olarak kullanılır. Elbette, daha önce de belirttiğimiz gibi, bu kadar büyük ölçekli analizlerde araç olarak her zaman Excel en iyi tercih olmayabilir; bunun yerine R veya Python daha uygundur.
Girdi dağılımlarını iyileştirme
Simülasyonların doğruluğu büyük ölçüde girdi olasılık dağılımlarının, altta yatan değişkenlerin gerçek belirsizliğini ve davranışını ne kadar iyi yansıttığına bağlıdır. Yukarıdaki örneğimizde, talep ve maliyet için normal dağılımı, satış fiyatı için uniform dağılımı varsaydık.
Ek olarak, dağılımları daha iyi parametreleştirmek için daha kapsamlı tarihsel verileri analiz edebiliriz. Alan uzmanlarının girdileriyle maliyet, satış ve talebin dış etkenlere göre davranışını daha iyi anlayabiliriz. Ayrıca log-normal, beta veya gamma gibi dağılımları kullanmayı ya da ampirik verilere dayalı özel dağılımlar oluşturmayı düşünebiliriz.
Duyarlılık analizi yapma
Bu analiz, diğerlerini sabit tutarken her bir girdiyi sistematik olarak değiştirerek, çıktı üzerinde en büyük etkiye hangi girdi değişkenlerinin sahip olduğunu anlamak için yapılır.
Yukarıdaki örneğimizde, iki değişkeni sabit tutup birinin dağılımını değiştirerek tahminlerdeki değişimleri anlayabiliriz. Ardından aynı işlemi kalan iki değişken için teker teker tekrarlarız. Sonuçta bu teknik, doğruluğu artırmak için odaklanılması gereken değişkenin hangisi olduğunu anlamaya yardımcı olur.
Yukarıdaki teknikleri yinelemeli olarak uygulamak ve sonuçları analiz etmek, daha doğru ve güvenilir çıktılara yol açabilir.
Sonuç
Bu eğitim, sizi Monte Carlo Simülasyonu ve ilgili istatistiksel kavramlarla tanıştırdı. İlgili Excel işlevlerini tanıttıktan sonra, gerçek dünya örneğiyle Excel'de Monte Carlo Simülasyonunu uygulamak için adım adım bir rehber sundu.
Son olarak, sonuçlarınızın daha doğru ve güvenilir olmasını sağlamak için bazı en iyi uygulamaları ve ileri teknikleri öğrendiniz.
Yukarıdaki Monte Carlo Simülasyonunu Python veya R gibi diğer araçlarla uygulamakla özellikle ilgileniyorsanız, şu iki kaynak işinize yarayacaktır:
Alternatif olarak, tanıdık Microsoft Excel ile devam etmek ve yaygın olarak benimsenen bu araçta becerilerinizi geliştirmek isterseniz, Excel Fundamentals eğitim yolumuzu incelemek isteyebilirsiniz.
Konular