Forecasting lost sales during stockouts

Forecasting lost sales estimates demand that an item could not serve during a stockout. Calculate a selling rate from comparable in-stock periods and apply it to the unavailable selling time. The result is an estimate with assumptions, so preserve actual sales and show a range alongside the central calculation.

Establish the stockout before estimating demand

Start with product-location sales, store opening hours and evidence of when the product was available. Inventory records, receiving times and confirmed shelf checks can support that evidence. A low sales figure alone does not establish the start or end of a stockout.

The worked example on inferring an out-of-stock covers the evidence problem. This calculation starts after the team has accepted an availability window.

Keep three quantities separate: observed sales while the item was available, estimated demand during unavailable time, and the sum of the two. The last quantity is an estimate of demand under availability, not a revised transaction total. The unconstrained demand forecast explains that broader planning distinction.

Define the loss being estimated. Unmet demand for one SKU can be larger than the retailer's net lost business if customers buy substitutes. A deferred purchase may move sales to a later period rather than remove them entirely. Label the calculation as item-level missed demand until those effects have been assessed.

Choose comparable in-stock periods

Use the same product and location where possible. Match weekday, opening pattern, season, selling price and promotion conditions. A weekday morning is not automatically comparable with a weekend afternoon, even if both contain the same number of hours.

Hyndman and Athanasopoulos explain that trading-day composition can materially affect sales. That supports matching the selling calendar before transferring a rate from one period to another.

For this worked example, a US retailer has three accepted reference Mondays for one product. All listed hours were confirmed in stock. Assume that the references and the target day's unavailable hours have comparable selling intensity; if the unavailable hours were concentrated at peak shopping time, use matched hour blocks instead.

The individual rates are 40 / 10 = 4.0, 36 / 8 = 4.5 and 44 / 10 = 4.4 units per hour. Pool the reference units and hours so each observed selling hour receives the same weight:

Comparable selling rate = Reference units sold / Reference in-stock selling hours

120 / 28 = 4.285714… units per hour

Use the unrounded rate in subsequent calculations. Only the displayed answers are rounded.

Apply the rate to the unavailable window

The target Monday had ten planned selling hours, six confirmed in-stock hours and 27 recorded sales. The item sold at an assumed price of $12 per unit, with no discounts in this illustration.

Estimated missed units = Comparable selling rate × Unavailable selling hours

Estimated demand under availability = Observed sales + Estimated missed units

Estimated units and dollars are displayed to two decimal places. Fractional estimated units describe an expected quantity; they do not claim that a fractional physical item could have been sold.

The arithmetic checks are 6 + 4 = 10 selling hours and 44.14 − 27 = 17.14 estimated missed units at displayed precision. The $205.71 is a gross sales opportunity estimate before substitution or deferred purchases. It is not recorded revenue or lost profit.

Rebuild the calculation in a spreadsheet

In Microsoft Excel, enter the reference rows in A2:D4: period, units, in-stock hours and comparable flag. Put the pooled rate in B10:

=SUMIFS(B2:B4,D2:D4,"Yes")/SUMIFS(C2:C4,D2:D4,"Yes")

Microsoft documents SUMIFS as adding cells that meet the associated criteria. Confirm that the selected hours sum to a positive value before dividing; no accepted hours means the rate is unavailable, not zero. Covirage is not affiliated with Microsoft.

Use row 7 for the target period. Enter observed units in B7, in-stock hours in C7, planned hours in D7 and price in H7. Then calculate:

E7: =MAX(0,D7-C7)
F7: =E7*$B$10
G7: =B7+F7
I7: =F7*H7

E7 is unavailable time, F7 estimated missed units, G7 estimated demand and I7 estimated missed sales value. Check separately that C7 is between zero and D7: MAX prevents a negative estimate but does not make invalid availability inputs acceptable.

Reconcile B7 to the transaction export. Check that the reference periods do not overlap and that their units and hours refer to the same product-location combination. Preserve the selected periods so another planner can reproduce the estimate.

Test a range before using the estimate

Use the lowest and highest reference rates as simple sensitivity scenarios. Here those rates are 4.0 and 4.5 units per hour. Apply both to the same four unavailable hours:

The lower calculation is 4.0 × 4 = 16 units, then 27 + 16 = 43 and 16 × 12 = $192. The higher calculation is 4.5 × 4 = 18 units, then 27 + 18 = 45 and 18 × 12 = $216.

This is a sensitivity range, not a statistical confidence interval. It tests the consequence of alternative observed rates without assigning probabilities to them. If a promotion affected the target day but not the references, use a separate sales promotion forecast assumption rather than quietly inflating the rate.

The demand planner owns reference selection; store operations confirms unavailable time; finance reviews valuation and any claim about net business loss. Guidance on judgmental forecasting emphasizes documenting assumptions and evaluating results. Keep those assumptions beside the estimate in your sales forecasting reports.

Where it goes wrong

• Zero sales treated as confirmed unavailability. Check the operating evidence before estimating missed demand.
• Calendar or promotion mismatch. A rate from a quieter or differently promoted period may not transfer to the target hours.
• Rounding the rate too early. Retain full precision until the displayed units and dollars are calculated.
• Replacing actual sales with estimated demand. Keep observed and estimated quantities in separate columns throughout the review.
• Calling item demand net lost business. Substitution and delayed purchases can reduce the loss to the company.

Check your stockout assumptions in Covirage

Bring a sample of your own sales, availability and comparable-period files to Covirage, including the assumptions behind your missed-demand estimate. Covirage maps the columns, and its tools compute and check the figures; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring your export.

See Covirage for supply chain teams for the supporting business context.