Sales Forecast: from Excel to ML in half a year

Sales Forecasting for Business: From Excel to ML Ensembles in Six Months

Business decisions rely on sales forecasts—whether to launch promotions or adjust budgets. A model with a 4.5% MAPE seems perfect, but one 20% miss erodes trust. Executives ignore the numbers, apply manual fixes, and overspend out of fear of missing targets. The real business need? Predictability: can we close the month on target without catastrophic errors?

The project goal: build a reliable 45-day forecast for sales, orders, and revenue across an entire large delivery service (79 cities, thousands of order fulfillment centers).

The Existing Model Zoo

Current approaches included:

• Prophet at each fulfillment center level: thousands of models for logistics. Issues: no handling of new centers (expert estimates in Excel), accuracy not validated at national level, optimized for daily precision—not monthly goals.
• Excel with annual seasonality: past year’s week × growth factor (e.g., 1.15 for +15%). Visual estimation, no metrics.
• Prophet at country level: hyperparameters tuned, external features (macro trends, holidays, planned discounts). Average error 1–2%, but short-term validation was an artifact.

Expert Excel methods were non-reproducible. All approaches needed a unified benchmark.

Honest Rolling Backtest as Foundation

A 12-month rolling backtest was implemented for objective evaluation: simulating daily forecasts using historical data with retraining after each new day. This tests models under real-world conditions—growth, dips, campaigns, holidays.

Backtest process:

• Day 1: train on data up to Jan 1 → forecast 45 days ahead.
• Day 2: add Jan 1 actual → retrain → new forecast.
• Repeat through the year.

Excel seasonality was replicated in Python—became the baseline. Prophet per center: optimistic results (no new centers). Country-level Prophet: revealed feature issues (planned vs. actual discounts).

Result: baseline (Excel) showed strong predictability; ML delivered smooth averages but volatile errors.

Excel Beats Prophet: Lessons from Simplicity

Unexpected backtest outcome: Excel with annual seasonality outperformed Prophet on the key metric—cumulative monthly error (business-relevant). Prophet won on daily accuracy but failed long-term due to overfitting on trends.

Metric comparison:

• MAPE (Mean Absolute Percentage Error): Prophet better.
• Cumulative error over 30/45 days: Excel leads.

Prophet decomposes series into trend, seasonality (days/months/years), holidays, and known features. Limitations: single series, features must be known in advance.

Moving to Granularity: Cities and Machine Learning

To improve, forecasts were broken down to city level (79 cities). This captured local effects: varying demand dynamics, new fulfillment centers.

Classic ML was introduced:

• Gradient boosting (LightGBM/XGBoost) with lags, rolling averages, dummy variables for weekdays/holidays.
• Features: historical lags (1–30 days), YoY growth, external signals (holidays).

Result: 10–15% improvement over baseline on cumulative error—but not transformative. Issues: weak capture of nonlinear patterns, sensitivity to anomalies.

TSMixer: Breakthrough and Limits

Shifted to neural networks. TSMixer (Temporal Sparse Mixer) is state-of-the-art for multivariate time series. Works like MLP-Mixer for time: channels are independent, captures cross-channel dependencies without recurrent layers.

Architecture:

• Past/Future towers for historical and future features.
• MLPMixer blocks: token mixing (over time) + channel mixing.
• Scalability: O(L * D) vs. O(L²) in transformers.

Trained on 79 cities × 3 metrics (sales/orders/revenue). Backtest: TSMixer beat baseline by 25%, gradient boosting by 12%. Weaknesses: still lags during holiday peaks, requires substantial data.

Final Ensemble: Combining Strengths

Single forecast = ensemble:

• Excel seasonality (30% weight): stability on long-term trends.
• Gradient boosting (40%): local patterns.
• TSMixer (30%): nonlinearities.

Weights chosen via stack-rank on backtest (sum of ranks across metrics). Result: MAPE 3.2%, monthly cumulative error <5% in 95% of cases. Predictability improved: business adopted the numbers without manual tweaks.

Key takeaways:

• Predictability matters more than average accuracy: focus on cumulative error over time.
• Rolling backtest is the gold standard for evaluation.
• Ensembles beat pure models: simplicity + ML power.
• Granularity (cities) beats aggregation.
• Business trusts models that pass stress tests (holidays, downturns).

Project Outcomes

In six months, the chaotic mix became P.E.S.E.C.—a daily strategic forecast. Business now makes decisions without panic. Key: honest evaluation, focus on business KPIs, hybrid approach.

— Editorial Team