Excel'de ABC Analizi Nasıl Yapılır: 5 Kolay Adım

ABC analizi, envanter kalemlerini yıllık kullanım değerlerine göre üç sınıfa ayırır. A sınıfı kalemler, paranın büyük kısmını oluşturan az sayıdaki kalemdir. C sınıfı kalemler, az miktarda harcama tutan çok sayıdaki kalemdir ve B sınıfı ise bu ikisinin arasında yer alır. Excel'de bunu tek bir tabloyla yapabilirsiniz: yıllık değer, toplam içindeki pay, kümülatif toplam ve her bir sınıfı atayan bir formül.

Bu kılavuz, sınıfların ne anlama geldiğini, beş Excel adımını, uygulamalı bir örneği ve sonucun nasıl grafik haline getirileceğini açıklamaktadır. Ayrıca sınır değerlerinizi nasıl seçeceğinizi ve analiz tamamlandıktan sonra her bir sınıfla ne yapacağınızı da ele almaktadır.

ABC analizi nedir

ABC analizi, hangi kalemlerin en çok ilgiyi hak ettiğine karar vermenin bir yoludur. Basit bir kalıba dayanır: kalemlerin küçük bir kısmı, harcamaların büyük bir kısmını oluşturur.

Management Sciences for Health (MSH) tarafından yayımlanan, ilaç harcamalarının analizi ve kontrolüne ilişkin 2012 tarihli bir bölüm bunu açıkça tanımlamaktadır. Bölümde, "nispeten az sayıda kalemin yıllık tüketim değerinin büyük kısmını oluşturduğu" belirtilmektedir. Şöyle devam etmektedir: "Bu olgunun analizi Pareto analizi veya daha yaygın adıyla ABC analizi olarak bilinir."

Aynı bölümde, kalemlerin "yıllık kullanım değerlerine göre üç kategoriye (A, B ve C) ayrılabileceği" açıklanmaktadır. İster ilaç, ister yedek parça, ister perakende ürün stoklayın, yöntem aynıdır.

Gözden kaçması kolay bir nokta var. Sınıflar kalıcı etiketler değildir. MSH, "Kullanım kalıpları değişirse, bir sonraki ABC analizi yapıldığında kalem farklı bir kategoriye düşebilir" diye belirtmektedir. Bu nedenle ABC analizi, tek seferlik bir proje olarak değil, rutin bir kontrol olarak en iyi şekilde çalışır.

A, B ve C sınıfları ne anlama gelir

MSH bölümü her sınıf için tipik aralıklar vermektedir:

Sınıf	Kalemlerin payı	Yıllık değer payı	Genellikle ne anlama geldiği
A	yüzde 10 ila 20	yüzde 75 ila 80	Az sayıda kalem, paranın büyük kısmı
B	yüzde 10 ila 20	yüzde 15 ila 20	Orta grup
C	yüzde 60 ila 80	yüzde 5 ila 10	Çok sayıda kalem, paranın az bir kısmı

Bunlar kurallar değil, tipik aralıklardır. MSH, "Bu sınırlar biraz esnektir" demektedir. Verdiği örnekte, A sınıfını fonların yüzde 70'ini oluşturan kalemler olarak belirlemektedir.

Sınıfları belirleyen değer, yıllık tüketim değeridir: bir yılda kullanılan birim sayısı ile birim maliyetin çarpımı. Çok büyük miktarlarda kullanılan ucuz bir kalem A sınıfına girebilir. Yılda bir kez kullanılan pahalı bir kalem ise C sınıfına düşebilir.

American Journal of Business Education'da yayımlanan 2014 tarihli bir makale, yalnızca değer kullanmayı sorgulamaktadır. Ders kitaplarının "tek kriter olarak dolar hacmine odaklandığını" savunmakta ve başka kriterlerin de eklenmesini önermektedir. İlk aşama için değer, MSH bölümünün kullandığı yöntemdir.

Başlamadan önce ihtiyacınız olanlar

Excel'de ABC analizi için kalem başına yalnızca birkaç sütun gerekir:

• Kalem adı veya SKU. Her kalem için bir satır.
• Yıllık kullanılan veya satın alınan birim sayısı. Her kalem için aynı 12 aylık dönemi kullanın.
• Birim maliyet. Sayım yaptığınız birim cinsinden bir birimin maliyeti.

MSH, eşleşen dönemin önemini vurgulamaktadır: "Geçersiz karşılaştırmalardan kaçınmak için tüm kalemler için aynı inceleme döneminin kullanıldığından emin olun." Ayrıca, paket boyutlarını karıştırmak yerine maliyet ve miktar için tablet veya tek bir kutu gibi aynı temel birimin kullanılmasını tavsiye eder.

Verileriniz bir envanter veya satın alma sisteminden geliyorsa, bunları bir CSV veya Excel dosyası olarak dışa aktarın. Dönem içinde hareket görmeyen kalemleri kaldırın veya bunları tutarak C sınıfına düşmelerini bekleyin.

Excel'de ABC analizi nasıl yapılır

Aşağıdaki beş adım, MSH bölümündeki yöntemin Excel formüllerine uyarlanmış halini takip eder. Örnekte 1. satıra bir başlık, 2. satıra sütun başlıkları ve 3 ila 12. satırlara 10 kalem yerleştirilmiştir. A, B ve C sütunları kalem adını, yıllık birimleri ve birim maliyeti tutar.

Adım 1: Kalemleri, birimleri ve birim maliyeti listeleyin

Adı, yıllık birimleri ve birim maliyetiyle birlikte her kalem için bir satır girin veya yapıştırın. Tablonun daha sonra kolayca sıralanabilmesi için 2. satıra başlıklar ekleyin.

Daha ileri gitmeden önce verileri kontrol edin. Boş maliyetleri, negatif miktarları ve yinelenen SKU'ları arayın, çünkü bunların her biri toplamları bozacaktır. Her sütunda hızlı bir filtreleme yapmak genellikle bunları bulur.

Aynı kalemin farklı fiyatlardan birden fazla satın alımı yapıldıysa, tutarlı tek bir maliyet kullanın. MSH, gerçek birim maliyetin takip edilmesinin zor olduğu durumlarda "ağırlıklı ortalama veya FIFO ortalamasının" en doğru alternatifler olduğunu belirtmektedir.

Adım 2: Yıllık değeri ve toplam içindeki payını hesaplayın

D sütununda, her kalemin yıllık değerini elde etmek için birimleri maliyetle çarpın. D3 hücresine =B3*C3 yazın ve formülü aşağıya doğru sürükleyin.

E sütununda, payını bulmak için her bir değeri tüm değerlerin toplamına bölün. E3 hücresine =D3/SUM($D$3:$D$12) yazın ve aşağıya doğru sürükleyin. Dolar işaretleri, formül kopyalandıkça toplam aralığının sabit kalmasını sağlar. E sütununu iki ondalık basamaklı yüzde olarak biçimlendirin.

MSH bu hassasiyeti bir nedenden dolayı önermektedir. Kendi ifadeleriyle, "birkaç kalemin değeri birbirine yakın olabilir ve birçoğu toplam değerin yüzde 1'inden daha azını temsil edebilir."

Adım 3: Kalemleri değerlerine göre en büyükten en küçüğe doğru sıralayın

Başlıklar dahil tüm tabloyu seçin ve D sütununa göre en büyükten en küçüğe doğru sıralayın. Excel'de bu, Veri, ardından Sırala seçeneğiyle, D sütunu ve düzen En Büyükten En Küçüğe olarak ayarlanarak yapılır.

Bir formül tercih ederseniz, SORT işlevi sıralanmış bir kopya döndürür. Microsoft'un sözdizimi =SORT(array,[sort_index],[sort_order],[by_col]) şeklindedir; burada -1 sıralama düzeni azalan anlamına gelir. Bu tablo için, =SORT(A3:E12,4,-1) dördüncü sütuna göre, en yüksek değer ilk sırada olacak şekilde sıralama yapar.

Bu adımdan sonra, en yüksek yıllık değere sahip kalem en üstte yer alır. Bu sıralama, bir sonraki adımdaki kümülatif toplamı anlamlı kılan şeydir.

Adım 4: Kümülatif yüzdeyi ekleyin

