Jak zastosować metodę ABC w Excelu?
Prosty sposób na kontrolę zapasów i optymalizację logistyki
W zarządzaniu logistyką ogromne znaczenie ma skuteczna analiza danych – szczególnie w przypadku dużej liczby pozycji magazynowych. Jednym z najprostszych, a zarazem najbardziej efektywnych narzędzi analitycznych jest metoda ABC. W połączeniu z funkcjonalnościami Excela staje się wyjątkowo praktycznym rozwiązaniem, które wspiera podejmowanie trafnych decyzji. W tym artykule pokażemy, jak przeprowadzić analizę ABC krok po kroku.
W oddzielnych wpisach przedstawiliśmy opis metody ABC/XYZ oraz sposobu obliczania metody XYZ w Excelu.
Spis treści
Na czym polega metoda ABC?
Metoda ABC ma za zadanie wyznaczenie najlepszych produktów „gwiazd”, które przynoszą największe zyski przy stosunkowo niewielkim udziale w zasobach magazynu.
Metoda ABC pozwala podzielić asortyment na trzy grupy – w zależności od ich wartościowego udziału w całości zapasów:
- Grupa A – najważniejsze produkty, które stanowią tylko 5–20% wszystkich pozycji, ale odpowiadają za aż 75–80% wartości magazynu.
- Grupa B – średnio istotne produkty – to zwykle około 15–20% pozycji i podobny udział w wartości.
- Grupa C – najmniej znaczące pozycje – stanowią większość (nawet do 80% wszystkich produktów), lecz mają marginalny udział w wartości zapasów (ok. 5%).
Taki podział pozwala firmom skoncentrować zasoby i uwagę na najważniejszych pozycjach – tych, które mają największy wpływ na wynik finansowy i efektywność operacyjną.
Dzielimy się z Wami bezpłatnie wiedzą programistyczną.
Jeżeli doceniacie naszą pracę możecie nas wesprzeć dobrowolną wpłatą:
Dlaczego warto stosować analizę ABC?
Wdrożenie tej metody niesie ze sobą wiele korzyści:
- Lepsze planowanie i kontrola zapasów,
- Skuteczniejsze zarządzanie zakupami i zaopatrzeniem,
- Zmniejszenie kosztów magazynowania,
- Możliwość szybkiego reagowania na zmiany w popycie lub podaży.
To prosta, ale bardzo efektywna strategia, która pozwala uporządkować dane i podejmować decyzje oparte na faktach.
| ZASADA PARETO | KLASYFIKACJA ABC |
80% udziału 20% pozycji { | A 80% udziału 20% pozycji ➔ Najistotniejsza grupa, nad którą należy skupić uwagę w pierwszej kolejności |
20% udziału 80% pozycji { | B 15% udziału 30% pozycji ➔ Grupa do analizy w drugiej kolejności |
C 5% udziału 50% pozycji ➔ Grupa o najmniejszej istotności |
Analiza ABC wartościowa vs. ilościowa – dwa podejścia, różne rezultaty
Choć cel analizy pozostaje ten sam – określenie, które produkty przy relatywnie niskim wolumenie generują największe zyski – kluczowe znaczenie ma sposób, w jaki klasyfikujemy dane do grup A, B i C.
Przykład 1: Analiza ilościowa (wg liczby sztuk)
W tym podejściu progi przypisania do grup ustalane są na podstawie udziału liczby sprzedanych sztuk w całości asortymentu. Przykładowo:
- grupa A – pierwsze 5% sprzedaży (liczonej ilościowo),
- grupa B – kolejne 15%,
- grupa C – pozostałe 80%.
Produkty są najpierw sortowane według wartości sprzedaży, ale klasyfikacja odbywa się na podstawie narastającej liczby sztuk. Jeśli dany produkt mieści się w pierwszych 5% sumy sztuk, trafia do grupy A itd.
Problem:
Załóżmy, że:
- próg grupy A to 5% liczby sztuk (np. 1000 szt.),
- próg grupy B to 20% (łącznie 4000 szt.),
- powyżej tej liczby przypisywana jest grupa C.
Jeśli produkt „czekolada” przynosi aż 40% całkowitego przychodu, ale liczbowo to 4050 sprzedanych sztuk, to — mimo że jest najlepiej sprzedającym się i najbardziej dochodowym produktem — trafi do grupy C. Co gorsza, podobny los spotka wszystkie inne produkty.
To klasyczny przykład, gdzie sztywne progi ilościowe prowadzą do błędnej interpretacji danych. Cała analiza przestaje mieć sens, ponieważ nie oddaje realnej wartości produktów.
Rozwiązanie:
Można dostosować progi ilościowe do skali danych lub oprzeć analizę na wartości sprzedaży, czyli zastosować podejście wartościowe.
Przykład 2: Analiza wartościowa (wg wartości sprzedaży)
Tutaj progi ABC bazują na udziale wartości sprzedaży, a nie liczby sztuk. Typowy podział wygląda tak:
- grupa A – produkty odpowiadające za pierwsze 80% łącznej wartości sprzedaży,
- grupa B – kolejne 15%,
- grupa C – pozostałe 5%.
Produkty są sortowane według wartości sprzedaży, a następnie analizowana jest wartość skumulowana. Produkt, który samodzielnie odpowiada za 40% sprzedaży firmy, trafi bezdyskusyjnie do grupy A — tam, gdzie jego miejsce.
To podejście lepiej odzwierciedla rzeczywisty wpływ produktów na wynik finansowy firmy, nawet jeśli niektóre z nich są sprzedawane w dużej liczbie sztuk.
Podsumowanie:
- Analiza ilościowa skupia się na liczbie sztuk — może prowadzić do mylnych wniosków, jeśli wartości sprzedaży między produktami są bardzo nierówne.
- Analiza wartościowa lepiej identyfikuje produkty kluczowe z punktu widzenia przychodów i powinna być preferowana, gdy zależy nam na rzeczywistym wpływie asortymentu na biznes.
Jak przeprowadzić analizę ABC w Excelu?
Krok 1: Wprowadź dane
Zacznij od listy produktów z kolumnami: nazwa, liczba sprzedanych sztuk, cena jednostkowa. Oblicz wartość każdego produktu (ilość × cena).
Krok 2: Posortuj dane
Wyznacz na jakiej pozycji znajduje się dana wartość sprzedaży w całej kolumnie z wartościami – sortowanie malejące, bo od największej wartości do najmniejszej. W tym celu wykorzystana zostanie formuła POZYCJA. Jako pierwszy parametr należy podać pojedynczą wartość, jako drugi cały zakres wartości dla wszystkich produktów, a jako trzeci 0 (sortowanie malejące).
Formuła ma postać: =POZYCJA(E2; $E$2:$E$99999;0)
Krok 3: Wyznacz sprzedaż bieżącą i jej udział procentowy
Kolejna kolumna, konieczna do zaprezentowania raportu to „Sprzedaż bieżąca” (dla raportu wartościowego). Jest to kolumna zawierająca skumulowaną sumę sprzedaży aktualnie wyznaczanej pozycji i wszystkich pozycji o niższych numerach, tj. numer 1 będzie zawierał tylko własną wartość sprzedaży, a numer ostatni będzie zawierał sumę wszystkich sprzedaży. W tym celu wykorzystana będzie formuła =SUMA.WARUNKÓW(E$2:E$99999;F$2:F$99999; „<=”&F2)
Dla tak wyznaczonej „sprzedaży bieżącej” każdej z pozycji, konieczne jest wyznaczenie jej procentowego udziału w całości – formuła =G2/MAX($G$2:$G$99999)
W ten sam sposób, dla raportu ilościowego, należy wyznaczyć „Ilość narastająco” oraz jej procentowy udział w całości sprzedaży „Ilość bieżąca %” formułami:
– dla komórki I2 =SUMA.WARUNKÓW(D$2:D$99999;F$2:F$99999; „<=”&F2)
– dla komórki J2 =I2/MAX($I$2:$I$99999)
Krok 4: Podział klas
Do przypisania klas w raporcie, konieczne jest zdefiniowanie progów procentowych dla każdej z klas. Najwygodniej jest posługiwać się tutaj dodatkową tabelką.
Dla raportu ilościowego, gdzie określone w tabelce progi wylicza się jako:
dla komórki R2 =Q2*SUMA($D$2:$D$100000)
dla komórki R2 =(Q3+Q2)*SUMA($D$2:$D$100000)
dla komórki R3 =(1-Q3-Q2)*SUMA($D$2:$D$100000)
Dla raportu wartościowego, gdzie określone w tabelce progi wylicza się jako:
dla komórki R2 =Q2*SUMA($E$2:$E$100000)
dla komórki R2 =(Q3+Q2)*SUMA($E$2:$E$100000)
dla komórki R3 =(1-Q3-Q2)*SUMA($E$2:$E$100000):
Krok 5: Przypisz klasy ABC
Dla tak przygotowanych danych, wystarczy już tylko porównać zestawione dane z tabelą podziału klas. W tym celu wykorzystana zostanie formuła JEŻELI.
Dla raportu ilościowego będzie to formuła odnosząca się do sprzedanych sztuk:
=JEŻELI(I2<=R$2;O$2;JEŻELI(I2<=R$3;O$3;O$4))
Dla raportu wartościowego będzie to formuła odnosząca się do wartości sprzedaży:
=JEŻELI(G2<=R$2;O$2;JEŻELI(G2<=R$3;O$3;O$4))
Co zyskasz, stosując analizę ABC?
Dzięki tej analizie:
- Skoncentrujesz działania na najważniejszych produktach,
- Zredukujesz straty wynikające z nadmiarowych zapasów,
- Uprościsz raportowanie i kontrolę stanów magazynowych,
- Poprawisz efektywność operacyjną całego łańcucha dostaw.
Podsumowanie
Metoda ABC to narzędzie, które nie wymaga zaawansowanych narzędzi informatycznych – wystarczy Excel i dobrze przygotowane dane. Prosta klasyfikacja pozwala skupić się na tym, co naprawdę ma znaczenie. Jeśli zależy Ci na sprawnym zarządzaniu zapasami i optymalizacji procesów logistycznych, analiza ABC jest rozwiązaniem, które warto wdrożyć już dziś.
