So erstellst Du in Excel eine ABC-Analyse (Teil I)

Eine betriebswirtschaftliche Methode zur Klassifizierung und Sortierung von Kunden. Materialien, Erzeugnissen oder anderen Objekten nach ihrer Bedeutung für das Unternehmen ist die ABC-Analyse.
Üblicherweise werden die Objekte in drei Klassen unterteilt:
A – sehr wichtig
B – wichtig
C – weniger wichtig
Zum Herausarbeiten der Bedeutung werden relevante Kenngrößen verwendet und danach wird sortiert.

Das Vorgehen will ich mit einem Beispiel aus der Materialwirtschaft bzw. Warenwirtschaft erklären.

Im Beispiel soll der Jahresbedarf an Materialien klassifiziert werden und eine Auswertung der Ergebnisse erfolgen.

Datenerfassung

Dazu lege ich mir nachfolgende Tabelle an und trage die Daten zusammen.

Ermittlung der Rangzahlen

Im ersten Schritt soll dem Jahresbedarf nach Mat.-Nr. eine Rangzahl zugeordnet werden. Excel bietet dazu unter den Statistischen Funktionen die Funktion RANG an.

Ich kopiere die Erfassungstabelle und lege sie in meiner Tabelle im Bereich B24:E35 ab.

In Zelle F26 schreibe ich die Formel:
=RANG(E26;E$26:E$35;0)
und ziehe sie mit dem Ausfüllkästchen bis F35 runter. Die Tabelle sieht nun so aus:

Sortierung

Im nächsten Schritt will ich die Tabelle etwas umstellen und erweitern. Der Rang soll in der ersten Spalte stehen. Die neue Tabelle soll so aussehen:

Die Spalte B habe ich manuell mit den Rangzahlen 1 bis 10 versehen. Die weiteren Spalten fülle ich mit diesen Formeln und hole die erforderlichen Daten aus der Vortabelle:
C41 =BEREICH.VERSCHIEBEN($F$25;VERGLEICH($B41;F$26:F$35;0);-4)
D41 =BEREICH.VERSCHIEBEN($F$25;VERGLEICH($B41;F$26:F$35;0);-1)
E41 =D41/$D$51*100
F41 =E41
F42 =F41+E42
Mit dem Ausfüllkästchen fülle ich die Spalten schließlich bis Zeile 50.
Ich füge nun eine weitere Spalte an und bezeichne sie mit „Wertgruppe“. Zelle G41 erhält diese Formel:
=WENN(F41<$E$54;”A”;WENN(F41<$E$57;”B”;”C”))
Auch diese Formel ziehe ich bis Zeile 50 herunter.
Da ich die Wertgruppen A und B noch nicht festgelegt habe, steht jetzt für alle Ränge die Wertgruppe „C“. Das ist so aber nicht gewollt. Ich lege die Gruppen also in einer gesonderten kleinen Tabelle fest:

Was lese ich daraus? 15% der Materialmengen haben einen Gesamtwert von kumuliert 80%. 35% der Materialmengen haben einen Gesamtwert von kumuliert 95% und die restlichen 50% Menge füllen bis zum Gesamtwert von 100% auf.
Sobald ich diese Tabelle fertig gestellt habe, zeigt meine sortierte Tabelle die richtigen Wertgruppen an:

Bleibt jetzt noch, das Ergebnis der ABC-Analyse zusammen zu stellen.

Ergebnis

Dazu erstelle ich eine weitere Tabelle:

In die Tabelle habe ich diese Formeln eingepflegt und mit dem Ausfüllkästchen herunter gezogen:
C67 =ZÄHLENWENN(G$41:G$50;B$67:B$69)
C70 =SUMME(C67:C69)
D67 =C67/$C$70*100
E67 =SUMMEWENN(G$41:G$50;B$67:B$69;D$41:D$50)

Was lässt sich daraus nun ablesen?
Zur Wertgruppe A gehören drei Materialien (Nr. 120, 121, 125), das macht 30% aller Materialien. Der Jahresbedarf in dieser Wertgruppe beträgt 70.505 € von gesamt 89.117 €.
Im Grunde lassen sich diese Informationen bereits aus der Erfassungstabelle ablesen. Bedenke aber, in meinem Beispiel geht es nur um 10 Material-Nr. In der Praxis werden weitaus mehr Materialpositionen vorhanden sein.

Ein Tipp für alle Blogger, Twitterer usw.:
Die ABC-Analyse kann durchaus auch genutzt werden, um die schlagkräftigsten Beiträge, die besten Kategorien oder die Tweets mit den meisten Retweets zu ermitteln, wenn diese Informationen nicht schon aus anderen Tools abgerufen werden können.
Probier es doch mal aus und schreibe mir. Danke!
Im Teil II stelle ich eine zweite Möglichkeit, eine ABC-Analyse durchzuführen, vor.

Hier kannst Du Dir die Beispieldatei

dowloaden.