Inventory Reconciliation and Reporting: A Step-by-Step Workflow (With Example)

TL;DR

• Inventory reconciliation compares on-hand quantities across your WMS or 3PL, ERP, and storefront, and flags every SKU and location where the systems disagree by more than an agreed tolerance.
• The workflow has five steps: pull data from each system, match records by SKU and location, flag discrepancies, route each exception to the team that owns it, and keep an auditable record.
• The direction of a gap usually points to its cause. ERP above the warehouse often means a receipt was booked in the ERP before the goods arrived. Storefront above the warehouse means you can sell units you don't have.
• Fast-moving brands often check storefront against warehouse daily to prevent overselling, and check ERP against warehouse weekly plus at month end for purchasing and the financial close.
• Prowork by Parabola builds this workflow as a Flow from a plain-language description and your real data. Deployed as a managed agent, it runs on a schedule and produces the full reconciliation table plus an exception view grouped by owner. Start from the inventory reconciliation use case.

To reconcile inventory, pull on-hand quantities from every system that tracks them (usually your WMS or 3PL, your ERP, and your storefront), match the records by SKU and location, and flag every row where the systems disagree by more than an agreed tolerance. Each discrepancy goes to the team that can fix it. Then save the inputs, the rules, and the resolutions so you can show how each number was reached.

This guide walks through that workflow step by step, works through an example with real-looking data, and covers how often to run it and how to automate it.

What is inventory reconciliation?

Inventory reconciliation means comparing your recorded inventory to your inventory on hand. It might include a physical count, but it often compares only the systems that track inventory, sales, and financial planning: the warehouse management system (WMS), the ERP, the storefront or marketplace, and any 3PL portals.

Inventory reconciliation and inventory management reporting have one basic goal: to identify discrepancies. Do all your records tell the same story, or are they full of holes? Using a WMS or another source of truth, you can keep both customer-facing and internal counts up to date.

The systems disagree for ordinary reasons. Each one updates on its own trigger and schedule: a sale decrements the storefront, a receipt increments the ERP, a cycle count overwrites the WMS, and units in transit belong to nobody for a while. Reconciliation finds the gaps before they turn into oversold orders, wrong reorder decisions, or a misstated inventory balance at period end.

How to reconcile inventory: a five-step workflow

1. Pull WMS, ERP, and store data

Export or connect to each system that reports a quantity:

• WMS or 3PL: on hand, allocated (committed to open orders), and damaged or on hold, by SKU and warehouse.
• ERP: on-hand quantity and inventory value by item and location, plus open purchase orders and recent receipts.
• Storefront and marketplaces: available-to-sell quantity by variant SKU and location.

Take every snapshot as close to the same moment as you can, and record the timestamp for each one. A two-hour gap between pulls during peak order volume will show up as false discrepancies. If you hold stock with more than one partner, pull each 3PL separately. If you track in-transit inventory, pull open inbound shipments too, since they explain a lot of ERP-versus-warehouse gaps.

2. Match records by SKU and location

Before you can compare quantities, every system has to agree on which row is which.

• Standardize the SKU. Trim spaces, normalize casing, and strip prefixes or suffixes that one system adds (a retailer prefix or a pack-size suffix, for example). If a SKU was renamed, keep a mapping table from the old code to the new one.
• Standardize the location. Map each system's location codes to one list. A 3PL's DFW1, an ERP's DALLAS-3PL, and a Shopify location named "Dallas Warehouse" should all become the same location key.
• Use the same unit of measure. Convert cases, inner packs, and eaches to one unit before comparing.
• Use a full outer join, not a lookup. You want one row per SKU and location with a column for each system, including rows that exist in only one system. A VLOOKUP from one sheet silently drops SKUs that are missing from it, and missing SKUs are often the discrepancies that matter most.

3. Flag discrepancies

Compare like with like. Storefront "available" should mean units you can actually sell, so compare it to the WMS's sellable quantity: on hand minus allocated, minus anything damaged or on hold. Don't compare it to raw on hand. Compare ERP on hand to WMS on hand.

For each pair, calculate the difference in units and as a percentage, then label every row:

• In sync: all systems match.
• Within tolerance: a small difference you've agreed to log but not chase (for example, two units or 1%, whichever is greater).
• Out of sync: the difference exceeds tolerance.
• Missing in a system: the SKU and location exist in one system but not another.

Exclude SKUs you've intentionally retired, so the report doesn't flag products you stopped selling months ago. Sort the out-of-sync rows by unit or dollar impact so the worst ones come first.

4. Route exceptions

A list of discrepancies doesn't fix anything until someone owns each one. The direction of the gap usually points to the cause and the owner:

Give each owner only their own rows, with the evidence they need to investigate: quantities from each system, the snapshot times, and any related receipts, adjustments, or open POs.

5. Keep an auditable record

Every reconciliation run should leave behind enough to answer "why did this number change?" months later. Save:

• The source snapshots, each with its timestamp.
• The rules applied: SKU and location mappings, the tolerance, and the retired-SKU list.
• The full reconciled table, not just the exceptions.
• For each exception: who owned it, the root cause, the resolution, and any adjusting entry made in the ERP or WMS.

Finance relies on this record at period end to support the inventory balance, and ops uses it to see which causes keep coming back. If the same cause shows up in run after run (receipts booked early, say, or one 3PL's adjustments never reaching the ERP), fix that process instead of correcting its output every week.

Worked example: reconciling two warehouses and a storefront

A brand sells through Shopify and stores inventory at two 3PL warehouses, Dallas (DAL) and Reno (RNO). Its ERP holds the financial inventory record. After pulling all three sources at 6:00 a.m. and normalizing SKUs and locations, the matched table looks like this. No units are damaged or on hold in this snapshot, so sellable stock is on hand minus allocated.

Getting to this table took two matching fixes. The WMS exported one SKU as hd-oat-m with a trailing space, and the WMS, ERP, and Shopify each used a different name for the Dallas warehouse. Without normalization, HD-OAT-M at Dallas would have shown up as three separate rows, each "missing" from the other two systems.

With a tolerance of two units or 1%, here is what happens to each row:

• HD-OAT-M / DAL (ERP +28): Shopify matches the WMS (452 − 12 = 440), so the storefront is fine and the gap is between ERP and WMS. Open purchase orders show a 28-unit inbound shipment that was received in the ERP when the advance ship notice arrived, but the 3PL hasn't checked it in yet. The row goes to receiving, and the record notes it as a timing difference that should clear when the 3PL checks in the shipment. If it doesn't clear by the next run, it's escalated as a possible short shipment.
• HD-OAT-M / RNO: in sync. It's logged with no action.
• HD-OAT-L / DAL (ERP +2): within tolerance. It's logged but not routed. If the same small gap shows up run after run, it's worth a cycle count.
• HD-OAT-S / RNO (Shopify +14): Shopify shows 130 available when the warehouse can ship only 116 (120 − 4). This is the urgent row, because the store can sell units that don't exist. It goes to ecommerce, who find that a manual quantity override was never removed after a promotion. They reset the quantity, and the record notes the override as the root cause.
• CAP-NVY-OS / RNO: a new SKU that's in the ERP and the warehouse but was never published to Shopify. It goes to the person who owns product setup.

The run's record saves all five rows, the three source snapshots, the tolerance, the mapping table, and the resolution for each exception. At month end, finance can trace the Dallas inventory balance back to the receipt timing issue without asking anyone to reconstruct it.

How often should you reconcile inventory?

As often as decisions depend on the numbers. The storefront-versus-warehouse comparison protects against overselling, so fast-moving brands often run it daily or even several times a day. The ERP-versus-warehouse comparison feeds purchasing and the financial close, so weekly plus a full run at month end is common.

Seasonality matters too. If stock moves seasonally (swimsuits, for example), increase the cadence ahead of and during peak periods. Whatever cadence you choose, run it on a schedule rather than only when something goes wrong. A reconciliation that runs only after a customer complaint has already missed its job.

Why manual reconciliation breaks down

The workflow above works in a spreadsheet for a quarterly check. It breaks when you need it weekly, when SKU counts reach the thousands, or when you add a second 3PL that sends data in a different format on a different schedule. Each run means re-exporting every source, redoing the cleanup, and re-running the joins by hand, and any of those steps can introduce an error. The exception routing and audit record are usually the first things to go, because they're the hardest to maintain by hand.

Automating inventory reconciliation with Prowork

In Prowork, each step above becomes a visible step in a Flow that you build by describing the reconciliation in plain language against your real data. The Flow pulls from your WMS, ERP, and storefront; standardizes SKUs and locations; joins the systems; calculates the deltas; and applies your tolerance and labels. The matching rules and calculations stay explicit, so you can see and edit exactly how each status was reached.

The output is an Artifact: the full reconciliation table for the record, plus an exception view grouped by owner and sorted worst-first. Deploy the Flow as a managed agent and it runs on your schedule against fresh data, keeping a run history so each reconciliation has its inputs and outputs on record. When a new 3PL comes on or a SKU format changes, you update the relevant step instead of rebuilding the spreadsheet.

The same Flow can feed your inventory reporting, such as days-on-hand, ABC analysis, and multi-warehouse consolidations, since the reconciled table is already clean.

To try it on your own systems, start from the inventory reconciliation use case, which includes a prompt that builds this workflow.