Inventory accuracy formula: three measures on one count

#Why one formula is not enough

Search for the inventory accuracy formula and you get several, often on the same page: counted over recorded, correct records over total records, one minus the variance. They give different numbers for the same count, and none of them is wrong. They answer different questions:

• Record accuracy: can I trust the number the system shows for any one item at this store?
• Unit accuracy: how many units are in the wrong place in the books, whichever way?
• Value accuracy: how much money do the errors add up to, at cost?

The one to avoid is the first one most pages show, total counted units divided by total system units. It nets overs against shorts. Three T-shirts short and four tote bags over is one unit over in total, and a score near 100% for a count that found five lines wrong.

#The three formulas

Work on lines: one row per store and SKU, with the system quantity at the moment the count started, the counted quantity and the unit cost. Leave out lines that were not counted.

Record accuracy, also called inventory record accuracy or IRA:

record accuracy = lines where counted = system / lines counted

Unit accuracy, with the variance taken as an absolute value so nothing cancels:

unit accuracy = 1 - sum(|counted - system|) / sum(system)

Value accuracy, the same with each line's variance at cost:

value accuracy = 1 - sum(|counted - system| x unit cost) / sum(system x unit cost)

All three divide by the system side, because the question is how far the record can be trusted. If your auditors divide by the counted side, the numbers move a little; say which you use and keep it.

#A tolerance for record accuracy

Strict record accuracy calls a line wrong if it is out by one unit of a 60-unit pack of socks. Many retailers allow a tolerance: a line is accurate if the absolute variance is within, say, one unit, or within a percentage for lines with high counts. Report the strict figure and the tolerant one side by side, and never change the tolerance between counts, or the trend means nothing.

#A worked example at ST-014

An invented chain of 120 stores counts ten lines at ST-014 one Monday morning, before opening:

Invented figures. Worked through, the same count gives four answers:

What the table shows:

• The net figure hides everything. Eleven units are wrong, ten of them cancel, and 99.5% is what is left.
• Record accuracy is the harshest. Half the lines are wrong. With a one-unit tolerance, the scarf and the socks pass and it rises to 70%.
• Value accuracy is lower than unit accuracy because the two missing rain jackets cost 42.00 each. They are 2 of the 11 units out, and 84.00 of the 152.30 at cost: more than half.

So which number goes on the dashboard? Record accuracy, if the question is whether staff can tell a customer an item is in stock. Value accuracy, if the question is what finance should book. Unit accuracy sits between the two, and is the fairest single figure for comparing stores of different sizes.

#The formulas in Excel

Put the count in columns A to E, one line a row from row 2: Store, SKU, Unit cost, System, Counted. Leave Counted empty for a line that was not counted. Put the tolerance in H1 (1 for the example). For the ten lines above, in rows 2 to 11:

Lines counted          =COUNT(E2:E11)
Record accuracy        =SUMPRODUCT((E2:E11<>"")*(E2:E11=D2:D11))/COUNT(E2:E11)
Record accuracy, tol.  =SUMPRODUCT((E2:E11<>"")*(ABS(E2:E11-D2:D11)<=$H$1))/COUNT(E2:E11)
Unit accuracy          =1-SUMPRODUCT((E2:E11<>"")*ABS(E2:E11-D2:D11))/SUMPRODUCT((E2:E11<>"")*D2:D11)
Value accuracy         =1-SUMPRODUCT((E2:E11<>"")*ABS(E2:E11-D2:D11)*C2:C11)/SUMPRODUCT((E2:E11<>"")*D2:D11*C2:C11)
Counted over system    =SUM(E2:E11)/SUMIFS(D2:D11,E2:E11,"<>")

Format the cells as percentages. The (E2:E11<>"") part keeps uncounted lines out, so an empty cell never matches a system quantity of zero. For one store in a sheet of many, wrap each range in a condition on column A, or use the free inventory reconciliation template, which works out record accuracy store by store.

#The formulas in SQL

When the count comes back from handheld scanners, it usually lands in a table. This runs as written in SQL Server, and gives every store's accuracy in one result:

CREATE TABLE dbo.StockCount (
    store_code  varchar(10)   NOT NULL,
    sku         varchar(30)   NOT NULL,
    unit_cost   decimal(10,2) NOT NULL,
    system_qty  int           NOT NULL,
    counted_qty int           NULL,
    CONSTRAINT PK_StockCount PRIMARY KEY (store_code, sku)
);

INSERT INTO dbo.StockCount (store_code, sku, unit_cost, system_qty, counted_qty) VALUES
('ST-014', 'TEE-BLK-M',   9.20, 24, 24),
('ST-014', 'TEE-BLK-L',   9.20, 18, 15),
('ST-014', 'CAP-NVY',     7.50,  9,  9),
('ST-014', 'SCARF-GRY',  11.40,  6,  7),
('ST-014', 'SOCK-WHT-3P', 4.10, 60, 59),
('ST-014', 'MUG-WHT',     3.80, 30, 30),
('ST-014', 'JKT-RAIN-M', 42.00,  5,  3),
('ST-014', 'BAG-TOTE',    6.30, 40, 44),
('ST-014', 'BELT-BRN',   14.90, 12, 12),
('ST-014', 'SUNG-BLK',   18.00,  8,  8);

DECLARE @tolerance int = 1;

