Varyans Analizi (Price Volume Mix Analysis)

Power BI ile yapılan satış, satış/bütçe modellerinin tamamında yaptığımız hesaplamalardan biri de varyans analizi. Yapılması da şart zaten, fakat gördüğüm birçok modelde sadece önceki seneye -veya bütçeye göre- basit gelir/miktar karşılaştırılması yapılıyor ve aradaki fark -yüzde veya rakamsal olarak- gösteriliyor.

Varyans analizi ne demek?

Varyans analizi gerçekleşenle hedefin kırılımlı karşılaştırılması üzerine bir teknik. Ki hedef genelde ya bütçedir ya da geçen senenin gerçekleşenidir.

Satış tutarı üzerinden temel metriklerimiz basit ve aşağıdaki gibi.

Satışlar := SUM( 'Satışlar'[Tutar] )

Satışlar ÖY := 
CALCULATE( [Satışlar] ; SAMEPERIODLASTYEAR( 'Tarih'[Tarih] ))

Satışlar ÖY Göre Fark # := [Satışlar] - [Satışlar ÖY]

Satışlar ÖY Göre Fark %:= 
DIVIDE([Satışlar] - [Satışlar ÖY] ; [Satışlar ÖY] )

Bir bilgi veriyor mu, evet veriyor! Fakat değişikliğin ne kadarının neyden kaynaklandığını açıklamak için yeterli değil! Örneğin TV ve Video satışlarında 2009 yılında bir önceki seneye göre 83K bir artı fark var. Önceki yıla göre daha fazla -veya daha az- satmış olabiliriz, önceki yıla göre daha yüksek -veya daha düşük- fiyattan satmış olabiliriz.

Yani gelir farkının en tepede iki bileşeni var: Fiyat (Price) ve Miktar (Volume). Aradaki farkın bir kısmı fiyattaki değişiklikten bir kısmı da miktardaki değişiklikten geliyor, hep birlikte toplamda 83K ‘lık farkı oluşturuyor.

Elbette matrise miktar ve fiyatlarla ilgili bilgileri düşürüp biraz daha anlam kazandırabiliriz! Metrikler gene basit:

Miktar := SUM( 'Satışlar'[Miktar] )
Fiyat := DIVIDE( [Satışlar] ; [Miktar] )
Miktar ÖY := CALCULATE( [Miktar] ; SAMEPERIODLASTYEAR( 'Tarih'[Tarih] ))
Fiyat ÖY := DIVIDE( [Satışlar ÖY] ; [Miktar ÖY] )

Hatta miktar fiyat önceki yıla göre ne kadar artmış/azalmış da ekleyebiliriz! Fakat bunları ekledikçe matrisin okunması gittikçe zorlaşmaya başlayacak!

Bunun yerine, miktar ve fiyattaki değişikliklerin varyansa katkısını hesaplamak için şöyle bir formül kullanabiliriz:

Miktardan Kaynaklanan Varyans := ( [Miktar] - [Miktar ÖY] ) * [Fiyat ÖY]

Fiyattan Kaynaklanan Varyans := ( [Fiyat] - [Fiyat ÖY] ) * [Miktar]

Mantığı iş diliyle şöyle açıklamak mümkün: Bu sene önceki seneye göre ne kadar fazla (ya da az) miktar sattıysam, bunları geçen senenin fiyatıyla satmış olsaydım ne kadar kazanırdım (ya da kaybederdim) !

İlk matrise devam etseydik okumak gittikçe zorlaşacaktı ama şimdi bu matrisi okumak da yorumlamak da daha kolay! Ev aletlerinde önceki seneye göre daha fazla satmışız ama öyle fiyat düşürmüşüz ki miktar artışının getirisinin çok ötesinde bir götürüsü olmuş! Gibi.

