Wie Sie eine ABC-Analyse in Excel erstellen: 5 einfache Schritte

Die ABC-Analyse sortiert Lagerartikel anhand des Werts ihres jährlichen Verbrauchs in drei Klassen. Klasse-A-Artikel sind die wenigen, auf die der Großteil des Geldes entfällt. Klasse-C-Artikel sind die vielen, die nur wenig ausmachen, und Klasse B liegt dazwischen. In Excel können Sie dies mit einer einzigen Tabelle erledigen: Jahreswert, Anteil am Gesamtwert, kumulierter Gesamtwert und eine Formel, die die jeweilige Klasse zuweist.

Dieser Leitfaden erklärt, was die Klassen bedeuten, beschreibt die fünf Excel-Schritte, zeigt ein praktisches Beispiel und erklärt, wie Sie das Ergebnis grafisch darstellen. Zudem wird beschrieben, wie Sie Ihre Grenzwerte wählen und was mit jeder Klasse zu tun ist, sobald die Analyse abgeschlossen ist.

Was die ABC-Analyse ist

Die ABC-Analyse ist eine Methode, um zu entscheiden, welche Artikel die meiste Aufmerksamkeit verdienen. Sie beruht auf einem einfachen Muster: Ein kleiner Anteil der Artikel macht einen großen Teil der Ausgaben aus.

Ein Kapitel aus dem Jahr 2012 über die Analyse und Kontrolle von Arzneimittelausgaben von Management Sciences for Health (MSH) beschreibt dies anschaulich. Es stellt fest, dass „eine relativ kleine Anzahl von Artikeln den Großteil des Werts des jährlichen Verbrauchs ausmacht“. Weiter heißt es: „Die Analyse dieses Phänomens ist als Pareto-Analyse oder, geläufiger, als ABC-Analyse bekannt.“

Dasselbe Kapitel erklärt, dass Artikel „basierend auf dem Wert ihres jährlichen Verbrauchs in drei Kategorien (A, B und C) eingeteilt werden können“. Die Methode ist dieselbe, egal ob Sie Medikamente, Ersatzteile oder Einzelhandelsprodukte lagern.

Ein Punkt wird leicht übersehen. Die Klassen sind keine dauerhaften Kennzeichnungen. MSH stellt fest: „Wenn sich die Verbrauchsmuster ändern, kann der Artikel bei der nächsten Durchführung der ABC-Analyse in eine andere Kategorie fallen.“ Daher funktioniert die ABC-Analyse am besten als Routineprüfung und nicht als einmaliges Projekt.

Was die Klassen A, B und C bedeuten

Das MSH-Kapitel nennt typische Bereiche für jede Klasse:

Klasse	Anteil der Artikel	Anteil am Jahreswert	Was es normalerweise bedeutet
A	10 bis 20 Prozent	75 bis 80 Prozent	Wenige Artikel, der Großteil des Geldes
B	10 bis 20 Prozent	15 bis 20 Prozent	Eine mittlere Gruppe
C	60 bis 80 Prozent	5 bis 10 Prozent	Viele Artikel, wenig Geld

Dies sind typische Bereiche, keine festen Regeln. MSH schreibt: „Diese Grenzen sind gewissermaßen flexibel.“ In ihrem Beispiel wird Klasse A stattdessen für die Artikel festgelegt, die zusammen 70 Prozent der Mittel ausmachen.

Der Wert, der die Klassen bestimmt, ist der jährliche Verbrauchswert: die in einem Jahr verbrauchten Einheiten multipliziert mit den Stückkosten. Ein günstiger Artikel, der in riesigen Mengen verbraucht wird, kann in Klasse A landen. Ein teurer Artikel, der nur einmal im Jahr benötigt wird, kann in Klasse C landen.

Ein Artikel aus dem Jahr 2014 im American Journal of Business Education stellt die ausschließliche Verwendung des Werts infrage. Er argumentiert, dass Lehrbücher „sich auf das Dollarvolumen als einziges Kriterium konzentrieren“ und empfiehlt, weitere Kriterien hinzuzufügen. Für einen ersten Durchlauf ist der Wert jedoch die Methode, die im MSH-Kapitel verwendet wird.

Was Sie vor dem Start benötigen

Die ABC-Analyse in Excel erfordert nur wenige Spalten pro Artikel:

• Artikelname oder SKU. Eine Zeile pro Artikel.
• Jährlich verbrauchte oder gekaufte Einheiten. Verwenden Sie für jeden Artikel denselben 12-Monats-Zeitraum.
• Stückkosten. Die Kosten für eine Einheit, in derselben Einheit, in der Sie zählen.

MSH betont den übereinstimmenden Zeitraum: „Stellen Sie sicher, dass für alle Artikel derselbe Überprüfungszeitraum verwendet wird, um ungültige Vergleiche zu vermeiden.“ Zudem wird empfohlen, dieselbe Basiseinheit für Kosten und Menge zu verwenden, wie z. B. eine Tablette oder eine einzelne Schachtel, anstatt Packungsgrößen zu mischen.

Wenn Ihre Daten aus einem Bestands- oder Einkaufssystem stammen, exportieren Sie sie als CSV- oder Excel-Datei. Entfernen Sie Artikel ohne Aktivität im Zeitraum oder behalten Sie sie und stellen Sie sich darauf ein, dass sie in Klasse C fallen.

Wie man eine ABC-Analyse in Excel durchführt

Die folgenden fünf Schritte folgen der Methode aus dem MSH-Kapitel, angepasst an Excel-Formeln. Das Beispiel platziert einen Titel in Zeile 1, Kopfzeilen in Zeile 2 und 10 Artikel in den Zeilen 3 bis 12. Die Spalten A, B und C enthalten den Artikelnamen, die jährlichen Einheiten und die Stückkosten.

Schritt 1: Artikel, Einheiten und Stückkosten auflisten

Geben Sie pro Artikel eine Zeile mit Name, jährlichen Einheiten und Stückkosten ein oder fügen Sie diese ein. Fügen Sie in Zeile 2 Kopfzeilen hinzu, damit die Tabelle später leicht sortiert werden kann.

Überprüfen Sie die Daten, bevor Sie fortfahren. Suchen Sie nach fehlenden Kosten, negativen Mengen und doppelten SKUs, da jeder dieser Fehler die Gesamtsummen verfälscht. Ein schneller Filter auf jede Spalte findet diese meist.

Wenn mehrere Käufe desselben Artikels zu unterschiedlichen Preisen getätigt wurden, verwenden Sie einheitliche Kosten. MSH stellt fest, dass „ein gewichteter Durchschnitt oder ein FIFO-Durchschnitt“ die genauesten Alternativen sind, wenn die tatsächlichen Stückkosten schwer nachzuverfolgen sind.

Schritt 2: Jahreswert und Anteil am Gesamtwert berechnen

Multiplizieren Sie in Spalte D die Einheiten mit den Kosten, um den Jahreswert jedes Artikels zu erhalten. Geben Sie in D3 =B3*C3 ein und ziehen Sie die Formel nach unten.

Teilen Sie in Spalte E jeden Wert durch die Summe aller Werte, um den Anteil zu erhalten. Geben Sie in E3 =D3/SUMME($D$3:$D$12) ein und ziehen Sie die Formel nach unten. Die Dollarzeichen fixieren den Gesamtbereich beim Kopieren der Formel. Formatieren Sie Spalte E als Prozentsatz mit zwei Dezimalstellen.

MSH empfiehlt diese Genauigkeit aus gutem Grund. In ihren Worten: „Mehrere Artikel können wertmäßig nah beieinander liegen, und viele machen möglicherweise weniger als 1 Prozent des Gesamtwerts aus.“

Schritt 3: Artikel nach Wert sortieren, der größte zuerst

Wählen Sie die gesamte Tabelle inklusive Kopfzeilen aus und sortieren Sie nach Spalte D vom größten zum kleinsten Wert. In Excel gehen Sie dazu auf Daten, dann Sortieren, wählen Spalte D und stellen die Reihenfolge auf „Nach Größe sortieren (absteigend)“.

Wenn Sie eine Formel bevorzugen, gibt die Funktion SORTIEREN eine sortierte Kopie zurück. Die Syntax von Microsoft lautet =SORTIEREN(Matrix;[Sortierindex];[Sortierreihenfolge];[Spalte]), wobei eine Sortierreihenfolge von -1 absteigend bedeutet. Für diese Tabelle sortiert =SORTIEREN(A3:E12;4;-1) nach der vierten Spalte, mit dem höchsten Wert zuerst.

