Excel'de Hedef Arama ve Senaryo Yöneticisiyle Durum Çözümlemesi

Durum çözümlemesi neden öğrenilir?

Çoğu Excel modeli tek yönlü çalışır: girdileri yazarsınız, formül sonucu verir. İş hayatındaki sorular ise çoğu zaman tersinden gelir. Satış hedefine ulaşmak için ortalama fiyat ne olmalı, kredinin aylık taksiti belirli bir tutarı geçmesin diye vade ne olmalı, kâr marjı belirli bir seviyeye inmesin diye maliyet ne kadar artabilir. Bu sorulara hücreleri tek tek deneyerek yanıt aramak hem yavaştır hem de hataya açıktır.

Excel'in Veri sekmesindeki Durum Çözümlemesi menüsü bu işi sistemli hale getirir. Menüde Senaryo Yöneticisi, Veri Tablosu ve Hedef Arama bulunur. Hedef Arama bir sonucu sabitleyip hangi girdinin bu sonucu doğuracağını bulur. Senaryo Yöneticisi ise aynı modelde birden çok varsayım kümesini adlandırıp saklar ve aralarında geçiş yapmanızı sağlar. Veri Tablosu ve Çözücü eklentisi bu ailenin diğer üyeleridir.

Bu araçların ortak noktası şudur: formüllerinizi bozmadan, modelin cevabını değiştiren girdileri sistemli biçimde yoklarlar. Bütçe, fiyatlama, yatırım değerlendirme ve kapasite planlama gibi işlerde bu beceri, tabloyu hesap makinesi olmaktan çıkarıp karar destek aracına dönüştürür.

Hedef Arama adım adım nasıl kullanılır?

Hedef Arama tek bir formül hücresini ve tek bir girdi hücresini ilişkilendirir. Microsoft'un resmî yönergesindeki sıra şöyledir:

• Veri sekmesinde Durum Çözümlemesi'ni açın ve Hedef Arama'yı seçin.
• Hücre ayarla kutusuna sonucu hesaplayan formül hücresini girin.
• Değer kutusuna ulaşmak istediğiniz sonucu yazın.
• Hücreyi değiştirerek kutusuna Excel'in oynatacağı girdi hücresini girin.
• Tamam'a basın. Excel girdiyi yinelemeli olarak değiştirir ve sonucu hedefe yaklaştırır.

Örneğin bir kredi modelinde faiz oranı, vade ve anapara girdi, aylık ödeme ise bir ödeme formülünün sonucu olsun. Aylık ödemeyi belirli bir tutarda tutmak istiyorsanız Hücre ayarla kutusuna ödeme hücresini, Değer kutusuna istediğiniz ödeme tutarını, Hücreyi değiştirerek kutusuna da faiz oranını ya da anapara hücresini girersiniz. Excel hangi anaparaya kadar borçlanabileceğinizi bulur.

Önemli bir ayrıntı: Hücre ayarla kutusundaki hücre bir formül içermeli ve bu formül değiştirilen hücreye doğrudan ya da dolaylı olarak bağlı olmalıdır. Formül sabit bir sayıysa ya da girdiden bağımsızsa Hedef Arama çözüm üretemez. Çözümü kabul etmeden önce Hedef Arama Durumu penceresindeki mesaja bakın; Excel çözüm bulamadıysa yakınsadığı değeri gösterir ve bunu açıkça belirtir.

Hedef Arama'da sık yapılan hatalar

En sık hata, girdi hücresine sabit değer yerine formül koymaktır. Hedef Arama değiştirdiği hücrenin üzerine sayı yazar; hücrede formül varsa o formül silinir. Bu yüzden değiştirilecek hücre mutlaka elle girilmiş bir değer olmalıdır.

Bir diğer sık hata, aracın tek değişkenle çalıştığını unutmaktır. Birden çok girdi aynı anda değişecekse Hedef Arama yetmez. Microsoft'un kendi yönergesi bu durumda Çözücü eklentisini önerir. Çözücü, kısıtlar tanımlamanıza ve birden çok hücreyi aynı anda ayarlamanıza izin verir; ürün karması, kaynak dağıtımı ya da yatırım portföyü gibi uygun karışımı arayan sorularda kullanılır.

Başka bir hata, sonucu yuvarlamak ya da biçimlemek yerine değeri kopyalayıp başka bir yere yapıştırmaktır. Hedef Arama girdiyi gerçekten değiştirdiği için model bundan sonra yeni değerle çalışır. Orijinal varsayımı kaybetmemek için işlemden önce girdinin eski değerini bir not hücresine yazın ya da dosyanın kopyasını alın.

Son olarak, ulaşılamaz hedefler konusunda dikkatli olun. Örneğin vade ne olursa olsun belirli bir ödemeye inemeyen bir modelde Excel yakınsayamaz. Bu durum bir hata değil, modelin söylediği bir bilgidir: hedef mevcut yapıyla mümkün değildir.

Senaryo Yöneticisi ile varsayımlar nasıl saklanır ve karşılaştırılır?

Senaryo, Excel'in sakladığı ve çalışma sayfasına istendiğinde otomatik olarak uygulayabildiği bir değer kümesidir. Senaryo Yöneticisi'ni Veri sekmesinde Durum Çözümlemesi menüsünden açarsınız. Her senaryoya bir ad verir, hangi hücrelerin değişeceğini seçer ve o senaryodaki değerleri girersiniz. Örneğin bütçe modelinde Kötümser, Beklenen ve İyimser adlı senaryolar oluşturup gelir ve maliyet hücrelerine her biri için farklı değerler yazabilirsiniz.

