KHIZHNYAKOVABC-XYZ-Analyse in Excel: Sortiment und Lager gezielt steuern
ABC-XYZ-Sortimentsanalyse – Excel-Vorlage — Zur Vorlage →
In fast jedem Sortiment gilt dasselbe Muster: Wenige Artikel bringen den Großteil des Umsatzes, viele andere liegen herum und binden Kapital. Welche das sind, sieht man im Tagesgeschäft aber selten klar.
Die ABC-XYZ-Analyse macht es sichtbar. Sie kombiniert zwei Fragen: Wie wichtig ist ein Artikel für den Umsatz (ABC)? Und wie gleichmäßig verkauft er sich (XYZ)? In diesem Artikel rechnen wir beides in Excel durch und klären, was Sie mit den neun Gruppen anfangen.
Teil 1: Die ABC-Analyse
Die ABC-Analyse ordnet Artikel nach ihrem Anteil am Umsatz. Üblich sind diese Grenzen:
- A: Artikel, die zusammen die ersten 80 % des Umsatzes bringen
- B: die nächsten 15 % (kumuliert bis 95 %)
- C: die restlichen 5 %
So gehen Sie vor
- Jahresumsatz pro Artikel bilden:
=SUMME(C2:N2)über die zwölf Monatsspalten. - Liste nach Umsatz absteigend sortieren.
- Anteil am Gesamtumsatz:
=O2/SUMME($O$2:$O$300) - Kumulierten Anteil berechnen: in der ersten Zeile gleich dem Anteil, darunter
=Q2+P3. - Gruppe zuordnen:
=WENN(Q2<=80%;"A";WENN(Q2<=95%;"B";"C"))
Beispiel
| Artikel | Umsatz | Anteil | kumuliert | Gruppe |
|---|---|---|---|---|
| Kaffee 1 kg | 42.000 | 42 % | 42 % | A |
| Espressotassen | 25.000 | 25 % | 67 % | A |
| Milchkännchen | 13.000 | 13 % | 80 % | A |
| Entkalker | 9.000 | 9 % | 89 % | B |
| Reinigungstabs | 6.000 | 6 % | 95 % | B |
| Geschenkbox | 5.000 | 5 % | 100 % | C |
Drei von sechs Artikeln bringen 80 % des Umsatzes. In echten Sortimenten mit Hunderten Positionen ist der A-Anteil oft noch kleiner.
Teil 2: Die XYZ-Analyse
Die XYZ-Analyse bewertet, wie stark die Nachfrage von Monat zu Monat schwankt. Das Maß dafür ist der Variationskoeffizient: Standardabweichung geteilt durch Mittelwert.
=STABW.N(C2:N2)/MITTELWERT(C2:N2)
In Excel 2007 heißt die Funktion STABWN. Übliche Grenzen:
- X: bis 10 % – sehr stabile Nachfrage
- Y: 10 bis 25 % – schwankend, etwa saisonal
- Z: über 25 % – unregelmäßig, schwer planbar
Beispiel: Kaffee verkauft sich monatlich zwischen 3.200 und 3.800 EUR, Mittelwert 3.500, Standardabweichung 210. Variationskoeffizient 6 % – also X. Die Geschenkbox verkauft sich vor allem im Dezember: Variationskoeffizient deutlich über 25 %, also Z.
Teil 3: Die 3×3-Matrix
Jetzt kombinieren Sie beide Buchstaben. Daraus ergeben sich neun Gruppen mit klaren Handlungsempfehlungen:
| X (stabil) | Y (schwankend) | Z (unregelmäßig) | |
|---|---|---|---|
| A | immer verfügbar, automatisch nachbestellen | Bestand mit Puffer, Saison beachten | genau beobachten, auf Auftrag beschaffen |
| B | regelmäßig nachbestellen | mittlerer Puffer | Bestand niedrig halten |
| C | einfach halten, große Intervalle | prüfen, ob nötig | Kandidaten zum Auslisten |
Die Gruppe in einer Zelle: =R2&S2, also z. B. „AX“. Die Anzahl Artikel je Gruppe zählt ZÄHLENWENN, den Umsatz je Gruppe SUMMEWENN.
Häufige Fehler
- Zu kurzer Zeitraum. Drei Monate sagen wenig über Schwankungen. Zwölf Monate sind der Standard.
- Mengen und Beträge mischen. Die Analyse muss für alle Artikel in derselben Einheit laufen.
- Neue Artikel mitbewerten. Ein Produkt, das erst seit zwei Monaten im Sortiment ist, landet fast automatisch in C und Z.
- CZ-Artikel blind auslisten. Manchmal sind sie Ergänzungen, ohne die A-Artikel schlechter laufen. Prüfen Sie Zusammenhänge, bevor Sie streichen.
- Einmalig rechnen. Sortimente verändern sich. Wiederholen Sie die Analyse mindestens einmal im Jahr, besser quartalsweise.
Fertige Vorlage: ABC-XYZ-Sortimentsanalyse
Mit unserer Excel-Vorlage sparen Sie sich das Sortieren, Kumulieren und Verketten. Es handelt sich um eine kostenpflichtige Vorlage:
- Auf dem Blatt „Parameter“ legen Sie die Grenzen der Gruppen A, B und X, Y fest – standardmäßig 80 % und 95 % sowie 10 % und 25 %.
- Im Blatt „Daten“ tragen Sie bis zu 300 Artikel mit Verkäufen für 12 Monate ein. Summe, Anteil, kumulierter Anteil, ABC- und XYZ-Gruppe werden daneben automatisch berechnet, die Gruppen ordnet die Tabelle selbst zu.
- Das „Ranking“ zeigt alle Artikel nach Umsatz absteigend mit ihren Gruppen.
- Die „Matrix“ zeigt, wie viele Artikel und wie viel Umsatz in jeder der neun Gruppen liegen, gibt für jede Kombination eine kurze Empfehlung und stellt alles in einem Diagramm dar.
Die Datei funktioniert ohne Makros in Excel 2007 und neuer sowie in LibreOffice. Im Paket sind eine leere Tabelle und ein ausgefülltes Beispiel mit fiktiven Daten.
Häufige Fragen
Sollte ich nach Umsatz oder nach Deckungsbeitrag analysieren?
Umsatz ist der Standard und am einfachsten zu beschaffen. Wenn Ihre Margen stark unterschiedlich sind, liefert eine ABC-Analyse nach Deckungsbeitrag aber oft die ehrlichere Antwort darauf, welche Artikel Ihnen wirklich Geld bringen.
Funktioniert die Analyse auch für Dienstleistungen oder Gastronomie?
Ja. Ein Café kann Getränke und Speisen genauso nach Umsatz und Nachfragestabilität einordnen. Statt Lagerbestand geht es dann eher um Einkauf, Vorbereitung und Karte.
Wie gehe ich mit saisonalen Artikeln um?
Sie landen meist in Y oder Z, obwohl ihre Nachfrage gut vorhersehbar ist. Behandeln Sie sie separat und planen Sie mit dem Vorjahresverlauf statt mit dem Jahresdurchschnitt.
Was mache ich mit Monaten ohne Verkauf?
Tragen Sie eine Null ein, keine leere Zelle. Nur so fließt der Monat korrekt in Mittelwert und Standardabweichung ein.
