Hoe u een ABC-analyse uitvoert in Excel: 5 eenvoudige stappen

ABC-analyse verdeelt voorraadartikelen in drie klassen op basis van de waarde van hun jaarlijkse verbruik. Klasse A-artikelen zijn de weinige artikelen die het grootste deel van het geld vertegenwoordigen. Klasse C-artikelen zijn de vele artikelen die weinig kosten, en klasse B zit daartussenin. In Excel kunt u dit doen met één tabel: jaarlijkse waarde, aandeel in het totaal, een cumulatief totaal en een formule die elke klasse toewijst.

Deze gids legt uit wat de klassen betekenen, de vijf Excel-stappen, een uitgewerkt voorbeeld en hoe u het resultaat in een grafiek weergeeft. Ook wordt besproken hoe u uw grenswaarden kiest en wat u met elke klasse doet zodra de analyse is voltooid.

Wat ABC-analyse is

ABC-analyse is een manier om te bepalen welke artikelen de meeste aandacht verdienen. Het is gebaseerd op een eenvoudig patroon: een klein deel van de artikelen is verantwoordelijk voor een groot deel van de uitgaven.

Een hoofdstuk uit 2012 over het analyseren en beheersen van farmaceutische uitgaven, van Management Sciences for Health (MSH), beschrijft dit duidelijk. Er staat dat "een relatief klein aantal artikelen verantwoordelijk is voor het grootste deel van de waarde van het jaarlijkse verbruik." Er wordt aan toegevoegd: "De analyse van dit fenomeen staat bekend als Pareto-analyse of, vaker, ABC-analyse."

Hetzelfde hoofdstuk legt uit dat artikelen "kunnen worden ingedeeld in drie categorieën (A, B en C) op basis van de waarde van hun jaarlijkse verbruik." De methode is hetzelfde of u nu medicijnen, reserveonderdelen of retailproducten op voorraad heeft.

Eén punt wordt gemakkelijk over het hoofd gezien. De klassen zijn geen permanente labels. MSH merkt op dat "als verbruikspatronen veranderen, het artikel de volgende keer dat er een ABC-analyse wordt uitgevoerd in een andere categorie kan vallen." ABC-analyse werkt dus het beste als een routinecontrole, niet als een eenmalig project.

Wat de klassen A, B en C betekenen

Het MSH-hoofdstuk geeft typische marges voor elke klasse:

Klasse	Aandeel van de artikelen	Aandeel van de jaarlijkse waarde	Wat het meestal betekent
A	10 tot 20 procent	75 tot 80 procent	Weinig artikelen, het meeste geld
B	10 tot 20 procent	15 tot 20 procent	Een middengroep
C	60 tot 80 procent	5 tot 10 procent	Veel artikelen, weinig geld

Dit zijn typische marges, geen regels. MSH zegt: "Deze grenzen zijn enigszins flexibel." In haar voorbeeld wordt klasse A in plaats daarvan vastgesteld op de artikelen die samen 70 procent van de middelen vertegenwoordigen.

De waarde die de klassen bepaalt, is de jaarlijkse verbruikswaarde: het aantal verbruikte eenheden in een jaar vermenigvuldigd met de eenheidsprijs. Een goedkoop artikel dat in enorme volumes wordt gebruikt, kan in klasse A terechtkomen. Een duur artikel dat eenmaal per jaar wordt gebruikt, kan in klasse C belanden.

Een artikel uit 2014 in het American Journal of Business Education trekt het gebruik van uitsluitend waarde in twijfel. Het stelt dat leerboeken "zich richten op het dollarvolume als het enige criterium" en adviseert om andere criteria toe te voegen. Voor een eerste aanzet is waarde de methode die het MSH-hoofdstuk gebruikt.

Wat u nodig heeft voordat u begint

ABC-analyse in Excel vereist slechts een paar kolommen per artikel:

