In diesem Blogbeitrag wird gezeigt, wie man eine Monatsverteilung von ganzzahligen Werten z.B. Jahresplandaten vornimmt. Dabei wird der Verteilungssatz anhand von Istdaten ermittelt. Der zu verteilende Wert wird dann über den zuvor ermittelten Verteilungssatz auf die Monate ganzzahlig aufgeteilt, ohne dass ein iterativer Prozess benötigt wird.

Einleitung

Vor allem in Planungsprojekten gibt es immer wieder Situationen bei denen Werte auf Jahresebene erhoben werden, die jedoch zu einem späteren Zeitpunkt auf Monatsebene verteilt werden müssen. Eine Verteilung auf Monate ist für unterjährige Plan-Ist-Vergleiche unerlässlich. Grundsätzlich auch kein Problem, solange der verteilte Wert über Dezimalstellen verfügen darf. Hier ist die Verteilung relativ simpel, da man einfach den Jahreswert mit dem ermittelten Verteilungssatz multiplizieren kann und direkt den korrekten Monatswert erhält. Bei zwingend ganzzahligen Werten, wie z.B. dem Absatz, gestaltet sich diese Monatsverteilung deutlich schwieriger. Dies kann gut anhand eines Beispiels veranschaulicht werden. Nehmen wir an, wir haben 7 Stück geplanten Jahresabsatz folgendermaßen zu verteilen:

Im Ergebnis (gerundet) sehen wir, dass der auf ganze Zahlen gerundete Wert in Summe nicht den zu verteilenden Jahresabsatz entspricht und damit zu einem falschen Jahreswert führt. Nur der nicht gerundete Wert würde ein korrektes Ergebnis auf Jahresebene ergeben. Der Restwert der beim gerundeten Ergebnis entsteht, wird oft durch einen iterativen Prozess solange verteilt oder versucht zu verteilen, bis die Unschärfe behoben ist.

Im Folgenden wird ein Ansatz gezeigt, wie man auf den iterativen Ansatz verzichten kann. Für diesen Ansatz wurde ein Beispiel eines Fahrradherstellers („Absolutbike“) gewählt, welcher seine Jahresabsatzplandaten von 2018 über die Monatsverteilungssätze der Istdaten von 2017 auf die Monate verteilen will.

Umsetzung

Grundlage für die Verteilung der Jahresplan-Absatzzahlen unseres Fahrradherstellers „Absolutbike“ auf die Monate stellen zwei Tabellen dar:

– T_IMPORT_Absolutbike_Absatzzahlen

– T_IMPORT_Absolutbike_Jahresabsatz_Plan

In der Tabelle T_IMPORT_Absolutbike_Absatzzahlen befinden sich die Absatzzahlen von 2017 auf Monatsebene.

Abbildung 1: Istabsatzzahlen von Absolutbike von 2017 (Monatsebene)

Wohingegen sich in der „T_IMPORT_Absolutbike_Jahresabsatz_Plan“-Tabelle die Plandaten von 2018 auf Jahresbasis befinden.

Abbildung 2: Planabsatzzahlen von Absolutbike von 2018 (Jahresebene)

Zur Veranschaulichung des Berechnungsprozesses sind die jeweiligen Schritte durch CTE’s strukturiert. Im ersten Schritt werden Istmonatsdaten mit den Planjahresdaten verbunden.

Abbildung 3: Verbindung von Monatsistwerten mit Jahresplanwerten

Im ersten Schritt sind die grundlegenden Werte für die Berechnung zusammengeführt. Um nun der Unschärfe der Verteilung von ganzzahligen Werten zu entgehen, wird mit kumulierten Monatswerten gearbeitet. Hierfür wird im zweiten Schritt zum einen der kumulierte Monatswert, sowie der Jahres-Istwert berechnet. Für diese Berechnung werden Window Functions verwendet:

Abbildung 4: Berechnung des kumulierten Monatsistabsatzes und des Jahresistabsatzes

Im dritten Schritt werden die wirklichen Verteilungssätze in kumulierter Form berechnet. Dieses stellt einen Zwischenschritt dar, um schlussendlich auf die tatsächliche Verteilung zu kommen.

Abbildung 5: Berechnung des kumulierten Verteilungssatzes

Der kumulierte Verteilungssatz an sich reicht nicht für die Berechnung der Verteilung auf Monate ohne Unschärfen aus. Hierfür ist es wichtig, zu ermitteln, was im kumulierten Vorgängermonat den Verteilungssatz darstellt. Damit kann ermittelt werden, was bereits von dem Jahreswert verteilt worden ist. Dazu wird im folgenden Schritt die Lag-Window Function genutzt. Über den zweiten Parameter der LAG-Function, wird der Vorgänger im Abstand von „1“ ermittelt. Mit dem dritten Parameter wird wiederum festgelegt, was als Default-Wert genutzt werden soll, wenn der Vorgänger nicht zu ermitteln ist. Dieser wird mit 0 definiert.

Abbildung 6: Berechnung des kumulierten Vorgängerverteilungssatzes

Im letzten Schritt wird der tatsächliche, verteilte, ganzzahlige Planungswert berechnet:

Gerundet (Planjahresabsatz * kumulierter Verteilungssatz)
   -
Gerundet (Planjahresabsatz * kumulierter Verteilungssatz des Vorgängers)

Abbildung 7: Berechnung des auf Monate verteilten Planjahresabsatz

Das Ergebnis sind korrekte, ganzzahlige, auf die Monate verteilte Werte ohne Iterationsschliefen und ohne Unschärfen.

Zum Vergleich wurde dieselbe Berechnung parallel durchgeführt, ohne die Logik des kumulierten Verteilungssatzes minus den kumulierten Vorgängerverteilungssatzes zu verwenden. Hier wurde nur der einfache, nicht kumulierte Monatsverteilungssatz berechnet und mit dem Planjahresabsatz multipliziert und gerundet.

Die folgende Tabelle zeigt zur Validierung die Summierung der berechneten Monatsverteilungswerte auf Jahresebene, um das Ergebnis der Ausgangsposition „Planjahresabsatz“ gegenüberzustellen. Im Ergebnis ist klar zu sehen, dass die Methode („Validierungswert_kummulations_Logik“) genau dem Planjahresabsatz entspricht, wogegen die einfache Methode („Validierungswert_einfache_Monatsverteilung“) nochmal die Unschärfe aufzeigt.

Abbildung 8: Gegenüberstellung der Ergebnisse mit einfacher und kumulierter Logik

Fazit

Zum Abschluss ist zu sagen, dass diese Variante der Monatsverteilung von ganzzahligen Werten nicht nur die Restwertaufteilungs-/Unschärfenproblematik löst, sondern auch den Einsatz eines iterativen Prozesses einhergehend mit der daraus entstehenden Notwendigkeit zur Nutzung einer Prozedur obsolet macht.