Inventory stock movement analysis in Excel

Inventory stock movement analysis in Excel starts with one reconciliation for each SKU and site: opening stock plus receipts minus issues plus signed adjustments equals expected closing stock. Compare that result with the closing inventory export and investigate every difference. Totals can hide warehouse errors.

Set the cutoff and the unit first

For an October reconciliation, take opening stock immediately before October 1, 2026, and closing stock immediately before November 1, 2026. Include movements from October 1 through October 31. Use the same time zone and posting basis for all three exports.

Bring an opening snapshot, a movement register and a closing snapshot. The movement register needs a posting date, SKU, site, movement type, quantity and transaction reference. Preserve transaction references even if they do not appear in the summary; they are how a warehouse supervisor finds the receipt or issue behind a difference.

Keep the unit of measure beside the SKU. Cases and individual units cannot share an unconverted quantity column. The example below uses individual units throughout. Reconcile quantities before comparing inventory values, since a cost change can move dollars without changing the physical stock.

This answers whether movements explain the balance. Inventory aging by site answers the separate question of how long stock has sat without movement.

Prepare the movement register

Keep the original export on its own worksheet. On a working copy, standardize site names and movement labels, identify duplicate transaction references, and confirm that quantities and dates are numeric spreadsheet values. Do not delete repeated references until you know whether they identify documents or individual lines.

Convert the working range to a table named Movements. Its columns are Date, SKU, Site, Type and Quantity, plus the transaction reference. Use positive quantities for receipts and issues, and signed quantities for adjustments.

A US distributor has these October movements. Each displayed row represents one source movement; Dallas has no adjustment row.

Keep returns explicit in the mapping. A customer return that restores physical stock increases quantity; a supplier return reduces it. Document which reporting column receives each source movement code so the same event cannot enter twice.

Build the reconciliation with SUMIFS

On a Reconcile sheet, put SKU in A, site in B, opening quantity in C, receipts in D, issues in E, adjustments in F, expected closing in G, reported closing in H and variance in I. Build the SKU and site list from the union of all three exports, including keys that appear only in movements.

Set P1 to =DATE(2026,10,1) and P2 to =DATE(2026,11,1). The exclusive end date also works when posting dates contain times. Microsoft documents that SUMIFS adds values only when all the specified criteria are met.

In D2, E2 and F2 respectively, enter:

=SUMIFS(Movements[Quantity],Movements[SKU],$A2,Movements[Site],$B2,Movements[Type],"Receipt",Movements[Date],">="&$P$1,Movements[Date],"<"&$P$2)
=SUMIFS(Movements[Quantity],Movements[SKU],$A2,Movements[Site],$B2,Movements[Type],"Issue",Movements[Date],">="&$P$1,Movements[Date],"<"&$P$2)
=SUMIFS(Movements[Quantity],Movements[SKU],$A2,Movements[Site],$B2,Movements[Type],"Adjustment",Movements[Date],">="&$P$1,Movements[Date],"<"&$P$2)

Enter the calculation and comparison in G2 and I2, then fill all formulas down:

=C2+D2-E2+F2
=H2-G2

Opening and reported closing come from the corresponding snapshots, matched on both SKU and site. If either snapshot contains multiple bins for a key, sum the bins first rather than taking the first match.

The Dallas result is 80 + 20 - 50 + 0 = 50 expected units. The snapshot has 54, so reported stock exceeds the movement result by 4 units. These are whole-unit calculations with no rounding.

Prove the total and resolve the exception

Use column totals as control checks. Microsoft's formula guide describes SUM for adding a range; for example, =SUM(G2:G4) totals expected closing quantity.

The control identity is 230 + 120 - 170 + 1 = 181. Reported closing less expected closing is 185 - 181 = 4, which equals the sum of row variances. Recheck the exported snapshot totals and each movement category against these summary totals.

Filter I for nonzero results. For Dallas, inspect the closing snapshot, receipt documents, issue postings and count adjustments for the same key and cutoff. Assign an owner and a reason supported by a source reference. Keep an unresolved difference visible; do not insert an adjustment merely to make the workbook balance.

After the balances reconcile, carry them into an inventory KPI dashboard. Use the available quantities and confirmed receipts in demand planning in Excel when deciding replenishment.

Where it goes wrong

• Subtracting a negative issue twice. Convert the source convention once and retain that convention throughout the workbook.
• Dropping new keys. A SKU received during October may be absent from the opening snapshot. A summary based only on opening keys misses it.
• Ignoring transfers in transit. A sending-site issue and receiving-site receipt can fall in different periods. Reconcile the transfer reference and the in-transit balance.
• Mixing cutoff definitions. A physical count at noon cannot reconcile directly to a movement register through midnight without a separate bridge.
• Netting differences between sites. Investigate the SKU and site rows even when the total variance is zero.

Covirage is not affiliated with Microsoft.

Reconcile your own inventory file in Covirage

Bring a workbook containing your opening stock, movement register and closing snapshot. Covirage maps the columns, and its tools compute and check the comparisons; 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 wider operating context.