Nach diesem Schritt steht der Artikel mit dem höchsten Jahreswert ganz oben. Diese Reihenfolge macht den kumulierten Gesamtwert im nächsten Schritt erst sinnvoll.

Schritt 4: Kumulierten Prozentsatz hinzufügen

Fügen Sie in Spalte F einen kumulierten Gesamtwert der Anteile hinzu. Geben Sie in F3 =SUMME($E$3:E3) ein und ziehen Sie die Formel nach unten. Der erste Teil des Bereichs bleibt fixiert, während der zweite Teil mit jeder Zeile um eins wächst.

Die letzte Zeile sollte 100 Prozent anzeigen. Wenn dies nicht der Fall ist, überprüfen Sie Spalten D und E auf leere Zellen oder Textwerte.

Diese Spalte ist das Herzstück der ABC-Analyse. Sie zeigt, wie viel vom Gesamtwert die Artikel oberhalb der jeweiligen Zeile zusammen ausmachen.

Schritt 5: Die Klassen A, B und C zuweisen

Verwenden Sie in Spalte G eine Formel, um jeden Artikel zu kennzeichnen. Geben Sie bei Grenzwerten von 80 und 95 Prozent Folgendes in G3 ein und ziehen Sie die Formel nach unten:

=WENNSS(F3<=0,8;"A";F3<=0,95;"B";WAHR;"C")

Die Funktion WENNSS prüft jede Bedingung der Reihe nach und gibt die erste Übereinstimmung zurück. Microsofts eigenes Beispiel verwendet dasselbe Muster mit WAHR als abschließendem Auffangwert. Artikel bis zu einem kumulierten Wert von 80 Prozent werden zu A, Artikel bis zu 95 Prozent zu B und der Rest zu C.

Zählen Sie schließlich jede Klasse mit =ZÄHLENWENN(G3:G12;"A") und verfahren Sie ebenso für B und C. Vergleichen Sie die Anzahl mit den typischen Bereichen oben. Passen Sie die Grenzwerte an, wenn Klasse A für Ihr Team viel zu groß oder zu klein ist, um sie effektiv zu verwalten.

Ein praktisches Beispiel

Hier ist eine veranschaulichende Tabelle für 10 Artikel, bereits nach Jahreswert sortiert. Die Zahlen sind Beispiele und stammen nicht von einem echten Unternehmen.

Artikel	Jährliche Einheiten	Stückkosten	Jahreswert	Anteil	Kumuliert	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

Der gesamte Jahreswert beträgt $150,000. Drei Artikel, also 30 Prozent der Liste, machen 73.33 Prozent des Werts aus und landen in Klasse A. Vier Artikel fallen in Klasse B und die letzten drei, die 6 Prozent des Werts ausmachen, fallen in Klasse C.

Zwei Details fallen auf. SKU-04 hat mit Abstand die meisten Einheiten, aber seine geringen Kosten stufen ihn in Klasse B ein. Und bei nur 10 Artikeln werden die Klassenanteile nicht mit den typischen Bereichen übereinstimmen, was bei einer kurzen Liste völlig normal ist.

Wie Sie das Ergebnis grafisch darstellen

Ein Diagramm macht das Muster in einem Meeting leicht verständlich. MSH schlägt vor, den kumulierten Prozentsatz im Verhältnis zur Artikelnummer darzustellen, was die bekannte ABC-Kurve ergibt.

Excel verfügt über ein integriertes Diagramm dafür. Microsoft beschreibt ein Pareto-Diagramm als ein Diagramm, das „sowohl absteigend sortierte Säulen als auch eine Linie enthält, die den kumulierten Gesamtzahl-Prozentsatz darstellt“. Um eines zu erstellen, wählen Sie die Artikelnamen und Jahreswerte aus und wählen Sie dann Einfügen, Statistikdiagramm einfügen und Pareto.

Fügen Sie zwei horizontale Linien oder Beschriftungen bei Ihren Grenzwerten hinzu, z. B. bei 80 und 95 Prozent, damit Betrachter sehen können, wo jede Klasse beginnt. Unser Leitfaden zur Erstellung eines Pareto-Diagramms mit KI befasst sich eingehender mit dem Diagramm selbst.

Wahl Ihrer Grenzwerte