• Artikelnaam of SKU. Eén rij per artikel.
• Jaarlijks verbruikte of ingekochte eenheden. Gebruik voor elk artikel dezelfde periode van 12 maanden.
• Eenheidsprijs. De kosten van één eenheid, in dezelfde eenheid waarin u telt.

MSH benadrukt de overeenkomstige periode: "Zorg ervoor dat voor alle artikelen dezelfde evaluatieperiode wordt gebruikt om ongeldige vergelijkingen te voorkomen." Ze adviseert ook om dezelfde basiseenheid te gebruiken voor kosten en hoeveelheid, zoals een tablet of een enkele doos, in plaats van verschillende verpakkingsgrootten te mengen.

Als uw gegevens afkomstig zijn uit een voorraad- of inkoopsysteem, exporteer deze dan als een CSV- of Excel-bestand. Verwijder artikelen zonder activiteit in de periode, of behoud ze en verwacht dat ze in klasse C vallen.

Hoe u een ABC-analyse uitvoert in Excel

De onderstaande vijf stappen volgen de methode uit het MSH-hoofdstuk, aangepast naar Excel-formules. Het voorbeeld plaatst een titel in rij 1, koppen in rij 2 en 10 artikelen in de rijen 3 tot en met 12. De kolommen A, B en C bevatten de artikelnaam, de jaarlijkse eenheden en de eenheidsprijs.

Stap 1: Maak een lijst van de artikelen, eenheden en eenheidsprijs

Voer één rij per artikel in of plak deze, met de naam, jaarlijkse eenheden en eenheidsprijs. Voeg koppen toe in rij 2, zodat de tabel later eenvoudig te sorteren is.

Controleer de gegevens voordat u verdergaat. Zoek naar lege prijzen, negatieve hoeveelheden en dubbele SKU's, omdat elk daarvan de totalen zal vertekenen. Een snelle filter op elke kolom brengt deze meestal aan het licht.

Als er meerdere aankopen van hetzelfde artikel zijn gedaan tegen verschillende prijzen, gebruik dan één consistente prijs. MSH merkt op dat "een gewogen gemiddelde of een FIFO-gemiddelde" de meest nauwkeurige alternatieven zijn wanneer de werkelijke eenheidsprijs moeilijk te achterhalen is.

Stap 2: Bereken de jaarlijkse waarde en het aandeel in het totaal

Vermenigvuldig in kolom D de eenheden met de prijs om de jaarlijkse waarde van elk artikel te krijgen. Voer in D3 =B3*C3 in en voer de formule door naar beneden.

Deel in kolom E elke waarde door het totaal van alle waarden om het aandeel te berekenen. Voer in E3 =D3/SUM($D$3:$D$12) in en voer dit door naar beneden. De dollartekens houden het totale bereik vast wanneer de formule wordt gekopieerd. Geef kolom E de opmaak van een percentage met twee decimalen.

MSH adviseert die precisie met een reden. In haar eigen woorden: "verschillende artikelen kunnen qua waarde dicht bij elkaar liggen en veel artikelen kunnen minder dan 1 procent van de totale waarde vertegenwoordigen."

Stap 3: Sorteer de artikelen op waarde, van groot naar klein

Selecteer de hele tabel, inclusief de koppen, en sorteer op kolom D van groot naar klein. In Excel is dat Gegevens, daarna Sorteren, waarbij u kolom D selecteert en de volgorde instelt op Van groot naar klein.

Als u de voorkeur geeft aan een formule, retourneert de SORT-functie een gesorteerde kopie. De syntaxis van Microsoft is =SORT(array,[sort_index],[sort_order],[by_col]), waarbij een sorteervolgorde van -1 aflopend betekent. Voor deze tabel sorteert =SORT(A3:E12,4,-1) op de vierde kolom, met de hoogste waarde eerst.

Na deze stap staat het artikel met de hoogste jaarlijkse waarde bovenaan. Die volgorde is wat het cumulatieve totaal in de volgende stap betekenisvol maakt.

