Zastosowanie arkusza kalkulacyjnego Excel do analizy ABC/XYZ
Działalność logistyczna przedsiębiorstwa wymaga gromadzenia i porządkowania informacji o przepływie towarów i usług. Gdy zbiorowości towarów są bardzo liczne, niezbędne jest wprowadzenie klasyfikacji poznawczych. W artykule przedstawiono wykorzystanie arkusza kalkulacyjnego Excel do analizy danych za pomocą dwóch podstawowych metod: statycznej ABC oraz dynamicznej XYZ, które syntetycznie grupują duże, niejednorodne zbiorowości w klasy.
Istotą metody ABC jest podział asortymentu na trzy grupy:
- grupa A – zapasy „cenne”, stanowiące 5-20% liczebności asortymentowej zapasów, ale mające znaczny udział w ich wartości, sięgający 75-80%; tej grupie pozycji asortymentowych o wysokiej wartości i (lub) dużym udziale w kosztach materiałowych ogółem należy poświęcić szczególną uwagę;
- grupa B – zapasy mające udział 15-20% zarówno w liczebności asortymentowej zapasów, jak i ich wartości;
- grupa C – zapasy o charakterze masowym, mające największy udział w liczebności asortymentowej, tj. 60-80%, i bardzo niski udział w liczebności w wartości – około 5%.
Analiza zapasów z wykorzystaniem tej metody pozwala na zmniejszenie wymiarowości zagadnienia, umożliwiając tym samym koncentrację uwagi na pozycjach newralgicznych, determinujących ekonomiczne wyniki działalności logistycznej.
W metodzie XYZ towar zostaje podzielony według regularności zapotrzebowania. Wyróżnia się trzy grupy:
- grupa X – asortyment, który charakteryzuje się regularnym zapotrzebowaniem (możliwe występowanie niewielkich wahań), jak również wysoką dokładnością prognozowania poziomu zużycia;
- grupa Y – towar, którego wielkość zużycia wykazuje wahania okresowe lub określony trend, a dokładność prognozowania zużycia jest średnia;
- grupa Z – produkty o bardzo nieregularnym zapotrzebowaniu i niskim poziomie dokładności prognozowania zużycia.
Połączona metoda ABC/XYZ daje podstawy zróżnicowanego podejścia do zarządzania zapasami poszczególnych towarów, od najbardziej znaczących wartościowo i zużywanych w dużych ilościach (grupa AX) do mało znaczących wartościowo i zużywanych sporadycznie (grupa CZ). Dla towarów z grupy AX, które charakteryzują się regularnym zapotrzebowaniem oraz niewielkimi wahaniami, można wyznaczyć z dużą dokładnością (np. stosując metodę trendu liniowego) prognozę zużycia na kolejny okres).
Połączenie analizy statycznej ABC oraz dynamicznej XYZ daje również możliwość racjonalnego podejścia do gospodarki magazynowej. Istotą połączonych metod ABC i XYZ jest minimalizacja łącznych czynności magazynowych w układzie wertykalnym i horyzontalnym (towar gromadzony i przechowywany na stojakach lub regałach). Wyodrębniony połączoną metodą towar należący do grupy AX, który charakteryzuje się największą częstotliwością wydań oraz wielkością zużycia, powinien być rozmieszczony:
- najbliżej miejsca wydawania w układzie horyzontalnym (zmniejsza się wówczas wysiłek energetyczny pracowników magazynu),
- na odpowiedniej wysokości w układzie wertykalnym (ergonomia pracy rąk magazyniera).
Dla uproszczenia analizy metodą ABC/XYZ z całości asortymentu wybrano 20 pozycji oznaczonych od 1 do 20. Dodatkowo przyjęto:
- wielkość zużycia (np. w okresie jednego roku),
- ocenę jednostkową towaru,
- wartość zużycia w kolejnych miesiącach badanego okresu.
Analizę przypadku opiszę w 7 krokach.
Krok 1. Rysunek 1 przedstawia menu aplikacji. Przyciski opracowane jako makro Visual Basic otwierają arkusze związane z kolejnymi procedurami analizy.
Rysunek 1. Menu aplikacji związanej z analizą ABC/XYZ.
Tworzenie menu rozpoczniemy od narysowania i nazwania tabel. Wybieramy i zaznaczamy, trzymając lewy przycisk myszki, zakres od E3 do J3. Klikamy na ikonę scalaj w menu Formatowanie (obok ikony symbolizującej wyrównanie do prawej). Później klikamy na ikonę zewnętrzne obramowanie na tym samym pasku – wybieramy pełne pogrubione obramowanie. Potem wpisujemy nazwę MENU – możemy wybrać pogrubioną czcionkę (ikona Bold) – deseń zakresu, klikając prawym przyciskiem myszy na zakresie – wyskoczy menu podręczne – wybieramy Formatuj komórki -> Desenie -> i wybieramy kolor – w tym przypadku jest to ciemno szary. Następnie wybieramy zakres E5:J29 i postępujemy analogicznie z obramowaniem i deseniem, z tym że w zakresie E6:J29 jest on jasno szary, a w zakresie E5:J5 ciemno szary. Nazywamy i scalamy zakresy E5:G5 „Analiza ABC” i H5:J5 „Analiza XYZ”. Zaznaczamy i scalamy zakres E6:G29, deseń jasno szary, grube obramowanie.
Teraz pora dodać przyciski. Dla uproszczenia pominąłem obiekty Button Visual Basic i zastąpiłem je prostokątami, które rysujemy za pomocą paska Rysowanie, który wywołujemy z menu podręcznego, klikając prawym przyciskiem myszy na pasku formatowanie lub w jego okolicy. Klikamy ikonę prostokąta na pasku Rysowanie i rysujemy prostokąt w zakresie E6:G29 o wielkości mniej więcej 1/6 tego zakresu, gdyż musi nam się zmieścić pozostałych 5 prostokątów. Klikamy na niego prawym przyciskiem myszy i wybieramy z menu podręcznego Formatuj -> Kolory i linie i wybieramy kolor ciemno szary i zaznaczamy obok półprzezroczysty. Potem kopiujemy go – klikamy prawym przyciskiem na niego – wybieramy z menu podręcznego „kopiuj”, potem klikamy prawym przyciskiem myszy w 5 komórek poniżej, w odpowiednich odstępach i wybieramy „wklej”. Po skopiowaniu wszystkich prostokątów klikamy po kolei na każdy prawym przyciskiem myszy i wybieramy z menu podręcznego Dodaj text. Naciskamy enter, wybieramy „wyśrodkowane” i wpisujemy nazwę każdego prostokąta i tak po kolei: „wprowadź dane”, „sortuj”, „kumuluj”, „grupuj towar”, „oznacz klasę”. Dopasowujemy rozmiary prostokątów do tekstu i rozmieszczamy symetrycznie. Analogicznie robimy w zakresie. H6:J29, tylko tam prostokąty nazywamy: "wprowadź dane”, „oblicz parametry do klasyfikacji”, „oznacz klasę”, „sortuj”, „oblicz trend i pokaż wykres”.
Teraz należy przypisać przyciskom makra. Zarejestrujmy pierwsze makro – będzie ono powodowało przejście do Arkusza2. Wybieramy w menu głównym Narzędzia -> Makra – Rejestruj makro. Wpisujemy nazwę makra „wprowadz_dane”. Wciskamy ok. Wybieramy Arkusz 2, komórkę C6 i naciskamy stop. Makro zostało zarejestrowane. Teraz klikamy prawym przyciskiem myszy na „przycisk wprowadź dane” w rubryce Analiza ABC i wybieramy z menu podręcznego przypisz makro – wybieramy makro „wprowadz_dane” i naciskamy ok.
Rysunek 2. Tabela danych do analizy ABC.
Klikamy na przycisk „wprowadź dane”. Pojawia się arkusz, który przedstawia Rysunek 2. Sporządzamy tabelę (E2:G2 i C5:I25) wraz z nagłówkami, ale bez danych, jak na Rysunku 2.
Teraz pora dodać przyciski z makrami. Tworzymy więc dwa przyciski – „menu” i „demo” – jak na Rysunku 2. Teraz rejestrujemy kolejne makro – nazywamy je „wybor_menu”. Po zatwierdzeniu ok. wybieramy Arkusz1 i naciskamy „stop”. Wracamy do Arkusza 2 i przypisujemy makro „wybor_menu” do przycisku menu – jego kliknięcie będzie powodowało powrót do menu głównego w Arkuszu1. Będąc w Arkuszu 2 rejestrujemy kolejne makro o nazwie „demo”. Po naciśnięciu ok. rozpoczynamy wpisywanie danych do kolumn „nazwa towaru”, „zużycie ilościowe” oraz „cena jednostkowa”. Po wypełnieniu wszystkich danych w zakresie C6:E25, zatrzymujemy rejestrację makra „demo”, które przypisujemy przyciskowi o tej samej nazwie. Jego naciśnięcie będzie wypełniało tabelę przykładowymi danymi. W kolumnie o nazwie „wartość zużycia” w pierwszej komórce (F6) wpisujemy prostą formułę =D6*E6 oraz przeciągamy ją myszą w dół do końca kolumny (trzymając prawy przycisk myszy w prawym dolnym rogu zaznaczenia komórki – pojawia się czarny krzyżyk). W pierwszą komórkę kolumny o nazwie „% udział wartości zużycia” (G6) wpisujemy następującą formułę: =JEŻELI(F6=0;0;F6/SUMA($F$6:$F$25)). Zastosowanie funkcji ”jeżeli” powoduje, że w przypadku zerowej wartości zużycia (kolumna F tabeli) nie wystąpi błąd dzielenia przez 0. Powyższą formułę przeciągamy w dół do końca kolumny.
Rysunek 3. Końcowa postać analizy ABC.
Krok 2. Wracamy do Arkusza 1. Rejestrujemy makro o nazwie „sort”. Po naciśnięciu ok. klikamy na Arkusz 2 i i zaznaczamy kolumnę „wartość zużycia” (komórka F5) i wybieramy na pasku Standardowym sortowanie malejąco (ikonka od Z do A). Zatrzymujemy rejestrację makra. Wracamy do Arkusza 1 i przypisujemy makro do przycisku „sortuj”.
Krok 3. Kolejnym krokiem jest uruchomienie przycisku „kumuluj” w Arkuszu 1. Wyświetli się arkusz z tabelą, w której nastąpi automatyczne kumulowanie wartości zużycia w procentach. W tym celu w Arkuszu 1 rejestrujemy makro „kumuluj’. Po naciśnięciu ok. przechodzimy do Arkusza 2. W pierwszą komórkę kolumny „skumulowana wartość zużycia w %” (H6) wpisujemy formułę G6, a następnie w drugą: =H6+G7 i przeciągamy ją myszą do końca kolumny. Zatrzymujemy rejestrację makra, które przypisujemy do przycisku „kumuluj”.
Krok 4. Z menu wybieramy kolejno przyciski „grupuj towar oraz „oznacz klasę”. Ostatecznie tabela z rysunku 2 przyjmuje postać, jak na rysunku 3.
Dwie ostatnie kolumny przedstawiają istotę zasady ABC, czyli ok. 20% towarów reprezentuje ok. 80% wartości zużycia. Przyciski „grupuj towar” i „oznacz klasę” uruchamiają procedury opracowane w Visual Basic for Application (rysunek 4).
Aby opracować powyższe procedury klikamy w Narzędzia -> Makro -> Edytor Visual Basic (VBA). Otwiera się okno jak na rysunku 4, w które wpisujemy odpowiednie kody (Moduł 1). Procedury sub o nazwach „klasa” i „abc” wykorzystują trzy podstawowe konstrukcje:
- for each-in-next,
- for i-to-next-i,
- if-then-else.
Po wpisaniu procedur, makro „klasa” przypisujemy do przycisku „grupuj towar”, natomiast makro „abc” do przycisku „oznacz klasę”. Na koniec rejestrujemy makro „wyczysc”. Po kliknięciu ok. przechodzimy do Arkusza 2 – zaznaczamy i czyścimy komórki C6:E25. Tak samo robimy z komórkami I6:25. Z kolei w komórkach H6:H25 ustawiamy tło na białe w menu podręcznym. Powracamy do Arkusza 1 i wybieramy komórkę A1. Zatrzymujemy rejestrację makra i przypisujemy je przyciskowi „czyść arkusze” znajdującemu się po lewej stronie.
Rysunek 4. Procedura sub z zaznaczeniem poszczególnych grup zgodnie z metodą ABC.
Procedura sub wprowadzająca oznaczenia literowe analizy. Zawierają komendy If, Then, ElseIf, End If, Integer, Cells, For, Next.
Krok 5. Krok ten zaczyna dynamiczną analizę XYZ. W Arkuszu 1 rejestrujemy makro „wprowadz2”. Po wciśnięciu ok. wybieramy Arkusz 3, komórkę A6. Makro przypisujemy przyciskowi „wprowadź dane” znajdującemu się w prawej części menu. Od teraz klikając na przycisk „wprowadź dane” z prawej strony menu, przechodzimy do kolejnego arkusza z tabelą o nazwie „wartości zużycia w skali roku”. Tabelę (bez danych) jak na rysunku 5 należy stworzyć w Arkuszu 3 w zakresie E2:G2 i A5:M25. Przycisk menu wraz z przypisanym mu makrem „wybor_menu” należy skopiować z Arkusza 2. Teraz należy stworzyć przycisk „demo” – analogicznie jak robiło się to w Arkuszu 2. Teraz należy zarejestrować makro „demo2” - w tabelę z rysunku 5 należy wprowadzić wartości zużycia towarów w badanych okresach (np. co miesiąc). Po zakończeniu rejestracji należy przypisać makro do przycisku „demo” w Arkuszu 3.
Rysunek 5. Wartości zużycia w skali roku.
Krok 6. Tworzymy Arkusz 4, klikając prawym przyciskiem myszy na zakładkę Arkusza 3 i wybierając z menu podręcznego „Wstaw”. W Arkuszu 4 tworzymy tabelę (bez danych) w zakresach D1:H1 i D3:H23, jak widać na rysunku 6. Poza tym kopiujemy przycisk „menu” z Arkusza 3. Wracamy do Arkusza1 i rejestrujemy makro „obliczp”. Po naciśnięciu ok. wybieramy Arkusz 4, komórkę D4. Wpisujemy do niej formułę =Arkusz3!A6.
Parametrem związanym z klasyfikacją XYZ jest współczynnik zmienności, określony następującym wzorem V = odchylenie standardowe / średnia wartość sprzedaży. Współczynnik ten wskazuje rozrzut od wartości oczekiwanej, np. średniej. Tabela na rysunku 8 przedstawia już uporządkowane (rosnąco według współczynników zmienności) parametry do analizy XYZ. W pierwsze komórki kolumn o nazwach „wartość średnia” (E4), „odchylenie standardowe” (F4) oraz „współczynnik zmienności” (G4) wpisujemy kolejno:
=ŚREDNIA(Arkusz3!B6:M6)
=ODCH.STANDARDOWE(Arkusz3!B6:M6)
=F6/E6
Zakres komórek E4:G4 przeciągamy do samego dołu tabeli. Odświeżamy obramowanie tabeli – zaznaczając cienką siatkę dla zakresu D3:H23 i grube obramowanie dla zakresów D3:H3 i D4:H23. Zatrzymujemy rejestrację makra i przypisujemy je do przycisku „oblicz parametry do klasyfikacji” w Arkuszu 1.
Formuły związane ze średnią oraz odchyleniem standardowym pobierają wartości z poprzedniego arkusza. Kolumna o nazwie „klasa” wskazuje, które towary charakteryzują się równomiernym zużyciem, a które np. sezonowością. Przyciski „oznacz klasę” i „sortuj” oraz procedury z nimi związane opracowuje się podobnie jak w przypadku analizy ABC. Procedura określająca klasy XYZ została opracowana w VBA (rysunek 7). Należy wpisać ją w Edytorze Visual Basic (Moduł 1) i przypisać do przycisku „oznacz klasę” w prawej części menu w Arkuszu 1.
Rysunek 6. Parametry do klasyfikacji XYZ.
Krok 7. W tym kroku sprawdzamy „zachowanie się” towaru. Aby uzyskać odpowiedź na pytania: jakie jest jego zużycie, czy charakteryzuje się regularnością, czy cechuje je sezonowość, klikamy na przycisk „olicz trend i pokaż wykres”. Pojawi się wówczas arkusz, który analizuje towary z grupy X oraz Z. Aby to wykonać, tworzymy Arkusz 5, klikając prawym przyciskiem myszy na zakładkę Arkusza 4 i wybierając z menu podręcznego „Wstaw”. W Arkuszu 5 tworzymy tabele w zakresach A2:N8 i A11:N15, jak widać na rysunku 8. Nie wypełniamy danymi komórek w zakresach B6:N8 i B13:N15. Następnie przechodzimy do Arkusza 1 i rejestrujemy makro o nazwie „obliczt”. Po naciśnięciu ok. wybieramy Arkusz 5. W komórce B5 wstawiamy funkcję =Arkusz4!D4, natomiast w komórce C5 =WYSZUKAJ.PIONOWO(Arkusz5!$B$5;Arkusz3!$A$6:$N$25;2) i przeciągnąć ją do komórki N5. W komórce B12 wstawiamy funkcję =Arkusz4!D23, natomiast w komórce C12 =WYSZUKAJ.PIONOWO(Arkusz5!$B$12;Arkusz3!$A$6:$N$25;2) i przeciągnąć ją do komórki N12.
W tabeli pokazanej na rysunku 8 w wierszach o nazwach „odcięta”, „nachylenie” i „trend” wprowadzono predefiniowane funkcje arkusza Excel, związane z obliczeniem trendu, który można określić następującym wzorem: y = a * t + b, gdzie: a, b – nachylenie i odcięta, t – okres.
Wartości parametrów a i b można wyznaczyć stosując klasyczną metodę najmniejszych kwadratów lub korzystając z predefiniowanych funkcji Excela. Do pierwszych komórek kolumn o określonych nazwach wpisujemy kolejno:
(B6) =ODCIĘTA($C$5:$N$5;$C$4:$N$4)
(B7) =NACHYLENIE($C$5:$N$5;$C$4:$N$4)
(B13) =ODCIĘTA($C$12:$N$12;$C$11:$N$11)
(B14) =NACHYLENIE($C$12:$N$12;$C$11:$N$11)
Rysunek 7. Procedura sub określająca klasy XYZ w analizie. Zawiera komendy If, Then,
ElseIf, End If, Integer, Cells, For, Next.
(C8) =$B$6+$B$7*C4 i przeciągamy do N8
(C15) =$B$13+$B$14*C11 i przeciągamy do N15.
Zatrzymujemy rejestrację makra i przypisujemy je do przyciku „oblicz trend i pokaż wykres”. Przechodzimy do Arkusza 1 i nagrywamy makro „powrot”. Po naciśnięciu ok. wybieramy Arkusz 5, komórkę A1. Zatrzymujemy rejestrację makra. Wracamy do Arkusza 5. Dodajemy dwa przyciski „wykres” i kopiujemy z Arkusza 4 do Arkusza 5 przycisk „menu”. Teraz pora dodać wykresy. W Arkuszu 5 nagrywamy makro „wykresx”. Po nacisnięciu ok. zaznaczamy zakres A4:N5 i wybieramy z menu głównego Wstaw -> Wykres. Wybieramy wykres XY (Punktowy) z punktami danych połączonymi wygładzonymi liniami. Potem Dalej. Edytujemy serie: -> Nazwa: Arkusz5!$B$5. Dalej.
Tytuł wykresu: Analiza towaru z grupy X
Oś wartości (X): miesiące
Oś wartości (Y): trend
Dalej. Następnie wybieramy jako nowy arkusz i dajemy zakończ. Wybieramy nowo powstały wykres i naciskamy prawym przyciskiem na linię wykresu i wybieramy Dodaj linię trendu. W prawym górnym rogu wykresu dodajemy przycisk „powrót” i kojarzymy z nim makro „powrot”. Zatrzymujemy rejestrację makra. Wracamy do Arkusza 5 i kojarzymy makro „wykresx” z przyciskiem wykres znajdującym się u góry.
W Arkuszu 5 nagrywamy makro „wykresz”. Po naciśnięciu ok. zaznaczamy zakres A11:N12 i wybieramy z menu głównego Wstaw -> Wykres. Wybieramy wykres XY (Punktowy) z punktami danych połączonymi wygładzonymi liniami. Potem Dalej. Edytujemy serie: -> Nazwa: Arkusz5!$B$12. Dalej.
Tytuł wykresu: Analiza towaru z grupy Z
Dalej. Następnie wybieramy jako nowy arkusz i dajemy zakończ. Wybieramy nowo powstały wykres i naciskamy prawym przyciskiem na linię wykresu i wybieramy Dodaj linię trendu. W prawym górnym rogu wykresu dodajemy przycisk „powrót” i kojarzymy z nim makro „powrot”. Zatrzymujemy rejestrację makra. Wracamy do Arkusza 5 i kojarzymy makro „wykresz” z przyciskiem wykres znajdującym się u góry.
Znaki $ oznaczają wartości bezwzględne w odwołaniu. Wartości trendu obliczone w zakresach C8:N8 i C15:N15 oraz wartości faktyczne można zobrazować graficznie, klikając na przyciski „wykres”.
Z wykresów pokazanych na rysunkach wynika, że zużycie towaru 8 cechuje się dużą nieregularnością. Odchylenia wyrażone przez procentowe wskaźniki pozwalają dobrze rozpoznać istotę regularności zużycia towaru 11 i występowanie sezonowości w przypadku towaru 8. Dla towaru o dużej regularności celowe jest obliczanie trendu w kolejnych okresach, np. w ramach prognozy zużycia lub sprzedaży.
Na koniec nagrywamy makro wyczysc2. Po naciśnięciu ok. zaznaczamy Arkusz 4 i czyścimy w nim zakres D4:H23 (del). Zaznaczamy Arkusz 3 i czyścimy zakres A6:M25. Zaznaczamy Arkusz 5 i czyścimy komórki B5 i B12. Potem usuwamy Wykres 1 i Wykres 2. Zatrzymujemy rejestrację makra i przypisujemy je przyciskowi „czyść arkusze” znajdującemu się w Arkuszu 1 – Analiza XYZ.
Zaletą aplikacji komputerowych w Excelu, poza łatwością obsługi, niską ceną oraz powszechną dostępnością, jest możliwość modyfikowania lub wprowadzania nowych danych bez konieczności ponownego tworzenia tabel czy formuł. Tak więc w prosty i szybki sposób można rozwiązać złożone zadania optymalizacyjne.
Umiejętność wykorzystania procedur w programowaniu Visual Basic for Application przyczynia się do powstawania aplikacji o przyjaznym interfejsie, które mogą wspomagać pracę logistyków w małych i średnich przedsiębiorstwach.
Rysunek 8. Analiza trendu.
Rysunek 9. Analiza trendu dla towaru 11.
Rysunek 10. Analiza trendu dla towaru 8.
Granii