Entwicklung eines Excel-basierten Absatzprognosetools
Projektstatus
Planung
Durchführung
Dokumentation
Hintergrund
Die tägliche Produktionsplanung in handwerklich arbeitenden Bäckereibetrieben basiert häufig auf Erfahrung, Routine und persönlicher Einschätzung. Saisonale Effekte, Wochentage, Wetterbedingungen oder Sortimentsveränderungen werden intuitiv berücksichtigt, jedoch selten systematisch ausgewertet. Gleichzeitig führen Überproduktion und Fehlmengen zu wirtschaftlichen Nachteilen – entweder durch Abschriften oder durch entgangene Umsätze.
Das Projekt mit Globus Naturkost hatte daher das Ziel, historische Verkaufsdaten systematisch auszuwerten und in ein einfach nutzbares Prognoseinstrument zu überführen. Grundlage bildeten mehrere Jahre Kassendaten auf Produktebene. Durch die Kombination aus Datenanalyse, statistischer Modellierung und praxisnaher Umsetzung entstand ein Excel-basiertes Tool, das auf Basis weniger Eingaben eine tägliche Absatzprognose ermöglicht.
Im Mittelpunkt stand dabei nicht die Entwicklung einer komplexen Softwarelösung, sondern die Schaffung eines alltagstauglichen Werkzeugs, das ohne zusätzliche IT-Infrastruktur genutzt werden kann. Die Prognose basiert auf einem statistischen Modell, dessen Parameter außerhalb von Excel geschätzt und anschließend transparent in einer Excel-Formel abgebildet wurden. So entsteht eine Verbindung aus datenbasierter Analyse und unmittelbarer praktischer Anwendbarkeit.
Herausforderung
Die größte Herausforderung bestand darin, aus umfangreichen und heterogenen Verkaufsdaten ein Modell zu entwickeln, das sowohl statistisch belastbar als auch betrieblich umsetzbar ist. Die Daten lagen auf Ebene einzelner Kassenvorgänge vor und mussten zunächst bereinigt, aggregiert und strukturiert werden. Dabei waren Schließtage, Sortimentswechsel sowie Produkteinführungen im Zeitverlauf zu berücksichtigen.
Im Analyseprozess wurden zahlreiche zusätzliche Merkmale getestet, darunter saisonale Zyklen, Wochentagseffekte, Wetterdaten, Feiertagskennzeichnungen, Rabattinformationen sowie zeitlich verzögerte Variablen wie Absatzwerte aus der Vorwoche. Auch komplexere Machine-Learning-Ansätze wie Random Forests oder Gradient Boosting wurden prototypisch erprobt. Diese Verfahren erzielten in Teilen bessere Prognosewerte, erfordern jedoch eine eigenständige Softwareumgebung und eine kontinuierliche Datenpflege.
Da die Lösung vollständig in Excel nutzbar sein sollte und der Betrieb lediglich Datum sowie einfache Wetterinformationen eingeben kann, musste die Modellstruktur bewusst vereinfacht werden. Die finale Lösung basiert daher auf einem linearen Regressionsmodell mit produktspezifischen Basiswerten und wenigen, automatisch berechenbaren Einflussgrößen. Die statistische Schätzung erfolgte in Python, während die Berechnung der Prognose in Excel über eine transparente Formel umgesetzt wurde.
Das Ergebnis ist ein pragmatisches Planungsinstrument, das datenbasierte Orientierung bietet, ohne den betrieblichen Ablauf durch zusätzliche Komplexität zu belasten. Die Prognosen stellen keine exakte Vorhersage dar, sondern dienen als strukturierte Entscheidungsgrundlage für die tägliche Produktionsplanung.
Lösung
m Rahmen der Analyse wurden die historischen Verkaufsdaten zunächst zu täglichen Absatzmengen pro Produkt zusammengeführt. Anschließend wurden verschiedene zusätzliche Merkmale berechnet, um mögliche Einflussfaktoren systematisch zu untersuchen. Dazu zählten unter anderem Wochentageffekte, Monats- und Jahresverläufe, saisonale Zyklen mittels Sinus- und Kosinus-Transformation, Wetterdaten wie Tageshöchsttemperatur und Niederschlag, Feiertags- und Ferienkennzeichnungen, Rabattinformationen sowie zeitliche Verzögerungsvariablen wie Absatz am Vortag oder in der Vorwoche. Darüber hinaus wurden gleitende Mittelwerte berechnet, um kurzfristige Trends abzubilden, sowie Variablen zur Berücksichtigung des Einführungszeitpunktes einzelner Produkte.
Auf Basis dieser erweiterten Datengrundlage wurden unterschiedliche Modellansätze getestet. Neben klassischen linearen Regressionsmodellen kamen auch komplexere Machine-Learning-Verfahren wie Random Forests und Gradient Boosting zum Einsatz. In internen Tests zeigten diese Modelle bei Nutzung vieler erklärender Variablen teilweise bessere Prognosewerte, insbesondere wenn historische Absatzverläufe unmittelbar einbezogen wurden.
Allerdings erwiesen sich diese Ansätze als nicht praktikabel im Hinblick auf die Projektvorgabe einer reinen Excel-Lösung. Komplexe Machine-Learning-Modelle lassen sich nicht als transparente Gleichung in Excel darstellen und erfordern in der Regel eine eigenständige Softwareumgebung sowie eine kontinuierliche Datenaktualisierung. Da diese infrastrukturellen Voraussetzungen im gegebenen Rahmen nicht realisierbar waren, wurde ein bewusst vereinfachtes lineares Modell als finale Lösung gewählt.
Die Modellspezifikation wurde so gestaltet, dass die Parameterschätzung in Python erfolgt und die resultierenden Koeffizienten anschließend in Excel hinterlegt werden können. Für jedes Produkt wurde ein eigener Basiswert geschätzt, ergänzt um globale Einflussgrößen wie Wochentag, saisonale Jahreskomponente sowie Wetterindikatoren. Die Excel-Datei berechnet auf dieser Grundlage automatisch die jeweilige Absatzprognose.
Umsetzung
Die technische Umsetzung erfolgte in mehreren aufeinander aufbauenden Schritten. Zunächst wurden die Rohdaten aggregiert, bereinigt und mit externen Wetterdaten angereichert. Im Anschluss wurden unterschiedliche Modellvarianten geschätzt und anhand von Trainings- und Testdatensätzen evaluiert. Dabei zeigte sich, dass eine stärkere Modellkomplexität zwar zu besseren statistischen Kennzahlen führen kann, jedoch die praktische Implementierbarkeit deutlich erschwert.
Das finale Modell wurde daher bewusst reduziert, sodass es vollständig in einer Excel-Datei abgebildet werden kann. Die geschätzten Koeffizienten wurden exportiert und in ein separates Tabellenblatt übertragen. Die eigentliche Prognoseberechnung erfolgt in Excel über eine transparente Formel, die Datum, Temperatur und Niederschlag berücksichtigt. Alle weiteren Variablen, wie beispielsweise saisonale Komponenten oder Wochentageffekte, werden automatisch aus dem eingegebenen Datum abgeleitet.
Zur besseren Einordnung der Prognose wurde zusätzlich eine einfache Schwankungsbandbreite von plus beziehungsweise minus zwanzig Prozent integriert, um Unsicherheit sichtbar zu machen und die Planung nicht auf eine einzelne Punktprognose zu verengen.