Jak przeprowadzić analizę ABC w Excelu: 5 prostych kroków

Analiza ABC dzieli pozycje asortymentowe na trzy klasy na podstawie wartości ich rocznego zużycia. Pozycje klasy A to nieliczne artykuły, które generują większość kosztów. Pozycje klasy C to liczne artykuły o niskiej wartości, a klasa B znajduje się pośrodku. W programie Excel można to zrobić za pomocą jednej tabeli: wartość roczna, udział w sumie, suma skumulowana oraz formuła przypisująca każdą klasę.

Ten poradnik wyjaśnia, co oznaczają poszczególne klasy, opisuje pięć kroków w Excel, przedstawia praktyczny przykład oraz pokazuje, jak przedstawić wynik na wykresie. Omawiamy również, jak dobrać progi odcięcia i co zrobić z każdą klasą po zakończeniu analizy.

Czym jest analiza ABC

Analiza ABC to sposób na określenie, które pozycje zasługują na największą uwagę. Opiera się na prostej zależności: niewielki odsetek artykułów generuje znaczną część wydatków.

Rozdział z 2012 roku dotyczący analizy i kontroli wydatków na produkty farmaceutyczne, opracowany przez Management Sciences for Health (MSH), opisuje to w prosty sposób. Zauważono w nim, że „stosunkowo niewielka liczba pozycji odpowiada za większość wartości rocznego zużycia”. Dodano również: „Analiza tego zjawiska jest znana jako analiza Pareto lub, powszechniej, analiza ABC”.

Ten sam rozdział wyjaśnia, że pozycje „można sklasyfikować do trzech kategorii (A, B i C) na podstawie wartości ich rocznego zużycia”. Metoda jest taka sama bez względu na to, czy magazynujesz leki, części zamienne, czy produkty detaliczne.

Łatwo przeoczyć jedną kwestię. Klasy nie są stałymi etykietami. MSH zauważa, że „jeśli wzorce zużycia ulegną zmianie, przy kolejnym przeprowadzeniu analizy ABC dana pozycja może trafić do innej kategorii”. Dlatego analiza ABC sprawdza się najlepiej jako rutynowa kontrola, a nie jednorazowy projekt.

Co oznaczają klasy A, B i C

Rozdział MSH podaje typowe przedziały dla każdej klasy:

Klasa	Udział pozycji	Udział w wartości rocznej	Co to zazwyczaj oznacza
A	10 do 20 procent	75 to 80 procent	Niewiele pozycji, większość pieniędzy
B	10 do 20 procent	15 to 20 procent	Grupa średnia
C	60 to 80 percent	5 to 10 procent	Wiele pozycji, niewiele pieniędzy

Są to typowe przedziały, a nie sztywne reguły. MSH wskazuje, że „granice te są do pewnego stopnia elastyczne”. W ich przykładzie klasę A wyznaczają pozycje, które sumują się do 70 procent funduszy.

Wartością decydującą o przynależności do klas jest roczna wartość zużycia: liczba jednostek zużytych w ciągu roku pomnożona przez koszt jednostkowy. Tani artykuł używany w ogromnych ilościach może trafić do klasy A. Drogi artykuł używany raz w roku może wylądować w klasie C.

Artykuł z 2014 roku w American Journal of Business Education kwestionuje opieranie się wyłącznie na wartości. Autorzy argumentują, że podręczniki „koncentrują się na wartości finansowej jako jedynym kryterium” i zalecają dodanie innych kryteriów. Przy pierwszym podejściu wartość jest jednak metodą stosowaną w rozdziale MSH.

Czego potrzebujesz przed rozpoczęciem

Analiza ABC w programie Excel wymaga tylko kilku kolumn dla każdej pozycji:

• Nazwa pozycji lub SKU. Jeden wiersz na pozycję.
• Roczna liczba zużytych lub zakupionych jednostek. Użyj tego samego 12-miesięcznego okresu dla każdej pozycji.
• Koszt jednostkowy. Koszt jednej jednostki, w tej samej jednostce miary, w której prowadzisz obliczenia.

