Jak wykonać analizę ABC w Excelu?
Analiza ABC — widzowie #50 
Analiza ABC polega na tym, żeby podzielić produktu na grupy w zależności od ich ważności najczęściej na podstawie zysku ze sprzedaży. Te, które przynoszą największy zysk trafiają do grupy A, następne do grupy B i ostatnie do grupy C.
Przeważnie podział w analizie ABC opiera się o procentowy udział w ilości sprzedanych produktów. Np: 10% ilościowo produktów ze wszystkich, które przynoszą największy zysk trafia do grupy A, następne np: 20% to grupa B, a pozostałe 70% ilości to grupa C.
Przykładowe dane zawierały ilość sprzedanych przedmiotów różnych typów oraz ich cenę za 1 egzemplarz. Można z tego obliczyć Wartość sprzedaży
=[@[Cena jednostkowa]]*[@Ilość]
Następnie chcemy zobaczyć, który produkt przyniósł największy przychód. Uzyskamy to za pomocą formuły:
=POZYCJA.NAJW([@[Wartość sprzedaży]];[Wartość sprzedaży];0)
Kolejna kolumna to wartość bieżąca sprzedaży, czyli łączna sprzedaż do pozycji danego produktu. Czyli dla produktu, który ma trzecią największą wartość sprzedaży to będzie suma 1, 2 i 3 produktu.
=SUMA.WARUNKÓW([Wartość sprzedaży];[Pozycja];"<="&[@Pozycja])
Warto jeszcze obliczyć też jako procent sumy wszystkich sprzedaży:
=[@[Sprzedaż bieżąca]]/MAX([Sprzedaż bieżąca])
W podobny sposób robimy analizy dla Ilości bieżącej i jej reprezentacji procentowej.
W drugiej tabeli potrzebujemy sobie obliczyć ilość bieżącą do danej grupy, czyli dla grupy byłaby to suma wszystkich elementów z grupy A i B:
=SUMA($N$2:N2)*SUMA(Magazyn[Ilość])
Dzięki temu możemy porozdzielać produkty do odpowiednich grup:
=JEŻELI([@[Ilość bieżąca]]<$O$2;$M$2;JEŻELI([@[Ilość bieżąca]]<$O$3;$M$3;$M$4))
warto tutaj posortować od największego na najmniejszego udziału w sprzedaży.
W tabeli pomocniczej potrzebujemy wyznaczyć jaki % mają udział we sprzedaży grupy do danej grupy:
=INDEKS(Magazyn[Sprzedaż bieżąca %];PODAJ.POZYCJĘ(O2;Magazyn[Ilość bieżąca]))
Na podstawie tych danych możemy obliczyć błędy (czy też przedziały grup) na + i na -.
Teraz możemy wstawić wykres na podstawie danych z Magazynu — Sprzedaż bieżąca % i Ilość bieżąca. Ważne, że trzeba te dane ręcznie dodać do wykresu punktowego z wygładzonymi liniami, żeby wyglądał odpowiednio.
Trzeba dodać jeszcze drugą serię 3 punktów z tabeli pomocniczej granicznych punktów i do niej nałożyć słupki błędów w pionie i poziomie.
Zostaje trochę dopracować wykres, żeby był czytelny i już masz pełną analizę ABC.
P.S.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić to napisz do mnie. Ja w miarę możliwości odpowiem na Twoje pytanie.
Bezpośredni odnośnik do filmu na youtube — Analiza ABC — widzowie #50
