peterbamuhigire Demand Forecasting
Overview
Use this skill to turn sales, inventory, branch, and operational signals into demand forecasts and replenishment recommendations. It is especially relevant when fixing SQL joins that duplicate products, deriving days until stockout, or documenting demand-driven planning assumptions.
Use When
- Use when a plan, dashboard, operational model, or funding case needs demand forecasts, reorder logic, stockout timing, or sales-rate assumptions.
- Use when branch, SKU, warehouse, or channel data must be converted into replenishment or capacity planning evidence.
Do Not Use When
- Do not use when the request only needs a generic sales target with no operational data.
- Do not use predictive methods when the history is too thin; state the forecast as a planning assumption instead.
Required Inputs
- Sales, inventory, stock movement, lead time, and inbound-order data where available.
- Forecast horizon, reporting grain, excluded events, and business action triggered by the forecast.
- Any known stockouts, promotions, closures, returns, or one-off events.
Workflow
- Define the reporting grain first: usually one row per product per shop, branch, outlet, or warehouse for the forecast horizon.
- Aggregate sales and stock movements before joining product, branch, and stock-balance tables. Do not join raw sales lines directly to item master or stock balances when the output expects one product row.
- Exclude or separately flag voided sales, returns, internal transfers, stockout days, and one-off events that would distort demand.
- Normalize demand to a daily rate. Use 7, 30, and 90 day windows when available, and explain which window drives the forecast.
- Derive days until stockout as
current_stock / daily_demand. If demand is zero, report "no active demand" rather than hiding the value as an unexplained N/A. - Calculate forecast consumption as
daily_demand * horizon_days. - Calculate reorder point as
daily_demand * lead_time_days + safety_stock. - Calculate suggested order as
max(0, forecast_consumption + safety_stock - current_stock - inbound_qty). - Backtest against historical periods using WAPE/MAPE, bias, and missed-stockout counts.
- Classify the forecast as predictive analytics and document the history window,
exclusions, method, uncertainty, and action trigger. Use
../../book-extractions/data-analytics-business-planning-extraction.mdwhen the forecast feeds a business plan, KPI dashboard, funding model, or management system.
Join Guardrails
- Use CTEs or subqueries for
sales_by_product_branch,stock_by_product_branch, andinbound_by_product_branch. - Group every CTE by the same business key before joining: product id plus branch/shop/outlet/warehouse id.
- Join product and branch names once, after aggregation.
- Assert that the final result has no duplicate product plus branch rows.
- If the UI needs one product row per selected branch, collapse variants after filtering by branch, not across all branches.
Quality Bar
- Forecast grain is explicit and duplicate-safe.
- Demand windows, exclusions, and assumptions are documented.
- Backtesting or limitation notes are included where historical data allows.
- The output states the action trigger: reorder, capacity increase, stock transfer, or management review.
Anti-Patterns
- Joining raw transaction lines to product master data and creating duplicate demand.
- Treating sales during stockout periods as true demand.
- Hiding zero demand or missing history as unexplained blanks.
- Presenting point forecasts without history window, method, or caveat.
Outputs
- Demand forecast, stockout timing, reorder calculation, branch/SKU demand table, or business-plan assumption note.
References
Load references/demand_forecasting.md for SQL templates, stockout formulas, and demand-driven planning notes.
Inputs
| Artefact | Source or provider | Required? | If absent |
|---|---|---|---|
| Time-stamped demand, sales, stock, price, promotion, stockout, branch, and product data | POS, ERP, inventory, ecommerce, or approved extracts | Required | Return a data-gap assessment and scenario range, not a point forecast |
| Business calendar, lead times, service levels, and known events | Operations, procurement, and commercial owners | Required for replenishment | Keep reorder outputs conditional |
| Revenue, return, inventory, and unit definitions | Chwezi Accounting Doctrine and data owners | Required | Stop until measures and duplicate-row risks reconcile |
Outputs
| Artefact | Consumer | Acceptance condition |
|---|---|---|
| Forecast dataset and method note | Operations, procurement, sales, and finance | Grain, horizon, method, features, exclusions, uncertainty, and backtest are explicit |
| Reorder or capacity decision table | Stock and operating owners | Each action links to forecast range, lead time, service level, constraint, and override owner |
Evidence Produced
| Evidence | Format | Acceptance condition |
|---|---|---|
| Data-quality and join audit | Row-count, uniqueness, missingness, stockout, and reconciliation checks | Aggregation has not multiplied sales or confused zero demand with no stock |
| Forecast evaluation | Backtest by horizon and segment | Error is compared with a simple baseline and weak segments are visible |
Capability Contract
Analysis defaults to read-only. Do not alter source data, purchase orders, prices, promotions, or stock. Execution may run authorised queries and models against safe copies; production writes, spending, and accounting conclusions require explicit authority and review.
Degraded Mode
Without adequate history, execution, or reliable stockout and return data, return a qualified bounded scenario, data requirements, and a backtest marked not assessed. Do not call missing sales zero demand or present an untested model as accurate.
Decision Rules
| Choice | Action | Failure or risk avoided |
|---|---|---|
| Sparse or short history | Use a transparent baseline and wide range | Model overfit |
| Stable repeated pattern with enough history | Compare seasonal and statistical methods by backtest | Unnecessary complexity |
| Stockouts censor observed sales | Estimate lost-demand range and flag confidence | Under-forecasting constrained items |
| SQL join changes row counts at the target grain | Stop and repair aggregation before modelling | Duplicated demand |
Workflow
- Define forecast target, grain, horizon, decision, cost of error, and override authority.
- Extract and reconcile demand, stock, price, returns, promotions, calendar, and lead-time data.
- Run uniqueness, join, missingness, stockout, outlier, and accounting-definition checks; stop on unresolved multiplication.
- Establish naive baselines and time-respecting backtests.
- Fit only methods justified by history and compare error by segment and horizon.
- Translate forecast ranges into reorder, staffing, or capacity actions with constraints.
- Monitor actuals, overrides, drift, and error; recover by reverting to the best validated baseline.
Quality Standards
Forecasts must be reproducible, time-safe, reconciled to defined demand, compared with a baseline, and expressed with uncertainty. Decision usefulness matters more than model sophistication.
Anti-Patterns
- Randomly splitting time-series rows. Fix: backtest forward in time.
- Joining sales to multiple stock rows. Fix: aggregate each source to the target grain before joining.
- Treating stockout days as zero demand. Fix: flag censored observations and estimate a range.
- Optimising one overall error metric. Fix: inspect horizon, branch, SKU, and business cost of error.
- Reordering from a point forecast alone. Fix: include lead time, service level, pack size, stock, and uncertainty.
- Claiming accuracy without a naive baseline. Fix: show whether the method beats the simple alternative.
Worked Example
If a branch-SKU join doubles rows after adding promotions, stop before forecasting, aggregate promotions to one row per branch-SKU-date, reconcile totals, then backtest the repaired dataset against a seasonal naive forecast.