Causes of Inventory Discrepancies in Stock Counts

The count finishes at ten past nine on a Saturday morning. A plumbing and heating distributor, 2,400 stock lines on a spreadsheet. Most of the sheet agrees with the shelves. One line does not: it says 118 of a 15mm compression elbow, and the shelf, counted twice, holds 103.

Fifteen units, about £180 of stock. Someone checks the CCTV, someone else pulls three months of purchase orders, and the office manager overwrites the cell with 103. In November the same line is out again, in the other direction.

Stock counts drift from the spreadsheet for predictable reasons rather than mysterious ones. The usual causes are timing gaps between a physical movement and the entry that records it, unrecorded returns and write-offs, duplicate or renamed product codes, unit-of-measure mismatches and shrinkage. A spreadsheet can hold the number but cannot govern the process that changes it, which is why the gap tends to reopen after a count.

What an inventory discrepancy is, and what an acceptable one looks like

An inventory discrepancy is the difference between recorded quantity and physical quantity at a single point in time. A count taken at eleven on a Tuesday, with four vans out, measures a moving object.

Two figures express it. Line accuracy counts how many lines matched exactly: 2,400 counted, 96 wrong, gives 96.0%. Value variance measures money, and it matters whether you take it net or absolute — the same count might net out at £180, because overs cancel unders, while the absolute variance is £14,200 against £610,000 of stock, or 2.3%.

What is acceptable depends on the item. On low-value consumables, two or three per cent on a line is normal and chasing it costs more than it saves. On serial-numbered tools or anything with an expiry date, the tolerance is zero. Set a band by class: 99% on the top 200 lines, 95% in the middle, 90% on the tail. Most businesses running stock on a spreadsheet rather than an inventory app have never produced either.

The seven most common causes of inventory discrepancies

These seven account for most of the gap.

Timing gaps. Stock moved before, or after, the entry recording it. The largest single cause, and the least suspected.

Unrecorded returns and credits. Four units come back and go straight onto the shelf. The credit note follows a week later, or never.

Write-offs, damages and samples. A pallet is dropped, two boxes scrapped, a unit leaves with a rep. Each legitimate, none entered.

Receiving errors. The delivery note says 12, the carton holds 10, the sheet is updated from the note. The wrong figure sits there for months.

Duplicate or renamed codes. One physical item existing as two or three rows.

Unit-of-measure mismatches. Bought by the case, sold by the unit, counted by whichever the clipboard-holder sees first.

Shrinkage. Theft, but more often misplacement: stock in the wrong bay, still in the building, invisible until a later count.

An eighth is uncomfortable: the count itself was wrong — a double-counted pallet, a skipped bay, a bay counted twice. Without a recount rule and an inventory tracking system holding the history, those are indistinguishable from real losses.

Timing gaps: when stock moves hours before the sheet does

Follow one van. Loaded at 07:20, away at 07:40 with 22 lines aboard, back at 16:10. The signed despatch notes reach the office at 16:30 and are keyed on Monday. Until then, the sheet shows stock that left the building nine hours earlier.

With 14 vans out on an average day, roughly 300 line movements sit in that gap at any midday. Count then and you find 300 potential discrepancies, most resolving themselves by Wednesday. Inter-site transfers stretch it over days: branch A relieves stock on despatch, branch B receives it three days later, and in between the units are on nobody's sheet.

Two disciplines close most of it. Set a cut-off: either nothing moves during a count, or every movement is logged and reconciled afterwards. And record movements when they happen rather than when the paperwork lands.

Duplicate codes, renamed items and unit-of-measure drift

Back to the elbow. Searching the sheet for "elbow" returns three rows: ELB-15C with 40, ELB15C with 18, and 15MM-ELBOW-COMP with 45. Three people added it over four years, each unaware of the others. Total 103, exactly what the shelf held. The count was right and the sheet was right; the sum was never computed, because nobody asked for it.

Renaming does quieter damage still: tidying a description from "Elbow 15mm comp" to "Compression elbow, 15mm" breaks every lookup matching on it, and the errors get wrapped in IFERROR.

Unit-of-measure drift is the most expensive, because it multiplies. The elbows arrive in cases of 24 and sell as singles. A counter sees eight sealed cases and writes 8, and the line drops from 192 to 8: a £2,200 adjustment on one row.

