Excel'de Duyarlılık Analizi (Örnek Veri Sayfası)

Finans alanındaki süreçler her zaman birbirine bağlıdır - bir faktör diğerine bağlıdır ve onunla birlikte değişir. Bu değişiklikleri izleyin ve gelecekte neler bekleyeceğinizi, belki Excel işlevlerini ve elektronik tablo yöntemlerini kullanarak anlayın.

Bir veri tablosu ile birden çok sonuç alma

Veri sayfası yetenekleri, genellikle Microsoft Excel aracılığıyla yapılan durum analizinin öğeleridir. Bu, duyarlılık analizinin ikinci adıdır.

Genel Bakış

Veri tablosu, bazı hücrelerdeki değerleri değiştirerek sorunları çözmek için kullanılabilen bir hücre aralığı türüdür. Formülün bileşenlerindeki değişiklikleri takip etmek ve bu değişikliklere göre sonuçlara güncellemeler almak gerektiğinde oluşturulur. Araştırmada veri tablolarının nasıl kullanılacağını ve ne tür olduklarını öğrenelim.

Veri tabloları hakkında temel bilgiler

İki tür veri tablosu vardır, bunlar bileşen sayısı bakımından farklılık gösterir. Kontrol etmeniz gereken uXNUMXbuXNUMXb değerlerinin sayısına odaklanan bir tablo derlemeniz gerekir.

İstatistikçiler, bir veya daha fazla ifadede sonucunu değiştirebilecek yalnızca bir değişken olduğunda tek değişkenli bir tablo kullanır. Örneğin, genellikle PMT işleviyle birlikte kullanılır. Formül, düzenli ödeme tutarını hesaplamak için tasarlanmıştır ve sözleşmede belirtilen faiz oranını dikkate alır. Bu tür hesaplamalarda değişkenler bir sütuna, hesaplamaların sonuçları diğer sütuna yazılır. 1 değişkenli bir veri plakası örneği:

Ardından, 2 değişkenli plakaları düşünün. Herhangi bir göstergedeki değişikliği iki faktörün etkilediği durumlarda kullanılırlar. İki değişken, en uygun geri ödeme süresini ve aylık ödeme miktarını belirlemek için kullanılabilecek krediyle ilişkili başka bir tabloda son bulabilir. Bu hesaplamada ayrıca PMT fonksiyonunu kullanmanız gerekmektedir. 2 değişkenli bir tablo örneği:

Tek değişkenli bir veri tablosu oluşturma

Stokta yalnızca 100 kitap bulunan küçük bir kitapçı örneğini kullanarak analiz yöntemini düşünün. Bazıları daha pahalıya satılabilir (50$), geri kalanı alıcılara daha ucuza (20$) mal olur. Tüm malların satışından elde edilen toplam gelir hesaplanır - kitap sahibi kitapların %60'ını yüksek bir fiyata satmaya karar verdi. Daha büyük hacimli malların fiyatını artırırsanız -% 70 vb. Gelirin nasıl artacağını bulmanız gerekir.

Çok dikkat! Toplam gelir bir formül kullanılarak hesaplanmalıdır, aksi takdirde bir veri tablosu derlemek mümkün olmayacaktır.

1. Sayfanın kenarından uzakta bir boş hücre seçin ve formülü içine yazın: =Toplam gelir hücresi. Örneğin, C14 hücresine gelir yazılırsa (rastgele atama belirtilir), şunu yazmanız gerekir: =S14.
2. Bu hücrenin solundaki sütuna mal hacminin yüzdesini yazıyoruz - altına değil, bu çok önemli.
3. Yüzde sütununun ve toplam gelir bağlantısının bulunduğu hücre aralığını seçiyoruz.

4. "Veri" sekmesinde "Analiz olursa" öğesini buluyoruz ve üzerine tıklıyoruz - açılan menüde "Veri tablosu" seçeneğini seçin.

5. “Satırlara göre ikame değerleri …” sütununda başlangıçta yüksek bir fiyata satılan kitapların yüzdesini içeren bir hücre belirtmeniz gereken küçük bir pencere açılacaktır. Bu adım, artan yüzdeyi dikkate alarak toplam geliri yeniden hesaplamak için yapılır.