MSH kładzie nacisk na spójność okresu: „Upewnij się, że dla wszystkich pozycji stosowany jest ten sam okres analizy, aby uniknąć błędnych porównań”. Zaleca również stosowanie tej samej podstawowej jednostki dla kosztu i ilości, np. tabletki lub pojedynczego pudełka, zamiast mieszania różnych rozmiarów opakowań.

Jeśli Twoje dane pochodzą z systemu magazynowego lub zakupowego, wyeksportuj je jako plik CSV lub Excel. Usuń pozycje, które nie wykazały żadnej aktywności w danym okresie, lub pozostaw je, spodziewając się, że trafią do klasy C.

Jak przeprowadzić analizę ABC w programie Excel

Poniższe pięć kroków opiera się na metodzie z rozdziału MSH, dostosowanej do formuł programu Excel. W przykładzie tytuł umieszczono w wierszu 1, nagłówki w wierszu 2, a 10 pozycji w wierszach od 3 do 12. Kolumny A, B i C zawierają odpowiednio nazwę pozycji, roczną liczbę jednostek oraz koszt jednostkowy.

Krok 1: Wypisz pozycje, jednostki i koszt jednostkowy

Wprowadź lub wklej pozycje (jeden wiersz na pozycję) wraz z ich nazwą, roczną liczbą jednostek i kosztem jednostkowym. Dodaj nagłówki w wierszu 2, aby ułatwić późniejsze sortowanie tabeli.

Sprawdź dane przed przejściem dalej. Poszukaj brakujących kosztów, ujemnych ilości i duplikatów SKU, ponieważ każdy taki błąd zniekształci sumy końcowe. Szybkie filtrowanie każdej kolumny zazwyczaj pozwala je wykryć.

Jeśli dokonano kilku zakupów tej samej pozycji po różnych cenach, użyj jednego spójnego kosztu. MSH zauważa, że „średnia ważona lub średnia FIFO” to najdokładniejsze alternatywy, gdy rzeczywisty koszt jednostkowy jest trudny do śledzenia.

Krok 2: Oblicz roczną wartość i jej udział w sumie

W kolumnie D pomnóż jednostki przez koszt, aby uzyskać roczną wartość każdej pozycji. W komórce D3 wpisz =B3*C3 i przeciągnij formułę w dół.

W kolumnie E podziel każdą wartość przez sumę wszystkich wartości, aby uzyskać jej udział. W komórce E3 wpisz =D3/SUM($D$3:$D$12) i przeciągnij w dół. Znaki dolara blokują zakres sumowania podczas kopiowania formuły. Sformatuj kolumnę E jako wartość procentową z dwoma miejscami po przecinku.

MSH zaleca taką precyzję nie bez powodu. Jak czytamy: „wartości kilku pozycji mogą być do siebie zbliżone, a wiele z nich może stanowić mniej niż 1 procent całkowitej wartości”.

Krok 3: Posortuj pozycje według wartości, od największej do najmniejszej

Zaznacz całą tabelę wraz z nagłówkami i posortuj według kolumny D od największej do najmniejszej. W programie Excel wybierz Dane, a następnie Sortuj, wskazując kolumnę D i porządek od największych do najmniejszych.

Jeśli wolisz formułę, funkcja SORTUJ zwraca posortowaną kopię. Składnia firmy Microsoft to =SORTUJ(tablica;[indeks_sortowania];[porządek_sortowania];[według_kolumny]), gdzie porządek sortowania równy -1 oznacza sortowanie malejąco. Dla tej tabeli formuła =SORTUJ(A3:E12;4;-1) sortuje według czwartej kolumny, od najwyższej wartości.

Po wykonaniu tego kroku pozycja o najwyższej rocznej wartości znajdzie się na samej górze. Taka kolejność sprawia, że suma skumulowana w następnym kroku ma sens.

Krok 4: Dodaj skumulowany udział procentowy

W kolumnie F dodaj sumę skumulowaną udziałów. W komórce F3 wpisz =SUMA($E$3:E3) i przeciągnij w dół. Pierwsza część zakresu pozostaje zablokowana, a druga powiększa się o jeden wiersz przy każdym skopiowaniu.

Ostatni wiersz powinien wskazywać 100 procent. Jeśli tak nie jest, sprawdź, czy w kolumnach D i E nie ma pustych komórek lub wartości tekstowych.

Ta kolumna to serce analizy ABC. Pokazuje, jaki odsetek całkowitej wartości generują łącznie pozycje znajdujące się powyżej danego wiersza.

Krok 5: Przypisz klasy A, B i C

W kolumnie G użyj formuły, aby oznaczyć każdą pozycję. Przy progach odcięcia wynoszących 80 i 95 procent, wpisz w komórce G3 następującą formułę i przeciągnij ją w dół:

=WARUNKI(F3<=0,8;"A";F3<=0,95;"B";PRAWDA;"C")

Funkcja WARUNKI sprawdza każdy warunek po kolei i zwraca pierwsze dopasowanie. Przykład firmy Microsoft wykorzystuje ten sam schemat, z wartością PRAWDA jako końcowym warunkiem domyślnym. Pozycje o skumulowanej wartości do 80 procent otrzymują klasę A, do 95 procent – klasę B, a pozostałe – klasę C.

Na koniec policz pozycje w każdej klasie za pomocą formuły =LICZ.JEŻELI(G3:G12;"A") (i analogicznie dla klas B i C). Porównaj te liczby z typowymi przedziałami podanymi wyżej. Dostosuj progi odcięcia, jeśli klasa A jest zbyt liczna lub zbyt mała, by Twój zespół mógł nią efektywnie zarządzać.

Praktyczny przykład

Oto przykładowa tabela dla 10 pozycji, posortowana już według rocznej wartości. Liczby są jedynie przykładem, a nie danymi z rzeczywistej firmy.

Pozycja	Roczna liczba jednostek	Koszt jednostkowy	Wartość roczna	Udział	Suma skumulowana	Klasa
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

Całkowita roczna wartość wynosi $150,000. Trzy pozycje, stanowiące 30 procent listy, odpowiadają za 73.33 procent wartości i trafiają do klasy A. Cztery pozycje kwalifikują się do klasy B, a ostatnie trzy, o wartości 6 procent sumy, należą do klasy C.

Dwa szczegóły zwracają uwagę. SKU-04 ma zdecydowanie największą liczbę jednostek, ale jego niski koszt plasuje go w klasie B. Ponadto przy zaledwie 10 pozycjach udziały poszczególnych klas nie będą dokładnie odpowiadać typowym przedziałom, co jest normalne dla krótkiej listy.

Jak przedstawić wynik na wykresie

Wykres ułatwia zaprezentowanie wyników na spotkaniu. MSH sugeruje wykreślenie skumulowanego udziału procentowego w odniesieniu do liczby pozycji, co pozwala uzyskać charakterystyczną krzywą ABC.

Program Excel posiada wbudowany wykres do tego celu. Microsoft opisuje wykres Pareto jako taki, który „zawiera zarówno kolumny posortowane w porządku malejącym, jak i linię reprezentującą skumulowany procent sumy”. Aby go utworzyć, zaznacz nazwy pozycji oraz wartości roczne, a następnie wybierz Wstawianie, Wstaw wykres statystyczny i Pareto.

Dodaj dwie poziome linie lub etykiety na poziomach progów odcięcia, np. 80 i 95 procent, aby odbiorcy mogli łatwo zobaczyć, gdzie zaczyna się każda klasa. Nasz poradnik o tym, jak utworzyć wykres Pareto za pomocą AI, omawia sam wykres bardziej szczegółowo.

Wybór progów odcięcia

Nie ma jednego właściwego progu odcięcia. MSH wyjaśnia, że wybór „zależy od tego, jak wolumen i wartość są rozproszone wśród pozycji na liście”. Zależy to również od tego, „w jaki sposób wyniki analizy ABC będą wykorzystywane”.

Praktycznym ograniczeniem są możliwości zarządcze. MSH ujmuje to wprost: „przypisanie pozycji do klasy A musi opierać się na możliwościach zarządczych”. Jeśli Twój zespół jest w stanie dokładnie przeanalizować 50 pozycji miesięcznie, klasa A licząca 300 pozycji mija się z celem.

Kilka popularnych podejść:

• Progi wartościowe. Klasa A do 80 procent wartości, klasa B do 95 procent, klasa C dla reszty. Jest to metoda zastosowana powyżej.
• Progi ilościowe. Najlepsze 20 procent pozycji pod względem wartości staje się klasą A, kolejne 30 procent klasą B, a reszta klasą C.
• Stałe listy. Niektóre zespoły definiują klasę A jako 25 lub 50 najważniejszych pozycji, niezależnie od ich udziału w wartości.

Niezależnie od wybranego podejścia, zapisz je i stosuj za każdym razem. Porównywanie klas z tego kwartału z poprzednim ma sens tylko wtedy, gdy progi odcięcia pozostają niezmienne.

Co zrobić z każdą klasą

Celem analizy ABC jest skupienie wysiłków tam, gdzie kryją się największe pieniądze. Rozdział MSH wymienia kilka sposobów wykorzystania wyników:

• Zamawiaj pozycje klasy A częściej. MSH wskazuje, że zamawianie pozycji klasy A „częściej i w mniejszych ilościach powinno prowadzić do obniżenia kosztów utrzymania zapasów”.
• Negocjuj ceny pozycji klasy A w pierwszej kolejności. „Obniżki cen artykułów sklasyfikowanych w analizie jako produkty klasy A mogą przynieść znaczne oszczędności” – czytamy w rozdziale.
• Inwentaryzuj zapasy klasy A częściej. MSH zauważa, że „cykliczne spisy zapasów powinny opierać się na analizie ABC, z częstszą inwentaryzacją pozycji klasy A”.
• Monitoruj status zamówień klasy A. Nieoczekiwany brak pozycji klasy A może prowadzić do kosztownych zakupów awaryjnych.

Dla pozycji klasy C można przyjąć prostsze zasady, takie jak większe, rzadsze zamówienia i rzadsza inwentaryzacja. Klasa B znajduje się pośrodku. Jeśli problemem są wolno rotujące zapasy, nasz poradnik o tym, jak identyfikować wolno rotujące zapasy, stanowi świetne uzupełnienie tej analizy.

Szybsza analiza dzięki AI

Kroki w programie Excel zajmują kilka minut, gdy dane są już uporządkowane. Jednak czyszczenie wyeksportowanych danych i powtarzanie tej pracy co kwartał zajmuje znacznie więcej czasu.

Środowisko pracy AI może wykonać obliczenia i sortowanie w ramach jednego zapytania. Prześlij wyeksportowane dane magazynowe lub zakupowe do Powerdrill Bloom i poproś w języku naturalnym o przeprowadzenie analizy ABC z uwzględnieniem Twoich progów odcięcia. Poproś o wyliczenie rocznej wartości, udziału, skumulowanego procentu i klasy dla każdej pozycji, a także o wygenerowanie wykresu Pareto.

Następnie sprawdź wyniki tak, jak w każdym arkuszu kalkulacyjnym. Porównaj całkowitą roczną wartość z własnymi obliczeniami i wyrywkowo skontroluj po dwie pozycje z każdej klasy. Nasza strona poświęcona asystentowi AI dla programu Excel opisuje tego rodzaju pracę z arkuszami bardziej szczegółowo. Aby uzyskać szerszy wgląd w narzędzia do prognozowania, zapoznaj się z zestawieniem narzędzi AI do prognozowania zapasów i popytu.

Typowe błędy, których należy unikać