Stap 4: Voeg het cumulatieve percentage toe

Voeg in kolom F een cumulatief totaal van de aandelen toe. Voer in F3 =SUM($E$3:E3) in en voer dit door naar beneden. Het eerste deel van het bereik blijft vaststaan, en het tweede deel wordt telkens met één rij uitgebreid.

De laatste rij moet 100 procent aangeven. Als dat niet het geval is, controleer dan op lege cellen of tekstwaarden in de kolommen D en E.

Deze kolom is de kern van de ABC-analyse. Het laat zien welk deel van de totale waarde de artikelen boven elke rij samen vertegenwoordigen.

Stap 5: Wijs de klassen A, B en C toe

Gebruik in kolom G een formule om elk artikel te labelen. Voer bij grenswaarden van 80 en 95 procent dit in G3 in en voer het door naar beneden:

=IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")

De IFS-functie controleert elke voorwaarde in volgorde en retourneert de eerste overeenkomst. Microsofts eigen voorbeeld gebruikt hetzelfde patroon, met TRUE als de uiteindelijke allesvanger. Artikelen tot een cumulatief percentage van 80 procent worden A, artikelen tot 95 procent worden B, en de rest wordt C.

Tel ten slotte elke klasse met =COUNTIF(G3:G12,"A") en doe hetzelfde voor B en C. Vergelijk de aantallen met de typische marges hierboven. Pas de grenswaarden aan als klasse A veel te groot of te klein is voor uw team om te beheren.

Een uitgewerkt voorbeeld

Hier is een illustratieve tabel voor 10 artikelen, al gesorteerd op jaarlijkse waarde. De cijfers zijn voorbeelden, geen gegevens van een echt bedrijf.

Artikel	Jaarlijkse eenheden	Eenheidsprijs	Jaarlijkse waarde	Aandeel	Cumulatief	Klasse
SKU-01	1,200	$45.00	$54,000	36.00%	36.00%	A
SKU-02	3,000	$12.00	$36,000	24.00%	60.00%	A
SKU-03	500	$40.00	$20,000	13.33%	73.33%	A
SKU-04	8,000	$1.50	$12,000	8.00%	81.33%	B
SKU-05	2,000	$4.00	$8,000	5.33%	86.67%	B
SKU-06	600	$10.00	$6,000	4.00%	90.67%	B
SKU-07	1,000	$5.00	$5,000	3.33%	94.00%	B
SKU-08	1,500	$3.00	$4,500	3.00%	97.00%	C
SKU-09	700	$5.00	$3,500	2.33%	99.33%	C
SKU-10	400	$2.50	$1,000	0.67%	100.00%	C

De totale jaarlijkse waarde is $150,000. Drie artikelen, 30 procent van de lijst, maken 73.33 procent van de waarde uit en komen in klasse A terecht. Vier artikelen vallen in klasse B, en de laatste drie, met een waarde van 6 procent van de totale waarde, vallen in klasse C.

Twee details vallen op. SKU-04 heeft veruit de meeste eenheden, maar door de lage prijs valt het in klasse B. En met slechts 10 artikelen zullen de klasse-aandelen niet overeenkomen met de typische marges, wat normaal is voor een korte lijst.

Hoe u het resultaat in een grafiek weergeeft

Een grafiek maakt het patroon gemakkelijk te tonen in een vergadering. MSH stelt voor om het cumulatieve percentage uit te zetten tegen het artikelnummer, wat de bekende ABC-curve oplevert.

Excel heeft hiervoor een ingebouwd diagram. Microsoft beschrijft een Pareto-diagram als een diagram dat "zowel kolommen bevat die in aflopende volgorde zijn gesorteerd, als een lijn die het cumulatieve totale percentage vertegenwoordigt." Om er een te maken, selecteert u de artikelnamen en jaarlijkse waarden, en kiest u vervolgens Invoegen, Statistische grafiek invoegen en Pareto.

Voeg twee horizontale lijnen of labels toe bij uw grenswaarden, zoals 80 and 95 procent, zodat kijkers kunnen zien waar elke klasse begint. Onze gids voor het maken van een Pareto-diagram met AI behandelt het diagram zelf in meer detail.

Uw grenswaarden kiezen

Er is geen enkele juiste grenswaarde. MSH legt uit dat de keuze "afhangt van hoe volume en waarde verdeeld zijn over de artikelen op de lijst." Het hangt ook af van "hoe de resultaten van de ABC-analyse gebruikt gaan worden."

Managementcapaciteit is de praktische limiet. MSH verwoordt het direct: "de toewijzing van artikelen aan klasse A moet gebaseerd zijn op managementcapaciteit." Als uw team elke maand 50 artikelen nauwkeurig kan beoordelen, schiet een klasse A van 300 artikelen zijn doel voorbij.

Een paar veelvoorkomende benaderingen:

• Grenswaarden op basis van waarde. A tot 80 procent van de waarde, B tot 95 procent, C voor de rest. Dit is de methode die hierboven is gebruikt.
• Grenswaarden op basis van aantal artikelen. De bovenste 20 procent van de artikelen op basis van waarde wordt A, de volgende 30 procent B, en de rest C.
• Vaste lijsten. Sommige teams stellen klasse A in als de top 25 of 50 artikelen, ongeacht hun aandeel in de waarde.

Welke u ook kiest, schrijf deze op en gebruik hem elke keer. Het vergelijken van de klassen van dit kwartaal met die van vorig kwartaal werkt alleen als de grenswaarden hetzelfde blijven.

Wat u met elke klasse moet doen

Het doel van ABC-analyse is om inspanningen te leveren waar het geld zit. Het MSH-hoofdstuk noemt verschillende manieren om de resultaten te gebruiken:

• Bestel klasse A-artikelen vaker. MSH stelt dat het bestellen van klasse A-artikelen "vaker en in kleinere hoeveelheden zou moeten leiden tot een verlaging van de voorraadkosten."
• Onderhandel eerst over de prijzen van klasse A. "Prijsverlagingen voor artikelen die in de analyse als A-producten zijn geclassificeerd, kunnen leiden tot aanzienlijke besparingen," aldus het hoofdstuk.
• Tel de voorraad van klasse A vaker. MSH merkt op dat "cyclische voorraadtellingen moeten worden geleid door ABC-analyse, met frequentere tellingen voor klasse A-artikelen."
• Houd de bestelstatus van klasse A in de gaten. Een onverwacht tekort aan een klasse A-artikel kan leiden tot kostbare noodaankopen.

Voor klasse C-artikelen kunnen eenvoudigere regels gelden, zoals grotere, minder frequente bestellingen en minder tellingen. Klasse B zit daartussenin. Als langzaamlopende artikelen een zorg zijn, sluit onze gids over het opsporen van langzaamlopende voorraad goed aan bij deze analyse.

Het sneller doen met AI

De Excel-stappen kosten een paar minuten zodra de gegevens schoon zijn. Het opschonen van de export en het elk kwartaal herhalen van het werk kost meer tijd.

Een AI-werkruimte kan de berekeningen en het sorteren in één verzoek uitvoeren. Upload de voorraad- of inkoopexport naar Powerdrill Bloom en vraag in natuurlijke taal om een ABC-analyse met uw grenswaarden. Vraag om de jaarlijkse waarde, het aandeel, het cumulatieve percentage en de klasse voor elk artikel, plus een Pareto-diagram.

Controleer het vervolgens zoals elk ander spreadsheet. Vergelijk de totale jaarlijkse waarde met uw eigen som en doe een steekproef op twee artikelen in elke klasse. Onze Excel AI assistant-pagina behandelt dit soort spreadsheetwerk in meer detail. Voor een bredere blik op prognosetools kunt u dit overzicht van AI-tools voor voorraad- en vraagvoorspelling bekijken.

Veelgemaakte fouten om te vermijden

• Tijdsperioden mengen. Twaalf maanden voor het ene artikel en zes voor het andere maakt de aandelen betekenisloos.
• Eenheden gebruiken in plaats van waarde. De klassen zijn afhankelijk van eenheden vermenigvuldigd met de prijs, niet van eenheden alleen.
• Vergeten te sorteren vóór het cumulatieve totaal. Een cumulatief percentage op een ongesorteerde lijst plaatst artikelen in de verkeerde klasse.
• Klassen als permanent beschouwen. Voer de analyse elk kwartaal of jaar opnieuw uit, omdat artikelen tussen klassen verschuiven.
• Grenswaarden die geen rekening houden met capaciteit. Een klasse A-lijst die te lang is om nauwgezet te beheren, krijgt niet meer aandacht dan klasse B.
• Kritieke goedkope artikelen negeren. Een artikel met een lage waarde kan het werk nog steeds stilleggen als het opraakt. Het MSH-hoofdstuk koppelt ABC-analyse aan een afzonderlijke beoordeling van vitale, essentiële en niet-essentiële artikelen.

Wanneer uw artikellijst afkomstig is uit een rommelige export, kunt u Powerdrill Bloom proberen om de eerste ABC-tabel en -grafiek te maken.

Veelgestelde vragen

Wat is ABC-analyse in voorraadbeheer?

ABC-analyse verdeelt artikelen in drie klassen op basis van de jaarlijkse verbruikswaarde. Klasse A-artikelen zijn de weinige artikelen die het grootste deel van de waarde vertegenwoordigen. Klasse C-artikelen zijn de vele artikelen die weinig waarde vertegenwoordigen, en klasse B zit daartussenin. Het helpt teams om hun controle-inspanningen te richten op waar het geld zit.

Hoe berekent u een ABC-analyse in Excel?

Vermenigvuldig de jaarlijkse eenheden met de eenheidsprijs voor elk artikel en deel dit door het totaal om het aandeel van elk artikel te krijgen. Sorteer op waarde van groot naar klein, voeg een cumulatief totaal van de aandelen toe en wijs klassen toe met een formule zoals IFS. Grenswaarden van 80 en 95 procent vallen binnen de typische marges in het MSH-hoofdstuk.

Wat zijn de percentages voor een ABC-analyse?

Een algemene richtlijn is dat klasse A 10 tot 20 procent van de artikelen en 75 tot 80 procent van de waarde bevat. Klasse B bevat nog eens 10 tot 20 procent van de artikelen en 15 tot 20 procent van de waarde. Klasse C bevat 60 tot 80 procent van de artikelen en 5 tot 10 procent van de waarde.

Wat is de formule voor ABC-classificatie in Excel?

Met het cumulatieve percentage in kolom F en gegevens die beginnen in rij 3, gebruikt u =IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C"). Wijzig 0.8 en 0.95 om overeen te komen met uw eigen grenswaarden. Geneste IF-formules kunnen hetzelfde werk doen.

Waarom is ABC-analyse belangrijk?

Het laat zien waar het meeste voorraadgeld naartoe gaat, zodat teams die artikelen nauwkeuriger kunnen beheren. Typische toepassingen zijn onder meer het vaker bestellen van klasse A-artikelen, het als eerste onderhandelen over hun prijzen en het frequenter tellen ervan. Het signaleert ook uitgaven die niet overeenkomen met de plannen.

Bronnen: Management Sciences for Health, MDS-3 Hoofdstuk 40: Analyseren en beheersen van farmaceutische uitgaven · Ravinder en Misra, ABC Analysis for Inventory Management (2014) · Microsoft Support, SORT-functie · Microsoft Support, IFS-functie · Microsoft Support, Een Pareto-diagram maken.