Excel’de Duyarlılık Analizi Eğitimi: Genel Bakış, Türler ve En İyi Uygulamalar

Bir hesap tablosunda model tasarlarken, ilgili değişkenlerden birinin değerini değiştirerek sonucun nasıl değişebileceğini bilmek isteyebiliriz. Bu süreç, Excel’de bir duyarlılık analizi çalıştırılarak gerçekleştirilebilir.

Finansal modelleme veya karar süreçlerinde çalışan bir veri uzmanıysanız, duyarlılık analizi yapmayı bilmek kritik önemdedir. 

Bu yazıda, duyarlılık analizini ayrıntılı biçimde inceleyeceğiz. “What-if” analiz ailesi içindeki bu kullanışlı tekniğin temellerini ve Excel’de temel ile ileri düzey duyarlılık analizini nasıl yürüteceğinizi ve sonuçlarını nasıl yorumlayacağınızı ele alacağız. Son olarak, daha güçlü duyarlılık analizlerini geliştirmedeki yapay zekâ ve makine öğreniminin rolünü, ayrıca en iyi uygulamalar ve yaygın hataları tartışacağız. 

Uygulamalı deneyim kazanmak için sabırsızlanıyorsanız, what-if analizini ayrıntılı biçimde ele alan Excel’de Finansal Analiz kursumuza göz atın. 

Excel’de Duyarlılık Analizi Nedir?

Duyarlılık analizindeki en yaygın yaklaşım, diğer tüm faktörleri sabit tutarken bir seferde tek bir değişkendeki değişimin nihai sonucu nasıl etkilediğini incelemektir. Bu, ele alınan tüm değişkenler için tekrarlanır. Bu yöntem, bir modeldeki en etkili unsurları belirlemeye yardımcı olur ve işletmelerin performanslarını etkileyen kilit faktörlere odaklanmasını sağlar. 

Duyarlılık analizi, performans üzerinde en büyük etkiye sahip faktörleri belirleyerek işletmelerin temel risk ve fırsatları önceliklendirmesine yardımcı olur. Bu doğası gereği, duyarlılık analizi; finans, iş dünyası ve proje yönetiminde yaygındır. Örneğin, faiz oranlarının zaman içindeki değişimini hesaplamak veya farklı büyüme eğilimlerini anlamak için kullanılır. 

Ayrıca duyarlılık, genellikle aynı anda değişkenlerin kombinasyonunu analiz eden senaryo analizi ile birleştirilir. Bu iki what-if analizi, bir bağımlı değişkenin belirli girdilere nasıl tepki vereceğini inceler. Ancak, senaryo analizi belirli bir “senaryoya” özgüdür; duyarlılık analizi ise girdiler ve değerler için bir aralık verdiği için daha açıktır. 

Duyarlılık analizinin amacı, bağımlı değişkenin bağımsız değişkenler olarak bilinen bir dizi girdi değerine nasıl tepki verdiğini anlamaktır.

Bu tekniği karar alma süreçlerine dahil ederek, kuruluşlar stratejilerini proaktif olarak ayarlayabilir ve belirsizliğe karşı dayanıklılıklarını artırabilir. 

Duyarlılık analizinin arkasındaki teoriye dair daha ayrıntılı bir açıklama için İleri Olasılık: Veride Belirsizlik kursumuzu şiddetle öneririz.

Duyarlılık analizi çoğunlukla, en yüksekten en düşüğe değerleri vurgulayan koşullu biçimlendirmeye sahip bir duyarlılık tablosunda sunulur. 

Aşağıda arz ve talebin fiyat duyarlılığına bir örnek bulunmaktadır. Arz ve talep eksenlerde etiketlenen bağımsız değişkenlerdir ve fiyat, tablodaki değerlere sahip bağımlı değişkendir.

Sonraki bölümlerde benzer Excel duyarlılık tablolarıyla çalışacağız.

Duyarlılık tablosu. Kaynak: DataCamp

Excel’de Duyarlılık Analizine Başlarken

Sürecin her adımını izleyerek Excel’de bir duyarlılık analizinin nasıl yapılacağını görelim. 

Excel’deki araçlar ve özellikler

