So geht’s: ABC-Analyse in Microsoft Excel in wenigen Minuten

ABC

Das neue ABC der idealen Vertriebsplanung – Teil 2

Im ersten Teil der Serie haben wir besprochen, warum nicht alle Kunden gleich sind – und weshalb daher eine provisionsorientierte Vertriebsplanung zwar für Umsätze sorgt, aber oft nicht für nachhaltigen Verkaufserfolg. In diesem zweiten Teil erhalten Sie nun eine ganz praktische Einleitung, um Ihr Kunden-Portfolio zu analysieren. Teil 3 in der kommenden Woche zeigt dann noch einmal detailliert auf, was Vertriebsleitung und Management tun können, um die entsprechenden Strukturen für eine gute, strategische Vertriebsplanung zu schaffen.

Was sind Ihre A-, B- und C-Kunden? Die ABC-Analyse liefert schnell Aufschluss. In diesem Beitrag zeigen wir Ihnen, wie Sie mit Microsoft Excel in wenigen Minuten eine solche ABC-Analyse erstellen. Dabei werden sowohl die Grundvoraussetzungen an die benötigten Daten beschrieben, als auch die genaue Vorgehensweise in Microsoft Excel. Darüber hinaus erfahren Sie, wie Sie die Ergebnisse interpretieren.


Ein Wort zu Microsoft Excel

Sämtliche Screenshots stammen aus Microsoft Excel 2013. Die Menüführung in den Versionen 2010 und 2007 ist weitgehend identisch, die benutzen Formeln sind es in allen Fällen. Die Menüführung in der Version 2003 und früheren Versionen unterscheidet sich deutlich; die benutzen Formeln sind jedoch auch hier gültig.

Beispieldaten: Zehn Werte für die Übersicht

Zur besseren Übersichtlichkeit haben wir ein Beispiel mit lediglich zehn Werten gewählt. Sämtliche benutzen Formeln sind jedoch so ausgelegt, dass Sie sie auf Datenmengen beliebiger Größe anwenden können. Ein Ändern der Formel ist, soweit die Spaltenreihenfolge eingehalten wird, nicht notwendig. Auch erhöht sich der Zeitaufwand für größere Datenmengen nur marginal.

Schritt 1: Ausgangsdaten ermitteln

Im ersten Schritt sind die Daten, die in die Analyse einbezogen werden sollen, auszuwählen. Folgende Informationen müssen Sie bestimmen:

  • Gegenstand der Untersuchung, z.B. Produkte, Kunden, Länder, etc.,
  • Zeitraum der Untersuchung, z.B. ein Tag, eine Woche, ein Monat, ein Jahr, etc..
  • Maß der Untersuchung, z.B. Verkaufsmengen, Umsatz, Lagermengen, Produktionsmengen, etc.

Im nachstehenden Beispiel haben wir als Gegenstand zehn Produkte aus der Warengruppe Lebensmittel eines Supermarkts ausgewählt. Für den Zeitraum der Untersuchung ist eine Woche (Montag – Samstag) angesetzt. Als Maß der Untersuchung dient die Verkaufsmenge in Stück.

Abbildung 1: Verkaufsmenge pro Stück und Tag

Abbildung 1: Verkaufsmenge pro Stück und Tag (Klick auf das Bild zeigt es in vergrößerter Ansicht)

Schritt 2: Aufbereitung der Daten

Im zweiten Schritt werden die Verkaufsmengen pro Tag aufsummiert. Dazu nutzen wir in einer neuen Spalte den folgenden Befehl:

=SUMME(B3:G3)

Abbildung 2: Einfügen der Summen pro Woche

Abbildung 2: Einfügen der Summen pro Woche (Klick auf das Bild zeigt es in vergrößerter Ansicht)

Anschließend übertragen wir den Befehl, z.B. per Copy & Paste, in die Zellen H4 bis H12. Darauf folgend ersetzen wir die Formel in der Spalte ‚Summe‘ durch die tatsächlichen Werte:

  • Markieren aller Werte in Spalte H.
  • Kopieren der Werte in Spalte H.
  • Rechtsklick in Zelle H3.
  • Im Menü ‚Einfügeoptionen‘ die zweite Schaltfläche von links auswählen (siehe folgender Screenshot).
Abbildung 3: Formeln mit Werten ersetzen

Abbildung 3: Formeln mit Werten ersetzen (Klick auf das Bild zeigt es in vergrößerter Ansicht)

Im Anschluss daran können Sie die Spalten B bis G entfernen.

Schritt 3: Vorbereiten der ABC Analyse

Zu Beginn von Schritt 3 sollte die Tabelle wie folgt aussehen:

Abbildung 4: Tabelle als Ausgangsbasis für die ABC Analyse

Abbildung 4: Tabelle als Ausgangsbasis für die ABC Analyse


