How to reconcile POS, shipments and inventory data

A retailer file shows 300 units sold, the ERP shows 400 units shipped and the stock count says 150 on hand. All three numbers are correct; they describe different moments of the same product flow. Comparing the totals directly produces a variance that no single number explains. The reconciliation connects consumer sales, supplier shipments and inventory movements through one stock equation.

This guide shows how to build that bridge: define the event model, choose one grain, lay out the stock equation, and then explain every remaining difference as timing, a missing movement or a classified exception.

Define the event model first

Write down what each source actually records. POS registers consumer sales at the till, by store, day and SKU. Shipments, or sell-in, record product leaving the supplier's control, by delivery, date and customer. Inventory records what is physically in the network at a point in time. The same product travels through all three, but each source captures a different event.

State the events before comparing numbers. If the comparison treats a shipment as a sale or a count as a flow, the bridge will produce a variance that the equation cannot explain.

The stock equation is the bridge

Opening stock + shipments received − POS sell-out − returns, damages and shrinkage = closing stock. If you have any four terms, the fifth is implied. The equation is the reconciliation: compute the implied closing stock from shipments and sales, then compare it with the counted stock. The difference is a movement the equation cannot see, not a rounding error.

Give every term a named source. Opening stock comes from the stock count, shipments from delivery notes, sell-out from the POS file, returns from credit notes and the closing stock from the next count. A term without a source becomes a hidden residual.

Choose one grain for the comparison

Decide the level at which the equation must balance: product-store-month, product-banner-week or product-market-month. The grain must be supported by all three sources. A shipment to a distribution centre can map to many stores, so the equation may balance at banner level but not at store level. State the grain and the location mapping before comparing.

If the sources cannot support the same grain, standardize them first through a canonical schema rather than forcing a comparison at a level the data does not reach.

A worked bridge

The table shows one product and one store for a month. Each term has a source, and the implied closing stock is compared with the physical count.

The equation balances to an implied closing stock of 120, but the count says 115. The five units become an open exception with a movement hypothesis: shrinkage, a missing delivery or a sale not yet filed. The bridge turned a vague variance into one specific, visible term.

Timing explains the boundary

Shipments can be booked on the invoice date while sell-out is reported by retail week. A delivery at month end may appear in the next month's sell-out. As with reporting cutoffs, these are calendar effects: carry them forward and expect them to clear in the following period. Only investigate a timing difference when it does not clear, because then it has stopped being a calendar issue.

Missing movements hide in the difference

Returns to the supplier, damages, write-offs, inter-store transfers, samples and giveaways are all movements that appear in no POS file. If the implied closing stock is consistently above the count, the likely cause is a movement category missing from the equation. Add it as its own term with its own source rather than absorbing it into an unexplained residual.

A clean bridge depends on an exhaustive list of movements. Keep that list explicit and versioned, and add a category when the evidence points to it instead of widening the residual.

Classify the exceptions

1. Timing differences that should clear next period. Carry them forward with evidence.
2. Missing movements with a known source to add, such as returns, transfers or damages. Add the term.
3. Identifier gaps where a product or store does not map. Route them to the master-data queue.
4. Genuine stock variance without a document, such as shrinkage. Record it as a count-based exception and investigate.
5. Source-quality issues such as duplicate deliveries or late files. Quarantine, correct and requeue.

Controls that keep the bridge honest

• A fixed list of movements and their sources. Nothing should be absorbed silently into a residual.
• Total controls at the grain. Compare row counts and totals before the equation runs.
• A versioned bridge and mapping table. The same grain, calendar and location logic every cycle.
• An exception queue with owner and status. Every open item is visible and assigned.
• Sign-off when the equation balances within tolerance. If it does not, the documented residual is the decision, not the number.

Practical takeaway

Reconcile POS, shipments and inventory through one stock equation at a stated grain. Explain every difference as timing, a missing movement or a classified exception. Controls and a traceable exception queue turn the bridge from a monthly argument into a decision the team can defend.

Marksyte's data reconciliation and controls service can design the event model, the stock equation and the exception controls across POS, shipment and inventory sources.

Frequently asked questions