Excel, what-if analizi de dahil olmak üzere matematiksel modeller oluşturmak için kullanışlı bir araçtır. What-if analizi, duyarlılık analizi gibi soruların keşfedilmesine olanak tanıyan formüller oluşturmak anlamına gelir.

Excel’in en güncel sürümlerinin çoğu, veriyle çalışmak için çok sayıda araç ve olanağı barındıran Veri Sekmesiyle gelir. Buna şeritte bir What-if Analizi düğmesi dahildir. Üç araç içerir. Veri Tablosu düğmesi, özellikle duyarlılık tabloları oluşturmak için tasarlanmıştır.

Senaryo analizi yapmak için şeritteki Senaryo Yöneticisi düğmesine tıklamanız yeterlidir.

Ayrıca Çözücü eklentisini de kullanacağız; bunu Geliştirici Sekmesinden etkinleştirebilirsiniz. 

Çözücü, bir çalışma sayfasındaki diğer formül hücrelerinin değerlerine ilişkin kısıtlar (veya sınırlar) altında, bir hücredeki formül için —hedef hücre— en uygun (maksimum veya minimum) değeri bulmak üzere what-if analizlerinde kullanılır.

Çözücüyü etkinleştirmeden önce, varsayılan olarak gösterilmeyen Geliştirici Sekmesine erişmeniz gerekir. Bu sekmeyi görüntülemek için:

• Şuraya gidin: Dosya > Seçenekler
• Sol panelden Şeridi Özelleştir’i seçin
• Sağ sütunda “Ana Sekmeler” altında Geliştirici kutusunu işaretleyin

Tek Değişkenli Duyarlılık Analizi Yapma

Artık araçlarımız hazır olduğuna göre, Excel’de basit, tek değişkenli bir duyarlılık analizi oluşturarak başlayalım.

Modelinizi kurma

Excel’de duyarlılık analizi yapmanın ilk adımı, modelinizin temel alacağı girdi ve çıktıların belirlenmesidir. 

Bu eğitimde, belirli girdi değişkenlerinin (satılan gitar sayısı, gitarın fiyatı veya üretim maliyetleri gibi) değerini değiştirdiğimizde bir gitar satan şirketin net kârının nasıl değişeceğini incelemek için duyarlılık analizini kullanacağız.

Aşağıda girdi ve çıktı değişkenlerine ait tabloları bulabilirsiniz. 

Duyarlılık analizi için veri tabloları kullanırken, Excel’in model değişkenleri arasındaki ilişkiyi anlayabilmesi için çıktı hücrelerini girdi değişkenlerine bağlamak önemlidir. Bu işlem formüllerle yapılır. Örneğimizde:

• Gelir, birim fiyat ile satılan birim sayısının çarpılmasıyla hesaplanır (=B2*B4)
• Satışların maliyeti, birim başına üretim maliyeti ile satılan birim sayısının çarpılmasıyla hesaplanır (=B3*B4)
• Kâr, gelirlerden satışların maliyeti çıkarılarak hesaplanır (=B7-B8).

Tek yönlü veri tablosu oluşturma

Birim fiyatı artırıp azaltırken, üretim maliyetini ve satılan birim sayısını sabit tutarak kârın nasıl değiştiğini bilmek isteyebilirsiniz. Bir çıktı değişkeninin, bir girdi değişkeninin değerini değiştirdiğimizde nasıl değiştiğini analiz ederken, tek yönlü bir veri tablosuna ihtiyaç duyarız.

Aşağıdaki GIF, Excel’de tek yönlü bir veri tablosunun nasıl oluşturulacağını gösterir. Görüldüğü gibi, tablo her satılan birim için kârı otomatik olarak yeniden hesaplar. Doğal olarak, 250 birim sattığımızdaki kâr, orijinal parametrelerle aynıdır (yani 50.000 €).

Ayrıca, birim satışlarını değiştirdiğimizde farklı çıktı değişkenlerinin nasıl değiştiğini de incelemek isteyebilirsiniz. Aşağıda gösterildiği gibi bu da oldukça kolaydır. Aşağıdaki GIF’te, birim satışlarının hem kârı hem de satışların maliyetini nasıl etkilediğini hesaplıyoruz.

İki Değişkenli Duyarlılık Analizi Yapma

