ABC-analyse in excel

[Bone]

Goedenavond,

Voor mijn huidige werkgever ben ik gevraagd een abc-analyse uit te voeren in Excel, wanneer mogelijk. Ik wil die dan ook wel aangaan.

De organisatie (met 4 vestigingen) kent totaal circa 130.000 artikelen, verdeeld over 24 artikelgroepen. Totaal worden er circa 24.000 artikelen op voorraad gehouden in de hoofdvestiging.

Ik wil dus een ABC analyse gaan doen (alleen bij de hoofdvestiging), echter vraag ik mij dan af of ik deze over de 24 artikelgroepen moet doen, of over alle 24.000 voorraadartikelen (SKU's).
(of misschien wel per artikelgroep? Maar dan wordt het te breed lijkt mij).

Heeft iemand hier ervaring mee?

Ik heb in Excel per artikelgroep de omzet van 2015 genoteerd. Echter is de stelling vaak dat 20% van de artikelen (of groepen) zorgt voor 80% van de omzet. Ik heb het gevoel dat er bij mijn gegevens dan wat niet klopt. Als ik aan het berekenen kom lijkt het net dat de eerste 8 groepen al tot A behoren...

De categorieën worden als volgt benoemt:
Categorie A: circa 20% van de artikelgroepen heeft een gezamenlijk aandeel van circa 80% van de inkoopomzet.
Categorie B: circa 30% van de artikelgroepen heeft een gezamenlijk aandeel van circa 15% van de inkoopomzet.
Categorie C: de overige 50% van de artikelgroepen heeft een gezamenlijk aandeel van circa 5% van de inkoopomzet.

Of moet ik met andere percentages gaan werken? Dien je die te berekenen of?


Hoe kijken jullie hier tegen aan? Hou zouden jullie dit gaan indelen?


P.s. bestaan er templates of invulbladen in Excel voor een ABC analyse?

 

[OctaFish]

Zoiets?

 

[Bone]

Zoiets?

Hallo Octafish

Bedankt voor de uitwerking.

Wellicht kan je het iets toelichten? Ik zie dat de A artikelgroepen dus circa 30% is? De B artikelen 20% en de C artikelen 50% ?

Ik ben er al 2 dagen mee bezig, dus wellicht ben ik niet helemaal helder meer

 

[OctaFish]

Als je de getallen per categorie optelt, zie je dat de uitkomst redelijk klopt:

A	 € 6.044.877,00 	76%	9	38%
B	 € 1.029.400,03 	13%	4	17%
C	 € 863.180,12 	 	11%	11	46%

Dat de percentages niet helemaal kloppen, is logisch omdat je met vrij weinig getallen werkt. Dan is de uitkomst altijd een benadering. Hoe meer bedragen, hoe beter de uitkomst.

 

[Bone]

Als je de getallen per categorie optelt, zie je dat de uitkomst redelijk klopt:

A	 € 6.044.877,00 	76%	9	38%
B	 € 1.029.400,03 	13%	4	17%
C	 € 863.180,12 	 	11%	11	46%

Dat de percentages niet helemaal kloppen, is logisch omdat je met vrij weinig getallen werkt. Dan is de uitkomst altijd een benadering. Hoe meer bedragen, hoe beter de uitkomst.

Dat klopt inderdaad. Zou het dan zo moeten zijn? Dat klopt toch niet? (Wat bedoel je tevens met de 38, 17 en 46%?)

Categorie A: circa 40% van de artikelgroepen heeft een gezamenlijk aandeel van circa 75% van de inkoopomzet.
Categorie B: circa 15% van de artikelgroepen heeft een gezamenlijk aandeel van circa 15% van de inkoopomzet.
Categorie C: de overige 45% van de artikelgroepen heeft een gezamenlijk aandeel van circa 10% van de inkoopomzet.

Het zit namelijk zo.

De organisatie (met 4 vestigingen) kent totaal circa 130.000 artikelen, verdeeld over 24 artikelgroepen. Totaal worden er circa 24.000 artikelen op voorraad gehouden in de hoofdvestiging.

Ik wil dus een ABC analyse gaan doen (alleen bij de hoofdvestiging), echter vraag ik mij dan af of ik deze over de 24 artikelgroepen moet doen, of over alle 24.000 voorraadartikelen (SKU's).
(of misschien wel per artikelgroep? Maar dan wordt het te breed lijkt mij).

Kan je mij hierin toevallig adviseren?

 

[Bone]

Kan iemand mij vertellen hoe ik in een grafiek een streep naar boven kan laten gaan, en daar waar die de lijn kruist naar links gaat zoals hieronder in het voorbeeld?

Kan iemand tevens zo'n grafiek maken?

 

[Wocky]

Hallo,

Heb mij wat geamuseerd met getallen.
Kan dit je op weg helpen?

Groeten Wocky.

 

[joost1]

Voor de grafiek : bekijk deze site eens

http://peltiertech.com/Excel/Charts/XYAreaChart2.html

 

[joost1]

Ik heb ook wat zitten stoeien met de cijfers.
20 % van de artikelgroepen zijn in de gegeven tabel ca 5 artikelgroepen.
80 % van de omzet is in de gegeven tabel € 6.349.965,72
Met 5 artikelgroepen kan maximaal 52,53 % omzet gehaald worden. Met 6 artikelgroepen maximaal 59,58% omzet
Mijn vraag is daarom waarop de percentages van 20/80 , 30/15 en 50/5 gebaseerd zijn.
In de bijlage heb ik me maar even alleen gebaseerd om de omzetpercentages van 80 , 15 en 5.

 

[Bone]

Hallo Wocky en Joost1,

Bedankt voor jullie reactie. Wordt erg gewaardeerd.

Ik zie wel dat jullie beide het eerste excel bestand hebben gebruikt uit het 1e bericht.

Echter zaten hier nog wat fouten in, en heb ik gisteren 22-1 om 20:05 een nieuw excel bestand geüpload (tevens ook veranderd in 1e bericht).

Zou dit grafiekje ook voor dit bestand gemaakt kunnen worden?

Ik zou er erg geholpen mee worden.

Vriendelijk bedankt alvast.

Gr. Tom

 

[joost1]

Hierbij de grafiekjes.
De verhoudingen zijn nu:
------ Art---------Omzet
A-----12,00%----36,3%
B---- 48,00%----55,3%
C---- 40,00%----8,4%

Met kleurtjes in de tabel de oorspronkelijke 20-30-50 voor de artikelen en 80-15-5 voor de omzet aangegeven.
Kun je zien dat dit voor deze tabel niet echt opgaat. Misschien kun er wat mee.

Groet

 

[Bone]

Hierbij de grafiekjes.
De verhoudingen zijn nu:
------ Art---------Omzet
A-----12,00%----36,3%
B---- 48,00%----55,3%
C---- 40,00%----8,4%

Met kleurtjes in de tabel de oorspronkelijke 20-30-50 voor de artikelen en 80-15-5 voor de omzet aangegeven.
Kun je zien dat dit voor deze tabel niet echt opgaat. Misschien kun er wat mee.

Groet

Geweldig bedankt Joost. Echt top.

Hier kan ik zeker wel wat mee:thumb:

 

[pixcel]

De analyse is nu gebaseerd op een tabel in Excel. Een "degelijke" oplossing haalt de gegevens direct uit de database van je werkgever. Daarbij kun je Power Pivot gebruiken. Google op "power pivot abc pareto" en je ziet dat er al veel over gepubliceerd is. Met alternatieven voor de visualisatie in Excel.

 

[joost1]

Naar aanleiding van de vraag in #6 nog wat gestoeid met gekleurde vlakken maken in een grafiek.
Hierbij is de methode gebruikt als beschreven in http://peltiertech.com/fill-under-between-series-in-excel-chart/
Het is een beetje een trukendoos door de secundaire x-as als datum-as te gebruiken, maar geeft toch een leuk resultaat.

Groet

 

[Bone]

De analyse is nu gebaseerd op een tabel in Excel. Een "degelijke" oplossing haalt de gegevens direct uit de database van je werkgever. Daarbij kun je Power Pivot gebruiken. Google op "power pivot abc pareto" en je ziet dat er al veel over gepubliceerd is. Met alternatieven voor de visualisatie in Excel.

Frans,

Bedankt voor de tip!!

 

[Bone]

Naar aanleiding van de vraag in #6 nog wat gestoeid met gekleurde vlakken maken in een grafiek.
Hierbij is de methode gebruikt als beschreven in http://peltiertech.com/fill-under-between-series-in-excel-chart/
Het is een beetje een trukendoos door de secundaire x-as als datum-as te gebruiken, maar geeft toch een leuk resultaat.

Groet

Hallo Joost,

Bedankt maar weer voor de uitwerking.

Ik heb echter zelf gekozen voor andere indeling van A, B en C-artikelen (met criteria >=200.000 = A, >=50.000 = B en verder is C).

Normaal zouden bijvoorbeeld A artikelen het grootste gedeelte van de omzet moeten realiseren, daarna de B- artikelen en tot slot de C-artikelen. Volgens mij komt dat beter uit de verf met de nieuwe criteria de ik hierboven gesteld heb.

Als ik het goed heb is nu circa 75% van de omzet een A-artikelgroep, circa 20% een B-artikelgroep en 5% een C-groep. Zeg ik dat goed?

Ik krijg alleen die grafiek(en) niet meer mooi.

Zou jij dat kunnen doen, Joost? Zie excel bijlage.

Ik hoor het graag.

 

[joost1]

Bij deze de aangepaste versie.
In het grafiekje met de kleurtjes zijn nog een paar dingen handmatig gedaan. ( zie de niet gekleurde x waarden ). Als je de overgangen variabel wilt maken op basis van bepaalde criteria, dan zal daar nog iets voor verzonnen moeten worden.

Groet

 

[Bone]

Super, dankjewel voor de waardevolle hulp!!