Excel'de Duyarlılık Analizi Nasıl Yapılır: Adım Adım

Excel'de duyarlılık analizi yapmak için modelinizin her bir girdisini kendi hücresine yerleştirin. Ardından bu girdileri değiştirin ve sonucun nasıl hareket ettiğini kaydedin. Tek bir girdi için tek değişkenli veri tablosu, iki girdi için iki değişkenli veri tablosu ve tam senaryolar için Senaryo Yöneticisi kullanın. Analizi, girdileri etki düzeyine göre sıralayan bir tornado grafiği ile tamamlayın.

Bu kılavuz, duyarlılık analizinin ne olduğunu, modelin nasıl kurulacağını ve her bir Excel aracının nasıl kullanılacağını açıklamaktadır. Gerçek formüller içeren uygulamalı bir örnek, AI ile daha hızlı bir yöntem ve kaçınılması gereken hataları içerir.

Duyarlılık analizi nedir

Duyarlılık analizi, bir varsayım değiştiğinde sonucun ne kadar değiştiğini gösterir. "Fiyat %10 düşerse kâr ne olur?" gibi soruları yanıtlar.

Çoğu iş modeli tahminlere dayanır. Satış hacmi, fiyat, maliyetler ve büyüme oranlarının hepsi birer tahmindir. Duyarlılık analizi, bu tahminlerden hangilerinin en çok önem arz ettiğini test eder.

Excel bu araçları Durum Çözümlemesi altında gruplandırır. Microsoft bunu, formüllerin sonuçlarının nasıl değiştiğini görmek için "hücrelerdeki değerleri değiştirme süreci" olarak tanımlar.

Microsoft yerleşik üç araç listeler: "Senaryolar, Hedef Ara, ve Veri Tabloları." Her birinin farklı bir görevi vardır:

• Veri tabloları, bir veya iki girdinin birçok değeri genelinde tek bir sonucu gösterir.
• Senaryo Yöneticisi, en kötü durum ve en iyi durum gibi tam girdi setlerini kaydeder.
• Hedef Ara geriye doğru çalışır. Hedeflenen bir sonuca ulaşmak için gereken girdiyi bulur.

Duyarlılık analizi genellikle ilk ikisini kullanır. Hedef Ara, başabaş fiyatı gibi ilgili bir soruda yardımcı olur.

Başlamadan önce modeli kurun

Excel'in araçları yalnızca model temiz bir şekilde oluşturulmuşsa çalışır. Önce bu noktaları kontrol edin.

Her girdiyi kendi hücresine koyun. Birimler, fiyat, maliyetler ve oranların her birinin bir hücreye ihtiyacı vardır. Formüllerin içine sayı yazmayın.

Sonucu girdilere bağlayın. Kâr gibi sonuç hücresi, girdi hücrelerine atıfta bulunan bir formül olmalıdır. Aksi takdirde, Excel yeni değerleri yerleştirdiğinde hiçbir şey değişmeyecektir.

Girdileri bir arada tutun. Bunları sayfanın üst kısmındaki tek bir blokta gruplandırın. Her birini etiketleyin ve girdi hücrelerini öne çıkacak şekilde renklendirin.

Test etmek için bir sonuç seçin. Duyarlılık analizi; kâr, net bugünkü değer veya yıl sonu nakit gibi tek bir çıktıyı takip ettiğinde daha kolay okunur.

Baz durumunuzu not edin. Başlangıç değerlerini yazın. Analizdeki her sonuç bu baz ile karşılaştırılır.

İşte takip edebileceğiniz basit bir kâr modeli. Bu, hayali bir bisiklet dükkanı olan Northgate Cycles'dır:

Hücre	Etiket	Değer
B1	Satılan birimler	2,400
B2	Birim başına fiyat	$850
B3	Birim başına değişken maliyet	$510
B4	Sabit maliyetler	$520,000
B5	Kâr	=B1*(B2-B3)-B4

Bu baz değerlerde kâr $296,000'dır.

