Excel'de What-If Analysis: Goal Seek, Scenario Manager ve Data Tables

Ona üç şey verirsiniz:

• Set cell: formülünüzün bulunduğu hücre (çıktı)
• To value: istediğiniz hedef sonuç
• By changing cell: Excel'in ayarlamasına izin verilen girdi hücresi

Bunu uygulamada görelim. 

Defter satan küçük bir işletme yürütüyorsunuz. Mevcut geliriniz $25,000, ancak hedefiniz $50,000. Fiyat $25 olarak sabit, bu nedenle kaç birim satmanız gerektiğini bulmanız gerekiyor. 

Şöyle yapın:

1. Data > What-If Analysis > Goal Seek yoluna gidin

2. Set cell alanında, B4 hücresine tıklayın (gelir formülünüzün olduğu hücre)

3. To value alanına 50000 yazın

4. By changing cell alanında B2 (Satılan Birim) hücresini seçin

5. OK tıklayın

Goal Seek, B2 değerini 1.000'den 2.000 birime ayarlar — birim başına 25 $ fiyatla 50.000 $ gelire ulaşmak için gereken tam sayı.

Belirli bir sonuca ulaşmak için Goal Seek kullanma. Görsel: Yazar.

Excel'de Scenario Manager nasıl kullanılır

Scenario Manager, aynı elektronik tabloda birden fazla girdi değerleri setini kaydedip aralarında geçiş yapmanızı sağlayan bir What-If Analysis aracıdır. Kaydedilen her sürüme senaryo denir. 

Gerektiği kadar çok senaryo oluşturabilir ve sonuçları karşılaştırmak için anında aralarında geçiş yapabilirsiniz. 

Üç ayrı dosya tutmadan en iyi durum, en kötü durum ve beklenen durum sonuçlarını planlamak istediğinizde kullanışlıdır. 

Ona üç şey verirsiniz:

• Scenario name: her varsayım seti için bir etiket
• Changing cells: senaryolar arasında farklılık gösteren girdi hücreleri
• Values: her senaryo için belirli sayılar

Bir senaryo oluşturun

Diyelim ki defter işiniz için üç aylık satışları planlıyorsunuz.

Mevcut kârınızın $15,000 olduğunu varsayalım. Üç senaryo modellemek istiyorsunuz: en iyi durum, en kötü durum ve beklenen durum. 

Bir senaryo böyle oluşturulur:

1. Data > What-If Analysis > Scenario Manager yoluna gidin

2. Scenario Manager ileti kutusunda Add tıklayın

3. Scenario name alanına Best Case yazın.

4. Changing cells alanında B2:B4 seçin.

5. Bir Scenario Value ileti kutusu açılır. Verilen alanlara şu değerleri girin:

• B2 = 2000, 

• B3 = $30

• B4 = $10

6. OK tıklayın. 

Aşağıdaki değerleri kullanarak kalan iki senaryo için de bu adımları yineleyin:

Üç durumla bir senaryo oluşturun. Görsel: Yazar.

Senaryolar arasında geçiş yapmak için listeden birini seçin ve Show tıklayın. Excel değerleri anında değiştirir ve kârınız güncellenir.

Her senaryonun değerlerini görmek için Show'a tıklayın. Görsel: Yazar.

Senaryoları düzenleyin

Listeden herhangi bir senaryoyu seçin ve Edit tıklayın. Adını değiştirebilir, kapsadığı hücreleri güncelleyebilir veya değerleri revize edebilirsiniz. Çalışma sayfanızda, Show tıklayana kadar hiçbir şey değişmez.

Senaryo raporları oluşturun

Scenario Manager, kaydedilen tüm senaryoları yan yana karşılaştıran bir rapor da oluşturabilir. 

Bir senaryo raporu oluşturmak için:

1. Scenario Manager içinde Summary tıklayın

2. Scenario Summary içinde Scenario Summary seçin

3. Result cells alanında B7 (kâr hücreniz) seçin

