Inventory reconciliation process for a store chain, in seven steps

#Why a chain needs its own process

The guides that rank for this search describe one building and one set of records: count the shelf, compare it to the system, investigate, adjust. That works in a warehouse. A retailer with 120 stores has three things a warehouse usually does not:

• Two systems per store. The ERP holds the stock finance books against. The tills or the store system hold their own figure, live, and the web shop often reads that one. They are meant to agree, and between syncs they don't.
• Movements that reach the ERP late. Till sales often post to the ERP in a nightly batch. A snapshot taken from the ERP after the store closes still holds the stock from before the day's sales.
• Stock between stores. A transfer is booked out of the sending store when it leaves and into the receiving store when somebody receives it in the system. Between the two, the goods are on a shelf that the ERP says is empty.

None of these is a counting error, and none should be adjusted as one. The process below separates them out before anything is posted.

#Step 1: Fix the scope and the cutoff

1. Choose the stores and the lines. A cycle count takes a slice: one department, the lines with the most value, or the lines that were out last time. A full count takes everything, usually once a year.
2. Set the cutoff as a date and time, and write it down. It is the moment the system figure is taken, and every movement after it has to be known.
3. Stop what can be stopped. No deliveries received and no transfers sent at the counting stores between the cutoff and the end of the count. Sales are the exception, and step 4 deals with them.
4. Export the system stock at the cutoff from the ERP, per store and SKU, with the unit cost. Take the store system's figure at the same moment.

#Step 2: Line up the two systems before you count

This is the step no other guide has, and it saves the most time. Before the count, compare the ERP export with the store system export, line by line on store and SKU. Every line where they differ goes into one of three groups:

• Timing: sales or receipts after the last sync, which the next batch will post. Note them; they are not variances.
• A sync that failed: a receipt or an adjustment that one system took and the other never did. Two days old is not timing. Raise it with whoever looks after the interface, because it will happen again.
• Unexplained: the systems disagree and nothing in either explains it. Count these lines first.

A count against a record that was already wrong measures the interface, not the store. The post on POS and ERP sales covers why the sales side of the two systems drifts apart.

#Step 3: Count blind, and recount the big ones

1. Hand out count sheets or scanner lists without the system quantity. A counter who can see the expected figure tends to find it.
2. Count by location, shelf by shelf, stockroom included, and mark each location done.
3. Recount any line out by more than a threshold you set in advance, for example 2 units or 25.00 at cost, with a different person. A recount that agrees with the first count stands; one that doesn't is counted a third time.

#Step 4: Allow for what the ERP has not seen yet

This is where a chain's reconciliation goes wrong. For each line, work out the expected quantity: what the ERP would hold if every movement up to the count had reached it.

expected = ERP at cutoff
         - sales made before the count but not yet posted to the ERP
         + goods received at the store but not yet booked in the ERP
         - goods sent from the store but not yet booked out in the ERP

The variance that matters is counted minus expected, not counted minus the ERP. Adjust on the raw figure and the nightly batch posts the same sales again, so the ERP falls short by exactly what you wrote off. Book the unreceived transfer as stock found, and the transfer stays open in transit for ever at the sending store.

#A worked example at ST-014

An invented chain of 120 stores counts eight lines at ST-014 on Monday from 07:00, before opening. The cutoff was Sunday 22:00, after close. The tills post Sunday's sales to the ERP at 02:00 on Tuesday, a day late. On Saturday, ST-014 received six tote bags sent from ST-027 on transfer TR-0418, in the store system; the receipt never reached the ERP.

Invented figures. What the table shows:

• Against the ERP, seven of eight lines are out. Record accuracy would be 12.5%, and 199.00 at cost would be adjusted.
• Against the expected figure, three are. Record accuracy is 62.5%, and the adjustment is 123.00 at cost, absolute: the shirts, the scarf and the rain jackets.
• The net totals barely move. Raw, the count is 100.60 short at cost; after timing, 100.20 short. A process that only looks at totals would never see the four lines it was about to get wrong.
• The store system was right on every timing line. It was wrong only where the shelf was, which is the count's real finding. The tote bags are a sync failure, two days old, and belong in step 2's list as well as here.

Record and unit accuracy are worked out three ways in the inventory accuracy formula post; this example only needs the first.

