Excel'de ABC Analizi Oluşturma İçin Adım Adım Kılavuz

Excel'de ABC analizi, ürünleri, müşterileri veya tedarikçileri işletme için önem derecelerine göre sıralama yöntemidir. Bunu yapmak için verileri bir metriğe (örneğin ciro) göre azalan şekilde sıralamanız, her bir öğenin toplam içindeki payını hesaplaymanız, kümülatif toplamı bulmanız ve pozisyonları üç gruba ayırmanız gerekir: A (sonucun en üst %70–80'i), B (sonraki %15–20) ve C (kalan %5–10). Tüm süreç 5–10 dakika sürer ve yalnızca temel formüller gerektirir.

Yöntemin özü ve dağıtım kriterleri

Yöntem Pareto ilkesine dayanır: çabanın %20'si sonucun %80'ini verir. Depo veya satış bağlamında bu, ürün çeşitlerinin küçük bir kısmının ana karı getirdiği anlamına gelir.

Klasik dağılım şu şekildedir:

• Grup A: En değerli pozisyonlar. Genellikle ciroyu %70–80 oranında sağlayan envanterin %10–20'sidir. Günlük kontrol ve doğru tahmin gerektirirler.
• Grup B: Ara kategori. Cirolarında %15–20 paya sahip olan ürünlerin %20–30'u. Kontrol periyodiktir (haftada/ayda bir).
• Grup C: Düşük değerli pozisyonlar. Cironun yalnızca %5–10'unu sağlayan ürün çeşitlerinin %50–60'ı. Nadiren veya "yedek" olarak büyük partiler halinde satın alınırlar.

Hesaplamadan önce verilerin hazırlanması

Doğru bir analiz için en az iki sütundan oluşan bir tabloya ihtiyacınız olacaktır:

1. Nesne adı (ürün, müşteri, tedarikçi).
2. Analitik metrik (ciro, satış adedi, brüt kar veya satın alma hacmi).

Verilerde boş satır olmadığından ve sayısal değerlerin doğru yazıldığından emin olun (toplam içeren hücrelerde "adet" gibi metin sembolleri olmamalıdır). Analizi sabit bir dönem (ay, çeyrek veya yıl) üzerinden yapmak, resmin objektif olmasını sağlar.

Talimat: Excel'de ABC analizi 5 adımda nasıl yapılır

Süreçti ürün satışlarını içeren bir tablo örneği üzerinden inceleyelim. Ürün adlarının A sütununda, satış tutarlarının ise B sütununda olduğunu varsayalım (veriler 2. satırdan başlıyor).

Adım 1. Toplam tutarın hesaplanması

İlk olarak, daha sonra payları hesaplayabilmek için toplam ciroyu öğrenmeniz gerekir. Herhangi bir boş hücreye (örneğin D1) şu formülü girin: =TOPLA(B:B) Bu hücrenin adresini aklınızda tutun veya mutlak referans kullanarak formüllerde sabitleyin.

Adım 2. Her bir öğenin payının hesaplanması

"Pay" adlı bir sütun oluşturun. C2 hücresine mevcut satışın toplam tutara bölünmesi formülünü girin: =B2/$D$1 (Dolar işaretleri $, kopyalama sırasında kaymaması için toplam hücresini sabitler). Formülü listenin sonuna kadar aşağıya doğru sürükleyin. Görünür kılmak için sütuna yüzde formatı uygulayın.

Adım 3. Verilerin sıralanması

Bu kritik bir aşamadır. Bunun olmadan kümülatif toplam doğru çalışmayacaktır.

1. Tüm tabloyu seçin.
2. Veri sekmesine gidin → Sırala.
3. "Ciro" sütununa (veya hesapladığınız metriğe) göre büyükten küçüğe sıralamayı seçin.

Artık en karlı ürünler listenin en üstünde yer almaktadır.

Adım 4. Kümülatif payın hesaplanması

"Kümülatif Pay" adlı bir sütun oluşturun.

• İlk hücrede (D2) sadece ilk ürünün payına referans verin: =C2.
• İkinci hücrede (D3) önceki satırın kümülatif payı ile mevcut payı toplayın: =D2+C3.
• Bu formülü tablonun sonuna kadar sürükleyin. Son satırdaki değer 1'e (veya %100'e) eşit olmalıdır.

Adım 5. Grupların otomatik atanması

Son sütun olan "Grup" sütununu oluşturun. Kümülatif paya dayalı otomatik kategorizasyon için iç içe geçmiş EĞER fonksiyonunu kullanacağız. E2 hücresi için formül: =EĞER(D2<=0,7; "A"; EĞER(D2<=0,9; "B"; "C"))

Formül mantığı: Kümülatif pay %70'e eşit veya daha azsa — bu A grubudur. %70'ten büyük ama %90'a eşit veya daha azsa — B grubudur. %90'ın üzerindeki her şey — C grubudur.

Kopyalamak için hazır formül şablonu

Hesaplamayı hemen uygulamak istiyorsanız, standart yapı için (Ürün A sütununda, Ciro B sütununda, Toplam B1000 hücresinde) formüllerin özet tablosu aşağıdadır:

Formülleri girdikten sonra, kümülatif toplam formülünü uygulamadan önce verileri ciroya göre azalan şekilde sıralamayı unutmayın veya sıralamadan sonra yeniden hesaplayın.

Analiz yapılırken yapılan yaygın hatalar

1. Sıralama yapılmaması. En büyük hata. Ürünleri pahalıdan ucuza sıralamazsanız, Pareto ilkesi bozulur ve "A" grubuna rastgele pozisyonlar girer.
2. Yanlış metrik seçimi. Sadece satılan adet sayısına göre (fiyat dikkate alınmadan) yapılan analiz, gerçek kar getirmeyen ucuz sarf malzemelerini lider konuma çıkarabilir. Ciro veya brüt kar kullanmak daha iyidir.
3. "Donmuş" veriler. Ürün çeşitliliği ve talep değişir. ABC analizini düzenli olarak (çeyrekte bir) yapın, aksi takdirde eskimiş bilgilere dayanarak stokları optimize edersiniz.
4. Yeni ürünlerin göz ardı edilmesi. Yeni ürünler henüz ciro yapmamış olabilir ve "C" grubuna düşebilir, oysa yüksek potansiyele sahiptirler. Bunları ayrı bir kategoriye ayırmak veya manuel olarak analiz etmek gerekir.

SSS

Aracı sütunlar olmadan tek bir formülle ABC analizi yapılabilir mi? Teknik olarak karmaşık dizi formülleri veya sıralama ile DÜŞEYARA fonksiyonları kullanılabilir, ancak anlaşılırlık ve filtreleme imkanı için pay ve grup için ayrı sütunlar ayırmak daha iyidir. Bu, raporla ilerideki çalışmayı kolaylaştırır.

Gruplar arasındaki sınır bir ürünün içinde kalırsa ne yapılmalı? Bu, büyük veri hacimlerinde nadiren görülür. Matematiğe sıkı sıkıya bağlı kalın: ürün, aralığın "kapatıldığı" gruba aittir. Örneğin, 15 numaralı üründe kümülatif pay %70,5 olduysa, bu ürün zaten B grubuna aittir.

Bu yöntem hizmetler için uygun mudur? Evet, kesinlikle. "Ürün" yerine "hizmet" veya "müşteri", "ciro" yerine ise "harcanan zaman" veya "sözleşmeden elde edilen kar" yazın. Hesaplama mantığı değişmez.