Excel-Tipp: ABC Analyse mit vertikalen Linien

Premium

Für uns als Controller ist es erforderlich den Managern Tools zur Verfügung zu stellen, um eine Entscheidungshilfe zu liefern. Speziell im Vertriebscontrolling müssen die Key Account Manager bzw. Vertriebsleiter auf Informationen der Kunden zurückgreifen. Damit die Controller nicht nur die nackte Zahl liefern, ist es hilfreich eine ABC-Analyse als Tool zu verwenden.
Jedoch werden dabei zu oft “einfache“ Standard-Modelle verwendet, die keine Aufmerksamkeit bei den Key Account Managern erzielen. Besser ist es, die ABC Analyse mit vertikalen Linien zu verfeinern, um einen besseren visuellen Eindruck zu bekommen.

Aufbau der Excel Tabelle

Zunächst erstellen wir hierzu die Excel-Tabelle. Zur Vereinfachung verwenden wir hier imaginäre Daten von 100 Kunden (aus Platzgründen werden hier nur 10 Kunden angezeigt).



Rechts neben der Tabelle bauen wir eine Hilfsspalte und verwenden hier für jeden Umsatzwert die Formel

=D5-ZEILE()/1000000

Dies ist gleich für die nötige Sortierung erforderlich. Sind zum Beispiel 2 Kunden mit dem gleichen Umsatz in der Tabelle vorhanden, kann Excel hier nicht mehr richtig sortieren.

Als nächstes erstellen wir eine weitere Tabelle für die Sortierung. Manuell schreiben wir die Werte 1 bis 100. Rechts daneben (Kunde) verwenden wir die Formel:

=INDEX($C$5:$C$104;VERGLEICH(KGRÖSSTE($E$5:$E$104;ZEILE(E1));$E$5:$E$104;0))

Der Zellbereich $C$5:$C$104 enthält die Kundennamen und in den Zellen $E$5:$E$104 sind die Umsätze enthalten. Die Dollarzeichen sorgen dafür, dass der Zellbereich nicht verschoben wird. Lediglich bei der ZEILE wird kein $-Zeichen verwendet, da hier das Ranking ermittelt wird. Für die Ermittlung des Umsatzes verwenden wir die SVERWEIS-Formel:

=SVERWEIS(H5;$C$5:$D$104;2;0)



Wir erweitern die Tabelle mit Kumuliert und verwenden die Formel:

=SUMME($I$5:I5)

Wenn diese hinunterkopiert wird, erscheinen die kumulierten Werte. Der letzte Wert ist logischerweise auch das Gesamtergebnis.



Neben Kumuliert ermitteln wir den prozentualen kumulierten Wert, welchen wir für die Ermittlung benötigen, ob es sich um einen A-, B-, oder C-Kunden handelt.

Dies ermitteln wir mit der folgenden Formel:

=WENN(K5<=65%;"A";WENN(K5>=90%;"C";"B"))

In diesem Beispiel wurde festgelegt, dass alle Kunden die einen Umsatz von 65% des gesamten Ergebnisses erzielen A-Kunden sind. Kunden, die die letzten 10% (hier 90% für die Berechnung) erzielen sind C-Kunden und, schlussendlich bleiben noch die B-Kunden übrig. Ergänzen Sie die Tabelle noch mit der Anzahl, also das jeder Kunde einmal gezählt wird. Selbstverständlich können auch andere Werte für die Definition herangezogen werden.



Die Anzahl benötigen wir, um festzulegen, mit wie vielen Kunden wir A-, B-, oder C-Umsätze erzielen.

Hier wurde die Formel SUMMEWENN für die Ermittlung der Anzahl der Kunden A,B, oder C verwendet.

Letzte Änderung W.V.R am 09.04.2020
Autor(en): Kristoffer Ditz