Mit einer ABC-Analyse teilen Sie verschiedene Objekte in die drei Klassen A, B und C auf. Bei diesen Objekten kann es sich beispielsweise um Kunden, Kundengruppen, Produkte oder auch Produktgruppen handeln. Im Mittelpunkt steht jedoch meist der Umsatz des Objekts beziehungsweise der Gruppe von Objekten. Die Aufteilung erfolgt nach Wertanteilen der ermittelten Werte am Gesamtwert. Wie Sie dies mit Access anstellen, zeigt der vorliegende Beitrag.
Technisch betrachtet verläuft eine ABC-Analyse wie folgt: Sie summieren die Umsätze der einzelnen Kunden, sortieren diese absteigend, ermitteln den prozentualen Anteil am Gesamtumsatz und kumulieren diesen Wert dann in einer weiteren Spalte.
Schließlich gruppieren Sie die Kunden nach den kumulierten Prozentwerten entsprechend den gewünschten Grenzen für die Gruppen A, B und C. Beispiele für solche Grenzen sind 80% für Gruppe A, 15% für Gruppe B und 5% für Gruppe C. Sie können jedoch auch 70%, 20% und 10% oder andere Kombinationen als Grenzwerte verwenden.
Auf der anderen Seite ermitteln Sie im nächsten Schritt, wie viele Kunden prozentual den drei Gruppen A, B und C angehören.
Zusammengefasst werden diese Zahlen dann in einem entsprechenden Diagramm angezeigt – mehr dazu im Beitrag Diagramme zur ABC-Analyse (www.access-im-unternehmen.de/881).
Beispieldaten
Die Beispieldaten stammen wieder einmal aus der Südsturm-Datenbank. Für dieses Beispiel benötigen wir daraus zunächst die Tabellen tblKunden, tblBestellungen und tblBestelldetails.
Technische Umsetzung
Im ersten Schritt erstellen wir eine Abfrage namens qryKundenNachUmsatz, welche die Umsätze der einzelnen Kunden summiert und diese absteigend nach dem Umsatz sortiert (s. Bild 1). Diese Abfrage enthält die drei Tabellen tblKunden, tblBestellungen und tblBestelldetails. Aus der Tabelle tblKunden benötigen wir nur die Felder KundeID und Firma.
Bild 1: Abfrage zum Ermitteln der Umsätze
Die Tabelle tblBestellungen sorgt lediglich dafür, dass die Bestellpositionen den einzelnen Kunden zugeordnet werden können. Die Tabelle tblBestellpositionen liefert die Felder zur Berechnung des Umsatzes einer jeden Bestellposition: Einzelpreis, Anzahl und Rabatt.
Der Umsatz wird aus diesen drei Feldern mit folgender Formel ermittelt:
Umsatz: [Einzelpreis]*[Anzahl]*(1-[Rabatt])
Damit die Abfrage nicht jede einzelne Position ermittelt, sondern diese gleich zum Gesamtumsatz der einzelnen Kunden summiert, aktivieren Sie die Anzeige der Zeile Funktion des Abfrageentwurfs (Klick mit der rechten Maustaste in das Entwurfsraster, dann Auswahl des Kontextmenüeintrags Summen).
Für die beiden Spalten KundeID und Firma können Sie den Standardwert Gruppieren in der Zeile Funktionen beibehalten. Der Inhalt des Feldes Umsatz soll hingegen summiert werden, daher legen Sie dafür den Wert Summe fest. Schließlich stellen Sie in der Zeile Sortierung für die Spalte Umsatz den Wert Absteigend fest. Der Wechsel in die Datenblattansicht der Abfrage liefert das Ergebnis aus Bild 2 – die Umsätze werden summiert und in der richtigen Reihenfolge angezeigt.
Bild 2: Umsatzsummen in absteigender Reihenfolge
Prozentualer Anteil
Nun benötigen wir den prozentualen Anteil des Umsatzes am Gesamtumsatz. Also fügen wir der Abfrage ein weiteres berechnetes Feld hinzu. Dieses soll den Inhalt des Feldes Umsatz durch den gesamten Umsatz aller Kunden dividieren. Der besseren Übersichtlichkeit halber fügen wir in einer weiteren Spalte zunächst ein Feld namens Gesamtumsatz ein, das die folgende Berechnungsformel verwendet:
Gesamtumsatz: DomSumme("Einzelpreis*Anzahl*(1-Rabatt)";"tblBestelldetails")
Hier kommt also eine Domänenfunktion zum Einsatz, welche die Summe der Umsätze aller Datensätze der Tabelle tblBestelldetails ermittelt. Diese Spalte versehen Sie in der Zeile Funktion wiederum mit dem Eintrag Gruppierung. In dieser Abfrage bringen wir nun noch den prozentualen Anteil des Umsatzes des aktuellen Kunden am Gesamtumsatz unter. Dazu verwenden Sie die folgende Berechnungsformel:
ProzentVomGesamtumsatz: [Einzelpreis]*[Anzahl]*(1-[Rabatt])/[Gesamtumsatz]
Hier greifen wir wieder auf die Formel zurück, die wir bereits zum Summieren des Umsatzes je Kunde verwendet haben. Legen Sie für dieses Feld wieder den Wert Summe in der Zeile Funktion fest, damit die Werte je Gruppe (also je Kunde) summiert werden – das Ergebnis zeigt Bild 3.
Bild 3: Umsätze mit Gesamtumsatz und prozentualem Anteil am Gesamtumsatz
Warum aber nutzen wir nicht einfach den Namen des bereits berechneten Ausdrucks, also Umsatz statt [Einzelpreis]*[Anzahl]*(1-[Rabatt]) Normalerweise funktioniert das, aber nicht in Abfragen mit Gruppierungen. Hier gibt es nur zwei Möglichkeiten: Entweder Sie geben den kompletten Ausdruck nochmals an (wie in diesem Beispiel) oder Sie verwenden eine erste Abfrage, die den Ausdruck ermittelt und benennt, und erstellen dann eine zweite Abfrage, welche diesen Ausdruck mit dem entsprechenden Namen weiterverwendet.
Prozente kumulieren
Nun wollen wir in einer weiteren Spalte die Prozentwerte kumulieren, um zu ermitteln, wie viele der umsatzstärksten Kunden nötig sind, um beispielsweise 80% des Umsatzes abzudecken, und wieviele die folgenden 15% und die letzten 5% bedienen. Diese sollen dann auch noch in die drei Gruppen A, B und C einsortiert werden.
Das Ergebnis soll schließlich wie in Bild 4 aussehen. Die vorletzte Zeile heißt Kumuliert und kumuliert alle bisherigen prozentualen Anteile am Umsatz auf. Die letzte Zeile heißt Gruppe und liefert die Werte A, B und C.
Bild 4: Kumulierte prozentuale Anteile und Einteilung in Gruppen
Diese Abfrage heißt qryABCDynamisch und verwendet die bereits erstellte Abfrage qryKundenNachUmsatz als Datenherkunft (s. Bild 5). Ziehen Sie die drei Felder Firma, Umsatz und ProzentVomGesamtumsatz in das Entwurfsraster der Abfrage.
Bild 5: Neue Abfrage, die auf der Abfrage qryKundenNachUmsatz basiert und zwei weitere Felder hinzufügt.
Dann fügen Sie zwei neue Felder hinzu, die wiederum berechnete Ausdrücke sind. Das erste Feld namens Kumuliert bedarf einer Erläuterung, denn dieses enthält eine Unterabfrage. Diese verwendet wiederum die Abfrage qryKundenNachUmsatz als Datenherkunft – also genau wie die Hauptabfrage qryABCDynamisch.
Da wir die Datensätze der Haupttabelle und der Untertabelle anhand des Feldes ProzentVomGesamtumsatz abgleichen möchten (wie genau, erfahren Sie gleich im Anschluss), müssen wir eine der beiden Instanzen dieser Abfrage mit einem alternativen Namen versehen, in diesem Falle t1 für die Datenherkunft der Unterabfrage.
Die Unterabfrage liefert die Summe des Werts des Feldes ProzentVomGesamtumsatz für alle Datensätze der Unterabfrage (hier t1 genannt), deren Feld ProzentVomGesamtumsatz einen Wert enthält, der größer oder gleich dem Wert dieses Feldes des aktuellen Datensatzes der Hauptabfrage ist. Bild 6 verdeutlicht diesen Zusammenhang: Sie zeigt die Haupt- und die Unterabfrage der Abfrage qryABCDynamisch. Dabei wird etwa dem ersten Datensatz die Summe aller Werte des Feldes ProzentVomGesamtumsatz der Unterabfrage t1 hinzugefügt, deren Wert größer oder gleich dem aktuellen Wert in der Hauptabfrage qryKundenNachUmsatz ist – und dies entspricht lediglich dem ersten Datensatz von t1. Für den vierten Datensatz hingegen liefert die Unterabfrage bereits die Summe der Werte des Feldes ProzentVomGesamtumsatz für alle Datensätze, deren Prozentsatz größer oder gleich dem Wert des Feldes ProzentVomGesamtumsatz des vierten Datensatzes der Abfrage qryKundenNachUmsatz ist.
Bild 6: Kumulieren der prozentualen Anteile am Umsatz
Der Ausdruck für das Feld Kumuliert sieht entsprechend wie folgt aus:
Kumuliert: (SELECT Sum(ProzentVomGesamtumsatz) FROM qryKundenNachUmsatz AS t1 WHERE t1.ProzentVomGesamtumsatz >= qryKundenNachUmsatz.ProzentVomGesamtumsatz)
Der Clou ist die Verwendung des gleichen Feldes aus der Haupt- und der Unterabfrage im Vergleichsausdruck, wobei die jeweils zu verwendende Datenherkunft durch t1 (Unterabfrage) und qryKundenNachUmsatz (Hauptabfrage) referenziert wird.
Damit erhalten wir nun die kumulierten Werte.
Einteilung in die Gruppen A, B und C
Fehlt noch der letzte Schritt – die Einteilung der Kunden in die Gruppen A, B und C entsprechend der vorgegebenen Prozentgrenzen. Im vorliegenden Fall soll der erste Grenzwert 80% sein und der zweite 95%. Der folgende Ausdruck wertet den Inhalt des Feldes Kumuliert aus und prüft, ob der Wert kleiner als 0,8 oder 0,95 ist. Im ersten Fall liefert der Ausdruck den Wert A, im zweiten den Wert B und in allen anderen Fällen den Wert C:
Gruppe: Wenn([Kumuliert]<0,8;"A";Wenn([Kumuliert]<0,95;"B";"C"))
Wenn Sie die Abfrage nun ausführen, werden Sie feststellen, dass die Berechnung bereits bei den hier vorliegenden gut 2.000 Datensätzen relativ lange dauert. Da diese Analyse gelegentlich auch für wesentlich größere Datenmengen eingesetzt werden soll, müssen wir im Anschluss noch ein wenig an der Performance arbeiten.
Prozentuale Anteile der Gruppen A, B und C ermitteln
Zuvor schauen wir uns jedoch noch an, wie Sie die prozentualen Anteile der Kunden an den einzelnen Gruppen ermitteln können.
Das Ergebnis sollte etwa wie in Bild 7 aussehen. Die Abfrage sollte drei Datensätze liefern, die jeweils den prozentualen Anteil der Anzahl der Kunden für die drei Gruppen A, B und C enthält.
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo