Jak wykonać analizę ABC w Excelu?

Analiza ABC — widzowie #50 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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Analiza ABC — widzowie #50