Tablonun derlenmesi için verilerin girildiği pencerede “Tamam” butonuna tıkladıktan sonra satırlarda hesaplamaların sonuçları görünecektir.

Tek Değişkenli Veri Tablosuna Formül Ekleme

Yalnızca bir değişkenle bir eylemin hesaplanmasına yardımcı olan bir tablodan, ek bir formül ekleyerek karmaşık bir analiz aracı oluşturabilirsiniz. Halihazırda var olan bir formülün yanına girilmelidir - örneğin, tablo satır odaklıysa, ifadeyi mevcut olanın sağındaki hücreye gireriz. Sütun yönü ayarlandığında eski formülün altına yeni formülü yazıyoruz. Ardından, algoritmayı izleyin:

1. Hücre aralığını tekrar seçin, ancak şimdi yeni formülü içermelidir.
2. “Ya olursa” analiz menüsünü açın ve “Veri Sayfası”nı seçin.
3. Plakanın yönüne bağlı olarak satır veya sütunlarda karşılık gelen alana yeni bir formül ekliyoruz.

İki değişkenli bir veri tablosu oluşturun

Böyle bir tablonun başlangıcı biraz farklıdır - yüzde değerlerinin üzerindeki toplam gelire bir bağlantı koymanız gerekir. Ardından, şu adımları gerçekleştiriyoruz:

1. Fiyat seçeneklerini bir satıra gelir bağlantısıyla yazın - her fiyat için bir hücre.
2. Bir hücre aralığı seçin.

3. Araç çubuğundaki “Veri” sekmesi aracılığıyla, tek değişkenli bir tablo derlerken olduğu gibi veri tablosu penceresini açın.
4. "Değerleri ..." sütununa göre değiştir sütununda, başlangıçta yüksek fiyatı olan bir hücre.
5. Pahalı kitapların ilk satış yüzdesini içeren bir hücreyi “Satırlara göre değiştir” sütununa bir hücre ekleyin ve “Tamam” ı tıklayın.

Sonuç olarak, tüm tablo, mal satışı için farklı koşullara sahip olası gelir miktarlarıyla doldurulur.

Veri tabloları içeren çalışma sayfaları için hesaplamaları hızlandırın

Tüm çalışma kitabının yeniden hesaplanmasını tetiklemeyen bir veri tablosunda hızlı hesaplamalara ihtiyacınız varsa, süreci hızlandırmak için yapabileceğiniz birkaç şey vardır.

1. Seçenekler penceresini açın, sağdaki menüden “Formüller” öğesini seçin.
2. “Çalışma kitabındaki hesaplamalar” bölümünde “Veri tabloları hariç otomatik” öğesini seçin.

3. Tablodaki sonuçları manuel olarak yeniden hesaplayalım. Bunu yapmak için formülleri seçin ve F tuşuna basın.

Duyarlılık Analizi Gerçekleştirmek için Diğer Araçlar

Programda duyarlılık analizi yapmanıza yardımcı olacak başka araçlar da vardır. Aksi takdirde manuel olarak yapılması gereken bazı eylemleri otomatikleştirirler.

1. İstenen sonuç biliniyorsa “Parametre seçimi” işlevi uygundur ve böyle bir sonuç elde etmek için değişkenin giriş değerini bilmeniz gerekir..
2. “Çözüm arama”, sorunları çözmek için bir eklentidir. Sınırları belirlemek ve onlara işaret etmek gerekir, bundan sonra sistem cevabı bulacaktır. Değerler değiştirilerek çözüm belirlenir.
3. Hassasiyet analizi, Senaryo Yöneticisi kullanılarak yapılabilir. Bu araç, Veri sekmesinin altındaki durum analizi menüsünde bulunur. Birkaç hücredeki değerleri değiştirir - sayı 32'ye ulaşabilir. Dağıtıcı bu değerleri karşılaştırır, böylece kullanıcının bunları manuel olarak değiştirmesi gerekmez. Komut dosyası yöneticisini kullanmaya bir örnek:

