KHIZHNYAKOVAnaliza ABC XYZ w Excelu: jak podzielić asortyment i co z tym zrobić
Analiza ABC-XYZ asortymentu – szablon Excel — Zobacz szablon →
W prawie każdym sklepie, hurtowni czy kawiarni jest ten sam problem: część towaru schodzi na pniu i ciągle go brakuje, a część leży na półce miesiącami i zamraża gotówkę. Intuicja podpowiada, co się sprzedaje, ale przy setkach pozycji zawodzi.
Analiza ABC XYZ porządkuje asortyment według dwóch kryteriów: ile produkt przynosi (ABC) i jak stabilny jest jego popyt (XYZ). Wystarczy sprzedaż z ostatnich 12 miesięcy i kilka formuł w Excelu. Poniżej pełny przykład.
Dane wejściowe
Potrzebujesz tabeli: produkt w wierszach, 12 miesięcy w kolumnach, wartość sprzedaży w złotówkach (lub w sztukach – ale wtedy dla wszystkich produktów). Dla analizy ABC liczy się suma roczna, dla XYZ – rozkład po miesiącach.
Analiza ABC
Zasada Pareto: niewielka część produktów daje większość przychodu. Typowe progi: grupa A to produkty dające łącznie pierwsze 80% przychodu, B – kolejne 15% (do 95%), C – ostatnie 5%.
Krok 1. Suma roczna: =SUMA(B2:M2).
Krok 2. Udział: =N2/SUMA($N$2:$N$301).
Krok 3. Posortuj produkty malejąco według przychodu (Dane → Sortuj).
Krok 4. Udział skumulowany: w pierwszym wierszu =O2, w kolejnych =P2+O3.
Krok 5. Grupa: =JEŻELI(P2<=80%;"A";JEŻELI(P2<=95%;"B";"C")).
Przykład małej palarni kawy:
| Produkt | Przychód | Udział | Skumulowany | ABC |
|---|---|---|---|---|
| Kawa ziarnista | 110 000 zł | 55% | 55% | A |
| Kawa mielona | 50 000 zł | 25% | 80% | A |
| Herbaty | 20 000 zł | 10% | 90% | B |
| Syropy | 8 000 zł | 4% | 94% | B |
| Kubki | 8 000 zł | 4% | 98% | C |
| Zestawy prezentowe | 4 000 zł | 2% | 100% | C |
Dwa produkty z sześciu dają 80% przychodu. To na nich trzeba się skupić w pierwszej kolejności.
Analiza XYZ
Tu liczy się przewidywalność. Miarą jest współczynnik zmienności: odchylenie standardowe sprzedaży miesięcznej podzielone przez średnią.
=ODCH.STAND.POPUL(B2:M2)/ŚREDNIA(B2:M2)
W Excelu 2007 ta funkcja nazywa się ODCH.STANDARD.POPUL. Progi: X – do 10% (popyt stabilny), Y – 10–25% (umiarkowane wahania), Z – powyżej 25% (popyt nieregularny lub sezonowy).
=JEŻELI(Q2<=10%;"X";JEŻELI(Q2<=25%;"Y";"Z"))
W naszym przykładzie kawa ziarnista sprzedaje się równo (X), herbaty rosną zimą (Y), a zestawy prezentowe sprzedają się głównie przed świętami (Z).
Macierz ABC XYZ
Połączenie obu ocen daje 9 grup. Najważniejsze wnioski:
| Grupa | Co robić |
|---|---|
| AX, AY | Nigdy nie dopuszczać do braków, stały zapas, regularne zamówienia |
| AZ | Ważne, ale nieprzewidywalne – zamawiać pod prognozę, obserwować |
| BX, BY | Standardowe zarządzanie, automatyczne zamówienia |
| CX | Tani w obsłudze, można zostawić, ale z minimalnym zapasem |
| CZ | Kandydaci do wycofania lub sprzedaży tylko na zamówienie |
Połączoną grupę zrobisz prostym złączeniem: =R2&S2. Liczbę produktów w każdej grupie policzysz funkcją LICZ.JEŻELI, a przychód – SUMA.JEŻELI.
Częste błędy
- Za krótki okres. Trzy miesiące nie pokażą sezonowości. Bierz pełne 12 miesięcy.
- Mieszanie jednostek. Sztuki dla jednych produktów, złotówki dla innych – wynik bez sensu.
- Nowości w analizie. Produkt wprowadzony dwa miesiące temu zawsze wyjdzie jako C i Z. Oceniaj go osobno.
- Ślepe wycofywanie grupy C. Czasem tani produkt z grupy C przyciąga klientów do zakupu produktów A. Wyniki analizy to punkt wyjścia do decyzji, a nie sama decyzja.
Gotowy szablon: analiza ABC-XYZ asortymentu
Nasz płatny szablon robi całą analizę automatycznie, bez ręcznego sortowania:
- „Parametry”: progi grup A, B oraz X, Y – domyślnie 80% i 95%, 10% i 25%, możesz je zmienić.
- „Dane”: do 300 produktów i sprzedaż za 12 miesięcy; po prawej od razu suma, udział, udział skumulowany, grupy ABC i XYZ.
- „Ranking”: produkty od najwyższego przychodu wraz z grupami.
- „Macierz”: ile produktów i przychodu jest w każdej z dziewięciu grup, krótka rekomendacja, co z nimi zrobić, i wykres.
Plik działa w Excelu 2007 i nowszych oraz w LibreOffice, bez makr – wszystkie obliczenia wykonują zwykłe formuły. W zestawie pusty arkusz i przykład z fikcyjnymi danymi. Obliczenia mają charakter orientacyjny.
Pytania i odpowiedzi
Analizę ABC robić według przychodu czy zysku?
Przychód jest najłatwiej dostępny, ale zysk (marża w złotówkach) daje często trafniejszy obraz. Produkt z dużym obrotem i minimalną marżą może okazać się mniej ważny, niż sugeruje grupa A.
Jak często powtarzać analizę?
Raz na kwartał lub raz na pół roku, zawsze na danych z ostatnich 12 miesięcy. Przesunięcia produktów między grupami są równie ciekawe jak same grupy.
Czy analiza nadaje się dla sklepu internetowego?
Tak, metoda jest ta sama. W e-commerce warto dodatkowo spojrzeć na grupę C – część z tych produktów może generować ruch z wyszukiwarki.
Co, jeśli produkt w niektórych miesiącach miał zero sprzedaży z powodu braku towaru?
Wtedy współczynnik zmienności będzie sztucznie zawyżony. Takie miesiące lepiej oznaczyć i interpretować wynik XYZ ostrożnie.