#The same in SQL

When the exports and the scanner file land in tables, this runs as written in SQL Server and gives the table above, with a flag on any line where the two systems still differ once timing is allowed for:

CREATE TABLE dbo.ErpSnapshot   (store_code varchar(10) NOT NULL, sku varchar(30) NOT NULL,
    unit_cost decimal(10,2) NOT NULL, qty int NOT NULL, PRIMARY KEY (store_code, sku));
CREATE TABLE dbo.StoreSnapshot (store_code varchar(10) NOT NULL, sku varchar(30) NOT NULL,
    qty int NOT NULL, PRIMARY KEY (store_code, sku));
CREATE TABLE dbo.CountLines    (store_code varchar(10) NOT NULL, sku varchar(30) NOT NULL,
    counted_qty int NOT NULL, PRIMARY KEY (store_code, sku));
CREATE TABLE dbo.UnpostedSales (store_code varchar(10) NOT NULL, sku varchar(30) NOT NULL,
    qty int NOT NULL);
CREATE TABLE dbo.UnbookedTransfers (transfer_no varchar(20) NOT NULL, from_store varchar(10) NOT NULL,
    to_store varchar(10) NOT NULL, sku varchar(30) NOT NULL, qty int NOT NULL);

INSERT INTO dbo.ErpSnapshot VALUES
('ST-014','TEE-BLK-M',9.20,24), ('ST-014','TEE-BLK-L',9.20,18), ('ST-014','CAP-NVY',7.50,9),
('ST-014','SCARF-GRY',11.40,6), ('ST-014','SOCK-WHT-3P',4.10,60), ('ST-014','MUG-WHT',3.80,30),
('ST-014','JKT-RAIN-M',42.00,5), ('ST-014','BAG-TOTE',6.30,40);
INSERT INTO dbo.StoreSnapshot VALUES
('ST-014','TEE-BLK-M',22), ('ST-014','TEE-BLK-L',18), ('ST-014','CAP-NVY',8),
('ST-014','SCARF-GRY',6), ('ST-014','SOCK-WHT-3P',57), ('ST-014','MUG-WHT',30),
('ST-014','JKT-RAIN-M',5), ('ST-014','BAG-TOTE',46);
INSERT INTO dbo.CountLines VALUES
('ST-014','TEE-BLK-M',22), ('ST-014','TEE-BLK-L',15), ('ST-014','CAP-NVY',8),
('ST-014','SCARF-GRY',7), ('ST-014','SOCK-WHT-3P',57), ('ST-014','MUG-WHT',30),
('ST-014','JKT-RAIN-M',3), ('ST-014','BAG-TOTE',46);
INSERT INTO dbo.UnpostedSales VALUES
('ST-014','TEE-BLK-M',2), ('ST-014','CAP-NVY',1), ('ST-014','SOCK-WHT-3P',3);
INSERT INTO dbo.UnbookedTransfers VALUES ('TR-0418','ST-027','ST-014','BAG-TOTE',6);

WITH movements AS (
    SELECT store_code, sku, -qty AS qty FROM dbo.UnpostedSales
    UNION ALL
    SELECT to_store, sku, qty FROM dbo.UnbookedTransfers
), timing AS (
    SELECT store_code, sku, SUM(qty) AS qty FROM movements GROUP BY store_code, sku
)
SELECT
    c.store_code,
    c.sku,
    e.qty                                   AS erp_qty,
    s.qty                                   AS store_qty,
    COALESCE(t.qty, 0)                      AS not_yet_in_erp,
    COALESCE(e.qty, 0) + COALESCE(t.qty, 0) AS expected_qty,
    c.counted_qty,
    c.counted_qty - COALESCE(e.qty, 0)      AS raw_variance,
    c.counted_qty - (COALESCE(e.qty, 0) + COALESCE(t.qty, 0)) AS true_variance,
    (c.counted_qty - (COALESCE(e.qty, 0) + COALESCE(t.qty, 0))) * e.unit_cost AS true_variance_at_cost,
    CASE WHEN COALESCE(s.qty, 0) <> COALESCE(e.qty, 0) + COALESCE(t.qty, 0)
         THEN 'systems still differ' ELSE '' END AS systems
