Forecast retail demand when stockouts hide lost sales

A beauty and personal-care retailer replenish its flagship Vitamin-C face serum (SKU-SERUM-VITC-30ML, MRP ₹680.00, landed cost ₹390.00, unit gross margin ₹290.00) every two weeks across its top regional distribution hub. Over the first 10 days of a 14-day review cycle, the SKU sells steadily at 15 units per day (150 units total), exhausting the hub's entire sellable stock by Tuesday evening (Day 10). For the remaining 4 days of the cycle (Days 11 to 14), Available-to-Sell (ATS) is 0 units and recorded POS/web sales are 0 units per day while awaiting the next vendor delivery. On Sunday night, the ERP's automated replenishment job divides total 14-day sales (150 units) by 14 calendar days to compute a trailing velocity of 10.71 units/day (150 units forecast for the next 14-day cycle). Because the algorithm treats 4 days of empty shelves as 4 days of zero customer interest, it under-orders by 60 units (150 ordered vs 210 true demand), guaranteeing that the SKU will stock out again on Day 10 of the next cycle and forfeiting ₹17,400.00 (60 x ₹290.00) in gross margin every fortnight.

The operational decision for a demand planning lead across retail chains and e-commerce merchants is how to combine daily inventory availability snapshots—grounded in accurate location-level inventory state tracking and open-to-buy and replenishment planning discipline—with a clearly labelled stockout-imputation rule so lost sales are corrected without inventing unverified demand (Shopify Help Center — Inventory states (Available, On hand, Committed, Incoming, Unavailable), Shopify Retail Blog — Open-to-buy (OTB) planning and inventory budgeting guide). By linking this control to promotion demand forecast pilot controls and retail metric definition contracts, demand planning lead teams prevent downstream inventory, customer-promise, and financial breaks before stock or cash moves.

Why unadjusted stockouts create a downward replenishment spiral

In retail demand planning, point-of-sale (POS) and order-capture tables record fulfilled transactions, not unconstrained customer demand. Whenever sellable inventory drops to zero (or drops below the minimum display fixture quantity in a store), demand is right-censored: customers who wanted to buy the product either abandon the purchase or switch to another item, leaving zero trace in the sales ledger.

When a forecasting model ingests raw historical sales without joining daily closing inventory states, three operational failures compound across cycles:

1. The self-reinforcing stockout death spiral: A 4-day stockout in a 14-day window reduces measured velocity by 4 / 14 = 28.57% (from 15.00 to 10.71 units/day). If the planner orders 150 units for the next 14-day window and true demand remains 15.00 units/day, the SKU stocks out after 10 days again. If a supplier delay extends the next stockout to 6 days, naive velocity drops further to 90 / 14 = 6.43 units/day, progressively starving your best-selling SKU of open-to-buy budget.
2. Opaque manual overrides that corrupt data provenance: To fight the downward bias, merchandisers often overwrite historical sales numbers directly in the planning spreadsheet (changing Day 11–14 sales from 0 to 20). Months later, finance and inventory planners cannot tell which historical numbers were actual cash-register sales (150 units) and which were planner guesses.
3. Unrecognised 'phantom stockout' days: In physical stores, an ERP ledger may show 2 units on hand (On_Hand = 2) when those 2 units are actually damaged in the backroom or mis-shelved. The shelf is physically empty (0 units sold for 4 consecutive days), yet a naive On_Hand == 0 filter fails to flag the stockout because the book balance is non-zero.

To correct stockout bias transparently while preserving auditability, enforce four explicit equations in your demand-planning data contract:

• Naive Biased Daily Velocity (V_naive) = Total_Observed_Sales / Total_Calendar_Days = 150 / 14 = 10.714 units/day
• In-Stock Uncensored Daily Velocity (V_instock) = Observed_Sales_On_Available_Days / Available_In_Stock_Days = 150 / 10 = 15.00 units/day
• Explicitly Labelled Lost-Sales Estimate (Q_lost_est) = Censored_Stockout_Days x V_instock = 4 x 15.00 = 60.00 units
• Reconstructed Baseline Cycle Demand (D_adj) = Observed_Sales (150) + Q_lost_est (60) = 210 units (protecting 60 x ₹290.00 = ₹17,400.00 margin)

Crucially, Observed_Sales (150) and Q_lost_est (60) must live in separate columns (actual_qty vs imputed_lost_sales_qty) with an explicit imputation_method_code so any planner can compare the zero-demand assumption against the imputed estimate side by side.

By keeping 150 actual units and 60 imputed lost-sales units visible as two distinct components of the 210-unit forecast, the demand planning lead eliminates stockout bias without sacrificing data integrity.

  <path d="M219 85 L248 85" stroke="#475569" stroke-width="1.8" marker-end="url(#arrow-r029)"/>

  <rect x="248" y="40" width="195" height="90" rx="8" fill="#fef9c3" stroke="#ca8a04" stroke-width="1.5"/>
  <text x="262" y="64" font-weight="700" fill="#854d0e">2. 2. Censoring Detection</text>
  <text x="262" y="84">Flag Days 11-14 as Censored</text>
  <text x="262" y="102">Exclude 4 days from denom</text>
  <text x="262" y="118" font-size="11" fill="#475569">In-stock days: 10 of 14</text>

  <path d="M443 85 L472 85" stroke="#475569" stroke-width="1.8" marker-end="url(#arrow-r029)"/>

  <rect x="472" y="40" width="195" height="90" rx="8" fill="#ffedd5" stroke="#ea580c" stroke-width="1.5"/>
  <text x="486" y="64" font-weight="700" fill="#9a3412">3. 3. Labelled Imputation</text>
  <text x="486" y="84">Naive: 150/14 = 10.71u/day</text>
  <text x="486" y="102">Uncensored: 150/10 = 15.00/d</text>
  <text x="486" y="118" font-size="11" fill="#475569">Imputed lost: 4x15 = 60u</text>

  <path d="M667 85 L696 85" stroke="#475569" stroke-width="1.8" marker-end="url(#arrow-r029)"/>

  <rect x="696" y="40" width="200" height="90" rx="8" fill="#dcfce7" stroke="#16a34a" stroke-width="1.5"/>
  <text x="710" y="64" font-weight="700" fill="#166534">4. 4. Dual-Column Forecast</text>
  <text x="710" y="84">Base observed: 150 units</text>
  <text x="710" y="102">Labelled uplift: +60 units</text>
  <text x="710" y="118" font-size="11" fill="#475569">Next 14d order: 210 units</text>

  <rect x="24" y="166" width="872" height="164" rx="8" fill="#ffffff" stroke="#cbd5e1" stroke-width="1.2"/>
  <text x="44" y="194" font-weight="700" font-size="13" fill="#0f172a">Demand planning stockout-censoring and imputation rules (demand planning lead)</text>
  <text x="44" y="220" fill="#334155">• Gate A: Daily Availability Join — Join daily POS/web unit sales with end-of-day `Available_to_Sell (ATS)`. Flag any day where `ATS == 0` (or `< Min_Display_Qty`) for `>6 hours`.</text>
  <text x="44" y="246" fill="#334155">• Gate B: Separate Actual vs Imputed Columns — Store `actual_sales = 150` and `imputed_lost_sales = 60` separately; never overwrite historical POS transaction tables.</text>
  <text x="44" y="272" fill="#334155">• Gate C: Promotional Spike Guardrail — If a stockout occurs during a flash promotion, cap imputed lost sales at the non-promotional baseline rate (`15.00/day`) unless promo extension is approved.</text>
  <text x="44" y="302" font-weight="600" fill="#0369a1">Outcome: Prevents a 60-unit (`28.6%`) under-order on the next replenishment cycle and protects ₹17,400.00 in gross margin.</text>
</g>
<defs>
  <marker id="arrow-r029" viewBox="0 0 10 10" refX="8" refY="5" markerWidth="6" markerHeight="6" orient="auto-start-reverse">
    <path d="M 0 0 L 10 5 L 0 10 z" fill="#475569"/>
  </marker>
</defs>

Worked illustrative example: comparing zero-demand treatment vs labelled estimation across a 4-day stockout

Illustrative example (hypothetical scenario): Examine the 14-day replenishment review for SKU-SERUM-VITC-30ML (MRP ₹680.00, Landed Cost ₹390.00, Gross Margin ₹290.00/unit) after the distribution hub runs out of stock for the final 4 days of a 14-day cycle.

Step 1: Establish the baseline operational and commercial parameters

1. Observed 14-day sales and inventory log:
   • Days 1 to 10 (In-stock period, ATS > 0): The SKU sells [14, 16, 15, 15, 17, 14, 15, 16, 14, 14] units across the 10 days = 150 units observed sales (Observed Revenue = 150 x ₹680.00 = ₹1,02,000.00; Observed Gross Margin = 150 x ₹290.00 = ₹43,500.00).
   • Days 11 to 14 (Stockout period, ATS = 0): Closing and intraday sellable stock is 0 units for 4 consecutive days. Recorded POS/web sales = [0, 0, 0, 0] = 0 units.
2. Option A — Treating stockout days as true zero demand (Naive ERP average):
   • Daily velocity calculation: V_naive = 150 units / 14 calendar days = 10.7143 units/day.
   • Next 14-day replenishment forecast: 10.7143 x 14 days = 150 units (working capital cost = 150 x ₹390.00 = ₹58,500.00).
   • Operational consequence in next cycle: Because true customer demand is still 15.00 units/day, ordering 150 units guarantees the hub will run out of stock again at the end of Day 10 (150 / 15.00 = 10.0 days), leaving Days 11 to 14 empty for a second consecutive fortnight.

Step 2: Reconcile states, exceptions, and financial impact

3. Option B — Censoring stockout days and applying a clearly labelled estimate (Governed workflow):
   • In-stock velocity calculation: Exclude the 4 censored days (ATS == 0) from the denominator: V_instock = 150 units / 10 in-stock days = 15.00 units/day (applying day-of-week seasonal weights if weekend traffic differs, which here averages 15.00 units/day).
   • Labelled lost-sales estimate (Days 11–14): Q_lost_est = 4 censored days x 15.00 units/day = 60 units (Label: IMPUTED_STOCKOUT_TRAILING_MEAN).
   • Reconstructed 14-day demand and next-cycle order: D_adj = 150 actual units + 60 imputed units = 210 units (15.00 units/day x 14 days).
   • Commercial and working-capital comparison: Ordering 210 units instead of 150 units requires an incremental working-capital outlay of 60 units x ₹390.00 = ₹23,400.00 (total PO cost = ₹81,900.00 vs ₹58,500.00), which generates 60 units x ₹680.00 = ₹40,800.00 in incremental revenue and 60 units x ₹290.00 = ₹17,400.00 in protected gross margin during the 14-day cycle.

Verified scenario calculation summary

• Observed_10_Day_Sales = 10 days * 15.00 units/day = 150 units (₹1,02,000.00 revenue; ₹43,500.00 gross margin)
• Naive_14_Day_Daily_Velocity (V_naive) = 150 units / 14 calendar days = 10.7143 units/day (-28.57% downward bias)
• Uncensored_InStock_Velocity (V_instock) = 150 units / 10 in-stock days = 15.00 units/day
• Imputed_4_Day_Lost_Sales (Q_lost_est) = 4 stockout days * 15.00 units/day = 60 units
• Reconstructed_14_Day_Forecast (D_adj) = 150 observed units + 60 imputed units = 210 units (15.00 * 14)
• Protected_Cycle_Gross_Margin = 60 incremental units * ₹290.00 margin/unit = ₹17,400.00 (on ₹23,400.00 incremental PO cost and ₹40,800.00 incremental revenue)

Showing the open-to-buy approver that the 210-unit recommendation consists of 150 verified POS sales plus 60 explicitly labelled stockout-imputed units (4 days x 15/day) gives finance complete confidence to approve the extra ₹23,400.00 purchase order.

Stockout-adjusted demand forecasting workflow: roles, exception handling, and system choice

To eliminate stockout bias from your replenishment forecasts while keeping every imputed unit auditable, implement a four-step planning cadence:

1. Nightly end-of-day and intraday availability snapshot logging (Owner: Data Engineering / Inventory Systems). Capture a daily SKU-Location Availability Snapshot recording opening ATS, closing ATS, and hours in stock. Mark is_stockout_censored = TRUE whenever ATS == 0 (or < minimum display threshold) for more than 6 selling hours.
2. Automated dual-series demand calculation (Owner: Demand Planning Lead). Calculate both V_naive (10.71/day) and V_instock (15.00/day) for every replenishment SKU. Store the imputed lost sales (60 units) in a dedicated imputed_lost_sales_qty field tagged with the imputation rule (Trailing_InStock_Mean_DayOfWeek).
3. Cap and exception review for long or promotional stockouts (Owner: Category Merchandise Planner). If a SKU has been out of stock for more than 50% of the review window (>7 of 14 days) or stocked out during a one-off flash sale, route the imputation to the planner's exception queue rather than extrapolating a short promotional spike across 14 days.
4. Post-replenishment forecast accuracy tracking (Owner: Commercial Finance + Demand Planning). In the cycle following an imputed uplift (when 210 units are stocked), measure actual realized sales against the 210-unit forecast to verify that the imputed demand (60 units) converted into real cash sales rather than excess aged stock.

Choosing the right tool: You do not need machine-learning black boxes to fix stockout bias. A deterministic SQL view or planning-sheet formula that joins daily closing ATS to daily sales, excludes ATS == 0 days from the velocity denominator, and displays Actual Sales (150) alongside Imputed Lost Sales (60) solves 90% of retail stockout under-forecasting.

Operational governance and audit controls for sustained compliance

To prevent this workflow from eroding after the initial implementation sprint, embed four recurring governance checkpoints into your weekly retail operations cadence:

1. Version-locked rule and threshold tables: Store every tolerance band, status-transition rule, and exception-routing map in a versioned configuration table rather than ad-hoc personal spreadsheets. Any threshold change requires sign-off from demand planning lead and commercial finance, recording the effective timestamp and the cohort of SKUs or locations affected.
2. Automated daily orphan and stale-state sweep: Run a scheduled morning query that flags records stuck in an intermediate state (Pending Verification, In Transit, Awaiting Confirmation, or Held for Review) beyond their agreed service-level cutoff. Route aged exceptions directly to the named role owner before customer promises or period-close cutoffs are breached.
3. Separation of operational execution from financial write-off authority: Staff who execute physical picks, catalogue edits, or customer service actions should never have unilateral system permission to clear reconciliation variances or post financial write-offs. Require dual sign-off once cumulative weekly adjustments on a SKU, store, or vendor exceed your materiality floor.
4. Monthly root-cause retrospective tied to source-system hygiene: Aggregate exception logs by upstream root cause—such as missing supplier identifiers, delayed webhook deliveries, or uncalibrated store stock counts—and require the responsible upstream team to remediate the top three recurring defects before the next monthly cycle.

Observable acceptance tests for your retail systems

Before relying on this workflow in production, execute three deterministic acceptance tests (ACT-R029-01 to ACT-R029-03) in your staging environment or controlled worksheet:

Putting stockout-aware demand forecasting into practice

To stop empty shelves from tricking your replenishment system into ordering less of your bestsellers, implement these four steps:

1. Start archiving daily closing Available-to-Sell (ATS) by SKU and location: Even if your ERP only stores current stock by default, schedule a nightly midnight table snapshot so your forecast knows exactly which historical days had zero stock.
2. Split your forecast review screen into Observed Sales and Imputed Stockout Demand: Never hide lost-sales adjustments inside a black-box number; show buyers 150 actual + 60 stockout estimate = 210 total.
3. Add a minimum display threshold (e.g., 2 units) to your store stockout flag: In physical retail, the last 1 or 2 units are often damaged or misplaced; censor store days when on-hand stock falls below a sellable fixture floor.
4. Audit post-imputation sell-through after every stockout recovery: Verify that SKUs given a stockout forecast uplift actually sell at the imputed rate once stock is restored.