Analiza ABC i XYZ zapasów: jak podzielić asortyment sklepu i co z tym zrobić
Analiza ABC i XYZ krok po kroku: formuły do Excela, przykład liczbowy dla sklepu i macierz 9 pól z decyzjami dla zakupów, magazynu i oferty. Sprawdź.
Analiza ABC dzieli asortyment na trzy grupy według udziału w wartości sprzedaży: A to nieliczne produkty, które przynoszą większość obrotu, B to środek stawki, a C to długi ogon o niewielkim znaczeniu. Analiza XYZ dokłada drugi wymiar, czyli przewidywalność popytu. Dla sklepu z kilkuset SKU całą analizę ABC i XYZ zrobisz w arkuszu w 1-2 godziny, mając tylko eksport sprzedaży z 12 miesięcy.
Poniżej znajdziesz instrukcję krok po kroku, gotowe formuły do Excela, przykład liczbowy dla sklepu z 12 produktami oraz tabelę decyzji dla każdego z 9 pól macierzy ABC XYZ, czyli to, co realnie zmienić w zakupach, magazynie i ofercie po podziale.
Na czym polega analiza ABC zapasów i czym jest analiza XYZ
Analiza ABC zapasów to uszeregowanie produktów od największej do najmniejszej wartości (sprzedaży, zużycia lub marży) i podział ich na grupy według skumulowanego udziału w tej wartości. Metoda wywodzi się z zasady Pareto: niewielka część pozycji odpowiada za większość wartości. Encyklopedia Zarządzania podaje typowe proporcje: grupa A to 5-20% liczby pozycji i 75-80% wartości, grupa B to około 15-20% pozycji i wartości, a grupa C to 60-80% pozycji i około 5% wartości.
Analiza XYZ klasyfikuje te same produkty według regularności popytu. Miarą jest współczynnik zmienności, czyli odchylenie standardowe sprzedaży z kolejnych okresów podzielone przez średnią sprzedaż. Produkty X sprzedają się równo, Y z wahaniami (sezon, promocje), a Z nieregularnie i trudno je prognozować.
Obie klasyfikacje razem tworzą macierz ABC XYZ z 9 polami: AX, AY, AZ, BX, BY, BZ, CX, CY i CZ. Dopiero ta kombinacja mówi, jak zamawiać dany produkt: ekspres sprzedający się głównie w grudniu wymaga innego planu niż kawa, która schodzi co miesiąc w podobnej ilości, choć oba produkty mogą być w grupie A.
Uwaga na pomyłkę nazw: logistyczna analiza ABC nie ma nic wspólnego z rachunkiem kosztów działań (Activity-Based Costing), który też bywa skracany do „ABC”. Ten tekst dotyczy wyłącznie klasyfikacji asortymentu.
Zanim zaczniesz: dane, czas i narzędzia
Do analizy ABC wystarczy jedna tabela: lista produktów (najlepiej na poziomie SKU, czyli każdego wariantu osobno) z ilością sprzedaną i ceną albo wartością sprzedaży w wybranym okresie. Do analizy XYZ potrzebujesz tej samej sprzedaży rozbitej na miesiące.
- Okres: 12 pełnych miesięcy. Krótszy okres w sklepie z sezonowością zafałszuje oba podziały.
- Dane ze wszystkich kanałów: sklep, Allegro, Amazon i inne marketplace'y razem. Produkt, który jest C w sklepie, może być A na marketplace.
- Kryterium: wartość sprzedaży netto (najczęściej), marża (lepsza, jeśli marże mocno się różnią) albo liczba pobrań z magazynu (do rozmieszczenia towaru na regałach).
- Narzędzie: Excel, Google Sheets albo LibreOffice Calc. Koszt: 0 zł, jeśli masz już arkusz.
- Czas: przy pierwszym podejściu 1-2 godziny, głównie na oczyszczenie danych. Kolejne aktualizacje na gotowym szablonie zajmują kilkanaście minut.
- Poziom trudności: niski. Potrzebujesz sortowania, sumy i funkcji JEŻELI.
Analiza ABC krok po kroku
Analiza ABC to sześć kroków: zbierz dane, policz wartość każdego produktu, posortuj malejąco, wylicz udział procentowy, wylicz udział skumulowany i przypisz grupy według progów 80% i 95%.
- Wyeksportuj sprzedaż z 12 miesięcy. Jeden wiersz na SKU: nazwa, ilość sprzedana, cena netto (albo od razu wartość). Usuń zwroty, pozycje testowe i produkty, które sprzedajesz krócej niż 3 miesiące - je oceniaj osobno.
- Policz wartość każdej pozycji. Ilość × cena netto. Jeśli wybierasz kryterium marży: ilość × marża jednostkowa.
- Posortuj produkty malejąco według wartości. Na górze ma się znaleźć produkt o największym obrocie.
- Policz udział procentowy. Wartość produktu ÷ suma wartości wszystkich produktów × 100. Suma udziałów musi dać 100%.
- Policz udział skumulowany. Dla każdego wiersza dodaj jego udział do sumy udziałów wszystkich wierszy powyżej. Ostatni wiersz musi mieć 100%.
- Przypisz grupy. Najczęściej stosowane progi: A do 80% skumulowanego udziału, B od 80% do 95%, C powyżej 95%. Ustal z góry, do której grupy trafia produkt, który przekracza próg (w przykładzie niżej trafia do grupy niższej), i stosuj to konsekwentnie.
Przykład: analiza ABC i XYZ dla sklepu z 12 produktami
Przykładowy sklep z kawą i akcesoriami sprzedał w ciągu roku towar za 909 344 zł netto. Wszystkie poniższe wartości policzono w kodzie na danych przykładowych: 12 SKU, sprzedaż miesięczna ze stycznia do grudnia. Progi ABC: 80% i 95% wartości skumulowanej. Progi XYZ: współczynnik zmienności do 0,5 (X), do 0,9 (Y), powyżej 0,9 (Z).
| Produkt | Sprzedaż roczna (szt.) | Wartość netto (zł) | Udział | Udział skumulowany | ABC | Współczynnik zmienności | XYZ |
|---|---|---|---|---|---|---|---|
| Kawa ziarnista 1 kg | 2 575 | 229 175 | 25,2% | 25,2% | A | 0,07 | X |
| Ekspres przelewowy | 491 | 171 359 | 18,8% | 44,0% | A | 0,85 | Y |
| Kawa ziarnista 250 g | 4 905 | 156 960 | 17,3% | 61,3% | A | 0,06 | X |
| Młynek do kawy żarnowy | 380 | 98 420 | 10,8% | 72,1% | A | 0,11 | X |
| Filtry papierowe 100 szt. | 3 690 | 70 110 | 7,7% | 79,8% | A | 0,04 | X |
| Kawiarka aluminiowa | 452 | 53 788 | 5,9% | 85,8% | B | 0,72 | Y |
| Zestaw prezentowy | 258 | 51 342 | 5,6% | 91,4% | B | 1,81 | Z |
| Termos 1 l | 385 | 38 115 | 4,2% | 95,6% | C | 0,52 | Y |
| Odkamieniacz | 733 | 21 257 | 2,3% | 97,9% | C | 0,04 | X |
| Dzbanek szklany | 153 | 12 087 | 1,3% | 99,3% | C | 0,14 | X |
| Waga baristyczna | 44 | 6 116 | 0,7% | 99,9% | C | 1,37 | Z |
| Uszczelki (części zamienne) | 41 | 615 | 0,1% | 100,0% | C | 2,15 | Z |
Źródło: dane przykładowe, obliczenia redakcji; progi XYZ (0,5 i 0,9) za: Politechnika Łódzka, Zeszyty Naukowe „Organizacja i Zarządzanie” z. 73, 2019, https://yadda.icm.edu.pl/baztech/element/bwmeta1.element.baztech-fb8ff721-50bf-44aa-9eb4-57e28fbab1bc/c/Kaczorowska_Z_Klasyfikacja_ABC_73.pdf
Co pokazuje wynik przykładu
Grupa A to tu 5 z 12 produktów (42% SKU) i 79,8% obrotu, a nie podręcznikowe 20/80. W małym katalogu proporcje Pareto rzadko wychodzą książkowo. Podobnie było w badaniu Politechniki Łódzkiej: w grupie A znalazło się 35% pozycji. Liczy się próg wartości, a nie to, ile produktów do niego wpadnie.
Ekspres przelewowy jest w A, ale ma współczynnik zmienności 0,85 (Y). Średnio sprzedaje się 40,9 sztuki miesięcznie, ale w listopadzie 90, a w grudniu 140. Gdyby zamawiać go „po średniej”, sklep miałby nadmiar przez 10 miesięcy i braki w szczycie sezonu.
Termos 1 l leży na granicach obu klasyfikacji. Udział skumulowany 95,6% daje mu C, współczynnik 0,52 ledwie przekracza próg X. Przy kryterium marży (35 zł na sztuce) termos przechodzi do grupy B, bo jego udział skumulowany w marży wynosi 93,6%. Produkty przy progach warto oceniać ręcznie, zamiast ślepo ufać literze.
Uszczelki to typowe CZ: 41 sztuk rocznie, sprzedaż w trzech miesiącach z dwunastu, 615 zł obrotu. To kandydat do sprzedaży na zamówienie albo do usunięcia z oferty, a nie do trzymania zapasu.
Analiza XYZ: jak policzyć zmienność popytu
W analizie XYZ dla każdego produktu liczysz współczynnik zmienności = odchylenie standardowe sprzedaży miesięcznej ÷ średnia sprzedaż miesięczna, a potem przypisujesz klasę według progów. W badaniu Politechniki Łódzkiej przyjęto: X do 0,5, Y od 0,51 do 0,9, Z powyżej 0,9. Ta sama publikacja przytacza też progi alternatywne: X do 0,4, Y do 0,99, Z od 1.
Dla kawy 1 kg z przykładu: średnia sprzedaż to 214,6 sztuki miesięcznie, odchylenie standardowe 15,5, więc współczynnik zmienności wynosi 0,07 (X). Dla zestawu prezentowego: średnia 21,5 sztuki, odchylenie 38,8, współczynnik 1,81 (Z), bo 200 z 258 sztuk sprzedało się w listopadzie i grudniu.
- X - popyt stabilny, prognoza z poprzednich miesięcy jest wiarygodna. Można zamawiać regularnie, w stałym rytmie.
- Y - wahania o znanych przyczynach: sezon, Black Friday, promocje. Prognozuj po miesiącach, nie po średniej rocznej.
- Z - sprzedaż sporadyczna. Prognoza jest mało warta, decyzja dotyczy raczej tego, czy w ogóle trzymać zapas.
Do analizy XYZ używaj danych miesięcznych, a nie tygodniowych: przy rzadko sprzedawanych produktach tygodnie z zerową sprzedażą sztucznie zawyżają zmienność. Wybierz też jeden wariant odchylenia standardowego (populacji albo próby) i trzymaj się go - w przykładzie użyto odchylenia populacji, przy odchyleniu z próby współczynnik ekspresu wyniósłby 0,89 zamiast 0,85.
Analiza ABC w Excelu: gotowe formuły
Analiza ABC w Excelu wymaga trzech kolumn z formułami: udziału, udziału skumulowanego i klasy. Załóżmy, że w kolumnie A są nazwy produktów, w B ilość roczna, w C cena netto, a dane zajmują wiersze 2-13 (polska wersja Excela, separator argumentów to średnik).
- Wartość (kolumna D): =B2*C2, przeciągnij w dół. Potem posortuj cały zakres malejąco po kolumnie D.
- Udział (kolumna E): =D2/SUMA($D$2:$D$13), format procentowy.
- Udział skumulowany (kolumna F): =SUMA($E$2:E2) - zakotwiczony początek, ruchomy koniec.
- Klasa ABC (kolumna G): =JEŻELI(F2<=80%;"A";JEŻELI(F2<=95%;"B";"C")).
- Sprzedaż miesięczna (kolumny H-S): 12 kolumn ze sprzedażą w sztukach w kolejnych miesiącach.
- Współczynnik zmienności (kolumna T): =ODCH.STAND.POPUL(H2:S2)/ŚREDNIA(H2:S2).
- Klasa XYZ (kolumna U): =JEŻELI(T2<=0,5;"X";JEŻELI(T2<=0,9;"Y";"Z")).
- Pole macierzy (kolumna V): =G2&U2 - daje wynik w rodzaju „AX” czy „CZ”, po którym łatwo filtrować.
Funkcja ODCH.STAND.POPUL to polski odpowiednik STDEV.P i liczy odchylenie dla całej populacji; dokumentacja Microsoft opisuje jej składnię i różnicę względem ODCH.STANDARD.PRÓBKI. Jeśli produkt w danym miesiącu nie był dostępny (brak towaru), wpisz pustą komórkę zamiast zera albo wyłącz ten miesiąc - inaczej braki magazynowe wyjdą w analizie jako „niestabilny popyt”.
Macierz ABC XYZ: co zrobić z każdą grupą produktów
Sam podział na litery nic nie zmienia, dopóki nie przypiszesz grupom reguł. Poniższa tabela przekłada 9 pól macierzy na decyzje w sklepie internetowym. Kierunki działań oparto na zaleceniach z badania Politechniki Łódzkiej (dokładne prognozy i dostawy na czas dla AX, zapasy bezpieczeństwa dla AY, AZ i BZ, upraszczanie i ograniczanie asortymentu dla CZ), dostosowanych do realiów e-commerce.
| Pole | Charakter | Zakupy i zapas | Kontrola i oferta |
|---|---|---|---|
| AX | wysoka wartość, stały popyt | częste, regularne dostawy w mniejszych partiach; dokładna prognoza | najczęstsza kontrola stanów, priorytet w negocjacjach z dostawcą, brak towaru niedopuszczalny |
| AY | wysoka wartość, sezonowość | plan zakupów miesiąc po miesiącu, zamówienia z wyprzedzeniem przed sezonem, zapas bezpieczeństwa w szczycie | przegląd planu przed każdym sezonem i większą promocją |
| AZ | wysoka wartość, popyt nieregularny | zapas bezpieczeństwa albo krótki czas dostawy od dostawcy; rozważ zamawianie pod konkretne zamówienia | analiza, skąd biorą się skoki (hurtowi klienci, jednorazowe akcje) |
| BX | średnia wartość, stały popyt | okresowe uzupełnianie w stałym rytmie (np. co 2 tygodnie) | automatyczne zamawianie po spadku do stanu minimalnego |
| BY | średnia wartość, wahania | okresowe uzupełnianie z korektą na sezon | przegląd raz w miesiącu |
| BZ | średnia wartość, popyt nieregularny | niewielki zapas bezpieczeństwa albo dostawa na żądanie | sprawdź, czy produkt nie powinien trafić do C |
| CX | niska wartość, stały popyt | rzadkie zakupy w większych partiach, prosty system „dwóch pojemników” | minimalna kontrola, inwentaryzacja najrzadziej |
| CY | niska wartość, wahania | większe partie przed sezonem, bez precyzyjnych prognoz | sprawdź, czy towar nie zalega po sezonie |
| CZ | niska wartość, popyt sporadyczny | brak zapasu albo minimalny; sprzedaż na zamówienie | kandydat do wycofania lub sprzedaży z dłuższym czasem realizacji |
Źródło: kierunki działań na podstawie: Politechnika Łódzka, Zeszyty Naukowe „Organizacja i Zarządzanie” z. 73, 2019, https://yadda.icm.edu.pl/baztech/element/bwmeta1.element.baztech-fb8ff721-50bf-44aa-9eb4-57e28fbab1bc/c/Kaczorowska_Z_Klasyfikacja_ABC_73.pdf; system dwóch pojemników dla grupy C za Encyklopedią Zarządzania (mfiles.pl).
Klasyfikacja ABC ma też zastosowanie fizyczne: produkty najczęściej pobierane stawia się najbliżej stanowiska pakowania, a rzadkie najdalej. Do tego celu kryterium powinna być liczba pobrań, a nie wartość sprzedaży - drogi produkt sprzedawany raz w tygodniu nie musi stać przy biurku pakowacza.
Jak wyglądał podział w realnej firmie
Badanie z 2019 roku objęło 538 towarów przedsiębiorstwa produkcyjnego (dane za 2016 rok, kryterium: wartość obrotu). Wynik dobrze pokazuje, że grupa A rzadko liczy dokładnie 20% pozycji, a większość asortymentu może mieć stabilny popyt.
| Grupa | Liczba pozycji | Udział w liczbie pozycji | Udział w wartości obrotu |
|---|---|---|---|
| A | 190 | 35,32% | 79,88% |
| B | 158 | 29,37% | 15,09% |
| C | 190 | 35,32% | 5,03% |
| X | 377 | 70,07% | - |
| Y | 70 | 13,01% | - |
| Z | 91 | 16,91% | - |
| AX | 132 | 24,54% | 59,68% |
| CZ | 37 | 6,88% | 1,02% |
Źródło: Z. Kaczorowska, I. Staniec, N. Szczygieł, Politechnika Łódzka, Zeszyty Naukowe „Organizacja i Zarządzanie” z. 73, 2019, https://yadda.icm.edu.pl/baztech/element/bwmeta1.element.baztech-fb8ff721-50bf-44aa-9eb4-57e28fbab1bc/c/Kaczorowska_Z_Klasyfikacja_ABC_73.pdf
Najważniejszy wniosek z tych danych: 132 pozycje AX, czyli co czwarty towar, dawały prawie 60% wartości obrotu. To właśnie na tej grupie opłaca się skupić prognozowanie i negocjacje z dostawcami.
Najczęstsze błędy w analizie ABC i XYZ
Najczęstsze błędy to złe dane wejściowe (jeden kanał, za krótki okres, braki towaru liczone jako brak popytu) i traktowanie wyniku jak wyroku zamiast punktu wyjścia do decyzji.
- Analiza tylko ze sklepu, bez marketplace'ów. Jeśli połowa sprzedaży idzie przez Allegro, wynik ze sklepu pokazuje połowę obrazu.
- Analiza na poziomie produktu zamiast wariantu. Koszulka może być w A, ale rozmiar XXL w kolorze limonkowym to CZ. Zapas zamawiasz na warianty, więc i klasyfikuj warianty.
- Kryterium ilości zamiast wartości. Filtry za 19 zł sprzedają się w tysiącach sztuk, ale to nie znaczy, że są ważniejsze od ekspresu za 349 zł. Ilość ma sens tylko przy planowaniu rozmieszczenia towaru.
- Zera z okresów braku towaru. Produkt, którego nie było przez 3 miesiące, wyjdzie jako Z, choć popyt na niego mógł być stabilny.
- Nowości oceniane razem z resztą. Produkt sprzedawany od 2 miesięcy prawie zawsze trafi do C. Oceniaj go osobno, po pełnym sezonie.
- Analiza zrobiona raz. Klasyfikacja się starzeje: zmieniają się ceny, trendy i sezon. Analiza sprzed roku może być bardziej szkodliwa niż jej brak.
- Usuwanie całej grupy C z automatu. Część produktów C to akcesoria kupowane razem z produktami A (filtry do ekspresu, uszczelki do kawiarki). Ich wycofanie może obniżyć sprzedaż produktów A.
Co zrobić, gdy wynik analizy nie ma sensu
| Objaw | Prawdopodobna przyczyna | Rozwiązanie |
|---|---|---|
| Ostatni udział skumulowany nie wynosi 100% | zakres w SUMA nie obejmuje wszystkich wierszy albo dane nie są posortowane | sprawdź zakotwiczenie $D$2:$D$13 i posortuj malejąco przed liczeniem |
| Prawie wszystko jest w grupie A | jeden produkt dominuje albo katalog ma kilka pozycji | przy kilkunastu SKU analizuj marżę i zmienność ręcznie; ABC ma sens od kilkudziesięciu pozycji |
| Bestseller wyszedł jako Z | braki towaru albo jednorazowa duża sprzedaż hurtowa | usuń miesiące bez towaru i nietypowe zamówienia, policz ponownie |
| Całe kategorie sezonowe są w Y lub Z | sezonowość jest naturalna dla tych produktów | nie traktuj Y jako problemu; planuj zakupy po miesiącach |
| Produkt z wysoką marżą jest w C | kryterium obrotu ignoruje marżę | zrób drugą analizę ABC po marży i porównaj wyniki |
Jak często powtarzać analizę ABC i XYZ
W większości sklepów wystarczy aktualizacja co kwartał na danych z ostatnich 12 miesięcy, a dodatkowo przed sezonem (np. we wrześniu przed Black Friday i świętami). Przy szybko zmieniającym się asortymencie, np. w modzie, lepiej liczyć co miesiąc. Ważne, żeby za każdym razem używać tych samych progów i tego samego kryterium - inaczej przesunięcia produktów między grupami nie będą nic znaczyć.
Porównuj też wyniki z poprzednią analizą. Produkt, który spadł z A do B, to sygnał do sprawdzenia ceny, opinii albo konkurencji. Produkt, który awansował z C do A, wymaga zmiany sposobu zamawiania, zanim zabraknie go w magazynie.
Warianty analizy: po marży, po pobraniach, po klientach
- ABC po marży - lepsza od ABC po obrocie, gdy marże w asortymencie różnią się kilkukrotnie. W przykładzie zmieniła klasę termosu z C na B.
- ABC po liczbie pobrań - do układu magazynu i kompletacji: co najczęściej pobierane, to najbliżej strefy wydań.
- ABC klientów - ta sama metoda zastosowana do odbiorców zamiast produktów, przydatna przy sprzedaży B2B.
- Analiza ABC bez XYZ - wystarczy w sklepie bez sezonowości i z kilkudziesięcioma SKU. Gdy sprzedaż mocno skacze między miesiącami, sama analiza ABC prowadzi do złych zamówień.
Jakość analizy zależy od tego, czy dane sprzedażowe ze wszystkich kanałów są w jednym miejscu. W ShopDepot zamówienia ze wszystkich sklepów i kont marketplace trafiają na jedną listę z filtrami kanału i statystykami dla przefiltrowanego zbioru, a magazyn prowadzi stany osobno dla każdego wariantu i od razu przekazuje zmiany do podłączonych marketplace'ów. Dla produktów z pola CZ możesz tam włączyć zamówienia oczekujące (backorder): klient kupuje towar chwilowo niedostępny i widzi szacowany czas realizacji, a Ty nie musisz trzymać jego zapasu.