FROM dbo.CountLines AS c
LEFT JOIN dbo.ErpSnapshot   AS e ON e.store_code = c.store_code AND e.sku = c.sku
LEFT JOIN dbo.StoreSnapshot AS s ON s.store_code = c.store_code AND s.sku = c.sku
LEFT JOIN timing            AS t ON t.store_code = c.store_code AND t.sku = c.sku
ORDER BY ABS((c.counted_qty - (COALESCE(e.qty, 0) + COALESCE(t.qty, 0))) * COALESCE(e.unit_cost, 0)) DESC;

The ORDER BY puts the most expensive true variances first, which is the order step 5 works in. A line counted but missing from the ERP shows with an empty erp_qty and no cost: add it to the ERP before you adjust it. In PostgreSQL the script runs with one change: remove dbo. throughout.

The sending store needs nothing here. ST-027 booked the six bags out when they left, so its ERP stock is already right; only the receiving side is waiting. A transfer that left a store and was never booked out is the opposite case: subtract it from the sending store's expected quantity, the way the query subtracts unposted sales.

#Step 5: Investigate by value, and give every line a reason

1. Sort the true variances by value at cost, largest first, and stop when the next line is worth less than an hour of someone's time.
2. For each line, look at its movements since the last count: deliveries, transfers, returns, write-offs. A line out by exactly a case quantity is usually a delivery booked against the wrong SKU.
3. Give it a reason from a fixed list: counting error, transfer not booked, delivery short, damaged, theft suspected, not yet known. The inventory reconciliation template has a reason column and a list to start from.

#Step 6: Post one adjustment, approved, and check it arrived

1. Post the true variances only, as one adjustment document per store, with the count's reference and the reason on each line.
2. Have it approved above a value you set, by someone outside the store.
3. Check it arrived in the store system, the same way step 2 lined the systems up. An adjustment that reaches the ERP and not the tills starts the next count already wrong.
4. Close what step 4 found: chase the receipt for TR-0418 into the ERP rather than adjusting the bags.

#Step 7: Measure the store, not the count

Record accuracy per store, after timing, from one count to the next, is the number to keep. Read the reasons as well: a store whose variances are mostly transfer not booked needs a different fix from one with theft suspected. And keep the step 2 list: if the ERP and the store system disagreed on the same SKUs before every count, the problem is the interface, and a count every quarter is a slow way to find it.

#Between counts, every night

The count is the right tool for the shelf. Step 2 is a different job: two systems that should agree, which can drift apart on any night, and a count once a quarter finds that drift months late.

DataMosaic reads both systems directly, SQL Server, Oracle and PostgreSQL among them, or their exports as CSV and Excel. A check lines up the ERP and the store system by store and SKU every night and compares the quantity. Its rules decide whether the night passed: Rows missing from a source catches the SKU one side has never heard of, Rows that differ catches the tote bags, and Rows compared at least 1 catches an export that came back empty. A difference that survives a run becomes an exception with an owner, and one that is understood can be marked Expected so it stops failing the run. A row that differs offers an investigation into why, and if you want Mosaic to act, say by mailing the store manager when a receipt is still missing the next night, that is a workflow you build.

#Questions people ask

#What are the steps in the inventory reconciliation process?

Fix the scope and cutoff, compare the systems, count, allow for movements not yet posted, investigate the variances by value, post an approved adjustment, and measure the result. For a single site the second and fourth steps are small; for a chain they decide whether the adjustment is right.

#Why does my stock count not match the system after a count?

Often because the system figure was taken before movements had posted: sales batched overnight, deliveries received but not booked, transfers in transit. Work out the expected quantity for each line before calling anything a variance.

#How do you handle stock in transit between stores during a count?

Count what is physically there, and add any transfer the store received but has not booked to the expected quantity. Then book the receipt itself, rather than adjusting the stock, so the transfer closes at both stores.

#How often should a multi-store retailer reconcile inventory?

Count by cycle, a slice of each store at a time, with a full count when your auditors need one. Compare the ERP and store systems far more often, nightly if you can, because they can drift apart any night.

#Who should approve inventory adjustments?

Someone outside the store, above a value threshold set in advance. The store explains the variance; head office or finance approves the adjustment.