Es gibt keinen einzig richtigen Grenzwert. MSH erklärt, dass die Wahl „davon abhängt, wie sich Menge und Wert auf die Artikel in der Liste verteilen“. Sie hängt auch davon ab, „wie die Ergebnisse der ABC-Analyse verwendet werden sollen“.

Die Managementkapazität ist die praktische Grenze. MSH bringt es direkt auf den Punkt: „Die Zuordnung von Artikeln zu Klasse A muss auf der Managementkapazität basieren.“ Wenn Ihr Team jeden Monat 50 Artikel genau überprüfen kann, verfehlt eine Klasse A von 300 Artikeln ihren Zweck.

Einige gängige Ansätze:

• Wertgrenzen. A bis zu 80 Prozent des Werts, B bis zu 95 Prozent, C für den Rest. Dies ist die oben verwendete Methode.
• Grenzwerte nach Artikelanzahl. Die obersten 20 Prozent der Artikel nach Wert werden zu A, die nächsten 30 Prozent zu B und der Rest zu C.
• Feste Listen. Einige Teams legen Klasse A als die obersten 25 oder 50 Artikel fest, unabhängig von ihrem Anteil am Wert.

Wofür auch immer Sie sich entscheiden: Schreiben Sie es auf und verwenden Sie es jedes Mal. Der Vergleich der Klassen dieses Quartals mit denen des letzten Quartals funktioniert nur, wenn die Grenzwerte gleich bleiben.

Was mit jeder Klasse zu tun ist

Der Sinn der ABC-Analyse besteht darin, den Aufwand dort zu betreiben, wo das meiste Geld liegt. Das MSH-Kapitel listet mehrere Möglichkeiten auf, wie die Ergebnisse genutzt werden können:

• Klasse-A-Artikel häufiger bestellen. MSH schreibt, dass die Bestellung von Klasse-A-Artikeln „häufiger und in kleineren Mengen zu einer Reduzierung der Lagerhaltungskosten führen sollte“.
• Preise für Klasse-A-Artikel zuerst verhandeln. „Preissenkungen für Artikel, die in der Analyse als A-Produkte eingestuft wurden, können zu erheblichen Einsparungen führen“, so das Kapitel.
• Bestände von Klasse-A-Artikeln häufiger zählen. MSH stellt fest, dass „zyklische Bestandszählungen durch die ABC-Analyse gesteuert werden sollten, mit häufigeren Zählungen für Klasse-A-Artikel“.
• Bestellstatus von Klasse-A-Artikeln überwachen. Ein unerwarteter Engpass bei einem Klasse-A-Artikel kann zu kostspieligen Notkäufen führen.

Für Klasse-C-Artikel können einfachere Regeln gelten, wie z. B. größere, weniger häufige Bestellungen und seltenere Zählungen. Klasse B liegt dazwischen. Wenn Ihnen Ladenhüter Sorgen bereiten, lässt sich unser Leitfaden zum Erkennen von langsam drehenden Lagerbeständen hervorragend mit dieser Analyse kombinieren.

Schneller ans Ziel mit KI

Die Excel-Schritte dauern nur wenige Minuten, sobald die Daten bereinigt sind. Das Bereinigen des Exports und das Wiederholen der Arbeit in jedem Quartal nimmt jedoch mehr Zeit in Anspruch.

Ein KI-Arbeitsbereich kann die Berechnungen und die Sortierung in einer einzigen Anfrage erledigen. Laden Sie den Bestands- oder Einkaufsexport bei Powerdrill Bloom hoch und fragen Sie in natürlicher Sprache nach einer ABC-Analyse mit Ihren Grenzwerten. Bitten Sie um den Jahreswert, den Anteil, den kumulierten Prozentsatz und die Klasse für jeden Artikel sowie um ein Pareto-Diagramm.

Überprüfen Sie es dann wie jede andere Tabellenkalkulation. Gleichen Sie den gesamten Jahreswert mit Ihrer eigenen Summe ab und machen Sie Stichproben bei zwei Artikeln in jeder Klasse. Unsere Seite zum Excel-KI-Assistenten beschreibt diese Art von Tabellenkalkulationsarbeit ausführlicher. Für einen breiteren Überblick über Prognosetools finden Sie hier eine Zusammenfassung der KI-Tools für Bestands- und Nachfrageprognosen.

Häufige Fehler, die Sie vermeiden sollten