4. OK tıklayın

Excel, yeni bir sayfada üç senaryoyu yan yana gösteren bir özet tablo oluşturur

Senaryo özet raporu oluşturun. Görsel: Yazar.

Excel'de Data Tables nasıl kullanılır

Data Tables, bir dizi girdi değeri üzerinde yinelenen hesaplamaları otomatikleştirir. Bir girdiyi elle değiştirip çıktıyı kaydetmek ve tekrarlamak yerine, aralığı bir kez tanımlarsınız ve Excel tüm sonuçları bir kerede doldurur.

Üç şey verirsiniz:

• Input range: test etmek istediğiniz değerler
• Row/column input cell: bu değerlerin formülünüzde yerine geçeceği hücre
• Formula cell: tablonuzun etrafında kurulu olduğu çıktı

Tek değişkenli Data Tables

Tek Değişkenli Data Table, tek bir girdinin değiştirilmesinin bir formül sonucunu nasıl etkilediğini test eder.

Örneğin, bir kredi ödeme formülünüz var ve aylık ödemenin %5 ile %10 arasındaki faiz oranlarında nasıl değiştiğini görmek istiyorsunuz.

Tek Değişkenli Data Table oluşturmak için:

1. Veri kümenizi kurun. Dikkat edilecek bir nokta: benim veri kümemde, E1 hücresinde, ilk oranın hemen üzerinde, sütun başlığının yanında değil, =B5 giriyorum. E1 boş bırakılırsa ya da formül başvurusu farklı bir hücreye yazılırsa, veri tablosu çalışır ancak ödeme değerleri yerine sıfırlar döndürür.

Tek Değişkenli Data Table oluşturun. Görsel: Yazar.

2. Ardından, D1:E9 tam aralığını seçin (E1 dahil)

3. Data > What-If Analysis > Data Table yoluna gidin

4. Row input cell alanını boş bırakın

5. Column input cell alanında B4 (faiz oranı) seçin

6. OK tıklayın.

Excel, her girdi değeri için hesaplanan sonuçlarla tabloyu doldurur. Ödemenin eşit artmadığına dikkat edin. %3'ten %4'e sıçrama 49 $, ama %9'dan %10'a 61 $. Faiz oranındaki değişiklikler, oran yükseldikçe daha sert etkiler.

Her girdi değeri için hesaplanan sonuçlar. Görsel: Yazar.

İki değişkenli Data Tables

İki değişkenli bir veri tablosu, aynı anda iki girdinin değiştirilmesinin bir formül sonucunu nasıl etkilediğini test eder.

Örneğin, bir fiyatlandırma modeli hem ürün fiyatına hem de satılan birim sayısına bağlı olabilir. İki değişkenli bir veri tablosu, farklı fiyat noktaları ve satış hacimleri için kârı tek bir görünümde gösterebilir.

Önce veri kümesini hazırlayın. 

Kullandığım veri kümesine bakın:

• D1 hücresine =B5 girin. Bu formül başvurusudur ve bir etiketin yanına değil, ızgaranın sol üst köşesine yazılmalıdır

• 1. satır boyunca, E1'den başlayarak test etmek istediğiniz satılan birim değerlerini listeleyin: 500, 1000, 1500, 2000

• D sütunu boyunca, D2'den başlayarak test etmek istediğiniz fiyat noktalarını listeleyin: $20, $25, $30, $35

İki Değişkenli Data Table için veri kümesi hazırlayın. Görsel: Yazar.

İki Değişkenli Data Table oluşturmak için:

1. D1:H5 tam aralığını seçin.

2. Data > What-If Analysis > Data Table yoluna gidin

3. Row input cell alanında B3 (satılan birim) seçin

4. Column input cell alanında B2 (birim başına fiyat) seçin

5. OK tıklayın.

Excel, her fiyat ve satılan birim kombinasyonu için kârı doldurur

Girdi değeri için hesaplanan sonuçlar. Görsel: Yazar.

İki Değişkenli Data Tables, tek bir varsayımın tek başına tüm hikâyeyi anlatmadığı durumlarda özellikle faydalıdır. Fiyat tek başına iyi görünebilir, ancak kâr aynı zamanda müşterilerin gerçekten kaç birim aldığına da bağlıdır. Modelin daha kullanışlı hale geldiği yer ikinci değişkendir.

Goal Seek vs. Scenario Manager vs. Data Tables

Goal Seek, Scenario Manager ve Data Tables, Excel'in What-If Analysis araç setinin parçasıdır, ancak farklı sorunları çözerler. 

Aralarında hızlı bir karşılaştırma şöyledir 

What-If Analysis vs. Solver

What-If Analysis ve Solver, Excel'de farklı sonuçları keşfetmemize yardımcı olur, ancak farklı türde sorunları çözerler.

• What-If Analysis varsayımları test eder. Bir veya daha fazla girdiyi değiştirir ve bu değişikliklerin sonucu nasıl etkilediğini görürsünüz. Amaç en iyi olanı bulmak değil, olası sonuçları anlamaktır.
• Solver farklı çalışır. Kârı maksimize etmek veya maliyeti minimize etmek gibi bir hedef verirsiniz ve hedefi en iyi sağlayan girdi kombinasyonunu arar. Bir varsayımı test etmez; en iyisini çözmeye çalışır. 

Örneğin, bir kredi başvuru sahibi, aylık 500 $ ödemeye yol açan kredi tutarını belirlemek için Goal Seek kullanabilir. Ancak bir üretim şirketi, iş gücü ve malzeme sınırları içinde kalarak en yüksek kârı sağlayan üretim karmasını belirlemek için Solver kullanabilir. 

Not: Solver, standart What-If Analysis araçlarının parçası değildir. Ayrı bir eklentidir. Excel'in çoğu sürümünde, Veri (Data) sekmesinde görünmeden önce Excel Seçenekleri'nden (Options) elle etkinleştirmeniz gerekir.

What-If Analysis En İyi Uygulamalar

What-If Analysis ile çalışırken izlenecek bazı en iyi uygulamalar şunlardır: 

1. Varsayımları formüllerden ayrı tutun

Girdi değerlerini özel hücrelerde saklayın ve bu hücreleri formüllerinizde referans gösterin. Sayıları doğrudan hesaplamalara sabitlemekten kaçının. 

Örneğin, şu formül güncellenmesi zordur:

=(50*1000)-10000

Ama bu sürümün bakımı çok daha kolaydır:

=(B2*B3)-B4

Varsayımlar değiştiğinde, çalışma kitabı genelinde formül düzenlemek yerine yalnızca girdi hücrelerini güncellersiniz.

2. Senaryoları açıkça etiketleyin

Senaryo adları, neyi temsil ettiklerini okuyucuya hemen söylemelidir. "Senaryo 1", "Senaryo 2" ve "Senaryo 3" altı ay sonra hiçbir şey ifade etmez. "En İyi Durum: %15 Büyüme" gibi, gerçekten neyi temsil ettiklerine göre adlandırın. 

3. Girdileri doğrulayın

What-If Analysis, yalnızca test edilen girdiler kadar güvenilirdir. Bir analiz çalıştırmadan önce şunları kontrol edin:

• Yüzdeler doğru girilmiş mi
• Tarihler tutarlı bir biçim kullanıyor mu
• Ölçü birimleri tutarlı mı
• Girdi değerleri gerçekçi aralıklarda mı

%500 satış artışına dayalı bir gelir tahmini teknik olarak doğru olabilir ama yine de karar verme için işe yaramaz.

4. Varsayımları belgeleyin

Bugün size mantıklı gelen bir model, üç ay sonra size ya da başkasına aynı şekilde mantıklı gelmeyebilir. 