F sütununa payların kümülatif toplamını ekleyin. F3 hücresine =SUM($E$3:E3) yazın ve aşağıya doğru sürükleyin. Aralığın ilk kısmı sabit kalır ve ikinci kısmı her seferinde bir satır büyür.

Son satır yüzde 100 göstermelidir. Göstermiyorsa, D ve E sütunlarında boş hücreler veya metin değerleri olup olmadığını kontrol edin.

Bu sütun ABC analizinin kalbidir. Her satırın üzerindeki kalemlerin birlikte toplam değerin ne kadarını oluşturduğunu gösterir.

Adım 5: A, B ve C sınıflarını atayın

G sütununda, her bir kalemi etiketlemek için bir formül kullanın. Yüzde 80 ve 95'lik sınır değerlerle, bunu G3 hücresine girin ve aşağıya doğru sürükleyin:

=IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")

IFS işlevi her bir koşulu sırayla kontrol eder ve ilk eşleşmeyi döndürür. Microsoft'un kendi örneği de son genel durum olarak TRUE ifadesini kullanarak aynı kalıbı benimser. Kümülatif olarak yüzde 80'e kadar olan kalemler A, yüzde 95'e kadar olanlar B ve geri kalanlar C olur.

Son olarak, =COUNTIF(G3:G12,"A") ile her bir sınıfı sayın ve aynısını B ile C için de yapın. Sayımları yukarıdaki tipik aralıklarla karşılaştırın. A sınıfı ekibinizin yönetemeyeceği kadar büyük veya küçükse sınır değerleri ayarlayın.

Uygulamalı bir örnek

İşte yıllık değere göre sıralanmış 10 kalem için açıklayıcı bir tablo. Sayılar örnektir, gerçek bir şirketten alınan veriler değildir.

Kalem	Yıllık birimler	Birim maliyet	Yıllık değer	Pay	Kümülatif	Sınıf
SKU-01	1,200	$45.00	$54,000	36.00%	36.00%	A
SKU-02	3,000	$12.00	$36,000	24.00%	60.00%	A
SKU-03	500	$40.00	$20,000	13.33%	73.33%	A
SKU-04	8,000	$1.50	$12,000	8.00%	81.33%	B
SKU-05	2,000	$4.00	$8,000	5.33%	86.67%	B
SKU-06	600	$10.00	$6,000	4.00%	90.67%	B
SKU-07	1,000	$5.00	$5,000	3.33%	94.00%	B
SKU-08	1,500	$3.00	$4,500	3.00%	97.00%	C
SKU-09	700	$5.00	$3,500	2.33%	99.33%	C
SKU-10	400	$2.50	$1,000	0.67%	100.00%	C

Toplam yıllık değer $150,000'dır. Listenin yüzde 30'unu oluşturan üç kalem, değerin yüzde 73.33'ünü meydana getirir ve A sınıfına girer. Dört kalem B sınıfına düşer ve değerin yüzde 6'sını oluşturan son üç kalem ise C sınıfına girer.

İki detay göze çarpıyor. SKU-04 açık ara en fazla birime sahiptir, ancak düşük maliyeti onu B sınıfına yerleştirir. Sadece 10 kalem olduğunda, sınıf payları tipik aralıklarla eşleşmeyecektir; bu da kısa bir liste için normaldir.

Sonucun grafiği nasıl çizilir

Bir grafik, kalıbı bir toplantıda göstermeyi kolaylaştırır. MSH, kümülatif yüzdeyi kalem numarasına göre çizmeyi önerir; bu da tanıdık ABC eğrisini verir.

Excel'de bunun için yerleşik bir grafik bulunur. Microsoft, Pareto grafiğini "hem azalan düzende sıralanmış sütunları hem de kümülatif toplam yüzdeyi temsil eden bir çizgiyi içeren" bir grafik olarak tanımlar. Bir tane oluşturmak için kalem adlarını ve yıllık değerleri seçin, ardından Ekle, İstatistik Grafiği Ekle ve Pareto seçeneklerini belirleyin.

