Excel'de KPI takip tablosu ve hedef gerçekleşme raporu nasıl kurulur?

İyi bir KPI takip tablosu neyi gösterir?

KPI takip tablosunun işi, her gösterge için aynı soruları yanıtlamaktır: hedef neydi, ne gerçekleşti, aradaki fark ne kadar ve durum iyiye mi kötüye mi gidiyor. Bu sorulardan biri eksikse tablo ya yalnızca bir sayı listesine ya da yorumlanamayan bir renk paletine dönüşür.

Tabloyu kurmadan önce göstergelerin tanımı yazılı olmalıdır. Her KPI için şu alanlar belirlenir: • Gösterge adı ve kısa tanımı (neyi, hangi veriyle ölçtüğünüz) • Birim (adet, tutar, oran, süre) • Yön: değer yükseldikçe mi iyi, düştükçe mi (satış tutarı yükselince iyidir, iade oranı düşünce iyidir) • Ölçüm sıklığı ve sorumlu kişi • Hedef ve hedefin geçerli olduğu dönem

Tanım tablosu en sona bırakılırsa tablo büyüdükçe herkes aynı göstergeyi farklı hesaplamaya başlar. Tanım, Excel dosyasının içinde ayrı bir sayfada durmalı ve rapor sayfasındaki her satırın yanında görülebilmelidir.

Dosyanın katmanları: ham veri, hedefler ve rapor

Sağlam bir KPI dosyası ayrı sayfalarda kurulur. Ham veri sayfasında her satır bir ölçümdür: dönem, gösterge, birim ya da bölüm, gerçekleşen değer. Veriyi Ctrl+T ile Excel tablosuna çevirmek yeni dönemler eklendiğinde formüllerin otomatik genişlemesini sağlar.

Hedef sayfasında her gösterge ve dönem için hedef değeri bulunur. Hedefi ham verinin yanına gömmek yerine ayrı tutmak, yıl ortasında hedef revizyonu geldiğinde yalnızca tek yeri güncellemenizi sağlar. Rapor sayfası ise yalnızca formül içerir; elle yazılmış sayı barındırmaz. Böylece raporun hangi hücrenin hangi kaynaktan geldiği izlenebilir kalır.

Gösterge adlarını bir açılır listeden seçtirmek (veri doğrulama) yazım farklarından doğan eşleşme hatalarını önler. Aynı göstergenin bir yerde “Net Satış”, başka yerde “Net satış ” yazılması, ETOPLA ya da DÜŞEYARA gibi eşleştirme formüllerinde sessizce sıfır döndürür.

Gerçekleşme oranı ve sapma formülleri

Rapor satırında temel hesaplar basittir. Gerçekleşen değeri, ham veri sayfasından gösterge ve dönem ölçütüne göre ETOPLA ile toplarsınız. Hedefi hedef sayfasından DÜŞEYARA ya da XLOOKUP ile çekersiniz. Sonra şunlar hesaplanır: • Mutlak sapma: gerçekleşen eksi hedef • Gerçekleşme oranı: gerçekleşen bölü hedef, hücre biçimi yüzde • Kümülatif gerçekleşen: yıl başından seçili döneme kadar ETOPLA ile dönem ölçütü “küçük eşit” kullanılarak

Kümülatif sütun raporlarda sıkça atlanır, oysa yönetimin asıl sorusu çoğunlukla “yıl sonu hedefine yetişiyor muyuz” sorusudur. Dönem sütunu gerçek tarih olarak tutulursa küçük eşit ölçütü ay sonu tarihine göre rahatça kurulur.

Hedef sıfır ya da boş olabilen göstergelerde bölme hatası çıkar. Bunu EĞER ile önceden kontrol edin: hedef boşsa oran hücresini boş bırakın. Hata mesajını susturmak için formülü genel bir hata yakalayıcıya sarmak kolaydır ama gerçek veri sorunlarını da gizler; önce hedefin neden boş olduğunu kontrol etmek daha sağlıklıdır.

Yönü ters olan ve oran türü göstergeler

İade oranı, hata sayısı, teslimat süresi gibi göstergelerde düşük değer iyidir. Aynı gerçekleşen bölü hedef formülünü kullanırsanız hedefin altında kalan başarılı sonuç yüzde olarak küçük görünür ve rapor yanlış mesaj verir. Çözüm, ham veri sayfasında değil kural sayfasında kurulur: gösterge tanımına bir yön alanı ekleyin, rapor formülü bu alana bakıp EĞER ile oranı ters çevirsin ya da durumu doğrudan sapma yönüne göre belirlesin.

Oran ve yüzde türü göstergelerde ayrı bir tuzak vardır. Dönemlik oranların ortalamasını almak ağırlıkları yok sayar. Örneğin dönüşüm oranı için aylık oranları ortalamak yerine pay ve paydayı ayrı sütunlarda toplamak ve oranı toplamlar üzerinden hesaplamak doğru sonucu verir. Bu yüzden ham veri sayfasında oranı değil, hem payı hem paydayı saklamak iyi bir alışkanlıktır.

Birimler farklı göstergeleri tek skorda toplamak istiyorsanız (örneğin ağırlıklı performans puanı), ağırlıkları hedef sayfasında tutun ve bileşik skoru TOPLA.ÇARPIM ile hesaplayın; ağırlıkların değiştirilebilir olması tartışmaları görünür kılar.

Durum renkleri, mini grafikler ve özet görünüm

Gerçekleşme oranına bağlı koşullu biçimlendirme, tabloyu bir bakışta okunur yapar. Eşikleri formül içine gömmeyin; hedef sayfasında “yeşil eşiği” ve “sarı eşiği” için ayrı hücreler tutun ve koşullu biçimlendirme kuralı bu hücrelere başvursun. Böylece eşik politikası değiştiğinde kurallara tek tek girmeniz gerekmez.

Renk tek başına yeterli değildir. Renk körlüğü olan okurlar ve siyah beyaz çıktı için durum sütununa kısa bir metin ya da simge de ekleyin. Trendi göstermek için satır sonuna mini grafik (sparkline) koymak, her gösterge için ayrı grafik çizmekten çok daha az yer kaplar ve son dönemlerdeki yönü gösterir.

Yöneticilere gidecek sürümde tablo çoğunlukla bir özetle başlar: kaç göstergenin hedefte olduğu, hangilerinin riskte olduğu. Bu özeti EĞERSAY ile durum sütunundan üretin. Sonrasında dilimleyici ile bölüm ya da ekip seçimi eklenirse aynı dosya birden fazla ekibin toplantısında kullanılabilir. Daha görsel bir pano gerekiyorsa tablo, pano hazırlama adımlarına temel olarak aktarılabilir.

Sık yapılan hatalar ve nasıl önlenir

• Her ay yeni bir sayfa açmak: karşılaştırma ve kümülatif hesaplar imkânsızlaşır. Tek veri tablosuna dönem sütunuyla satır eklemek daha dayanıklıdır.
• Hedefi rapor hücresine elle yazmak: revizyonda hangi hücrelerin değiştiği unutulur. Hedefler ayrı sayfada, formüllerle çekilir.
• Göstergeleri tanımsız bırakmak: aynı KPI’yı ekipler farklı hesaplar. Tanım sayfası zorunludur.
• Boş hücre ile sıfırı karıştırmak: veri henüz girilmedi mi, gerçekten sıfır mı? Boş gerçekleşme için oran hesaplanmamalı, tabloda “veri bekleniyor” görünmelidir.
• Çok fazla gösterge koymak: her KPI’ın bir sahibi ve bir karar bağı olmalıdır. Kararı değiştirmeyen gösterge raporu kalabalıklaştırır.
• Formülleri korumasız bırakmak: rapor sayfasındaki formül hücrelerini kilitleyip yalnızca veri girişi alanlarını açık bırakın.

Tabloyu devreye almadan önce geçmiş döneme ait bilinen bir sonucu girerek raporun doğru değer üretip üretmediğini deneyin. Bilinen bir sonuçla doğrulanmamış rapor, ilk toplantıda sorgulanan rapor olur.

Bu beceri Mindset programlarında nerede çalışılır?

Satış Raporlama ve KPI Tasarımı programının modül başlıkları göstergenin tasarım tarafını kapsar: Satış KPI Tasarımının Temelleri, Temel Satış Metrikleri Kütüphanesi, Satış Pano Tasarımı, Yönetim Raporu Hazırlama ve KPI Kültürü ve Performans Yönetimi. Hangi göstergenin neden izlendiğini tanımlayan satış operasyonu, satış yönetimi ve raporlama ekipleri için uygundur.

Microsoft Excel ile Veri Analizi Eğitimi (İleri Excel) ise tablonun teknik tarafını besler. Veri Analizi ve İleri Formüller, Veri Tabloları ve PivotTabloları, Veri İçeren Grafikler ve Grafik Formatlama modül başlıkları, yukarıdaki formül ve görselleştirme adımlarının çalışıldığı alanlardır. Kurumsal ekiplerde ortak bir KPI şablonu üzerinde çalışmak isteyen birimler, tanım ve teknik tarafı birlikte ele alabilir.