Her varsayımın yanına kısacık bir not, sadece "Q3 gerçekleşenlerine dayanır" demek bile, ileride çok fazla tahmin yürütmeyi önler. Bunları belgelemek, başkalarının sayıların nereden geldiğini anlamasına yardımcı olur ve gelecekteki güncellemeleri çok daha kolay hale getirir.

What-If Analysis ile İlgili Yaygın Sorunlar

What-If Analysis ile ilgili sorunların çoğu araçlardan ziyade çalışma sayfası kurulumundan kaynaklanır. Goal Seek, Scenario Manager veya Data Tables beklenmedik sonuçlar veriyorsa, birkaç yaygın problem alanını kontrol edin; genellikle sorunu çözer.

1. Formüller güncellenmiyor 

Bir girdiyi değiştiriyorsunuz ve hiçbir şey olmuyor. Genellikle bu, Excel'in hesaplama modunun bir noktada elle (manual) olarak değiştirilmiş olduğu anlamına gelir. 

Düzeltmek için: Formulas > Calculation Options yoluna gidin ve Automatic olarak ayarlandığından emin olun. Zaten öyleyse, yeniden hesaplama zorlamak için F9 tuşuna basın.

2. Yanlış hücre başvuruları

What-If Analysis araçları, doğru hücre başvurularına bağlıdır. Yanlış hücreler seçilirse, Excel hesaplamayı başarıyla tamamlasa bile sonuçlar yanlış olur. 

Örneğin, Goal Seek yanlış girdi hücresini değiştirebilir. Bir Data Table yanlış satır veya sütun girdi hücresine başvurabilir. Scenario Manager yanlış hücreler için değerleri kaydedebilir.

Düzeltmek için: Analizi çalıştırmadan önce girdi hücrelerinizi gözden geçirin, her aracın ileti kutusunda seçili olanları iki kez kontrol edin ve her hücrenin neyi temsil ettiğinin açık olması için açıklayıcı etiketler kullanın. 

3. Goal Seek'in sınırlamalarını yanlış anlamak

Birçok kullanıcı, Goal Seek'in birden fazla değişen değişken içeren sorunları çözmesini bekler. Çözemez. Goal Seek, bir hedef değere ulaşmak için aynı anda yalnızca bir girdi hücresini değiştirir. 

Örneğin, Goal Seek 15.000 $ kâr elde etmek için gereken satış hacmini belirleyebilir. Satış hacmi, fiyat ve maliyetleri aynı anda ayarlayarak bu hedefe ulaşamaz.

Düzeltmek için: Yalnızca bir değişkenin değişmesi gerektiğinde Goal Seek kullanın. Birden fazla değişken söz konusuysa Scenario Manager veya Solver kullanın. 

4. Aşırı karmaşık senaryo modelleri

Scenario Manager teknik olarak senaryo başına 32 değişen hücreye kadar izin verir, ancak yapabiliyor olmanız, yapmanız gerektiği anlamına gelmez. 

Dört beş değişkenden sonra, senaryoları karşılaştırmak zorlaşır ve bir başkasına açıklamak daha da zorlaşır. Modelinizin bu kadar çok hareketli parçaya ihtiyacı varsa, hepsini tek bir senaryoya sığdırmak yerine yapıyı yeniden düşünmenin zamanı gelmiş demektir.

Son Düşünceler 

Her elektronik tabloda bir yerlerde varsayımlar vardır. Bu bir büyüme oranı, bir fiyat ya da bir faiz oranı olabilir.

What-If Analysis bu varsayımları ortadan kaldırmaz. Yanlış çıkarlarsa ne olacağını gösterir; iyi karar vermenin büyük kısmı da aslında buna dayanır.

Buna yeni başlıyorsanız, beş değişken ve üç iç içe senaryolu bir modelle başlamayın. Önce küçük bir şey kurun: bir girdi, bir çıktı, bir Goal Seek ve girdinin sonuçla birlikte nasıl hareket ettiğini öğrenin. Temeller yerleşince karmaşık kısım sonra gelir.