Excel'de bir yatırım projesinin duyarlılık analizi

What-if analizi, yatırım gibi tahminin gerekli olduğu durumlarda özellikle yararlıdır. Analistler, bazı faktörlerdeki değişikliklerin bir sonucu olarak bir şirketin hisse senedinin değerinin nasıl değişeceğini öğrenmek için bu yöntemi kullanır.

Yatırım Duyarlılığı Analiz Yöntemi

“Ya olursa” analizini yaparken, manuel veya otomatik numaralandırmayı kullanın. Değer aralığı bilinir ve formüle birer birer değiştirilirler. Sonuç bir dizi değerdir. Onlardan uygun numarayı seçin. Finans alanında duyarlılık analizi yapılan dört göstergeyi ele alalım:

1. Net Bugünkü Değer – Yatırım tutarının gelir tutarından çıkarılmasıyla hesaplanır.
2. Dahili getiri / kâr oranı – bir yılda bir yatırımdan ne kadar kâr elde edilmesi gerektiğini gösterir.
3. Geri ödeme oranı, tüm kârların ilk yatırıma oranıdır.
4. İndirimli kar endeksi – yatırımın etkinliğini gösterir.

formül

Gömme hassasiyeti şu formül kullanılarak hesaplanabilir: Çıkış parametresinde % olarak değişiklik / Giriş parametresinde % olarak değişiklik.

Çıkış ve giriş parametreleri daha önce açıklanan değerler olabilir.

1. Standart koşullar altında sonucu bilmeniz gerekir.
2. Değişkenlerden birini değiştiririz ve sonuçtaki değişiklikleri izleriz.
3. Belirlenen koşullara göre her iki parametrenin yüzde değişimini hesaplıyoruz.
4. Elde edilen yüzdeleri formüle ekliyoruz ve hassasiyeti belirliyoruz.

Excel'de bir yatırım projesinin duyarlılık analizi örneği

Analiz metodolojisinin daha iyi anlaşılması için bir örneğe ihtiyaç vardır. Projeyi aşağıdaki bilinen verilerle analiz edelim:

1. Üzerindeki projeyi analiz etmek için tabloyu doldurun.

2. OFFSET fonksiyonunu kullanarak nakit akışını hesaplıyoruz. İlk aşamada, akış yatırımlara eşittir. Ardından, formülü uygularız: =IF(OFFSET(Sayı;1;)=2;TOPLA(Giriş 1:Çıkış 1); TOPLA(Giriş 1:Çıkış 1)+$B$ 5)
   Formüldeki hücre tanımları, tablonun düzenine bağlı olarak farklı olabilir. Sonunda, ilk verilerden gelen değer eklenir - kurtarma değeri.

3. Projenin amorti edeceği süreyi belirliyoruz. İlk dönem için şu formülü kullanıyoruz: =ÖZETLE(G7:G17;»<0″). Hücre aralığı, nakit akışı sütunudur. Daha sonraki dönemler için şu formülü uyguluyoruz: =İlk dönem+EĞER(İlk e.akım>0; İlk e.akım;0). Proje 4 yılda başabaş noktasında.

4. Projenin karşılığını aldığı dönemlerin sayıları için bir sütun oluşturuyoruz.

5. Yatırım getirisini hesaplıyoruz. Belirli bir zaman dilimindeki kârın ilk yatırıma bölündüğü bir ifade yapmak gerekir.

6. Bu formülü kullanarak indirim faktörünü belirleriz: =1/(1+Disk.%) ^Sayı.

7. Çarpmayı kullanarak bugünkü değeri hesaplıyoruz - nakit akışı iskonto faktörü ile çarpılıyor.

8. PI'yi (kârlılık endeksi) hesaplayalım. Zaman içindeki mevcut değer, projenin başlangıcındaki yatırıma bölünür.

9. IRR işlevini kullanarak iç getiri oranını tanımlayalım: =IRR(Nakit akışı aralığı).

Veri Sayfası Kullanarak Yatırım Duyarlılık Analizi