Veri tablosu ile tek değişkenli duyarlılık analizi nasıl yapılır

Tek değişkenli bir veri tablosu, bir girdi için bir dizi değeri test eder. Microsoft bunun, "bir veya daha fazla formüldeki bir değişkenin farklı değerlerinin sonuçları nasıl değiştireceğini" gösterdiğini belirtir.

Örnek modelde fiyatın nasıl test edileceği aşağıda açıklanmıştır:

1. D2'den D6'ya kadar test edilecek fiyatları yazın: $800, $825, $850, $875 ve $900.
2. E1'e, ilk fiyatın bir satır yukarısına ve bir sütun sağına =B5 yazın.
3. D1:E6 aralığını seçin.
4. Veri sekmesinde, Durum Çözümlemesi > Veri Tablosu'nu seçin.
5. Sütun giriş hücresi kısmına fiyat hücresi olan B2'yi girin. Satır giriş hücresi kısmını boş bırakın.
6. Tamam'a tıklayın.

Excel, E2:E6 aralığını her fiyat için kârla doldurur. Formülün yerleşimi önemlidir. Microsoft bunu "değerler sütununun bir satır yukarısına ve bir hücre sağına" yazmanızı söyler.

Northgate Cycles için sonuçlar:

Fiyat	Kâr
$800	$176,000
$825	$236,000
$850	$296,000
$875	$356,000
$900	$416,000

Fiyattaki her $25'lık değişim kârı $60,000 hareket ettirir. Bu, kârın fiyata olan duyarlılığıdır.

İki değişkenli duyarlılık analizi nasıl yapılır

İki değişkenli bir veri tablosu, iki girdiyi aynı anda test eder. Microsoft bunun, "bir formüldeki iki değişkenin farklı değerlerinin o formülün sonuçlarını nasıl değiştireceğini" gösterdiğini belirtir.

Fiyat ve birimlerin birlikte nasıl test edileceği aşağıda açıklanmıştır:

1. D10 gibi boş bir hücreye =B5 yazın.
2. Altına, D11'den D13'e kadar birim hacimlerini yazın: 2,000, 2,400 ve 2,800.
3. Sağına, E10'dan G10'a kadar fiyatları yazın: $800, $850 ve $900.
4. D10:G13 aralığını seçin.
5. Durum Çözümlemesi > Veri Tablosu'nu seçin.
6. Satır giriş hücresi kısmına B2'yi girin. Sütun giriş hücresi kısmına B1'i girin.
7. Tamam'a tıklayın.

Sonuç bir kâr ızgarasıdır:

Birimler \ Fiyat	$800	$850	$900
2,000	$60,000	$160,000	$260,000
2,400	$176,000	$296,000	$416,000
2,800	$292,000	$432,000	$572,000

Izgara, dükkanın test edilen her kombinasyonda kârlı kaldığını gösterir. En zayıf köşe olan $800 fiyatta 2,000 birim bile $60,000 kazandırır.

Burada kesin bir sınır vardır. Microsoft, "Bir veri tablosu ikiden fazla değişkeni barındıramaz" der. Daha fazla girdi için Senaryo Yöneticisi kullanın.

Veri tabloları ayrıca sayfa her yeniden hesaplandığında yeniden hesaplanır. Büyük bir çalışma kitabında bu, Excel'i yavaşlatabilir. Microsoft, hesaplamayı veri tablolarının yalnızca F9'a bastığınızda yeniden hesaplanacağı şekilde ayarlamanızı önerir.

Senaryo Yöneticisi ile tam vakalar nasıl karşılaştırılır

Senaryo Yöneticisi, tam girdi setlerini kaydeder ve bunları yer değiştirerek uygular. Microsoft bir senaryoyu, "Excel'in kaydettiği ve çalışma sayfanızda otomatik olarak değiştirebileceği bir dizi değer" olarak tanımlar.

Üç vakanın nasıl kurulacağı aşağıda açıklanmıştır:

1. Veri sekmesinde, Durum Çözümlemesi > Senaryo Yöneticisi'ni seçin.
2. Ekle'ye tıklayın, senaryoyu Baz olarak adlandırın ve değişen hücreler olarak B1:B4'ü girin. Mevcut değerleri koruyun.
3. Bir En Kötü durum ekleyin: 2,000 birim, $825 fiyat, $530 değişken maliyet ve $540,000 sabit maliyet.
4. Bir En İyi durum ekleyin: 2,800 birim, $875 fiyat, $495 değişken maliyet ve $510,000 sabit maliyet.
5. Bir senaryo seçin ve değerlerini yüklemek için Göster'e tıklayın.
6. Özet'e tıklayın ve bir rapor oluşturmak için sonuç hücresi olarak B5'i seçin.

Northgate Cycles için özet üç kâr rakamı gösterir. En kötü durum $50,000, baz durum $296,000 ve en iyi durum $554,000 kazandırır.

Microsoft birkaç sınıra dikkat çeker. Her senaryo en fazla 32 değişen değer tutabilir. Özet raporu otomatik değildir: "Senaryo raporları otomatik olarak yeniden hesaplanmaz." Bir senaryoyu değiştirirseniz yeni bir özet oluşturun.

Hedef Ara ile başabaş noktası nasıl bulunur

Duyarlılık analizi sonucun ne kadar hareket ettiğini gösterir. Hedef Ara ise ters soruyu yanıtlar: Hangi girdi değeri hedef sonucu verir?

Microsoft, Hedef Ara'nın "bir sonucu aldığını ve bu sonucu üreten olası girdi değerlerini belirlediğini" söyler. Örneği, bir kredi ödemesi için gereken faiz oranını bulur.

Northgate Cycles için başabaş fiyatının nasıl bulunacağı aşağıda açıklanmıştır:

1. Veri sekmesinde, Durum Çözümlemesi > Hedef Ara'yı seçin.
2. Ayarlanacak hücre kısmına kâr hücresi olan B5'i girin.
3. Sonuç değer kısmına 0 girin.
4. Değişecek hücre kısmına fiyat hücresi olan B2'yi girin.
5. Tamam'a tıklayın.

Excel, kâr sıfıra ulaşana kadar fiyatı değiştirir. 2,400 birimde ve baz maliyetlerde, başabaş fiyatı yaklaşık $726.67'dir. Bu, mevcut $850 fiyatının yaklaşık %15 altındadır.

Hedef Ara'nın bir sınırı vardır. Microsoft bunun "yalnızca bir değişken girdi değeriyle çalıştığını" söyler. Birden fazla girdi için Microsoft, Çözücü eklentisine işaret eder.

Tornado grafiği nasıl oluşturulur

Tornado grafiği, girdileri sonucu ne kadar hareket ettirdiklerine göre sıralar. Duyarlılık analizini sunmanın en net yoludur.

Nasıl oluşturulacağı aşağıda açıklanmıştır:

1. Her girdinin %10 daha düşük ve %10 daha yüksek olması gibi bir test aralığı seçin.
2. Her seferinde bir girdiyi değiştirin ve düşük ve yüksek sonucu kaydedin. Diğer girdileri bazda tutun.
3. Sonuçları, her girdi için bir satır olacak şekilde bir tabloya koyun.
4. Satırları, en geniş aralığa sahip girdi en üstte olacak şekilde sıralayın.
5. Düşük ve yüksek sonuçlardan oluşan bir çubuk grafik ekleyin.

Northgate Cycles için, her girdi %10 yukarı ve aşağı hareket ettirildiğinde:

Girdi	−%10'da Kâr	+%10'da Kâr	Aralık
Fiyat	$92,000	$500,000	$408,000
Değişken maliyet	$418,400	$173,600	$244,800
Satılan birimler	$214,400	$377,600	$163,200
Sabit maliyetler	$348,000	$244,000	$104,000