Bir çıktının bir girdiden nasıl etkilendiğini analiz etmek yerine, iki girdi değişkeninin etkisini analiz etmek için iki değişkenli bir tablo da oluşturabilirsiniz. Diyelim ki gitar satışlarını ve gitar başına fiyatı değiştirirsek kârın nasıl değiştiğini bilmek istiyorsunuz. 

Önce, satırlarda birim fiyat aralıklarının ve sütunlarda birim satışlarının yer aldığı iki boyutlu bir tablo oluşturmanız gerekir. Ardından, tablonun sol üst köşesinde incelemek istediğiniz değişkeni seçin. Son olarak, girdi değişkenleri tablosundaki bağımsız değişkenlerin hücre başvurularını seçin (yani birim fiyat ve satılan birim sayısı).

Görüldüğü gibi, tabloyu oluşturduktan sonra, sol üst köşedeki çıktı değişkenini kolayca başka bir değişkenle değiştirebilir ve Excel tüm değerleri yeniden hesaplar.

Excel Duyarlılık Analizi Sonuçlarını Yorumlama

Artık veri tablolarınız olduğuna göre, son adım girdilerdeki değişimlerin çıktıları nasıl etkilediğine dair içgörüler üretmek için sonuçları yorumlamaktır.

Veri tablosu çıktılarının analiz edilmesi

Bu duyarlılık analizine dayanarak kâr hakkında bilinçli bir karar verebiliriz. İşte bazı içgörüler:

• Gitarların fiyatını 350 €’ya yükseltirsek, yalnızca 200 gitar satarak mevcut 50.000 € kârı yakalayabiliriz.
• Gitarların fiyatını 200 €’ya düşürürsek, mevcut kârı yakalamak için 500 birim satmamız gerekir.
• Satılan gitar sayısını 150’ye düşürürsek, gitarların fiyatını 400 €’nun üzerine çıkarmadıkça mevcut kârı yakalayamayız.

Analizin yorumlanması, şirketinizin özel ihtiyaçlarına bağlı olacaktır. Kaç gitar üretebileceğiniz, şirketinizi sağlıklı tutmak için gereken asgari kâr düzeyi ve gitarın üretim maliyetini düşürmek için elinizde neler olduğu gibi soruları sormak, duyarlılık analizinden anlamlı içgörüler çıkarmak açısından kritik önemdedir.

Duyarlılık analizi için Çözücü kullanma

Duyarlılık analizi yaptığınızda, bir modelin girdi değişkenlerindeki değişikliklere bağlı olarak potansiyel çıktılar aralığı elde edersiniz. Ancak, modelinizin iç işleyişi hakkında daha fazla bilgi edinmek isteyebilirsiniz.

Neyse ki Excel, bu görevde size yardımcı olabilecek güçlü bir araçla birlikte gelir: Çözücü. Belirtildiği gibi, Çözücü; bir çalışma sayfasındaki diğer formül hücrelerinin değerlerine ilişkin kısıtlar veya sınırlar altında, bir formül için en uygun (maksimum veya minimum) değeri bulmak üzere what-if analizlerinde kullanılır. 

Bu, tek bir değişkeni istenen bir sonuca ulaşacak şekilde ayarlamaya odaklanan başka bir What-If analizi türü olan Hedef Ara’nın amacına benziyor olabilir. Ancak Çözücü, Hedef Ara’dan çok daha fazlasıdır. 

Öncelikle, Çözücü birden fazla değişkene dayalı olarak en uygun sonucu hesaplamanıza olanak tanır ve modellere kısıtlar eklemenize imkân verir. 

Dahası, Çözücü; modelin katsayılarını değiştirdiğinizde en uygun çözümün nasıl değiştiğine dair içgörü sağlayan isteğe bağlı bir duyarlılık özelliğiyle birlikte gelir. 

Çözücünün gücünü göstermek için gitar şirketimize geri dönelim. Şirketin her biri belirli bir maun ve sedir kombinasyonu gerektiren iki gitar modeli (A ve B modeli) ürettiğini ve her modelin farklı bir fiyata sahip olduğunu hayal edin. 

Şirket, mevcut maun ve sedir stokunu dikkate alarak, kârını maksimize etmek için her modelden kaç birim üretmesi gerektiğini bilmek istiyor. 

