Stockout analysis: causes, frequency, and lost sales

Stock out analysis connects recorded inventory failures with the orders they affected. Measure how often an event occurred, how long the SKU was unavailable, and how much unfilled order value was later recovered or canceled. Keep these measures separate so a long outage is not mistaken for several independent failures.

Start with two files and one event definition

Use an inventory exception log for availability and an order-line exception export for commercial impact. The availability log needs event ID, SKU, location, start timestamp, end timestamp, and a reviewed cause. The order file needs event ID, order-line ID, requested units that were unfilled, net unit price, later recovered units, and canceled units.

Define a stockout event as one continuous period when the selected SKU could not be supplied from the selected location under the promised service conditions. A second alert during the same outage updates that event; it does not create another one. Decide whether stock reserved for other orders or stock on quality hold counts as unavailable for this purpose.

This page uses recorded events. When shelf or stock records are missing, inferring an out-of-stock from selling history is a separate method. Keep that inference label visible rather than blending inferred events into a confirmed incident count.

Calculate duration from the incident log

A fictional US distributor monitors three SKU-location pairs continuously from October 1 through October 10, 2026. That is 10 calendar days for each pair, or 30 SKU-location days and 720 monitored hours. Its event log contains these four distinct incidents; all timestamps use one local time zone.

In an Excel sheet named Events, use A for event ID, B for SKU, C for location, D for the start timestamp, E for the end, F for cause, and G for hours. Store timestamps as date-time values rather than text. Enter in G2 and fill through G5:

=(E2-D2)*24

The calendar-time exposure policy gives the following measures:

Round rates to two decimals. A business-hours policy is also possible, but both the event hours and exposure hours must then use that schedule. An event still open at period end contributes observed unavailable hours through the cutoff, while its completed duration remains unknown.

Reconcile unfilled orders to their outcomes

The order exception export below has one distinct affected order line per event. Each line's original unfilled quantity is split between later recovery and confirmed cancellation attributed to the stockout. Net unit price excludes tax and shipping.

For a sheet named Orders, put event in A, order-line ID in B, SKU in C, location in D, unfilled units in E, price in F, recovered units in G, and canceled units in H. Add value columns J:L:

J2 = E2*F2
K2 = G2*F2
L2 = H2*F2

Recovered value is 2 × 40 + 5 × 40 = $280. The reconciliation is $1,160 original unfilled value = $280 recovered value + $880 canceled value. Units also tie: 35 = 7 + 28. These checks use the original net unit price for all three columns; any later price change belongs in a separate explanation.

The $880 is recorded canceled order value attributable to these stockouts. It is not an estimate of every customer who might have purchased. Customers who never placed an order are absent from the exception file. The lost lines from stockouts guide examines affected order lines by branch and account in more detail.

Compare SKUs and locations without duplicating events

Put a selected SKU in N2 and location in O2. Count incidents from Events and sum hours from that same sheet:

=COUNTIFS(Events!$B$2:$B$5,N2,Events!$C$2:$C$5,O2)
=SUMIFS(Events!$G$2:$G$5,Events!$B$2:$B$5,N2,Events!$C$2:$C$5,O2)

Microsoft documents COUNTIFS for counting rows that meet multiple criteria and SUMIFS for adding values that meet multiple criteria. Apply the same SKU and location criteria to Orders when summing J or L.

SKU A at East has two incidents, 16 unavailable hours, $600 originally unfilled value, and $320 canceled value. It deserves a review of receipt timing and reorder release, rather than a blanket conclusion that all $600 was lost. The operations owner validates events, customer service confirms outcomes, and purchasing checks the reviewed cause against receipt and replenishment records.

Use out-of-stock analysis from sell-out gaps when the missing evidence is availability itself. Sell-in against sell-out addresses inventory movement through a retail channel, which is a different question from this distributor's recorded incidents.

Where it goes wrong

• One event repeats across order lines. Count events from the incident log. Adding its duration once per affected line inflates unavailable time.
• Backorders become lost sales immediately. Keep recovered, canceled, and unresolved quantities separate until outcomes are known.
• Exposure changes silently. Compare the same monitored assortment and time schedule, or disclose the denominator change.
• Overlapping outages are added twice. Merge overlapping intervals for the same SKU-location before calculating unavailable time.
• Cancellation reason is missing. A canceled line is not automatically evidence of a stockout-driven loss. Have customer service validate the attribution.

Covirage is not affiliated with Microsoft.

Check your stockout files in Covirage

Bring a sample of your own incident log and order exception file with matching identifiers. Covirage maps the columns, and its tools compute and check the recorded comparisons; 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 files.

See Covirage for distributors for the supporting workflow.