Fiyat, açık ara en çok önem arz eden unsurdur. %10'luk bir fiyat düşüşü kârı $296,000'dan $92,000'a indirir. Sabit maliyetler en az etkiye sahiptir. Bu, sahibine varsayımları kontrol etmek için nereye zaman ayırması gerektiğini söyler.

Excel'deki bir çubuk grafik, çubukları soldan sağa çizer. Tornado şeklini elde etmek için, ham değerler yerine her girdinin baz kârdan değişimini çizin. O zaman düşük durum çubukları sola, yüksek durum çubukları sağa bakar.

AI ile duyarlılık analizi nasıl yapılır

Büyük bir model için veri tabloları, senaryolar ve bir tornado grafiği oluşturmak zaman alır. Bir AI aracı, çalışma kitabınızdan aynı testleri çalıştırabilir.

Powerdrill Bloom kullanarak üç adımda yöntem şöyledir.

Adım 1: Modelinizi yükleyin

Modelinizi tutan elektronik tablo ile başlayın. AI Duyarlılık Analizi Oluşturucu sizden "DCF, iş vakası, işletme planı veya herhangi bir elektronik tablo modeli" yüklemenizi ister.

Girdilerin ve sonuç hücresinin net bir şekilde etiketlendiğinden emin olun. Aracın hangi sayıların varsayım olduğunu bilmesi gerekir.

Adım 2: Doğal dilde duyarlılık görünümünü seçin

İstediğiniz testi tanımlayın. Örneğin: "Her girdinin %10 yukarı ve aşağı hareket ettirildiği kâr için bir tornado grafiği göster. Fiyat ve satılan birimler için iki yönlü bir tablo ekle."

Araç sayfası dört görünüm listeler: "tornado, iki yönlü, Monte Carlo veya başabaş." Tornado çıktısını "cevabı en çok hareket ettiren girdileri sıralayan" bir grafik olarak tanımlar. Ayrıca "önem arz eden iki varsayım üzerinden Duyarlılık tablosu" sunar.

Adım 3: Sonuçları kontrol edin ve analizi dışa aktarın

Paylaşmadan önce bir veya iki sonucu kendi veri tablonuzla karşılaştırın. Baz durum modelinizle tam olarak eşleşmelidir.

Ardından dışa aktarın. Araç sayfası "Excel çalışma kitabı, markalı PDF veya tuval bağlantısı" listeler. Bir sunum için Excel to PPT aracı aynı dosyayı slaytlara dönüştürür. AI Finansal Modelleme sayfası modelin kendisini oluşturmayı kapsar.

Sonuçlar nasıl okunur ve sunulur

Duyarlılık analizi yalnızca insanlar ona göre hareket ederse yararlıdır. Bu alışkanlıklar yardımcı olur.

Sıralama ile başlayın. Tornado grafiği ile açılış yapın. Hangi varsayımların sonucu yönlendirdiğini tek bir resimde gösterir.

Aralıkları kararlara dönüştürün. "Fiyat %10 düşerse kâr $92,000'a geriler" bir gerçektir. "Kiralama sözleşmesini imzalamadan önce fiyatı test etmeliyiz" bir karardır.

Başabaş noktasını gösterin. Analizi, sonucun sıfıra ulaştığı değerle eşleştirin. Northgate Cycles için başabaş noktası, baz fiyat ve maliyetlerde yaklaşık 1,530 birimdir. Excel'de başabaş analizi kılavuzumuz bu hesaplamayı kapsar.

Gerçekçi aralıklar kullanın. %10'luk bir hareket istikrarlı girdiler için uygundur. Yeni bir ürünün hacmi çok daha geniş bir aralığa ihtiyaç duyabilir.

Neyin sabit tutulduğunu belirtin. Her seferinde tekli yapılan testler diğer girdileri bazda tutar. Bunu slaytta veya bir dipnotta belirtin. Okuyucular böylece %10'luk bir fiyat değişiminin aynı hacim, maliyet ve sabit harcamayı varsaydığını bilirler. Bu tek satır, yöntemle ilgili çoğu takip sorusunu önler.