Çözücünün hesaplamasını istediğimiz üç şey var: Kâr, A Modeli birim sayısı ve B Modeli birim sayısı. Yukarıdaki görsel, modelin temel alacağı girdileri, formülleri ve kısıtları gösterir. 

Aşağıdaki GIF’te, ahşap kullanılabilirliği kısıtlarını kullanarak ve birimlerin tam sayı olmasını sağlayarak, kârı maksimize edecek en uygun birim değerlerini bulmak için Çözücü’nün nasıl kullanılacağını gösteriyoruz. Çözücü’nün, doğrusal ve doğrusal olmayan problemler için modeli optimize etmek üzere kullanabileceği çeşitli stratejiler vardır. Bizim durumumuzda Simple LP kullanacağız. 

Tüm parametre değerlerini ekledikten sonra Çözücü, kârı (17.591 €) maksimize etmek için en iyi kombinasyonun A modelinden 48 ve B modelinden 9 birim olduğunu tahmin eder. Çözücü kutusunu kapatmadan önce Duyarlılık’a tıklayabilir ve Excel bir Duyarlılık raporu içeren yeni bir sayfa oluşturur.

Duyarlılık, girdi değişkenlerini değiştirirsek kârın nasıl farklılaşacağını tahmin etmek için değerli içgörüler sağlar. Gölge Fiyat (Shadow Price), kısıtlı bir kaynağın bir ek birimi olması durumunda kârın ne kadar artacağını gösterir. Bu örnekte, her ilave metrekare maun kârı 98 € artırırken, sedir 103 € artırır; bu da sedirin daha yüksek potansiyel yatırım getirisini vurgular.

Duyarlılık raporundaki bir diğer önemli unsur, modelin sonuçlarını değiştirmeden modellerin fiyatlarını hangi aralıklarda tutabileceğinizdir. 

Kâr, her modelin birim sayısının fiyatıyla çarpılmasıyla hesaplanır; bu değerler Nihai Değer (Final Value) ve Hedef Katsayısı (Objective Coefficient) sütunlarında verilir. Aralıklar ise İzin Verilen Artış (Allowable Increase) ve İzin Verilen Azalış (Allowable Decrease) sütunlarında verilir.

Örneğin, A Modelinin değerini 1140 € artırırsanız veya 180 € azaltırsanız, Çözücü aynı üretilecek birim sayılarını bulacaktır.

Koşullu biçimlendirme ile verileri görselleştirme

Tek ve çift yönlü duyarlılık tabloları oluşturursanız, sonuçları biçimlendirmek, ilgili bilgileri hızla fark etmek ve karar almayı güçlendirmek için oyunun kurallarını değiştirebilir. Koşullu biçimlendirme, önemli eşikler ve etkileyici ısı haritaları oluşturmanın anahtarıdır. 

İki yönlü tablomıza geri dönelim. Tabloyu beyaz hücrelerde göstermek yerine, GIF’te gösterildiği gibi özel bir eşik belirleyerek iki renk ölçeğinde çok renkli bir ısı haritası oluşturmak için koşullu biçimlendirme kullanabiliriz.

Yapay Zekâ ve Makine Öğrenimini Entegre Etme

Yapay zekâ ve makine öğreniminin duyarlılık analizinizi nasıl iyileştirebileceğini kısaca ele alalım.

YZ ile güçlendirilmiş duyarlılık analizi

Şimdiye kadar, duyarlılık analizinin nasıl manuel olarak yapılacağını gördük. Ancak, modellerinizin karmaşıklığı arttıkça, duyarlılık analizinin yürütülmesi ve sonuçlarının yorumlanması zaman alıcı hâle gelebilir. 

İşte burada yapay zekâ devreye girer. YZ ve makine öğrenimiyle, belirli koşullar karşılandığında hesaplamaları makinelere yaptırabilir ve sonuçları etkileyici panolara yerleştirebilirsiniz. 

Yapmak istediğiniz hesaplamaları belirledikten sonra, bir sonraki adımınız süreci yalınlaştırmak ve otomatikleştirmek için net bir YZ hattı (pipeline) tanımlamak olacaktır. 

Nasıl yapılacağını merak ediyor musunuz? Makine Öğrenimi, Hatlar, Dağıtım ve MLOps Eğitimimize göz atın.

Monte Carlo simülasyonları

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.

Sorun, doğası gereği belirsizlik içeren her değişken için bir olasılık dağılımıyla önce 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ç, olası sonuçların bir dağılımını oluşturmak için birçok kez tekrarlanır; bu dağılım istatistiksel olarak analiz edilerek bir sistemin nasıl davranacağına ilişkin tahminler sunar. Monte Carlo, belirsizliği geleneksel duyarlılık tablolarına kıyasla daha kapsamlı analiz etmeye yardımcı olur.

Bu güçlü teknik hakkında her şeyi Excel’de Monte Carlo Simülasyonu Rehberimizden öğrenebilirsiniz.

Monte Carlo simülasyonunun doğası gereği, çıktıyı en çok etkileyen değişkenleri bulmak için duyarlılık analizinizin gücünü artırmak üzere bunu kullanabilirsiniz. 

Gitar örneğimizde, maliyet fiyatını ve satılan birim sayısını sabit tutup birim fiyatı değiştirerek tahminlerdeki değişimleri anlayabiliriz. Ardından, kalan iki değişken için de aynı süreci teker teker tekrarlarız. Sonuçta bu teknik, doğruluğu artırmak için hangi değişkene odaklanılması gerektiğini anlamaya yardımcı olur.

En İyi Uygulamalar ve Yaygın Hatalar

Excel, duyarlılık analizini son derece kolay ve erişilebilir kılsa da akılda tutulması gereken bazı yaygın hatalar vardır. İşte göz önünde bulundurulması gereken birkaç nokta.

Sağlam modeller tasarlama

Bu eğitimde gördüğünüz gibi, duyarlılık analizinin çalışması için Excel’in girdi ve çıktı değişkenleri arasındaki ilişkileri bilmesi gerekir. Bu da açıkça tanımlanmış hücre başvuruları ve aralıklar sağlayarak formülleri akıllıca kullanmanızı gerektirir. 

Veri tablolarına yeni değerler eklemek isterseniz, yeniden hesaplama sırasında olası sorunlardan kaçınmak için iyi bir uygulama olarak, doğası gereği dinamik olan ve yeni satır veya sütunlar eklendiğinde otomatik olarak genişleyen Excel Tablolarını kullanmaktır. 

Bu, doğru biçimde bilgileri korumak için doğru formül yeniden hesaplamalarını yapmanıza yardımcı olacaktır. 

Hataları azaltma ve doğruluğu sağlama

Duyarlılık analizleriniz net biçimde tanımlanmış olsa bile sonuçlarınızı dikkatle doğrulamanız gerekir. El ile test ve grafikler, sonuçları görselleştirmek ve çıktılardaki aykırı değerleri veya anormallikleri kontrol etmek için harika seçeneklerdir.  

Ancak, duyarlılık analizinin kullanım durumunuz için basitçe işe yaramayabileceğini hatırlamak önemlidir. Bu analiz; girdi değişkenlerinin bağımsızlığı, doğrusal ilişkilerin varlığı ve girdi değişkenlerinin durağan doğası gibi bir dizi basit varsayıma dayanır. 

Bununla birlikte, modeliniz daha karmaşık olabilir ve doğası gereği duyarlılık analizinin göz ardı ettiği dış faktörlerden etkilenebilir. Dolayısıyla, faydalarına rağmen, doğru bir analiz için duyarlılığın ötesinde modelinizi kapsamlı biçimde anlamak önemlidir.

Sonuç

Bu eğitimin sonuna geldiğiniz için tebrikler. Duyarlılık analizi, karar alma sürecinizde oyunun kurallarını değiştirebilecek güçlü bir what-if analizidir. Finansal modellerinizi tamamlayan başka what-if analizleri de vardır; örneğin Hedef Ara veya Senaryo Yöneticisi. Neyse ki DataCamp yanınızda. Özenle seçilmiş Excel kurs ve materyallerimize göz atın:

• Excel’de Veri Analizi
• Excel’de Finansal Modelleme
• Excel Temelleri
• Google E-Tablolar’da Veri Analizi
• İleri Düzey Excel İşlevleri
• Excel’de Güven Aralığı Nasıl Hesaplanır
• Excel Formülleri El Kılavuzu
• Excel’de Değişim Katsayısı Nasıl Hesaplanır
• Excel’de Monte Carlo Simülasyonu: Kapsamlı Rehber