Jak korzystać ze sprawdzonych metod i dobrych praktyk dla sporządzania raportu ABC Pareto (zasada 20/80). Segmentacja ABC zbioru SKU jako przykład praktyczny

Zanim zaczniesz czytać artykuł:

1. Zerknij na pierwszą część artykułu: Jak przygotować dane do poprawnego wykonania raportów: ABC Pareto, XYZ i ABC/M – cz. 1

2. Pobierz pliki:

a) Wskazówki korzystania z pliku Raporty_Odniesienie

b) Plik Raporty_Odniesienie

Na pewno pamiętasz także, że segmentacja ABC jest wykonywana w danym momencie czasowym np. dla stanu magazynowego w danym dniu lub dla sprzedaży po Klientach w okresie 9 miesięcy. Jeśli segmentacja ABC jest stale wykorzystywana w operacyjnej aktywności firmy, trzeba ją korygować po każdym okresie rozliczeniowym lub w innych ramach czasowych mających racjonalne uzasadnienie.

Nowe rozłożenie segmentów najlepiej wykonywać uruchamiając odpowiedni algorytm (moduł programowy) w systemie informatycznym (jeśli go nie ma, namów informatyków, by go stworzyli) lub uruchamiając własne makro napisane np. w Excelu. Niektóre korekty w segmentach wymagają „fizycznych interwencji” w obiektach firmy – np. korekty lokalizacji niektórych indeksów (SKU) w magazynie. Na szczęście dynamika zmian w segmentach jest przeważnie niska, więc wykonanie takich interwencji nie angażuje dużych zasobów.

Raport ABC Pareto wykonujemy na bazie danych źródłowych znajdujących się na zakładce 1_Oryg_Sprzed w pliku Raporty_Odniesienie. Naszym zadaniem jest wykonanie segmentacji na zbiorze SKU, który powinien zawierać unikalne elementy (bez duplikatów SKU). Jak łatwo zauważysz dane źródłowe zawierają duplikaty SKU (pole/kolumna Art._id), gdyż bazą dla generacji tych danych jest data sprzedaży (pole – Data).

Naszym pierwszym zadaniem jest więc przekształcenie danych źródłowych do postaci, w której podstawą będzie zbiór SKU bez duplikatów. Takie dane już po przekształceniach znajdują się na zakładce 3_ABCPar_XYZ (do przekształcenia danych posłużono się tabelą przestawną). Na tej samej zakładce znajdują się dane sprzedaży za okres 9 miesięcy (od kwietnia do grudnia). Dla wykonania raportu ABC dane te można zwinąć klikając przycisk „Zwiń/Rozwiń”.

Mając tak przygotowane dane możemy podział na segmenty dokonać w następujących krokach:

1. Sumujemy sprzedaż w okresie 9 miesięcy na poziomie każdego SKU. Wyniki sumowań są pokazane w polu ∑-Przych.

2. Sortujemy sumy w polu ∑-Przych malejąco

3. Sumujemy ∑-Przych do postaci skumulowanej. Wyniki kumulacji pokazana są w polu PrzychSkum. Zastosowano tu użyteczny trik, czyli funkcję „=SUMA($K$3:K3)”, która powielona w kolumnie PrzychSkum umożliwia kumulację przychodów (danych) w jednym kroku.

4. Liczymy procentowy udział skumulowanych wartości w sumarycznych przychodach, które znajdziesz klikając link „Idź do wartości sumarycznych”. Wyniki procentowych udziałów pokazane są w polu Skum%. Zauważ, że % udziały liczymy w dwóch krokach.

5. Liczymy procentowy udział skumulowanej ilości SKU w sumarycznej ilości SKU „skonsumowanej” w okresie 9 miesięcy. Korzystamy tu z triku stosując wyrażenie „=ILE.LICZB(A3:$A$3)/$A$18992” powielone w kolumnie %_ilSKU. Funkcja „=ILE.LICZB(A3:$A$3)” liczy skumulowane ilości na każdym poziomie SKU, które podzielone przez sumaryczną ilość SKU („zamrożona” komórka $A$18992) daje w wyniku procentowy udział opisany jw. Sumaryczną ilość SKU znajdziesz klikając link Idź do wartości sumarycznych. Wyniki udziału ilościowego są pokazane w polu %_ilSKU. Zwróć uwagę, że udział procentowy SKU liczymy w jednym kroku – tę samą metodę możemy zastosować do liczenia udziałów przychodowych.

6. Wyznaczamy segmenty ABC – przy pomocy zagnieżdżonych funkcji arkuszowych: „=JEŻELI(R3<=0,8;”A”;JEŻELI(R3<=95%;”B”;”C”))” – jak widać segment A określa ilość indeksów, które w okresie 9 miesięcy wygenerowały 80% przychodów, segment B – ilość indeksów, które wygenerowały kolejne 15% przychodów, a segment C – ilość indeksów, które wygenerowały pozostałe 5% przychodów. Chcąc zejść na poziom granicy między segmentami klikaj Linki „Do Seg. B”, „Do Seg. C” i „Koniec” na poszczególnych granicach – granice pokazują na jakim poziomie relacji między udziałem ilościowym a wartościowym następuje zmiana segmentu np. zmiana z B na C następuje wtedy, gdy udział wartościowy nie przekracza 95%, co następuje przy ponad 23% udziale ilościowym. Segmenty można także wyznaczyć przy pomocy makra – klikając przycisk „Makro ABC XYZ” możesz to makro uruchomić.

7. Wyniki analizy Pareto możesz zobaczyć w tabeli przestawnej „Raport ABC Pareto – dane zagregowane” oraz na wykresie „Wykres ABC – Pareto”. Dane szczegółowe możesz wyświetlać klikając dwa razy tabelę w wybranej pozycji obszaru danych – np. klikając 80% w tabeli przestawnej Excel wygeneruje dodatkowy arkusz, na którym wyświetli się 915 SKU ze szczegółowymi danymi przypisanymi do segmentu A. Zwróć uwagę, że tylko 4,8% ilości SKU generuje 80% przychodów, co odbiega od statystycznej definicji zasady Pareto 20/80 – jaka jest tego przyczyna, spróbujemy wyjaśnić w artykule o klasyfikacji XYZ.

Na zakończenie warto jeszcze raz podkreślić, że niezależnie od tego jaka jest postawa analizy ABC Parto (SKU, Klienci, dostawcy producenci, regiony, itp.) powinieneś się upewnić, że dane poddawane „obróbce” są unikatowe.

Fot: pixabay.com