Wie wir schlechte Prognosen in etwas bessere Prognosen umwandeln (Fortsetzung)

In einem früheren Artikel habe ich darüber gesprochen, wie Leute eine große und komplexe Excel-Datei ( Sie können sie hier lesen ) für die Umsatzprognose erstellt haben. Wir haben uns entschlossen, in diese Schande einzugreifen, und vorgeschlagen, das Prognosemodell zu wiederholen, damit weniger Fehler, eine einfachere Bedienung und eine flexible Abstimmung möglich sind.

Was sind die Hauptprobleme im beschriebenen Modell:

1. Daten, Modell und Ansichten werden zu einer Entität gemischt. Aus diesem Grund zerstört eine Änderung an mindestens einem Element diesen gesamten Monolithen.
2. Übermäßige Berechnung für die manuelle Verarbeitung, die in großen Mengen zu Fehlern und Tippfehlern führt.

Was wir vorgeschlagen haben:

1. Im ursprünglichen Modell wurden die ursprünglichen Daten, auf denen es erstellt wurde, nirgendwo angezeigt. Wir haben vorgeschlagen, diese Daten im Format der 2. Normalform in die Excel-Datei selbst in zwei separate Blätter (Umsatz und Anzahl der Kunden) einzugeben. Glücklicherweise sind die Verkaufsdaten in unserer monatlichen Aggregation nur Zehntausende von Zeilen, nicht Millionen. Wir haben auch konfiguriert, dass diese Daten mithilfe von Power Query direkt aus der Datenbank empfangen werden.
2. Wir haben ein Modellierungsblatt erstellt, das aus 3 Blöcken besteht:
   
   • Revenue Pivot-Tabelle
   • Übersichtstabelle der Anzahl der Clients
   • Abrechnungstabelle der durchschnittlichen Schecks
     Jede Pivot-Tabelle ist eine Pivot-Tabelle, die auf den Quelldaten in den Details basiert, die für die aktuelle Simulation nach Abteilungen und Einheiten erforderlich sind, sowie in den erforderlichen Details zu Zeiträumen (Monaten).
     
3. Im Simulationsblatt haben wir einfache Prognosemodelle basierend auf historischen Zeitreihen erstellt. Wir haben die Anzahl der Kunden und die durchschnittliche Rechnung erweitert, und der prognostizierte Gesamtumsatz wurde als Produkt dieser Werte betrachtet. Nach Überprüfung der Daten haben wir drei Prognosemodelle erstellt: Median für vergangene Perioden, exponentielle dreifache Glättung und Nullstellung (wenn wir 0 Prognosen benötigen).
   
4. Die Berechnung des durchschnittlichen Eingangs (Fakt) und des Umsatzes (Prognose) erfolgt nicht durch Referenzieren der Zellen, sondern unter Verwendung von VLR und Offset-Markierung, wodurch die Berechnungen gegenüber Änderungen der Anfangsdaten resistent werden.
   
5. Es ist klar, dass das Modell jetzt für den Benutzer nicht lesbar ist, weil Es gibt zu viele Bedeutungen. Zu diesem Zweck haben wir separate Einheitenblätter erstellt. Jedes Blatt verfügt über eine Zelle, in der ausgewählt wird, welche Daten in diesem Blatt zusammengefasst werden sollen. Basierend auf VLOOKUP ziehen wir Daten aus dem Modellblatt in das Blatt.
6. Die Erstellung von 30 Blättern nach Einheiten erfolgte nach einem speziellen Verfahren. Zunächst wird das erste Blatt erstellt, eine der Einheiten, die alle möglichen Abteilungsnamen enthält. Wenn es in der Einheit keine Abteilung gibt, ziehen die Formeln sie auf 0 zurück. Um alle 30 Einheiten zu erstellen, erstellen wir Duplikate und ändern die Namen in der Kontrollzelle (sie wird zum Generieren des VLOOKUP-Schlüssels verwendet), und wir haben die erforderlichen Daten in Form einer Präsentation. Der VLOOKUP kann mehr als eine Zelle als Schlüssel verwenden, wenn Sie den Trick verwenden: Kombinieren Sie die benötigten Zellen mithilfe der Verkettung (der CLIP-Funktion oder des & -Symbols) zu einer.
7. Dem Präsentationsformular wurde ein Element hinzugefügt, mit dem Sie das Modell steuern können: ein einfacher Faktor für die vorhergesagten Werte der durchschnittlichen Prüfung und Anzahl. Dieses Element wird mithilfe der INDIRECT-Funktion, mit der Sie den generierten Link verwenden können, auf einem speziellen technischen Blatt erfasst. Aus diesem technischen Datenblatt werden alle diese Korrekturen mit dem VLOOKUP auf das Modellblatt übertragen.
   
8. Verallgemeinerungsblätter sind keine Summierung von Präsentationsblättern mehr, sondern werden genauso aufgebaut wie alle anderen Blätter - durch Summieren der Daten auf dem Modellblatt. Repräsentationen sind daher reine Repräsentationen und haben keine Abhängigkeiten untereinander.

Was wir haben:

1. Es ist immer klar, von welchen Ziffern wir die Daten erhalten haben (da die Power Query-Abfrage gespeichert wurde).
2. Wir können die Daten ändern, ohne das Modell zu beschädigen.
3. Änderungen in der Struktur und Hierarchie erfordern geringfügige Änderungen (Sie müssen nur die Namen in einem Blatt der Präsentation ändern und dann duplizieren).
4. Wir haben die Anzahl möglicher Fehler drastisch reduziert, weil Die meisten Daten werden mit Formeln, Links und Schlüsseln ausgefüllt.
5. Der Kunde hat eine interaktive Prognose erhalten, in der er die Werte selbst ändern und sofort eine Prognose erhalten kann.
6. Wir konnten gleichzeitig die Anforderungen erfüllen, dass wir Daten sowohl jährlich als auch monatlich benötigen.
7. Kann in der nächsten Budgetperiode verwendet werden.
8. Sie können die Prognosemodelle ändern, wenn diese uns unangemessen erscheinen.

Warum haben wir uns entschieden, in Excel zu bleiben, und es bei einigen anderen Technologien nicht wiederholt?

1. Wir mussten diese Datei im Betrieb der derzeitigen Mitarbeiter belassen. In Excel ist es für uns einfacher zu zeigen, wie dies alles funktioniert und was sie beheben können.
2. Excel bewältigt die Aufgabe und andere Lösungen - überflüssige Entitäten.
3. Der Kunde ist an dieses Formular gewöhnt und hat bestimmte Arbeitskosten, die wir uns nicht leisten konnten, „umgeschult“.

Wie viel Zeit wir gebraucht haben: ungefähr 5 Arbeitstage, an denen 1 Person 2-4 Stunden am Tag verbracht hat, und am Ende des Tages haben wir die Ergebnisse überprüft.