SELECT
    store_code,
    COUNT(*) AS lines_counted,
    CAST(100.0 * SUM(CASE WHEN counted_qty = system_qty THEN 1 ELSE 0 END)
         / COUNT(*) AS decimal(6,1)) AS record_accuracy_pct,
    CAST(100.0 * SUM(CASE WHEN ABS(counted_qty - system_qty) <= @tolerance THEN 1 ELSE 0 END)
         / COUNT(*) AS decimal(6,1)) AS record_accuracy_tol_pct,
    CAST(100.0 * (1 - 1.0 * SUM(ABS(counted_qty - system_qty))
         / NULLIF(SUM(system_qty), 0)) AS decimal(6,1)) AS unit_accuracy_pct,
    CAST(100.0 * (1 - SUM(ABS(counted_qty - system_qty) * unit_cost)
         / NULLIF(SUM(system_qty * unit_cost), 0)) AS decimal(6,1)) AS value_accuracy_pct,
    CAST(100.0 * SUM(counted_qty)
         / NULLIF(SUM(system_qty), 0) AS decimal(6,1)) AS net_units_pct
FROM dbo.StockCount
WHERE counted_qty IS NOT NULL
GROUP BY store_code
ORDER BY value_accuracy_pct;

On the example it returns one row: 10 lines, 50.0, 70.0, 94.8, 90.9 and 99.5. In PostgreSQL the same statements run with two changes: drop dbo. from the table name, and write the tolerance as a number in place of @tolerance, removing the DECLARE line.

Two edge cases to know. A line the system holds at zero but where stock was found adds to the variance and nothing to the system total, so a small count can score below zero on unit accuracy. And a line that is on the shelf but not in the system at all must be added with a system quantity of 0, or the formula never sees it.

#Between counts: two systems, not a system and a shelf

A count measures the books against the shelf, once a quarter or once a cycle. In a chain, the stock figure also lives in more than one system: the ERP holds one quantity per store and SKU, the tills or the store system hold another, and the web shop reads one of them to say what is in stock near you. When those two disagree, every count starts from a record that was already wrong.

The same record formula works across two systems, with one change: neither side is the truth, so call it agreement rather than accuracy, and count a line that is on one side only as not agreeing. In SQL Server, with each system's stock in a table:

CREATE TABLE dbo.ErpStock (store_code varchar(10) NOT NULL, sku varchar(30) NOT NULL, qty int NOT NULL,
    CONSTRAINT PK_ErpStock PRIMARY KEY (store_code, sku));
CREATE TABLE dbo.PosStock (store_code varchar(10) NOT NULL, sku varchar(30) NOT NULL, qty int NOT NULL,
    CONSTRAINT PK_PosStock PRIMARY KEY (store_code, sku));

WITH lined_up AS (
    SELECT COALESCE(e.store_code, p.store_code) AS store_code,
           COALESCE(e.sku, p.sku)               AS sku,
           e.qty AS erp_qty,
           p.qty AS pos_qty
    FROM dbo.ErpStock AS e
    FULL OUTER JOIN dbo.PosStock AS p
      ON p.store_code = e.store_code AND p.sku = e.sku
)
SELECT
    store_code,
    COUNT(*) AS lines,
    SUM(CASE WHEN erp_qty IS NULL OR pos_qty IS NULL THEN 1 ELSE 0 END) AS lines_on_one_side,
    SUM(CASE WHEN erp_qty <> pos_qty THEN 1 ELSE 0 END) AS lines_that_differ,
    CAST(100.0 * SUM(CASE WHEN erp_qty = pos_qty THEN 1 ELSE 0 END)
         / COUNT(*) AS decimal(6,1)) AS agreement_pct
FROM lined_up
GROUP BY store_code
ORDER BY agreement_pct;

That works while both tables sit on one server. When the ERP is on one database and the store system on another, the post on comparing two tables in SQL covers where a single query stops.

#Checking stock every night, between counts

The count is a quarterly job, and the formulas above are enough for it. The question in between is the one that costs a sale: does the stock the ERP holds for ST-014 still match what the store system says this morning?

DataMosaic reads both systems directly, SQL Server, Oracle and PostgreSQL among them, or their exports as CSV and Excel. A check lines them up by store and SKU and compares the quantity, with a tolerance for the difference still counted as a match. Its rules decide whether the night passed: Rows that differ (%) is the strict record figure turned round, and Rows missing from a source catches the SKU one system has never heard of. A row that differs offers an investigation into why, and if you want Mosaic to act, say by emailing the store manager when a line is still out the next night, that is a workflow you build.

#Questions people ask

#What is the formula for inventory accuracy?

The most useful one is record accuracy: lines where the counted quantity matches the system, divided by the lines counted. Add unit accuracy, 1 minus absolute unit variance over system units, and value accuracy, the same at cost, to see how big the errors are.

#How do you calculate inventory record accuracy?

Count a set of lines, mark each one accurate if its count equals the system quantity (or is within your tolerance), and divide the accurate lines by the lines counted. Five accurate lines out of ten is 50%.

#Why should I not divide counted units by system units?

Because overs and shorts cancel in the total. A count with half its lines wrong can come out at 99.5% if the errors happen to net off.

#What is the difference between inventory accuracy and value accuracy?

Unit accuracy weighs every unit the same; value accuracy weighs each one by its cost. Two missing jackets at 42.00 move value accuracy far more than four extra tote bags at 6.30.

#Should inventory accuracy use a tolerance?

It can, for lines with high counts where one unit is noise. Report the strict figure beside the tolerant one, and keep the tolerance the same from one count to the next.