Sonuçlar geldikten sonra tekrar ziyaret edin. Gerçek rakamlar ulaştığında, bunları aralıklarınızla karşılaştırın. Bir bütçe vs. gerçekleşen raporu doğal bir takiptir.

Yaygın duyarlılık analizi hataları

Bu hatalar yaygındır ve kaçınılması kolaydır.

Formüllerde sabit kodlanmış sayılar. Bir formül B2 yerine 850 içeriyorsa, veri tablosu bunu değiştiremez. Her girdiyi bir hücrede tutun.

Aynı anda çok fazla girdiyi test etmek. Bir veri tablosu bir veya ikisini işler. Daha fazlası için Senaryo Yöneticisi'ne veya bir tornado grafiğine geçin.

Her girdi için aynı aralığı kullanmak. Fiyat ve hacim nadiren aynı belirsizliği taşır. Gerçekte bildiklerinizi yansıtan aralıklar seçin.

Girdiler arasındaki bağlantıları görmezden gelmek. Daha düşük bir fiyat hacmi artırabilir. Tek seferde yapılan bir test bunu gözden kaçırır, bu nedenle her ikisini de hareket ettiren bir senaryo ekleyin.

Güncelliğini yitirmiş senaryo özetleri. Senaryo raporları kendi başlarına güncellenmez. Herhangi bir değişiklikten sonra bunları yeniden oluşturun.

Baz durumu unutmak. Her tablo baz sonucu göstermelidir, böylece okuyucular her bir değişikliğin boyutunu görebilir.

Bir sonraki modelinizi stres testine tabi tutmaya hazır olduğunuzda, planlama için kullandığınız çalışma kitabıyla Powerdrill Bloom'u deneyin.

Sıkça sorulan sorular

Excel'de duyarlılık analizi nedir?

Girdiler değiştiğinde bir sonucun nasıl değiştiğini test etmenin bir yoludur. Excel'de, yeni girdi değerlerini yerleştirmek ve sonuçları kaydetmek için esas olarak veri tabloları ve Senaryo Yöneticisi olmak üzere Durum Çözümlemesi araçlarını kullanırsınız.

Excel'de tek değişkenli duyarlılık analizini nasıl yaparım?

Girdi değerlerini bir sütunda listeleyin. Sonuca atıfta bulunan bir formülü bir satır yukarıya ve bir sütun sağa koyun. Aralığı seçin, Durum Çözümlemesi > Veri Tablosu'nu seçin ve girdi hücresini sütun giriş hücresi olarak girin.

Veri tablosu ile Senaryo Yöneticisi arasındaki fark nedir?

Bir veri tablosu, bir veya iki değişkeni birçok değer üzerinden test eder ve tüm sonuçları tek bir ızgarada gösterir. Senaryo Yöneticisi, her biri 32'ye kadar değişen değer içeren tam girdi setlerini kaydeder ve bunları tek tek değiştirir.

Excel'de tornado grafiği nasıl yapılır?

Her bir girdiyi her seferinde bir tane olacak şekilde aynı miktarda yukarı ve aşağı hareket ettirin ve sonucu kaydedin. Girdileri aralık boyutuna göre sıralayın, ardından her girdinin baz sonuçtan değişimini yatay çubuklar olarak grafiklendirin.

AI duyarlılık analizi yapabilir mi?

Evet. Bir AI aracı elektronik tablo modelinizi okuyabilir, tornado ve iki yönlü testler çalıştırabilir ve sonuçları bir çalışma kitabı veya rapor olarak dışa aktarabilir. Yine de baz durumu kontrol etmeniz ve gerçekçi aralıklar seçmeniz gerekir.

Kaynaklar: Microsoft Desteği, Durum Çözümlemesine Giriş · Microsoft Desteği, Veri tablosu kullanarak birden çok sonuç hesaplama · Microsoft Desteği, Senaryoları kullanarak çeşitli değer kümeleri arasında geçiş yapma.