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 02.06.2022
Autor(en):
Kristoffer Ditz
Bleiben Sie auf dem Laufenden mit unserem Newsletter
Tragen Sie sich für den
kostenfreien und unverbindlichen
Newsletter von Controlling-Portal.de ein und erhalten Sie jeden Monat aktuelle Neuigkeiten für Controller. Wir informieren Sie über neue Fachartikel, über wichtige News, aktuelle Stellenangebote, interessante Tagungen und Seminare. Wir empfehlen Ihnen spannende Bücher und geben Ihnen nützliche Excel-Tipps. Verpassen Sie nie mehr wichtige Diskussionen im Forum und stöbern Sie in Software-Angeboten, die Ihnen den Arbeitsalltag erleichtern.
Beispiel-Newsletter >>
Jetzt Newsletter gratis erhalten
Eigenen Fachbeitrag veröffentlichen?
Sie sind Autor einer Fachpublikation oder Entwickler einer Excel-Vorlage? Gern können Sie sich an der Gestaltung der Inhalte unserer Fachportale beteiligen! Wir bieten die Möglichkeit Ihre Fachpublikation (Fachbeitrag, eBook, Diplomarbeit, Checkliste, Studie, Berichtsvorlage ...) bzw. Excel-Vorlage auf unseren Fachportalen zu veröffentlichen bzw. ggf. auch zu vermarkten.
Mehr Infos >>
Tipps, Charts und Diagramme für Ihre tägliche Arbeit mit Microsoft Excel® im Controlling. Präsentiert von Controlling-Portal.de. Sogenannte Dashboards werden heute vom Management erwartet. Möglichst auf einem Blatt sollen alle wichtigen Kennzahlen auf einem Blick erfassbar sein.
Rollierende Liquiditätsplanung auf Wochenbasis. Mit der Excel-Vorlage „Liquiditätstool“ erstellen Sie schnell und einfach ein Bild ihrer operativen Liquiditätslage für die nächsten (bis zu 52) Wochen.. 
