So erstellst Du in Excel eine ABC-Analyse (Teil II)

In Teil I zum Thema ABC-Analyse habe ich ein Beispiel aus dem Kostenbereich der Materialwirtschaft betrachtet. Das Ergebnis wurde über mehrere Tabellen mit Hilfsspalten errechnet.
Dieser Teil II beschreibt ein Beispiel aus dem Umsatzbereich mit Hilfsspalten und ohne Hilfsspalten. Klassifiziert werden sollen Beratungs- und Dienstleistungen eines fiktiven Excel-Beraters.

Beispiel mit Hilfsspalten

In einer Erfassungstabelle sind die differenzierten Leistungen, die dazu verwendeten Tage (eine Person) sowie der erzielte Umsatz bei 500 € pro Tag pro Person zusammen getragen. Die Tabelle ist nach Umsatz absteigend sortiert.

Zunächst erweitere ich die Tabelle um einige Hilfsspalten. Wichtig sind mir noch der kumulierte Umsatz und der daraus resultierende Anteil am Gesamtumsatz.

Im nächsten Schritt gilt es, die eigentliche ABC-Analyse durchzuführen. Dazu trage ich in F49 diese Formel ein
=WENN($D49<$D$60*0,85;“A“;WENN($D49<$D$60*0,95;“B“;“C“))
und ziehe sie mit dem Ausfüllkästchen bis F60 runter.

Dabei war zu berücksichtigen, dass A die Produkte bezeichnen sollte, die zur Erreichung von 85% des Umsatzes beigetragen haben, B bis über 95% und C den kleinen Rest.
Zur Kontrolle mache ich ein paar kleine Nebenrechnungen. 85% des Gesamtumsatzes von 104.000 € sind 88.400 €. Bis zur Zeile Toolentwicklung erreicht der Umsatz 86.500 €, eine Zeile weiter schon 91.000 €.
Zu sehen ist das auch am kum. Anteil. Bis Toolentwicklung sind 83,17%, eine Zeile weiter 87,50% erreicht.

85% liegen demnach dazwischen. Und 88.400 € liegen zwischen 86.500 und 91.000 €. Allerdings trägt ein Teil des Umsatzes für die Tooleinführung auch noch zu den 85% bei, ein anderer Teil geht aber schon in die 95% ein.
Für die Analyse habe ich bestimmt, dass eine Leistung vollständig in den 85% enthalten sein muss, um die Bewertung „A“ zu bekommen. Ansonsten erhält sie die Bewertung „B“. Ebenso verhält es sich mit den 95%.

Beispiel ohne Hilfsspalten

Das Beispiel ohne Hilfsspalten gestaltet sich nicht komplizierter. Letztlich ist nur eine Tabelle vorhanden. Ausgangspunkt ist wieder die anfängliche Erfassungstabelle.

Ich füge die Spalte „ABC“ hinzu und versehe die Zelle D68 mit dieser Formel:
=WENN(SUMME($C$71:$C71)<$C$83*0,85;“A“;WENN(SUMME($C$71:$C71)<$C$83*0,95;“B“;“C“))
Mit dem Ausfüllkästchen ziehe ich die Formel bis D79 runter.
Das Ergebnis entspricht dem Ergebnis aus der Variante mit Hilfsspalten.

Dem Anwender bleibt es überlassen, welche Variante er bevorzugt. Die Entscheidung kann evtl. daran fest gemacht werden, welche Informationen zu sehen sein sollen.
Auch in Teil II noch der Tipp für alle Blogger, Twitterer usw.:
Die ABC-Analyse kann auch genutzt werden, um die schlagkräftigsten Beiträge, die besten Kategorien oder die Tweets mit den meisten Retweets zu ermitteln, wenn diese Informationen nicht schon aus anderen Tools abgerufen werden können.
Probier es doch mal aus und schreibe mir. Danke!

Hier kannst Du Dir die Beispieldatei

dowloaden.

Zum Thema gibt es einen weiteren Artikel.