• Mieszanie okresów. Przyjęcie dwunastu miesięcy dla jednej pozycji i sześciu dla innej sprawia, że udziały tracą sens.
• Używanie liczby jednostek zamiast wartości. Klasy zależą od liczby jednostek pomnożonej przez koszt, a nie od samej liczby jednostek.
• Zapominanie o sortowaniu przed obliczeniem sumy skumulowanej. Skumulowany udział procentowy obliczony na nieposortowanej liście przypisze pozycje do niewłaściwych klas.
• Traktowanie klas jako stałych. Powtarzaj analizę co kwartał lub co rok, ponieważ pozycje mogą przechodzić między klasami.
• Progi odcięcia ignorujące możliwości zespołu. Lista klasy A, która jest zbyt długa, by nią dokładnie zarządzać, nie otrzyma większej uwagi niż klasa B.
• Ignorowanie kluczowych tanich pozycji. Artykuł o niskiej wartości może nadal wstrzymać pracę, jeśli go zabraknie. Rozdział MSH łączy analizę ABC z osobną oceną pozycji na kluczowe, niezbędne i nieistotne.

Jeśli Twoja lista pozycji pochodzi z nieuporządkowanego pliku eksportu, możesz wypróbować Powerdrill Bloom, aby stworzyć pierwszą tabelę i wykres ABC.

Najczęściej zadawane pytania

Czym jest analiza ABC w zarządzaniu zapasami?

Analiza ABC dzieli pozycje na trzy klasy na podstawie rocznej wartości zużycia. Pozycje klasy A to nieliczne artykuły, które generują większość wartości. Pozycje klasy C to liczne artykuły o niskiej wartości, a klasa B znajduje się pośrodku. Pomaga to zespołom skupić wysiłki kontrolne tam, gdzie koszty są największe.

Jak obliczyć analizę ABC w programie Excel?

Pomnóż roczną liczbę jednostek przez koszt jednostkowy dla każdej pozycji, a następnie podziel przez sumę, aby uzyskać udział każdej z nich. Posortuj według wartości od największej do najmniejszej, dodaj sumę skumulowaną udziałów i przypisz klasy za pomocą formuły, np. WARUNKI. Progi odcięcia na poziomie 80 i 95 procent mieszczą się w typowych przedziałach podanych w rozdziale MSH.

Jakie są wartości procentowe w analizie ABC?

Powszechną wytyczną jest to, że klasa A obejmuje od 10 do 20 procent pozycji i od 75 do 80 procent wartości. Klasa B obejmuje kolejne 10 do 20 procent pozycji i od 15 do 20 procent wartości. Klasa C obejmuje od 60 do 80 procent pozycji i od 5 do 10 procent wartości.

Jaka jest formuła do klasyfikacji ABC w programie Excel?

Przy skumulowanym udziale procentowym w kolumnie F i danych rozpoczynających się od wiersza 3, użyj formuły =WARUNKI(F3<=0,8;"A";F3<=0,95;"B";PRAWDA;"C"). Zmień wartości 0,8 i 0,95, aby dopasować je do własnych progów odcięcia. Zagnieżdżone formuły JEŻELI mogą wykonać to samo zadanie.

Dlaczego analiza ABC jest ważna?

Pokazuje, na co przeznaczana jest większość budżetu magazynowego, dzięki czemu zespoły mogą dokładniej zarządzać tymi pozycjami. Typowe zastosowania obejmują częstsze zamawianie pozycji klasy A, negocjowanie ich cen w pierwszej kolejności oraz częstszą inwentaryzację. Pozwala również wykryć wydatki niezgodne z planami.

Źródła: Management Sciences for Health, MDS-3 Chapter 40: Analyzing and controlling pharmaceutical expenditures · Ravinder and Misra, ABC Analysis for Inventory Management (2014) · Microsoft Support, funkcja SORTUJ · Microsoft Support, funkcja WARUNKI · Microsoft Support, Tworzenie wykresu Pareto.