Senaryoyu Göster düğmesi seçilen değer kümesini sayfaya uygular; model anında yeniden hesaplanır. Böylece toplantıda bir varsayımı değiştirmek için hücreleri elle düzenlemeniz gerekmez. Birden çok senaryoyu yan yana görmek istediğinizde Senaryo Yöneticisi'nden özet rapor oluşturabilirsiniz. Microsoft'un belgesine göre rapor senaryoları yan yana listeleyebilir ya da bir PivotTable raporu olarak sunabilir.

Bir senaryoda değişebilecek hücre sayısının bir sınırı vardır; çok sayıda değişkeni olan modellerde senaryoları bölmek ya da girdileri sadeleştirmek gerekir. Ayrıca özet raporlar otomatik güncellenmez: senaryodaki değerleri değiştirirseniz mevcut rapor eskir ve yeniden oluşturmanız gerekir. Ayrı çalışma kitaplarındaki senaryoları tek dosyada birleştirmek de mümkündür; bu, farklı ekiplerin varsayımlarını tek modelde toplamak için kullanışlıdır.

Hedef Arama, Senaryo Yöneticisi, Veri Tablosu ve Çözücü: hangisi ne zaman?

Seçimi sorunun yönü belirler.

Hedef Arama: Sonucu biliyorsunuz, gereken girdiyi arıyorsunuz. Tek girdi, tek hedef. Örneğin başa baş noktasına ulaşmak için gereken satış adedi.

Senaryo Yöneticisi: Birkaç değişkenin birlikte oynadığı, adlandırılmış durumları karşılaştırmak istiyorsunuz. Örneğin yönetim kuruluna iyimser, beklenen ve kötümser tabloyu aynı modelden göstermek.

Veri Tablosu: Az sayıda girdiyi geniş bir aralıkta gezdirip sonuçları ızgara halinde görmek istiyorsunuz. Örneğin faiz ve vade kombinasyonlarına göre taksit tablosu. Microsoft'a göre veri tabloları en çok sınırlı sayıda değişkeni barındırabilir; daha fazlası için senaryolar önerilir.

Çözücü: Kısıtlar altında en uygun sonucu arıyorsunuz. Çözücü eklenti olduğu için Dosya menüsündeki Seçenekler üzerinden etkinleştirmeniz gerekebilir. Sonuçlarını senaryo olarak da kaydedebilirsiniz.

Pratikte bu araçlar birlikte kullanılır: önce Hedef Arama ile başa baş noktasını bulur, sonra o noktanın çevresindeki varsayımları senaryolara dönüştürür, son olarak sonuçları bir özet sayfada gösterirsiniz.

Örnek iş akışı: yıllık bütçeyi savunmak

Bir satış müdürünün gelir bütçesini hazırladığını düşünelim. Modelde ürün bazında hacim, ortalama fiyat, indirim oranı ve değişken maliyet girdi; brüt kâr çıktıdır. Yönetim, brüt kârın belirli bir tabanın altına düşmemesini istiyor.

Önce girdileri tek bir bölgede toplayın ve formüllerin bu bölgeye başvurduğundan emin olun; senaryolar bu temiz yapı olmadan güvenle çalışmaz. Sonra Hedef Arama ile brüt kâr tabanını sabitleyip indirim oranını serbest bırakın: Excel, tabanı korumak için en fazla ne kadar indirim verilebileceğini bulur. Bu sonucu yazılı olarak not alın.

Ardından Senaryo Yöneticisi'nde farklı durumlar tanımlayın: talep zayıf kalır, plan gerçekleşir, talep beklenenden güçlü gelir. Her durumda hacim ve maliyet hücrelerine farklı değerler girin. Özet raporu sunumun ekine koyun. Toplantıda biri farklı bir varsayım sorarsa Senaryoyu Göster ile anında yanıt verebilirsiniz.

Bu akışta dikkat edilecek nokta, girdi hücrelerini açıkça etiketlemek ve renkle ayırmaktır. Sonuç hücrelerini kilitleyip yalnızca girdilerin değişmesine izin vermek, modeli devreden kişinin yanlışlıkla formül silmesini önler.

Bu beceriyi nerede geliştirirsiniz?

Durum çözümlemesi, formül mantığı oturduktan sonra gelen bir adımdır. Önce EĞER, ETOPLA ve arama fonksiyonlarını rahat kullanmanız, sonra tabloyu girdi-hesap-çıktı olarak ayırmayı öğrenmeniz gerekir. Formül temeli için Excel formülleri ve fonksiyonları, modelleri sunum düzeyine taşımak için Excel dashboard hazırlama konuları tamamlayıcıdır.

Mindset kataloğunda Microsoft Excel ile Veri Analizi Eğitimi, İleri Excel uygulamalı programı İleri Veri Analizi Araçları ve Veri Analizi ve İleri Formüller gibi modül başlıkları içerir. Excel ile Finansal Modelleme eğitiminin müfredatında ise Senaryo ve Duyarlılık Analizi, Gelir ve Gider Projeksiyonu ile Nakit Akışı ve Değer Tespiti modülleri yer alır; durum çözümlemesinin doğal kullanım alanı olan finansal modelleme bu programda bütüncül çalışılır.