İzleyicilerin her bir sınıfın nerede başladığını görebilmesi için sınır değerlerinize (yüzde 80 ve 95 gibi) iki yatay çizgi veya etiket ekleyin. Yapay zeka ile Pareto grafiği oluşturma kılavuzumuz, grafiğin kendisini daha derinlemesine ele almaktadır.

Sınır değerlerinizi seçme

Tek bir doğru sınır değer yoktur. MSH, seçimin "hacim ve değerin listedeki kalemler arasında nasıl dağıldığına bağlı olduğunu" açıklamaktadır. Ayrıca "ABC analizi sonuçlarının nasıl kullanılacağına" da bağlıdır.

Yönetim kapasitesi pratik sınırdır. MSH bunu doğrudan ifade eder: "kalemlerin A sınıfına tahsis edilmesi yönetim kapasitesine dayanmalıdır." Ekibiniz her ay 50 kalemi yakından inceleyebiliyorsa, 300 kalemlik bir A sınıfı amaca aykırıdır.

Birkaç yaygın yaklaşım:

• Değer sınırları. Değerin yüzde 80'ine kadar A, yüzde 95'ine kadar B, geri kalanı için C. Bu, yukarıda kullanılan yöntemdir.
• Kalem sayısı sınırları. Değere göre kalemlerin en üstteki yüzde 20'si A, sonraki yüzde 30'u B ve geri kalanı C olur.
• Sabit listeler. Bazı ekipler, değer payları ne olursa olsun, A sınıfını en üstteki 25 veya 50 kalem olarak belirler.

Hangisini seçerseniz seçin, bunu bir kenara yazın ve her seferinde kullanın. Bu çeyreğin sınıflarını geçen çeyreğinkilerle karşılaştırmak, yalnızca sınır değerlerin aynı kalması durumunda işe yarar.

Her bir sınıfla ne yapılmalı

ABC analizinin amacı, çabayı paranın olduğu yere harcamaktır. MSH bölümü, sonuçları kullanmanın birkaç yolunu listelemektedir:

• A sınıfı kalemleri daha sık sipariş edin. MSH, A sınıfı kalemlerin "daha sık ve daha küçük miktarlarda sipariş edilmesinin envanter tutma maliyetlerinde bir azalmaya yol açması gerektiğini" belirtmektedir.
• Önce A sınıfı fiyatları müzakere edin. Bölüme göre, "analizde A ürünü olarak sınıflandırılan kalemler için fiyat indirimleri önemli tasarruflar sağlayabilir."
• A sınıfı stokları daha sık sayın. MSH, "periyodik stok sayımlarının ABC analizi ile yönlendirilmesi ve A sınıfı kalemler için daha sık sayım yapılması gerektiğini" belirtmektedir.
• A sınıfı sipariş durumunu izleyin. A sınıfı bir kalemin beklenmedik bir şekilde tükenmesi, maliyetli acil durum satın alımlarına yol açabilir.

C sınıfı kalemler için daha büyük, daha seyrek siparişler ve daha az sayım gibi daha basit kurallar uygulanabilir. B sınıfı ise bu ikisinin arasında yer alır. Yavaş hareket eden stoklar bir endişe kaynağıysa, yavaş hareket eden envanteri tespit etme kılavuzumuz bu analizle iyi bir uyum sağlar.

Yapay zeka ile daha hızlı yapma

Veriler temizlendikten sonra Excel adımları birkaç dakika sürer. Dışa aktarılan verileri temizlemek ve çalışmayı her çeyrekte tekrarlamak ise daha uzun sürer.

Bir yapay zeka çalışma alanı, aritmetik işlemleri ve sıralamayı tek bir istekte yapabilir. Envanter veya satın alma dışa aktarımını Powerdrill Bloom'a yükleyin ve doğal dilde sınır değerlerinizle bir ABC analizi isteyin. Her bir kalem için yıllık değer, pay, kümülatif yüzde ve sınıfın yanı sıra bir Pareto grafiği talep edin.

Ardından, herhangi bir e-tablo gibi kontrol edin. Toplam yıllık değeri kendi toplamınızla karşılaştırarak onaylayın ve her sınıftan iki kalemi rastgele kontrol edin. Excel AI assistant sayfamız bu tür e-tablo çalışmalarını daha ayrıntılı olarak ele almaktadır. Tahmin araçlarına daha geniş bir bakış için, envanter ve talep tahmini için en iyi yapay zeka araçları derlememize göz atın.

