50
In Schritt 5 muss sich über die
Kategorisierung
Gedanken gemacht werden. Die
80/20-Regel
orientiert sich an dem Pareto-Prinzip:
Kunden,
die 80% des Umsatzes ausmachen
, werden
als A klassifiziert. B- und C- Kunden definieren
nun jeweils kumuliert die verbleibenden 10 %
des Umsatzes. Ein weitere Variante ist die
60/90-Regel:
Kunden, deren Umsätze zu-
sammen mehr als 60 % des Gesamt-DBs
ausmachen
, entfallen in die Kategorie „A“. Kun-
den im Bereich von 60% bis 90% des Gesamt-
DBs fallen in die Kategorie „B“; die restlichen
10% in die weniger wichtige C-Kategorie.
Im nachfolgenden Beispiel wird die
60/90-Re-
gel
angewandt. Die Kategorisierung erfolgt da-
bei über eine WENN-Funktion. Wenn ein Wert
in Spalte H, also die Kumulierung der DBs in
Prozent, kleiner-gleich 60% ist, wird er der A-
Kategorie zugeordnet; ansonsten bleibt er leer
(in Excel gibt man hierfür die Hochkommas ein
““). Für die Kategorisierung in B und C benötigt
man ebenfalls die WENN-Funktion, allerdings
in Kombination mit der einfachen Funktion
UND. In diesem Fall ist die WENN-Funktion nur
erfüllt, wenn alle in UND formulierten Kriterien
erfüllt sind. Für die B-Kategorie (Spalte K) gilt:
Spalte J muss leer sein (der Kunde darf also
kein A-Kunde sein) und der kumulierte DB in
Prozent muss kleiner-gleich 90% sein. Für C
gilt ganz einfach: SPALTE K und J dürfen keine
Werte enthalten, damit ist der Kunde automa-
tisch ein C-Kunde (vgl. auch Abbildung 1).
Excel-Wissen III: WENN-Funktion
WENN-Funktionen gehören zu den Standard-
Formeln in Excel und ermöglichen die Analyse
einer Datenmenge nach einer definierten Be-
dingung (beispielsweise größer/kleiner/gleich/
ungleich). Häufig werden
zwei Werte mitein-
ander verglichen
– und je nachdem wie der
Vergleich ausfällt, wird eine bestimmte Aktion
oder Berechnung durchgeführt. WENN gibt ei-
nen bestimmten Wert zurück, wenn eine ange-
gebene Bedingung als WAHR bewertet wird,
und einen anderen Wert, wenn die Bedingung
als FALSCH bewertet wird. Zur Anwendung von
WENN werden daher drei Argumente benötigt:
·
·
Prüfung: ist ein Wert oder eine Zeichenfolge,
der/die WAHR oder FALSCH sein kann
·
·
Dann_Wert: ist der Wert, der als Ergebnis aus-
gegeben wird, wenn die Prüfung WAHR ergibt
(dies kann auch eine andere Formel sein)
Wie beschrieben, muss in Schritt 3 eine
abstei-
gende Sortierung
erfolgen. In Excel lässt sich
dies am besten – da einfach, flexibel und trans-
parent – über die Funktion KGRÖSSTE (unter an-
deren zusammen mit einer einfachen, absteigen-
den Hilfs-Zahlenliste, hier von 1-15) erledigen.
Excel-Wissen I: KGRÖSSTE/KKLEINSTE
Mit MIN und MAX kann der kleinste und größ-
te Wert aus einer Daten-Tabelle ausgegeben
werden. Soll jedoch nicht nur der größte oder
kleinste Wert, sondern der zweit, dritt, viert
größte/kleinste Wert usw. ermittelt und bei-
spielsweise
eine sortierte Reihe oder Top/
Flop-Analyse erstellt
werden, kann dies am
besten mit den Funktionen KKLEINSTE und
KGRÖSSTE(Matrix;k) gelöst werden. Diese
geben den k-kleinsten/größten Wert einer
Datengruppe, abhängig vom gewünschten
Rang/k zurück.
Eine sinnvolle dynamische Zuordnung der Kun-
den-Namen zu den nun sortierten DBs bekom-
men Sie über die Verschachtelung der Funktio-
nen INDEX und VERGLEICH. Der bei Control-
lern oft so beliebte SVERWEIS lässt sich bei der
Suche nach Zahlen oft nicht einsetzen, da die-
ser nur nach rechts suchen kann; die zugehöri-
ge Beschriftung in einer Tabelle aber meist
links von der gesuchten Zahl steht.
Excel-Wissen II: INDEX(VERGLEICH)
Durch Verschachteln von INDEX(VERGLEICH)
können Verweisprobleme effizient gelöst wer-
den – insbesondere in Fällen, in denen der
SVERWEIS nicht angewendet werden kann.
VERGLEICH(Suchkriterium;Suchmatrix; [Ver-
gleichstyp]) gibt die relative Position eines
definierten Suchobjekts in einer Matrix aus,
findet also Anwendung bei der Suche nach
der Position eines Wertes. Diese Position wird
dann als Argument Zeile/Spalte von
INDEX(Matrix;Zeile;Spalte) eingesetzt, die
dann den gewünschten Inhalt einer Matrix/
Tabelle ausgibt.
Im vierten Schritt muss eine
einfache prozen-
tuale Kumulierung der Deckungsbeträge
erfolgen. Hierzu wird eine weitere Hilfsspalte
benötigt. Jede Zeile wird dabei durch den Ge-
samtumsatz geteilt – natürlich wird diese für
den letzten Wert (hier 15 Kunden) der Tabelle
immer 100% betragen.
(z. B. Umsatz, Deckungsbeitrag, Einkaufsvolu-
men, Kosten, etc.). Die Datenerfassung erfolgt
in einer so genannten Wert-Mengen-Tabelle.
3.
Sortieren:
Die Mitglieder der Gruppe wer-
den sortiert. Die Sortierreihenfolge wird
durch den berechneten Wert des zu analy-
sierenden Parameters bestimmt. Der für das
Unternehmen wichtigste Wert wird an den
Anfang der Liste gesetzt, so dass eine ab-
steigende Sortierung entsteht.
4.
Berechnung/Kumulierung:
Für jedes Mit-
glied der Gruppe wird festgestellt, welchen
prozentualen Anteil der Wert an der Summe
aller Parameterwerte hat. Es wird z. B. für je-
den Kunden der Liste festgestellt, wie viel
Prozent des Gesamtumsatzes der Liste auf
den jeweiligen Kunden entfallen. Die gefun-
denen Prozentsätze werden kumuliert.
5.
Kategorisierung:
Die Mitglieder der Gruppe
werden jetzt in die drei Klassen eingeteilt.
Diese Einteilung muss individuell – je nach
Ziel der Analyse – erfolgen. Dazu wird meis-
tens eine Einteilung der kumulierten Prozent-
sätze verwendet. Die Gruppenmitglieder
werden dann in der Reihenfolge der Sortie-
rung betrachtet (siehe hierzu die konkrete
Umsetzung anhand des Beispielfalls 3A).
6.
Ableitung von Maßnahmen/Visualisie-
rung:
Zu wissen, welche Elemente wichtig
sind, reicht noch nicht:
es sollten Handlun-
gen abgeleitet werden, die die Analyse-
Ergebnisse auch in konkrete Verbesse-
rungen umsetzen
. Eine visualisierte Dar-
stellung kann dabei helfen, diese besser zu
verdeutlichen.
Umsetzung von ABC-Analysen
in Excel
A.) Berechnung (60/90-Regel)
Die Datenerfassung erfolgt in einer einfachen
Wert-Mengen-Tabelle
. Als Werte-Parameter
nehmen wir nachfolgend den
Deckungsbei-
trag
(DB). Die Menge wird hier vereinfachend
durch die bloße
Anzahl der Kunden
bestimmt,
die aus einem Vor-System nach Excel exportiert
wurden. Eine Erweiterung, indem nicht nur die
Anzahl der Kunden, sondern auch deren Zahl
an Bestellungen betrachtet wird, findet sich in
der zum Beitrag gehörenden Excel-Datei im Ar-
beitsblatt „Bestellungen“.
Visualisierung von ABC-Analysen mit Pareto-Diagrammen in Excel