Yatırım alanındaki projelerin analizi için veri tablosundan başka yöntemler daha uygundur. Birçok kullanıcı bir formül derlerken kafa karışıklığı yaşar. Bir faktörün diğerlerindeki değişikliklere bağımlılığını bulmak için, hesaplamaları girmek ve verileri okumak için doğru hücreleri seçmeniz gerekir.

Hesaplama otomasyonu ile Excel'de faktör ve dağılım analizi

Duyarlılık analizinin diğer bir tipolojisi, faktör analizi ve varyans analizidir. İlk tip sayılar arasındaki ilişkiyi tanımlar, ikincisi bir değişkenin diğerine bağımlılığını ortaya çıkarır.

Excel'de ANOVA

Böyle bir analizin amacı, değerin değişkenliğini üç bileşene bölmektir:

1. Diğer değerlerin etkisinin bir sonucu olarak değişkenlik.
2. Onu etkileyen değerlerin ilişkisi nedeniyle değişir.
3. Rastgele değişiklikler.

Varyans analizini Excel eklentisi “Veri Analizi” ile gerçekleştirelim. Etkinleştirilmemişse, ayarlardan etkinleştirilebilir.

İlk tablo iki kuralı izlemelidir: her değer için bir sütun vardır ve içindeki veriler artan veya azalan sırada düzenlenir. Eğitim düzeyinin çatışmalı davranış üzerindeki etkisini kontrol etmek gerekir.

1. Veri sekmesinde Veri Analizi aracını bulun ve penceresini açın. Listede, tek yönlü varyans analizini seçmeniz gerekir.

2. İletişim kutusunun satırlarını doldurun. Giriş aralığı, başlıklar ve sayılar hariç tüm hücrelerdir. Sütunlara göre gruplandırın. Sonuçları yeni bir sayfada görüntülüyoruz.

Sarı hücredeki değer birden büyük olduğu için varsayım yanlış kabul edilebilir – eğitim ile çatışma halindeki davranış arasında bir ilişki yoktur.

Excel'de faktör analizi: bir örnek

Satış alanındaki verilerin ilişkisini analiz edelim - popüler ve popüler olmayan ürünleri belirlemek gerekir. İlk bilgiler:

1. İkinci ayda hangi mal talebinin en fazla arttığını bulmamız gerekiyor. Talepteki büyümeyi ve düşüşü belirlemek için yeni bir tablo hazırlıyoruz. Büyüme şu formül kullanılarak hesaplanır: =EĞER((Talep 2-Talep 1)>0; Talep 2-Talep 1;0). Azaltma formülü: =IF(Büyüme=0; Talep 1- Talep 2;0).

2. Mallara olan talepteki büyümeyi yüzde olarak hesaplayın: =EĞER(Büyüme/Sonuç 2 =0; Azalma/Sonuç 2; Büyüme/Sonuç 2).

3. Netlik için bir grafik yapalım – bir hücre aralığı seçin ve “Ekle” sekmesinden bir histogram oluşturun. Ayarlarda dolguyu kaldırmanız gerekir, bu, Veri Serisini Biçimlendir aracıyla yapılabilir.

Excel'de iki yönlü varyans analizi

Varyans analizi birkaç değişkenle yapılır. Bunu bir örnekle düşünün: Farklı ses seviyesindeki bir sese verilen tepkinin erkeklerde ve kadınlarda ne kadar hızlı kendini gösterdiğini bulmanız gerekiyor.

1. “Veri Analizi” ni açıyoruz, listede tekrarlar olmadan iki yönlü bir varyans analizi bulmanız gerekiyor.
2. Giriş aralığı – veri içeren hücreler (başlıksız). Sonuçları yeni bir sayfada görüntülüyoruz ve “Tamam” ı tıklıyoruz.

F değeri, F-kritik değerinden daha büyüktür, bu, zeminin sese tepki hızını etkilediği anlamına gelir.

Sonuç

Bu makalede, bir Excel elektronik tablosundaki duyarlılık analizi, her kullanıcının uygulama yöntemlerini anlayabilmesi için ayrıntılı olarak tartışılmıştır.