Blog · Data quality and reconciliation · Supply chain
Demand forecasting data starts with orders, shipments and stock availability at a consistent SKU and period. Use a worked example and spreadsheet checks to find missing history, duplicate rows and unexplained differences before accepting a forecast.
The data demand forecasting teams need includes complete customer orders, shipments and stock availability at the same SKU, location and period. A shipment history alone can hide demand that could not be filled. Check the coverage and reconcile the movements before treating an export as a reliable record of customer demand.
Choose what the forecast will predict: newly requested units, units due for delivery or units shipped. These are different series. The Census Bureau's manufacturing definitions distinguish new orders from shipments and describe unfilled orders as an opening balance plus net orders less sales. Your company's unit records need equally explicit definitions.
For a distributor planning replenishment, use requested units by requested delivery week, then keep shipment dates alongside them. Preserve the original request when a customer accepts a later delivery. Otherwise a shortage moves demand into the future and makes the old week look quieter than it was.
This is the input review before demand forecasting. It establishes what the history means; it does not choose a forecasting method.
Bring an order-line export, shipment-line export, stock availability history and promotion calendar. Keep the identifiers that connect them, rather than joining files on product descriptions. Order lines and shipment lines can have different row counts because an order can ship in parts.
| Input | Fields to retain | Question it answers |
|---|---|---|
| Orders | Order and line ID, SKU, site, requested date, ordered units, canceled units | What did customers request? |
| Shipments | Shipment ID, order-line ID, ship date, shipped units | What was fulfilled, and when? |
| Availability | SKU, site, date, available units or availability status | Could customers buy the item? |
| Product history | Active dates, replacement SKU, pack size | Was the item comparable over time? |
| Promotions | SKU, site or channel, start and end date, promotion flag | Which periods had a planned demand event? |
Write down whether cancellations reduce the original request period or the cancellation period. Keep returns separate from new orders unless the planning definition explicitly requires net demand. Convert cases and eaches before combining them.
An illustrative Ohio distributor expects weekly history for three active SKUs, A, B and C, across two weeks. The expected grid therefore contains six combinations. Its summarized export contains only these four:
| SKU | Week starting | Net orders (units) | Shipments (units) | Opening backlog (units) | Closing backlog (units) | Stockout days | Promotion |
|---|---|---|---|---|---|---|---|
| A | October 5, 2026 | 100 | 90 | 20 | 30 | 0 | No |
| A | October 12, 2026 | 120 | 100 | 30 | 50 | 2 | Yes |
| B | October 5, 2026 | 80 | 80 | 0 | 0 | 0 | No |
| B | October 12, 2026 | 0 | 0 | 0 | 0 | 0 | No |
Put these columns in A:H, rows 2:5. Enter the expected SKU and week combinations in J:K, rows 2:7. For each expected combination, count the matching summarized rows in L2 and fill down:
=COUNTIFS($A$2:$A$5,J2,$B$2:$B$5,K2)
COUNTIFS counts rows meeting all supplied conditions. A result of zero identifies a missing combination; a result above one identifies a duplicate at this summarized grain. Multiple order lines in the original transaction file are expected, so run this uniqueness test after aggregation.
Covirage is not affiliated with Microsoft.
Calculate period coverage with:
=COUNTIF(L2:L7,">0")/COUNTA(J2:J7)
Coverage is 4 / 6 = 66.7%, rounded to one decimal place. Both C weeks are missing. B's second week is present with a confirmed zero; it is not the same problem. If C is a new product, route that exception to demand forecasting without historical data rather than inventing history.
In I2, compute the movement residual and fill down:
=E2+C2-D2-F2
Each row returns zero. A's opening backlog of 20 units plus 100 orders minus 90 shipments equals its 30-unit closing backlog. The following week's opening must equal the preceding week's closing for the same SKU and site.
Across the full period, take opening balances from the first week and closing balances from the last week:
| Full-period check | Units |
|---|---|
| Opening backlog, A and B | 20 |
| Net orders, all four rows | 300 |
| Less shipments, all four rows | 270 |
| Calculated ending backlog | 50 |
| Reported ending backlog, A and B | 50 |
| Difference | 0 |
Do not sum weekly opening or closing backlog balances: they are snapshots. Orders and shipments are flows and do sum across weeks. Confirm the reported balances are complete before extending this reconciliation to missing SKU C.
A's second week has both a promotion and stockouts. The 100 shipped units cannot establish unconstrained demand, and the 120 ordered units may omit customers who left without ordering. Retain both flags. Investigate availability using out-of-stock sell-out gaps; do not silently inflate the observed units.
Keep this readiness review separate from forecast bias by SKU and site, which evaluates forecasts after comparable actuals exist.
Bring your order, shipment and availability exports, with the expected SKU-period list and source definitions. Covirage maps the columns, and its tools compute and check coverage, totals and supplied movements; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data; account creation takes about a minute.
See Covirage for supply chain teams for the wider business context.
Shipments show what left the business, which can differ from what customers wanted. Compare shipments with orders and stock availability. A shortage can suppress shipments while orders accumulate, and a later recovery can make shipments rise without an equivalent increase in new demand.
Only enter zero when the source confirms that the SKU was active and no demand occurred. A missing export, discontinued product or unavailable item needs its own status. Replacing every missing row with zero can create an artificial fall in demand.
Collect enough history to cover the patterns relevant to the decision, including seasonal cycles when they matter. Retain dates for assortment, channel and operating changes. More history is useful only when its definitions and business conditions remain comparable with the period being forecast.
The planner coordinates the review, but sales operations should confirm order definitions and operations should confirm shipment and availability records. The product owner should explain launches, discontinuations and replacements. Each unresolved exception needs an owner and a documented treatment before the history is accepted.