Design: Demand Forecasting

Clarify:

• goal: less waste of fresh food and fewer stock-outs, together;
• scale: 400 stores × 50,000 products ≈ 20,000,000 series; forecasts for each of the next 7 days;
• timing: every night, finished by 4 a.m. so purchase orders reach suppliers;
• users: the replenishment system orders automatically; category managers review exceptions;
• hard parts: perishables, promotions, festivals, new products and new stores, and slow sellers that sell zero most days.

This is a batch problem with a strict deadline, unlike the online designs.

• Latency per forecast hardly matters; total job time does.
• The forecast feeds a decision (how much to order), and the cost of error differs by product.

Forecast demand, as a range, then decide separately:

• target: units customers would buy at each store, for each product, each day (demand, not sales; see below);
• output: several percentiles (for example the 10th, 50th and 90th), not one number;
• offline metrics: WAPE and bias by category and store; pinball loss for the percentiles (module 16);
• business metrics: waste (units thrown away), stock-out rate, sales lost to stock-outs, inventory held.

A range is what the ordering decision needs.

• For fresh bread, ordering at the median wastes too much; for rice, ordering at the median runs out too often.
• The decision layer chooses the percentile per product from costs (below).

Sales are not demand. When a product sells out, sales stop at the stock level, while customers would have bought more:

• those days are censored: sales are a floor on demand, not its value;
• training on raw sales teaches the model to forecast too low for popular items;
• too-low forecasts lead to smaller orders, more stock-outs, and even lower recorded sales: a feedback loop (module 22).

Fixes: mark stock-out days, estimate the missing demand (from the hour it sold out, or similar stores), or drop or down-weight censored days.

This is the most common hidden error in retail forecasting.

• It hits the best-selling items hardest, which matter most.
• Logging stock levels and sell-out times through the day is what makes the correction possible.

Forecasting features, computed only from information available at forecast time:

• lags and rolling statistics: sales 1, 7 and 14 days ago; rolling means and spreads over 7 and 28 days (with stock-out days corrected);
• calendar: day of week, month, holidays and festivals (with days before and after), paydays, school terms;
• promotions and price: planned discounts, price relative to normal, placement on the home screen, competitor events if known;
• weather: forecast temperature and rain (ice cream, umbrellas);
• product and store: category, brand, shelf life, store size, area type;
• hierarchy: sales of the category and of the store overall.

Promotions and festivals cause the biggest swings and are known in advance, so they must be features.

• Lags capture each series' level and recent trend.
• Hierarchy features let thin series borrow strength from their category and store.

Three broad approaches:

• classical, per series: exponential smoothing, ARIMA, Prophet. Simple and explainable; struggles with millions of series, promotions and sparse data;
• one global model: a gradient-boosted model trained on all series together, with series features. The common choice at scale;
• deep models: global neural forecasters (DeepAR, temporal fusion transformers, newer foundation models for time series). Strong with lots of data; more costly to run.

A global model learns patterns shared across series.

• A new product or a slow seller benefits from what the model learned from similar ones.
• One model is far easier to train, monitor and release than twenty million.
• The M5 forecasting competition (Walmart data) was won largely by gradient-boosted global models.

Special cases:

• intermittent demand: many products sell zero on most days at a store; forecast the chance of any sale and the size when it happens, or forecast at a coarser level (weekly);
• new products and stores: no history, so borrow from similar products (same category, brand, price band) and similar stores (cold start via hierarchy);
• reconciliation: store-level forecasts should add up to city and national forecasts used for supplier orders; reconcile them so the levels agree.

Most products in a large catalogue are slow sellers, and errors there add up.

• Percentage measures break on them (module 16), so WAPE and pinball loss are used.
• Reconciliation keeps warehouse and store plans consistent.

The order quantity comes from the forecast range and the costs:

• cost of being one unit short: the lost margin (and some lost goodwill);
• cost of being one unit over: waste for perishables, holding cost for long-life goods;
• the best percentile to stock is short ÷ (short + over): the newsvendor rule;
• order = that percentile of demand over the period until the next delivery, minus stock on hand.

It turns a forecast into a decision with arithmetic, per product.

• Perishables with high waste cost are stocked below the median; long-life goods well above.
• When costs change (a supplier price rise), the decision changes with no retraining.

A batch pipeline with a hard deadline:

• load the day's sales, stock and sell-out times; correct censored days;
• build features for every series and horizon in Spark or the warehouse;
• score all series with the global models (parallel across categories);
• reconcile levels; validate the output (no negative forecasts; no series jumping 10× without a promotion);
• write to the replenishment system; flag exceptions for category managers.

With tens of millions of rows a night, the design is about throughput and reliability.

• Partitioning by category or region parallelises scoring.
• Output validation stops a broken input from emptying or flooding every store.
• If the job fails, yesterday's forecasts (shifted by a day) are the fallback.

Forecasts are judged over many periods:

• offline: rolling-origin backtests over at least six months (module 16), reported for normal weeks, promotion weeks and festival weeks separately;
• by segment: category, store type, fast and slow sellers, fresh and long-life;
• online: a store-level pilot or switchback (some stores on the new model) measuring waste and stock-outs;
• monitoring: bias by category (steady over- or under-forecasting), WAPE weekly, stock-out and waste rates, and checks after every festival.

Bias is the first thing to watch: a small, steady over-forecast quietly fills stores with waste.

• Festival weeks are rare, so the backtest must cover past festivals explicitly.
• Store-level pilots measure the business effect, since the forecast's real test is what ends up on shelves.

The questions interviewers ask about the forecasting design, from the first screening call to the senior round. Cover the answer, say yours aloud, then compare.

Reading an idea and explaining it are different skills.

• Each answer below is the two or three sentences an interviewer is listening for.
• The reasoning behind each one is in the cards above. If an answer surprises you, go back to that card.

A short build that uses this module's ideas. Write it yourself before looking anything up; the checks at the end tell you whether it works.

An idea you have only read fades in a week.

• Building it once, even in fifty lines, is what makes it yours.
• Interviewers can tell the difference between someone who has read about a loop and someone who has debugged one.