KHIZHNYAKOVExcel'de ABC-XYZ analizi: adım adım ürün yelpazesi analizi
ABC-XYZ Ürün Yelpazesi Analizi — Excel Şablonu — Şablonu aç →
Mağazanızda 300 ürün var ama cironun büyük kısmını muhtemelen birkaç düzine ürün getiriyor. Geri kalanların bir kısmı rafta yer kaplıyor, depoda para bağlıyor. Hangi ürünün asla tükenmemesi gerektiğini, hangisinin listeden çıkarılabileceğini sezgiyle bilmek zordur.
ABC-XYZ analizi bu soruyu iki eksende cevaplar. ABC, ürünlerin ciroya katkısını; XYZ ise talebin ne kadar istikrarlı olduğunu ölçer. İkisi birleşince dokuz gruplu bir matris çıkar ve her grup için farklı bir stok ve satın alma stratejisi uygulanır.
Bu yazıda analizi Excel'de nasıl yapacağınızı formüller ve örnekle adım adım anlatıyoruz.
Hangi veriler gerekli?
Her ürün için son 12 ayın aylık satışları: tutar (₺) veya adet olarak. Önemli olan tüm ürünlerde aynı birimi kullanmaktır. Satırlarda ürünler, sütunlarda aylar olan basit bir tablo yeterlidir. Yeni çıkan ürünlerde 12 aylık veri olmayacağı için onları ayrıca değerlendirin; aksi hâlde hem ABC'de hem XYZ'de yanlış gruba düşerler.
Adım 1: ABC analizi
- Her ürünün yıllık toplamını hesaplayın:
=TOPLA(B2:M2) - Ürünleri bu toplama göre büyükten küçüğe sıralayın.
- Her ürünün payını bulun:
=N2/TOPLA($N$2:$N$301) - Kümülatif payı hesaplayın: ilk satırda
=O2, sonra=P2+O3 - Grubu atayın:
=EĞER(P2<=0,8;"A";EĞER(P2<=0,95;"B";"C"))
Klasik sınırlar: A grubu cironun ilk %80'i, B sonraki %15'i, C kalan %5'i.
Örnek (yıllık ciro, toplam 1.000.000 ₺):
| Ürün | Ciro | Pay | Kümülatif | Grup |
|---|---|---|---|---|
| Ürün 1 | 420.000 | %42 | %42 | A |
| Ürün 2 | 260.000 | %26 | %68 | A |
| Ürün 3 | 150.000 | %15 | %83 | B |
| Ürün 4 | 90.000 | %9 | %92 | B |
| Ürün 5 | 50.000 | %5 | %97 | C |
| Ürün 6 | 30.000 | %3 | %100 | C |
Altı üründen ikisi ciroyu taşıyor. Gerçek bir mağazada bu tablo yüzlerce satır olur ama mantık aynıdır.
Adım 2: XYZ analizi
XYZ, aylık satışların ne kadar dalgalandığını değişim katsayısı ile ölçer:
Değişim katsayısı = standart sapma / ortalama
Excel'de: =STDSAPMA.P(B2:M2)/ORTALAMA(B2:M2) (Excel 2007'de STDSAPMAS işlevini kullanın).
Grup ataması: =EĞER(Q2<=0,1;"X";EĞER(Q2<=0,25;"Y";"Z"))
- X: %10'a kadar — talep istikrarlı, kolay tahmin edilir.
- Y: %10–25 — dalgalanma var (mevsimsellik, kampanyalar).
- Z: %25'in üzeri — düzensiz, tahmini zor.
Örneğin her ay 95–105 adet arası satılan bir ürünün katsayısı yaklaşık %3'tür (X). Bir ay 5, bir ay 90 adet satılan ürününki ise %65 civarındadır (Z). Perakendede birçok ürün Y ve Z'ye düşer; sınırları kendi işinize göre ayarlamaktan çekinmeyin.
Adım 3: Matris ve kararlar
İki grubu birleştirin: =R2&S2 → "AX", "BZ" gibi.
| X (istikrarlı) | Y (dalgalı) | Z (düzensiz) | |
|---|---|---|---|
| A | Her zaman stokta tut, otomatik sipariş | Emniyet stoku artır, mevsimi izle | Siparişle çalış, yakından takip et |
| B | Düzenli sipariş, standart stok | Periyodik kontrol | Minimum stok |
| C | Asgari stok, seyrek sipariş | Stoğu azalt | Gözden geçir, gerekirse çıkar |
Her hücredeki ürün sayısını ÇOKEĞERSAY, cirosunu ÇOKETOPLA ile hesaplayın:
=ÇOKEĞERSAY($R:$R;"A";$S:$S;"X")
Sık yapılan hatalar
- Adet ve tutarı karıştırmak. Bazı ürünler tutarla, bazıları adetle girilirse paylar anlamsız olur.
- Stoksuz ayları satış düşüşü sanmak. Ürün depoda yoksa satış sıfırdır ama talep sıfır değildir. Bu aylar XYZ'yi yapay olarak Z'ye iter; mümkünse bu ayları ayıklayın.
- Kârlılığı görmezden gelmek. A grubu ciroya göre belirlenir; düşük marjlı bir A ürünü, yüksek marjlı bir B ürününden daha az kâr getirebilir. Gerekirse analizi brüt kâr üzerinden de tekrarlayın.
- CZ ürünlerini hemen silmek. Bazıları başka ürünlerin satışını destekler (örneğin tamamlayıcı aksesuarlar). Karar vermeden önce bağlamı düşünün.
Hazır şablon: ABC-XYZ Ürün Yelpazesi Analizi
Tüm bu adımları elle kurmak yerine ücretli Excel şablonumuzu kullanabilirsiniz. Siz yalnızca aylık satışları girersiniz, gerisini tablo yapar:
- «Parametreler»: A, B ve X, Y gruplarının sınırları; varsayılan olarak %80 ve %95, %10 ve %25, istediğiniz gibi değiştirilebilir.
- «Veriler»: 300 ürüne kadar ve 12 aylık satışlar; sağda toplam, pay, kümülatif pay, ABC ve XYZ grupları otomatik hesaplanır.
- «Sıralama»: gruplarıyla birlikte ciroya göre azalan sırada ürünler, elle sıralama yapmanıza gerek kalmaz.
- «Matris»: dokuz grubun her birinde kaç ürün ve ne kadar ciro olduğu, her kombinasyon için ne yapılacağına dair kısa öneri ve grafik.
Excel 2007 ve üzeri ile LibreOffice'te makrosuz çalışır. Pakette boş tablo ve kurgusal verilerle doldurulmuş bir örnek bulunur. Hesaplama yol göstericidir.
Sık sorulan sorular
ABC-XYZ analizini ne sıklıkla yapmalıyım?
Çoğu işletme için çeyrekte bir yeterlidir. Her seferinde son 12 ayı kullanarak "kayan" bir pencereyle çalışın; böylece mevsimsel değişimler zamanla görünür hâle gelir.
Kafe veya restoran için de uygun mu?
Evet. Menüdeki yemekleri ve içecekleri ürün olarak alın. AX grubundaki yemeklerin malzemesi her zaman hazır olmalı; CZ grubundakiler menüden çıkarılmaya adaydır.
12 ay yerine 6 ayla analiz yapabilir miyim?
ABC için 6 ay da fikir verir. XYZ için ise ne kadar çok ay, o kadar güvenilir sonuç; az veriyle değişim katsayısı tesadüflere çok duyarlı olur.
Analizi tutar yerine adetle yapmanın farkı ne?
Tutar, paranın nereden geldiğini gösterir ve satın alma bütçesi için uygundur. Adet ise depo ve raf yükünü daha iyi yansıtır. İkisi farklı sonuç verebilir; amacınıza göre seçin.