Als Erstes sortieren wir nun Spalte B ‚Summe‘ absteigend. Dazu gehen wir wie folgt vor:

  • Markieren der Zelle B3 (erster Wert in der Tabelle).
  • Öffnen des Menüs ‚Daten‘.
  • Auswahl der Schaltfläche aus dem Untermenu ‚Sortieren und Filtern‘.
Abbildung 5: Tabelle, nachdem die Werte in absteigender Reihenfolge sortiert wurden

Abbildung 5: Tabelle, nachdem die Werte in absteigender Reihenfolge sortiert wurden (Klick auf das Bild zeigt es in vergrößerter Ansicht)

Schritt 4: Ermittlung der ABC-Rohdaten

Jetzt ermitteln wir die ABC-Rohdaten. Dazu wird berechnet, zu wie viel Prozent der Gesamtverkäufe ein jedes Produkt beiträgt. Das Ergebnis wird dabei als laufende Summe von oben nach unten berechnet. Folgende Formel fügen wir in Zelle C3 ein und kopieren sie anschließend in die Zellen C4 bis C12:

=SUMME($B$3:B3)/SUMME($B$3:$B$12)

Erläuterung der Formel:

Der erste Teil ‚SUMME($B$3:B3)‘ dient dazu, die laufende Summe der Verkaufsmengen in Spalte B zu berechnen. Dabei werden die Zeichen ‚$‘ benutzt, um den Anfang der laufenden Summe ($B$3) zu fixieren. Mit jeder neuen Zeile wird sich das Ende der laufenden Summe (B3) um eine Reihe nach unten verschieben.

Der zweite Teil ‚SUMME($B$3:$B$12)‘ berechnet die gesamte Verkaufsmenge aller Produkte. Die Zeichen ‚$‘ werden dazu benutzt sowohl den ersten Wert (Zelle B3), als auch den letzten Wert (Zelle B12) der laufenden Menge zu fixieren. (Siehe nachstehender Screenshot mit Beispiel für Zelle C9)

Im Anschluss daran sollte die Tabelle wie folgt aussehen:

Abbildung 6: Tabelle nachdem die laufende Summe in % ausgerechnet wurde.

Abbildung 6: Tabelle nachdem die laufende Summe in % ausgerechnet wurde.


Die laufende Summe über alle Produkte entspricht 100%. Ob die Kalkulationen richtig ausgeführt wurden, erkennen Sie daran, dass der letzte Wert (Zelle C12) den Wert ‚1‘ – 100% – anzeigt.

Schritt 5: Ermittlung der ABC Faktoren

Bitte bedanken Sie sich mit einem LIKE, um weiter zulesen. Danke!

Im ersten Teil der Serie haben wir besprochen, warum nicht alle Kunden gleich sind – und weshalb daher eine provisionsorientierte Vertriebsplanung zwar für Umsätze sorgt, aber oft nicht für nachhaltigen Verkaufserfolg. Im dritten Teil, der im Januar erscheint, gehen wir darauf ein, wie Sie die Ergebnisse der Analyse in Vertriebsleitung und Management nutzen. – Sie wollen diesen und weitere Beiträge automatisch erhalten? Dann nutzen Sie doch einfach das E-Mail-Abo gleich rechts hier in der Seitenleiste.

Haben Sie dazu Fragen? Dann stellen Sie sie gerne in den Kommentaren unter diesem Beitrag!

Der Autor: Dirk Liebich ist Managing Director und Gründer von Digital Tempus. Digital Tempus betreut mit Standorten in den USA und in Europa weltweit agierende Unternehmen und Konzerne in der Vertriebs- und Operationsplanung.

Kontakt: magazin@digitaltempus.comwww.digitaltempus.de

  1. Mrvn M.05-27-2014

    @Alle: das Komma statt dem Punkt ist wichtig. Das hängt im Einzelfall von den Einstellungen in Excel ab.

    @Alexander:
    Die Formel die du gepostet hast ist nicht richtig formatiert.
    Für Excel in deutscher Sprache muss deine Formel folgendermaße lauten:

    =WENN(C3<=0,8;"A";WENN(C3<=0,95;"B";"C"))

    MfG

    M.M.

  2. Alexander05-01-2014

    Und in wie weit ist die Formel richtig für mich, wenn ich haben möchte:

    Wertanteil 80 % = A
    Wertanteil 15 % = B
    Wertanteil 5 % = C

  3. Alexander05-01-2014

    Hallo zusammen,
    vielen Dank für diese ausführliche Erklärung.

    Es klappt bei mir auch alles, bloß beim letzten Befehl, der Kategorisierung der A-B-C Hersteller zeigt er mir in Excel eine Fehler Meldung an.

    =WENN(C3<=0.8,”A”,WENN(C3<=0.95,”B”,”C”))

    Hier zeigt Excel 2010 mir einen Fehler an, können Sie mir zufällig sagen welchen? :-) Vielen Dank !

  4. Daniel05-13-2013

    Toller Artikel. Gutes Beispiel und dazu einfach erklärt.
    Danke.

Follow

Get every new post delivered to your Inbox

Join other followers: