Blog · Board and management reporting · Supply chain
A stock movement report explains how opening quantities become closing stock. Build a reconciled report from movement exports, separate transfers from consumption, and identify unexplained differences by SKU.
A simple stock movement report explains closing stock as opening quantities plus receipts, less issues, plus signed transfers and adjustments. Calculate it for each SKU and location, then compare it with a separately exported closing balance. A report is ready for management when differences are resolved or clearly assigned for investigation.
Use the same unit of measure, location coverage, and posting cutoff throughout the report. A carton and an individual component cannot share a quantity column until you convert them to an agreed base unit. Preserve both the original unit and the conversion used.
Calculated closing quantity = Opening quantity + Receipts - Issues + Net transfers + Signed adjustments
Unexplained difference = Reported closing quantity - Calculated closing quantity
Receipts are purchases or production receipts in this example. Issues are customer shipments or production consumption. Transfers move existing stock between locations. Adjustments include approved count corrections and write-offs, with positive quantities increasing stock and negative quantities reducing it.
Define customer returns separately if they are material, or document their inclusion within receipts. Do the same for supplier returns within issues. An adjustment must have a reason and document reference; it should never be a plug that makes the reconciliation work.
For the wider package of stock measures, see inventory reporting. This report answers the narrower question of how quantities changed.
Bring an opening stock snapshot, the period's posted movement export, and an independent closing snapshot. Include SKUs with no movements, since their opening balances still belong in closing stock.
Use these columns on a worksheet named Movements:
| Column | Field | Rule |
|---|---|---|
| A | Posting date | A real spreadsheet date; preserve time if present |
| B | SKU | Stable item code, including leading zeros |
| C | Location | Include an in-transit location when applicable |
| D | Document and line reference | Identifies the posted entry |
| E | Movement type | Receipt, Issue, Transfer, or Adjustment |
| F | Signed quantity | Positive into the location, negative out |
Retain reversals as separate entries with the opposite sign. Deduplicate repeated exports using the actual entry identifier, rather than deleting rows simply because their dates and quantities match. Two legitimate shipments can have identical quantities.
In this illustrative example, Pine Creek Components is an Ohio distributor reviewing September 2026. On a summary worksheet, columns A through I are SKU, opening, receipts, issues, net transfers, adjustments, calculated closing, reported closing, and difference. Opening and reported closing come from the dated snapshots.
For receipts in C2, use this Excel formula and fill down:
=SUMIFS(Movements!$F$2:$F$1000,Movements!$B$2:$B$1000,$A2,Movements!$E$2:$E$1000,"Receipt",Movements!$A$2:$A$1000,">="&DATE(2026,9,1),Movements!$A$2:$A$1000,"<"&DATE(2026,10,1))
For D2, use the same formula with "Issue" replacing "Receipt" and a minus sign before SUMIFS. That displays issues as positive quantities to subtract. For E2 and F2, use "Transfer" and "Adjustment" respectively, retaining their signed quantities.
Microsoft's SUMIFS documentation explains how the function sums values satisfying multiple criteria. Extend the ranges to cover every exported row; all criteria ranges must cover the same rows. The exclusive October cutoff includes September entries that contain timestamps.
Calculate G2 and I2, then fill down:
=B2+C2-D2+E2+F2
=H2-G2
For a location report, add a location criterion to each movement formula and use snapshots for that same location. The stock movement analysis in Excel guide covers the separate analysis of movement patterns.
All quantities below are individual components, recorded as whole units. No rounding is required. Net transfers are zero because both included warehouses are in the company scope.
| SKU | Opening | Receipts | Issues | Net transfers | Adjustments | Calculated closing | Reported closing | Difference | |---|---:|---:|---:|---:|---:|---:|---:|---:|---:| | A100 | 500 | 200 | 350 | 0 | -5 | 345 | 345 | 0 | | B200 | 240 | 100 | 180 | 0 | 3 | 163 | 163 | 0 | | C300 | 120 | 60 | 90 | 0 | -2 | 88 | 88 | 0 | | Total | 860 | 360 | 620 | 0 | -4 | 596 | 596 | 0 |
The control total is 860 + 360 - 620 + 0 - 4 = 596. Each SKU also reconciles independently. Check the total absolute difference, since positive and negative errors can cancel:
=SUMPRODUCT(ABS(I2:I4))
The result must be zero. Also reconcile receipt and issue totals to the corresponding posted transaction exports; a matching closing balance alone can conceal offsetting omissions.
The A100 company row can be rebuilt from its locations:
| Location | Opening | Receipts | Issues | Net transfers | Adjustments | Closing |
|---|---|---|---|---|---|---|
| Cleveland | 300 | 120 | 220 | -40 | -3 | 157 |
| Columbus | 200 | 80 | 130 | 40 | -2 | 188 |
| Total A100 | 500 | 200 | 350 | 0 | -5 | 345 |
The transfer reduces Cleveland and increases Columbus by the same quantity. Company issues remain 350; the transferred units were relocated rather than consumed. If the receiving entry has not posted, reconcile the shipment to in-transit stock instead of assuming loss.
Physical accuracy needs another check. Microsoft's inventory counting guidance distinguishes calculated quantities from actual counted quantities and explains recording their differences. Investigate the cause before approving an adjustment. Covirage is not affiliated with Microsoft.
Upload your opening balances, posted movements, and closing quantities as a file or Google Sheet; Covirage maps the columns, and its tools compute and check the quantity reconciliation. 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 export.
See Covirage for supply chain teams for the supporting business context.
An inventory balance report shows quantities at a particular time. A movement report explains the receipts, issues, transfers, and adjustments between two times. Use the movement report to investigate a changing balance and the balance report to confirm what the system records at the cutoff.
Keep transfers in a separate movement category when reviewing purchases and consumption. A transfer changes the balance at each location but cancels across the included locations when both sides are recorded. Goods still in transit need an explicit location or a separately reconciled balance.
No. It proves that the reported movements explain the recorded closing balance. A physical count tests whether that balance exists on the shelf. Keep count differences separate from unexplained report differences, and retain the approved adjustment that connects the count to the inventory records.