Eğer analiz için ihtiyacınız sadece bu kadarsa, yani fiyat ve miktar kaynaklı varyans yeterliyse buna benzer formüller işimizi görecektir. Hatta bu durumda -misal- [Fiyattan Kaynaklanan Varyans] = [Satışlar ÖY Göre Fark #] – [Miktardan Kaynaklanan Varyans] da diyebilirsiniz.

Ama genelde bundan daha fazlasına ihtiyacımız olur. Örneğin farklı para birimleriyle satış yapan bir şirketseniz fiyat kaynaklı varyansı, kur etkisine, parite etkisine kırmak gerekebilir. Benzer şekilde miktar varyansını da mix (hem geçen sene hem de bu sene satılmakta olan ürünlerin satışı) etkisi, yeni ürün (geçen sene satışı olmayıp bu sene satılmaya başlayan ürünler) etkisi, delist ürün (geçen sene satılan ama artık satılmayan) etkisi gibi alt kırılımlara bölmek gerekebilir.

Satışlar geçen seneye göre arttı (ya da azaldı), iyi güzel, bu artışın ne kadarını yeni ürünlerden, ne kadarı artık satmadığımız ürünlerden, ne kadarını devam eden ürünlerden kazandık, ne kadarı kur farkı, ne kadarı parite vs vs devam eder!

Bu çeşitliliği üretim-satış yapan şirketlerin çoğu için yapmak gerekiyor, ürün yaşam süreleri kısaldı, yeni ürün geliştirme hızı özellikle giyim-perakende gibi sektörlerde inanılmaz hızlı, farklı para birimleriyle satınalma satış neredeyse her şirketin kaçınılmazı.

Bizim modelimizde de -ki neredeyse tüm yazılarda aynı standart Contoso’nun Türkçe’leştirilmiş halini kullanıyorum- bazı ürünlerin önceki sene satışı var bu sene yok -yani delist olmuş- , bazılarının geçen sene satışı yok bu sene var -yani yeni ürün-, bazıları hem geçen sene satılmış hem de bu sene satılmaya devam ediyor.

Eski bir “iş kullanıcısı” olarak uyarmadan da geçmeyeyim, bu tür bilgileri hesaplarken de yorumlarken de grup bazında ya da ortalama gibi  hesaplardan / yorumlardan kaçınmak lazım. Ortalama / grup bazında bakmak/hesaplamak her zaman için alttaki detayları saklar, görmenize engel olur. Tüm bu hesapları -misal- kategori bazında yapabiliriz. Kategorinin artışı iyidir (ya da kötüdür), altında 100 tane ürün  vardır, 90 tanesinin performansı yerlerdedir, sırf 10 tane süper ürün var diye “Kategori” bazında baktığınızda her şey iyiymiş (ya da kötüymüş) gibi gözükebilir. En alt seviyedeki varlık (entity) kimse onun detayı üzerinden yapmak daha doğru olur, ki bu da örnek modelimiz için ürünün kendisi!

Örneğe devam edelim : Geçen seneye göre satış varyansını, fiyat, miktar, mix, yeni ürün ve delist ürün bazında kırmak istiyoruz!

Kırılımlar “önceki sene satışı olan ama artık satılmayan” gibi ibareler içerdiği için metriklerimizi yazarken bunları göz önüne almamız gerekiyor. Eğer bunu yapmazsak hatalı sonuçlar alırız!

Ve hedefimiz aşağıdaki gibi bir “waterfall” grafiği elde edebilmek!

Waterfall görseli herhangi bir değişimin kategorik kırılımını anlamak açısından en uygun görsellerden biri. Power BI ‘daki AI kabiliyetlerinen olan, line-bar chart türü grafiklerdeki “Explain Increase-Decrease” (artışı-azalışı açıkla) özelliğinde de bu görsel kullanılıyor.

Görseldeki Fiyat, Miktar, Mix vs gibi modelin normal verisinde olmayan kırılımlar için bize bir tane “bağlantısız” (disconnected) -yani diğer tablolarla ilişkisi olmayan tablo lazım.

Bunu da en kolay Home tabındaki Enter Data ‘dan yaparız.

** En son güncellemeyle yeni ribbon artık varsayılan seçenek olarak geliyor.

Bu teknik çok bilinen bir teknik ve mantığı basit. SWITCH fonksiyonuyla birlikte basit bir metrik yazarak, 1 ise görselde “Fiyat” gözüksün ve şu metriği  göster, 2 ise Miktar gözüksün ve şu metriği göster …. diyeceğiz.

Burada bir karar vermemiz lazım: 5 tane ayrı ayrı, her bir etken için bir metrik yazabiliriz ve bir 6. metrik daha yazıp bunda da SWITCH ile 1 ise şu, 2 ise bu vs. diyebiliriz. Ya da hepsini VAR -değişken yapısını- kullanarak tek bir metrikle çözebiliriz.

Model ne kadar sade olursa o kadar iyi! Bu yüzden ikinci yöntemi kullanacağım. Kod biraz uzun olacak belki ama mantığı gayet basit!

Varyans Analizi = 
VAR _Fiyat_Etkisi =
SUMX(
    ADDCOLUMNS(
        VALUES( 'Ürünler'[Ürün ID] ),
        "Price",
            ( [Miktar ÖY] )
                * IF(
                    ISBLANK( [Fiyat ÖY] )
                        || ISBLANK( [Fiyat] ),
                    0,
                    [Fiyat] - [Fiyat ÖY]
                )
    ),
    [Price]
)
VAR _Miktar_Etkisi =
SUMX(
    ADDCOLUMNS(
        VALUES( 'Ürünler'[Ürün ID] ),
        "Volume",
            (
                IF(
                    ISBLANK( [Fiyat ÖY] )
                        || ISBLANK( [Fiyat] ),
                    0,
                    [Miktar] - [Miktar ÖY]
                )
            ) * ( [Fiyat ÖY] )
    ),
    [Volume]
)
VAR _Mix_Etkisi =
    SUMX (
        ADDCOLUMNS (
            VALUES ( 'Ürünler'[Ürün ID] ),
            "Mix", IF (
                ISBLANK ( [Fiyat ÖY] ) || ISBLANK ( [Fiyat] ),
                0,
                ( [Fiyat] - [Fiyat ÖY] )
            ) * ( [Miktar] - [Miktar ÖY] )
        ),
        [Mix]
    )
VAR _YeniUrun_Etkisi =
SUMX(
    ADDCOLUMNS(
        VALUES( 'Ürünler'[Ürün ID] ),
        "Yeni Ürün",
            IF(
                ISBLANK( [Satışlar ÖY] )
                    || [Satışlar ÖY] = 0,
                [Satışlar],
                0
            )
    ),
    [Yeni Ürün]
)
VAR _DelistUrun_Etkisi = 
[Satışlar ÖY Göre Fark #] - _Fiyat_Etkisi - _Miktar_Etkisi - _YeniUrun_Etkisi - _Mix_Etkisi

RETURN
    SWITCH (
        MAX ( 'Fiyat Miktar Mix Delist Yeni Ürün'[Sıralama] ),
        1, _Fiyat_Etkisi,
        2, _Miktar_Etkisi,
        3, _Mix_Etkisi,
        4, _YeniUrun_Etkisi,
        5, _DelistUrun_Etkisi,
        BLANK ()
    )

Yapıyı özetlemek gerekirse:

• Her bir etkeni hesaplamak için bir değişken kullan!
• SUMX ( ADDCOLUMNS ( VALUES ‘Ürünler'[Ürün ID ] ) kısmı, hesaplamaları her bir ürün bazında yapmak için. Her bir ürün için ilgili etkenin değerini hesapla, sonra gördüğün tüm ürünler için bunları topla!
• ADDCOLUMNS altındaki “Price”, “Volume”, “Mix” ‘i hem yazdığım diğer metriklerle isimleri karışmasın diye böyle yazdım, hem de bu tekniğin İngilizce jargondaki karşılığı “Price-Volume-Mix Analysis” .
• ISBLANK( …) kısımları önceki sene satış var mı bu sene satış var mı yok mu testleri için. [Metrik] <> 0 gibi bir yazım da kullanılabilir.
• Condition’ları birleştirirken || yerine OR ‘da kullanılabilir.
• Bütün değişkenleri hesapladıktan sonra RETURN deyip SWITCH ile birlikte, oluşturduğumuz tablodaki sıralamalarla uyumlu bir şekilde “1 ise Fiyat_Etkisi değişkenini göster, görselde de Fiyat gözüksün” … diye sıralamaları yapmak.
• SWITCH kısmında MAX yerine MIN de kullanılabilir. Farketmez. Sonuçta tablodaki tek bir satırın değerini bulmak istiyoruz.
• En sondaki BLANK () bir kodlama alışkanlığı, yazmasak da çalışır mı çalışır, tüm “condition” ‘ları yazdık çünkü. Ama veride ne olduğunu bilemeyiz diye düşünüp alışkanlık haline getirmek daha doğru bence. “Hiç birine uymuyorsa bir şey gösterme”

Varyans analizi sizin veriniz, işiniz, şirketiniz özelinde farklı kırılımlar içerebilir. Fiyat, Miktar Mix genelde herkeste var, ama bunların alt kırılımları size bağlı. Tüm satış ve finans modellerinizde kullanmanızı öneririm.

Yazıdaki modeli -bloga üyeyseniz- indirebilirsiniz.

Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!