A cell accepts whatever is typed into it: no field insists the number is in cases, no rule blocks a duplicate code, nothing records who changed what. Teams that move stock off a spreadsheet into a structured application are buying those three constraints.

How to measure inventory accuracy so you can tell if it is improving

Without a measure, every count is an anecdote. Line accuracy is lines correct divided by lines counted, scored strictly: a line out by one unit is wrong, not nearly right. Absolute value variance is every difference taken as a positive number, multiplied by unit cost, over total stock value, and it refuses to let overs hide unders.

Then add the part almost nobody does. Cause-code every adjustment: timing, receiving, return, damage, duplicate code, unit-of-measure, unexplained. Ten seconds each. After a quarter you have the shape of your own problem, not an industry generalisation. If 58% of adjustments are timing and 6% unexplained, you need a cut-off procedure and you do not have a security issue.

Cycle counting versus the annual stocktake

The annual stocktake shuts the business for a day or two, produces one large adjustment, and diagnoses nothing. An error introduced in March surfaces in December, by which point the paperwork is filed and the driver has left.

Cycle counting spreads the same work across the year and keeps the trail warm. Across the same 2,400 lines: 200 A-class monthly, 600 B-class quarterly, 1,600 C-class annually, or about 533 line counts a month. That is 27 a working day: one person, forty minutes, before the first delivery.

What matters is that a discrepancy surfaces within four weeks, while the paperwork is in the tray and the person who put the pallet away still remembers where. Causes found at that distance can be fixed; causes found in December can only be absorbed.

What a discrepancy costs once you count the knock-on effects

The adjustment value is the smallest number involved. Fifteen missing elbows at £42 is £630 of stock. Around it: two orders re-picked, one customer promised Tuesday and given Thursday after a £68 next-day courier, and four hours of buyer, supervisor and office manager time at roughly £120. That customer now telephones before ordering.

The structural cost is larger and never appears on an adjustment report. The response to unreliable figures is to hold more of everything: two extra weeks of cover across 400 fast-moving lines, at an average line value of £900, is £69,000 of working capital compensating for a data problem. It shows up as an overdraft, not as the cost of a discrepancy.

Where the stock is tools or plant, a missing item is hired in rather than reordered, and an asset register recording who holds what pays for itself the first time a £3,000 item turns up in a van.

How to reduce discrepancies without adding admin

Each of these removes work rather than adding it, which is the only kind of change that survives a busy fortnight.

One code per item, one row per code. Dedupe the file once and put one person in charge of new codes. Most sheets we see carry between 3% and 8% duplicate lines.

Fix the unit of measure at entry. Record what was counted — eight cases — and let the system convert. A number without its unit is not a quantity.

Count where the stock is. A clipboard typed up afterwards adds a transcription step, and at half a per cent keying error rate that invents about a dozen discrepancies in 2,400 lines.

Give damages, samples and returns a one-tap route. They get recorded when it takes five seconds, and skipped when it needs a form and a countersignature.

Firms that build an inventory app from the spreadsheet they already use get all four at once, along with a record of who changed a figure and when. The structure is already in the file. Only the rules around it were missing.

When a stock sheet needs to become a stock system

A spreadsheet handles stock well up to a set of thresholds. More than one person editing at once. More than a few hundred lines. More than one location. Batch numbers, serial numbers, or expiry dates. And the question that always arrives on a Monday: Which version is current?

Crossing two or three of those is the signal. The content is rarely the problem: the codes, cost prices and reorder levels are years of accumulated knowledge worth keeping. What changes is the container, and the mechanics are set out in our walkthrough on turning an Excel file into a working application.

The distributor we opened with cause-coded their adjustments for a quarter: timing 61%, duplicate codes 14%, unexplained 5%. They set a cut-off, merged 71 duplicate rows, and moved the count onto a phone at the bay.

What changed was the Saturday morning. The count stopped being an audit that produced a list of problems and became a check that confirmed what the records already said, finished by half past eight, with the two lines that were out written down alongside the reason. Nobody watched the CCTV. Any business willing to record why a number moved, as well as what it moved to, can have that morning back.