Cellwise Start free

Excel guide · 4 min read

How to calculate inventory record accuracy in Excel

Quick answerInventory record accuracy = SKUs whose counted quantity matches the system within tolerance / SKUs counted. Work out each line's variance % as ABS(counted - system) / system, mark it accurate when it's within the tolerance for its ABC class, then divide: =COUNTIF(H2:H11,TRUE)/ROWS(H2:H11). In this cycle count, 6 of 10 SKUs were accurate, 60%, even though the value was out by only 1.5%.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJK
1SKUClassSystem qtyCountedUnit costVarianceVariance %AccurateClassTolerance
2A-100A1201204500TRUEA0
3A-101A807862-20.025FALSEB0.02
4A-102A2002001800TRUEC0.05
5B-200B3403359.5-50.014705882353TRUE
6B-201B1501581280.053333333333FALSE
7B-202B500500400TRUE
8C-300C120011500.8-500.041666666667TRUE
9C-301C9009600.5600.066666666667FALSE
10C-302C60602.2500TRUE
11C-303C041.141FALSE

Download this sheet as an Excel practice file (.xlsx)

Set up the cycle count sheet

One row per SKU counted: SKU in A, ABC class in B, the quantity the system says you have in C, the quantity counted on the shelf in D and the unit cost in E. Column F is the variance, =D2-C2: negative means fewer on the shelf than the records say.

Put the tolerances in their own small table, J1:K4, rather than inside formulas: 0% for A items, 2% for B and 5% for C. These are your policy, not a standard, so they belong in input cells where anyone can see and change them. Tight tolerance on the few high-value A items and looser on the many cheap C items is the usual pattern, matching your ABC analysis.

Variance % and the zero-quantity trap

The variance % is the size of the miss relative to what the system expected. Use ABS so a shortage of 8 and an overage of 8 are equally wrong: being out in either direction means the record can't be trusted.

The obvious =ABS(F2)/C2 breaks on C-303. The system says 0, the counter found 4, and dividing by zero gives #DIV/0!. A record of 0 with stock on the shelf is a 100% miss, not an error to hide, so handle it on purpose: =IF(C2=0,IF(D2=0,0,1),ABS(F2)/C2) returns 0 when both are zero and 100% when only the system is.

=ABS(F11)/C11

Result: #DIV/0!

#DIV/0!: C-303 has a system quantity of 0. Column G uses the IF version, which shows 100% for this row.

What does the variance % look like for a normal row?

A-101 had 80 in the system and 78 on the shelf, a variance of -2.

=G3

Result: 0.025 shows as 2.5% with Percent format

0.025, 2.5%: 2 units out of 80. For an A item with 0% tolerance, that's inaccurate.

Accurate or not, with tolerance by class

In H2, =G2<=XLOOKUP(B2,$J$2:$J$4,$K$2:$K$4) looks up the tolerance for the row's class and compares. Fill it down. B-200 is 1.5% out and passes the 2% B tolerance; B-201 is 5.3% out and fails. C-300 is 4.2% out and passes the 5% C tolerance, which a single 2% rule for everything would have failed.

Then count the TRUEs. ROWS gives the number of SKUs counted, so the formula keeps working when you paste in a bigger count. In Excel 2019 or earlier without XLOOKUP, use VLOOKUP(B2,$J$2:$K$4,2,FALSE) instead.

=COUNTIF(H2:H11,TRUE)/ROWS(H2:H11)

Result: 0.6 shows as 60% with Percent format

0.6, 60% record accuracy: 6 of the 10 SKUs were within tolerance.

How do I show accuracy for A items only?

Count accurate A rows and divide by all A rows.

=COUNTIFS(B2:B11,"A",H2:H11,TRUE)/COUNTIF(B2:B11,"A")

Result: 0.66666666667 shows as 66.7% with Percent format

0.667, 66.7%: A-101 is the one A item out of three that missed.

Accuracy by SKU count vs by value

The count-based rate treats a pallet of bolts and a box of motors the same. A value-based view weights each miss by what it's worth: the absolute variance at cost over the system value at cost. SUMPRODUCT multiplies the two columns row by row and adds them up.

Here the count says 60% but the value accuracy is 98.5%: the misses were mostly cheap C items. Report both. The count rate tells you how often the records are wrong, which drives picking errors and stock-outs; the value rate tells finance how far the books are from the shelf.

=1-SUMPRODUCT(ABS(F2:F11),E2:E11)/SUMPRODUCT(C2:C11,E2:E11)

Result: 0.98482804526 shows as 98.5% with Percent format

0.985, 98.5% value accuracy: 341.90 of absolute variance at cost against 22,535 of system value.

Net vs gross variance: overs hiding unders

Add up the variance at cost without ABS and the overs cancel the unders. This count has 130.40 of overages and 211.50 of shortages, so the net variance is only -81.10, against a gross variance of 341.90. A net figure close to zero can sit on top of a lot of wrong records.

Use net variance for what it is, the adjustment to the stock value in the books, and gross variance for record accuracy. A pattern of shortages in the same SKUs is also worth comparing with your shrinkage figures. If the system quantity comes from a movement log, check its running balance first: a plain running SUM mixes SKUs together, and Excel formulas for inventory shows a SUMIF version that keeps each SKU separate.

=SUMPRODUCT(F2:F11,E2:E11)

Result: -81.1

-81.1: the net variance at cost, the overs and unders netted against each other.

How do I total overs and unders separately?

Multiply the variance by a test that's TRUE only for positive (or negative) rows.

=SUMPRODUCT(F2:F11*(F2:F11<0),E2:E11)

Result: -211.5

-211.5: the shortages at cost. Swap < for > to get the 130.40 of overages.

Common mistakes

Netting overs against unders

Use ABS for accuracy. Net variance is a book adjustment, not a measure of how right the records are.

Dividing by a system quantity of 0

Wrap the variance % in IF: a 0 record with stock on the shelf is a 100% miss.

Typing tolerances into the formula

Keep them in a class table and look them up, so changing the policy is one cell.

Reporting only the value accuracy

A high value figure can hide many wrong records. Show the SKU count rate next to it.

FAQ

What is the formula for inventory record accuracy?

Number of SKUs counted within tolerance / number of SKUs counted. In Excel: =COUNTIF(H2:H11,TRUE)/ROWS(H2:H11) with TRUE/FALSE in column H.

How do I calculate cycle count variance in Excel?

Counted minus system quantity, =D2-C2, and as a percentage =ABS(D2-C2)/C2, guarded with IF for a system quantity of 0.

What tolerance should I use for a cycle count?

That's a policy decision for your business. Many teams use a tighter tolerance for high-value A items than for C items; keep the numbers in input cells.

Should I measure inventory accuracy by units or by value?

Both. Accuracy by SKU count shows how often records are wrong; accuracy by value shows how far the stock value is off.

Functions used

Related guides

Updated