Kaçınılması gereken yaygın hatalar

• Zaman dilimlerini karıştırmak. Bir kalem için on iki ay, diğeri için altı ay kullanılması payları anlamsız hale getirir.
• Değer yerine birimleri kullanmak. Sınıflar yalnızca birimlere değil, birimler ile maliyetin çarpımına bağlıdır.
• Kümülatif toplamdan önce sıralama yapmayı unutmak. Sıralanmamış bir listedeki kümülatif yüzde, kalemleri yanlış sınıfa yerleştirir.
• Sınıfları kalıcı olarak görmek. Kalemler sınıflar arasında geçiş yaptığından, analizi her çeyrekte veya yılda bir yeniden çalıştırın.
• Kapasiteyi göz ardı eden sınır değerler. Yakından yönetilemeyecek kadar uzun bir A sınıfı listesi, B sınıfından daha fazla ilgi görmez.
• Kritik ucuz kalemleri göz ardı etmek. Düşük değerli bir kalem, tükendiğinde yine de işi durdurabilir. MSH bölümü, ABC analizini hayati, temel ve temel olmayan kalemlerin ayrı bir değerlendirmesiyle eşleştirmektedir.

Kalem listeniz karmaşık bir dışa aktarımdan geliyorsa, ilk ABC tablosunu ve grafiğini oluşturmak için Powerdrill Bloom'u deneyebilirsiniz.

Sıkça sorulan sorular

Envanter yönetiminde ABC analizi nedir?

ABC analizi, kalemleri yıllık tüketim değerlerine göre üç sınıfa ayırır. A sınıfı kalemler, değerin büyük kısmını oluşturan az sayıdaki kalemdir. C sınıfı kalemler, az miktarda değer tutan çok sayıdaki kalemdir ve B sınıfı ise bu ikisinin arasında yer alır. Ekiplerin kontrol çabalarını paranın olduğu yere odaklamasına yardımcı olur.

Excel'de ABC analizi nasıl hesaplanır?

Her bir kalem için yıllık birimleri birim maliyetle çarpın, ardından her kalemin payını bulmak için toplama bölün. Değere göre en büyükten en küçüğe doğru sıralayın, payların kümülatif toplamını ekleyin ve IFS gibi bir formülle sınıfları atayın. Yüzde 80 ve 95'lik sınır değerler, MSH bölümündeki tipik aralıklar içinde yer alır.

ABC analizi için yüzdeler nelerdir?

Yaygın bir kılavuz ilkeye göre, A sınıfı kalemlerin yüzde 10 ila 20'sini ve değerin yüzde 75 ila 80'ini tutar. B sınıfı kalemlerin diğer bir yüzde 10 ila 20'sini ve değerin yüzde 15 ila 20'sini tutar. C sınıfı ise kalemlerin yüzde 60 ila 80'ini ve değerin yüzde 5 ila 10'unu tutar.

Excel'de ABC sınıflandırması için formül nedir?

F sütunundaki kümülatif yüzde ve 3. satırdan başlayan verilerle, =IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C") formülünü kullanın. Kendi sınır değerlerinize uyması için 0.8 and 0.95 değerlerini değiştirin. İç içe geçmiş IF formülleri de aynı işi yapabilir.

ABC analizi neden önemlidir?

Envanter bütçesinin büyük kısmının nereye gittiğini gösterir, böylece ekipler bu kalemleri daha yakından yönetebilir. Tipik kullanımlar arasında A sınıfı kalemlerin daha sık sipariş edilmesi, fiyatlarının önce müzakere edilmesi ve daha sık sayılması yer alır. Ayrıca planlarla eşleşmeyen harcamaları da işaret eder.

Kaynaklar: Management Sciences for Health, MDS-3 Bölüm 40: İlaç harcamalarının analizi ve kontrolü · Ravinder ve Misra, Envanter Yönetimi için ABC Analizi (2014) · Microsoft Desteği, SORT işlevi · Microsoft Desteği, IFS işlevi · Microsoft Desteği, Pareto grafiği oluşturma.