Zusammenfassende Aufgabe

Eine ABC-Analyse - Worum geht es hier?

Ähnlich wie im letzten Kapitel sollst du nun unter Anleitung eine komplexere Tabellenkalkulation durchführen: es geht um eine sogenannte ABC-Analyse. Hier wendest du so ziemlich alle Funktionen und Bearbeitungsmöglichkeiten an, die du bisher kennen gelernt hast. Es kommen aber auch noch neue Funktionen hinzu.

Situation und Begriffserklärung

Die Cafeteria der IGS Alan Mathison Turing möchte wissen, wie stark die einzelnen Artikel ihres Angebots am Gesamtumsatz eines Jahres beteiligt sind. Die Artikel werden in A - Artikel (sehr stark am Umsatz beteiligt), B - Artikel (mittelstark am Gesamtumsatz beteiligt) und C - Artikel (nur wenig am Umsatz beteiligt) untergliedert. Diese Art der Betrachtung wird in der Betriebswirtschaftslehre als ABC - Analyse bezeichnet und hilft bei Planungs- und Entscheidungsfindungen. Man könnte sich beispielsweise überlegen, ob einige C - Artikel ganz aus dem Sortiment herausgenommen werden sollen oder ob man für diese Artikel mehr Werbung machen sollte. Möglicherweise handelt es sich hier aber auch um Artikel, die aus "taktischen" Gründen im Sortiment bleiben müssen, weil man andernfalls wichtigen Kunden verärgern würde.

Das Ergebnis

Erklärung

In den Zellen A9:H21 erfolgt die Auswertung. Die wichtigste Spalte für die Gruppeneinteilung ist Spalte G. Hier steht der prozentuale Anteil des jeweiligen Artikels am Gesamtumsatz. Wichtig ist, dass die ganze Tabelle aufsteigend nach dieser Spalte sortiert sein muss. Da die Werte zu Beginn der Auswertung noch nicht feststehen, wird sortiert, wenn die Zelleninhalte von Spalte F berechnet sind. Dann werden diese Prozentzahlen kumuliert. In den Zellen A3:B5 wird festgelegt, wie die Gruppierungen zustande kommen. Zur Gruppe A gehören alle Artikel, deren kumulierter Anteil am Gesamtumsatz kleiner gleich 70% ist. Dies gilt für alle Artikel bis Zeile 12, da in Zeile 13 die 70% überschritten sind.
Die nächsten 20% (also 70% + 20% = 90%) gehören zur Gruppe B, der Rest sind C - Artikel. Spalte H zeigt das Ergebnis an.
Ab Zeile 24 erfolgt eine Übersicht/Zusammenfassung der Analyse.

Die Ausgangssituation

Du wirst nun die einzelnen Arbeitsschritte mit Hilfe dieses Kapitels durchführen. Hier kannst du dir das Calc-Dokument herunterladen. Die Artikelstammdaten sind wie bei dem Beispiel im letzten Kapitel in einer separaten Tabelle gespeichert (s. die Tabellentabulatoren "Artikel" und "ABC"). In der Tabelle ABC fehlen natürlich noch die Zelleninhalte.
Beachte: Die Artiknummern stehen jetzt in einer beliebigen Reihenfolge, die oben angesprochene Sortierung hat noch nicht stattgefunden. Außerdem wurden nicht alle Artikel der Artikel-Tabelle erfasst, weil sich diese Auswertung ja auf das vergangene Jahr bezieht, da war das Angebot der Cafeteria kleiner.

1. Schritt

Die Inhalte der Spalten B, C und D kannst du mit einem S-Verweis aus der Artikel-Tabelle übernehmen. Das hast du im letzten Kapitel gelernt. Achte auf die Kopierfähigkeit der Formeln!

2. Schritt

Der Umsatz pro Jahr (Spalte E) ergibt sich aus der Multiplikation von Verkaufspreis * Menge. In der Zelle E21 muss der Gesamtumsatz erscheinen.

3. Schritt

Spalte F: Jetzt werden für die einzelnen Artikel die Anteile am Gesamtumsatz (E21) berechnet.

4. Schritt

Dieser Schritt bewirkt, dass die ABC-Analyse nach dem vorliegenden Schema auch durchführbar ist. Alle Zeilen sind absteigend nach Spalte F zu sortieren (Symbolleiste). Auch eine Sortierung nach Spalte E führt zum gleichen Ergebnis (warum?).

5. Schritt

Die Artikel sind nun absteigend sortiert nach dem größten Umsatz. Nun werden die Prozentzahlen von Spalte F zeilenweise kumuliert. Darunter ist Folgendes zu verstehen:
In die Zelle von G9 wird der Wert von der Zelle F9 übernommen.
In Zelle G10 steht die Addition von G9 und F10. In G11 entsprechend die Addition von G10 und F11, usw.
In Zelle G20 ist dann der Wert "100%" erreicht (warum?).

6. Schritt

Nun erfolgt die Zuordnung der Artikel zu den Gruppen A, B und C.
Hier die Formel für die Zelle H9:
Bitte analysiere diese verschachtelte WENN - Funktion. Erkläre, was die absoluten Zellbezüge bedeuten.

7. Schritt

Du bist fast fertig!

In der Übersicht (s. unten) müssen in Spalte B die Summen für die einzelnen Gruppen (A, B oder C) gebildet werden. Das machst du mit dieser Funktion:
=SUMMEWENN($H$9:$H$20;A26;???)
Die drei ??? stehen für den Bereich, aus dem diese Summen zu bilden sind. Informiere dich hinsichtlich der Funktion SUMMEWENN und ergänze die unvollständige Formel (kopierfähig).

Spalte C zeigt die Anzahl der einzelnen Gruppen. Hier kommt die Funktion ZÄHLENWENN zum Einsatz. Informiere dich über die Funktionsargumente und setze die richtige Formel (ebenfalls kopierfähig) ein.

8. Das Diagramm

Hier ein Vorschlag:

Hinweis: Die Buchstaben A, B und C sowie die orangefarbenen Linien wurden nachträglich mit Hilfe von "Zeichnen" - Objekten eingefügt.
Diese findest du am unteren Bildrand (Menü: Ansicht->Symbolleisten->Zeichnung).