• Mischen von Zeiträumen. Zwölf Monate für einen Artikel und sechs für einen anderen machen die Anteile aussagelos.
• Verwendung von Einheiten anstelle des Werts. Die Klassen hängen von den Einheiten multipliziert mit den Kosten ab, nicht von den Einheiten allein.
• Vergessen zu sortieren, bevor der kumulierte Gesamtwert berechnet wird. Ein kumulierter Prozentsatz auf einer unsortierten Liste ordnet Artikel der falschen Klasse zu.
• Klassen als dauerhaft betrachten. Führen Sie die Analyse jedes Quartal oder Jahr erneut durch, da Artikel zwischen den Klassen wechseln.
• Grenzwerte, die die Kapazität ignorieren. Eine Klasse-A-Liste, die zu lang ist, um sie genau zu verwalten, erhält nicht mehr Aufmerksamkeit als Klasse B.
• Ignorieren kritischer, günstiger Artikel. Ein Artikel mit geringem Wert kann dennoch die Arbeit lahmlegen, wenn er ausgeht. Das MSH-Kapitel kombiniert die ABC-Analyse mit einer separaten Bewertung von lebenswichtigen, wesentlichen und nicht wesentlichen Artikeln.

Wenn Ihre Artikelliste aus einem unordentlichen Export stammt, können Sie Powerdrill Bloom ausprobieren, um die erste ABC-Tabelle und das erste Diagramm zu erstellen.

Häufig gestellte Fragen

Was ist die ABC-Analyse im Bestandsmanagement?

Die ABC-Analyse sortiert Artikel anhand ihres jährlichen Verbrauchswerts in drei Klassen. Klasse-A-Artikel sind die wenigen, auf die der Großteil des Werts entfällt. Klasse-C-Artikel sind die vielen, die nur wenig ausmachen, und Klasse B liegt dazwischen. Sie hilft Teams dabei, ihre Kontrollbemühungen dort zu bündeln, wo das meiste Geld liegt.

Wie berechnet man eine ABC-Analyse in Excel?

Multiplizieren Sie die jährlichen Einheiten mit den Stückkosten für jeden Artikel und teilen Sie das Ergebnis durch die Gesamtsumme, um den Anteil jedes Artikels zu erhalten. Sortieren Sie nach Wert vom größten zum kleinsten, fügen Sie einen kumulierten Gesamtwert der Anteile hinzu und weisen Sie die Klassen mit einer Formel wie WENNSS zu. Grenzwerte von 80 und 95 Prozent liegen innerhalb der typischen Bereiche im MSH-Kapitel.

Welche Prozentsätze gelten für die ABC-Analyse?

Eine gängige Richtlinie besagt, dass Klasse A 10 bis 20 Prozent der Artikel und 75 bis 80 Prozent des Werts ausmacht. Klasse B umfasst weitere 10 bis 20 Prozent der Artikel und 15 bis 20 Prozent des Werts. Klasse C macht 60 bis 80 Prozent der Artikel und 5 bis 10 Prozent des Werts aus.

Wie lautet die Formel für die ABC-Klassifizierung in Excel?

Verwenden Sie bei einem kumulierten Prozentsatz in Spalte F und Daten ab Zeile 3 die Formel =WENNSS(F3<=0,8;"A";F3<=0,95;"B";WAHR;"C"). Ändern Sie 0,8 und 0,95 entsprechend Ihren eigenen Grenzwerten. Verschachtelte WENN-Formeln können dieselbe Aufgabe erfüllen.

Warum ist die ABC-Analyse wichtig?

Sie zeigt, wohin der Großteil des Geldes für den Lagerbestand fließt, sodass Teams diese Artikel genauer verwalten können. Typische Anwendungen sind die häufigere Bestellung von Klasse-A-Artikeln, die vorrangige Verhandlung ihrer Preise und ihre häufigere Zählung. Zudem deckt sie Ausgaben auf, die nicht mit den Plänen übereinstimmen.

Quellen: Management Sciences for Health, MDS-3 Kapitel 40: Analyse und Kontrolle von Arzneimittelausgaben · Ravinder und Misra, ABC-Analyse für das Bestandsmanagement (2014) · Microsoft Support, Funktion SORTIEREN · Microsoft Support, Funktion WENNSS · Microsoft Support, Ein Pareto-Diagramm erstellen.