Viele kennen das: wir stehen mitten im Jahr und alle wollen wissen, wo man am Ende des Jahres steht. Im Folgenden ein einfaches Beispiel, wie man in Excel eine simple Fortschreibung von periodischen Daten hinbekommt; mit der Annahme des anhaltenden Wachstums ohne Einbruch durch Corona. Die Daten geben eine gewisse Periodizität preis; also ein Muster, was sich über die Jahre hinweg wiederholt. Immer Mitte des Jahres gibt es einen starken Absatz.
Um einen besseren Überblick ohne etwaige Ausreisser zu bekommen, schauen wir uns den gleitenden Durchschnitt an: das konstante Wachstum und die Wellenbewegungen werden sichtbar.
Aber wie geht es weiter? Hierzu dient die Linest()-Funktion - eine Array-Formel zur linearen Regression (je nach Betriebssystem wird eine Arrayformel anders eingegeben). Damit können wir eine lineare Regression auf Basis des Indexes und des gleitenden Durchschnitts aufstellen und bis zum Ende des Jahres fortschreiben. Die Spalte H berechnet sich aus $G$4 + $F$4 * A2.
Nun brauchen wir die Periodizität auf Basis von Monaten. Spalte I berechnet sich aus der Division von Spalte D durch Spalte H; die Prozentzahl sagt also aus, wie groß der wirkliche Wert im Vergleich zum linearen Wert für jenen Monat ist.
In Spalte K wird der Durchschnitt nach Monat berechnet mit der Formel
= AVERAGEIF($C$2:$C$51,J2,$I$2:$I$51).
Dieser monatliche Wert wird im Screenshot schließlich auf die lineare Regression angewendet.
Nun haben wir ein Modell zur linearen, periodisierten Fortschreibung. Die Kombination aus den tatsächlichen Werten und dem forecast ergibt schließlich folgendes Schaubild:
Dies ist natürlich ein sehr simples Vorgehen. Auch eine Validierung der Güte wurde hier ausgelassen. Zum Beispiel könnten wir bei der Funktion linest() die Option stats=TRUE angeben, um weitere Infos zum Modell zu bekommen und das Regressionsmodell zu validieren. Oder ein Trainings- und Testdatenset benutzen.
Foto von Clem Onojeghuo von Pexels