Stock Count Variance: Find Why Your Count and System Disagree

Rank every variance by value (quantity difference times unit cost), then work down from the largest and test three causes in order: unit of measure, unbooked scrap or workshop use, and goods-in timing. Those three explain most large gaps in a small shop. Chase theft and miscounts only for what remains.

The order matters because a raw variance list lies. A line with a tiny quantity gap can look harmless while a line counted in the wrong unit shows a gap in the thousands that is not real. Value first, then cause, saves you from recounting shelves that were never wrong.

Follow me on Instagram@sagnikteaches

What a variance list looks like before anyone has checked it

Say a bicycle repair shop that also sells parts finishes a count on a Saturday. Total stock at cost is about $11,500 across 300 lines. The count sheet and the system disagree on ten lines. Here they are in the order the system exported them, which is the order most people start working through.

Connect on LinkedInSagnik Bhattacharya

Added up without signs, the apparent damage is $4,031.60. Sorted by value, the picture changes at once: one line is 76 per cent of the total, and it is a cable reel. Nobody stole 114 reels of cable. That is the first lesson of the ranking: the biggest number on the page is more often a bookkeeping artefact than a loss.

Subscribe on YouTube@sagnikteaches

Build the sheet so the ranking does itself

You need a plain spreadsheet with one row per counted line. Use Excel or Google Sheets, whichever you already have. Seven columns are enough.

1. Item code and description, copied from the system export, not retyped.
2. System quantity at the moment the count was frozen.
3. Counted quantity.
4. Variance quantity: counted minus system (a formula, so a plus sign means more on the shelf than the system thinks).
5. Unit cost, from the system's last purchase cost or average cost.
6. Variance value: variance quantity times unit cost.
7. Absolute value: the variance value with the sign stripped (=ABS(F2)), which is the column you sort by, largest first.

Add two empty columns on the right: "Cause" and "Action". You will fill them as you work. Keep the sheet as one file for each count, named by date, so you can compare counts later and see whether the same lines keep failing. If your system export is messy, the steps in cleaning messy data before analysis will get it into shape first.

Cause one: the unit of measure does not match

Unit of measure (UoM) errors are the commonest cause of huge variances and the quickest to rule in or out. The system holds stock in one unit and a person counts in another. Cable sold by the metre but bought by the reel, brake pads held as pairs but counted as pads, screws bought by the box of 100 and sold loose: each creates a gap that is an exact multiple of something.

That regularity is your test. Divide the counted quantity by the system quantity and look at the result.

In the bicycle shop, brake pads show a ratio of exactly 2 (80 over 40). The counter counted pads; the system holds pairs. Forty pairs on the shelf is what the system says, so the real variance is zero. The cable shows 118 against 4. A 30 m reel divided into 118 m gives 3.93 reels, which means about 2 m is missing from the fourth reel, worth $1.80. Real variance: negative $1.80, not positive $3,078.

Two corrections close these for good. Put the counting unit on the count sheet header ("pairs, not pads"), and set the cable item up in the system so that its stock unit is the metre with the reel as a purchasing unit. Otherwise the same error will return at the next count.

Let the sheet flag ratios for you

Add a ratio column (=ROUND(C2/B2,2), wrapped in an IF to skip rows where the system quantity is zero) and conditional formatting that colours any ratio that is a whole number above 1, or one over a whole number. Rows that light up go to the front of the queue for a UoM check before anyone touches a shelf.

Cause two: stock left without being booked out

The second check covers things that physically disappeared through the front door of your own business: scrap, breakages, workshop consumption, samples, warranty replacements, staff use, free extras in a parcel. They are real stock movements that nobody wrote down, so the count is right and the system is stale.

In the bicycle shop, the negative lines left after cause one look like this:

• Inner tubes, down 24 ($76.80). Fitting a tube during a repair is a sale of labour plus a part, but the mechanics sometimes pull a tube from the rack and charge a flat repair fee. The tube never touches the system.
• Light sets, down 5 ($60.00). Warranty swaps given across the counter against an old receipt.
• Bar tape, down 15 ($42.00). A box fell off a high shelf and was binned after a spill.
• Chain lube, down 8 ($32.80). Used as workshop consumable.

None of these is theft. Together they come to $211.60, and every one of them can be prevented by a booking route that is quicker than skipping it. A paper tally sheet by the workshop door with columns for item, quantity and reason is enough. Once a week, whoever does the books keys the tally into the system as a stock write-off with a reason code. The same principle applies in other trades: a security installer's van stock of connectors, an HVAC contractor's refrigerant tags and brazing rods, a solar installer's cable offcuts. The consumables that are cheap enough that nobody books them are the ones that make a count disagree.

Ask the adjustments log what was booked

Before you declare something unbooked, check what was booked. Most inventory tools keep a log of adjustments and write-offs. Pull the entries between the previous count and this one and compare them against your negative variances. Sometimes the write-off exists but was posted to the wrong item, which produces a matching positive variance on another line (see the pairs example below).

Cause three: the paperwork landed on the wrong side of the count

Timing errors come in two forms. Goods arrive and are put on the shelf, but the delivery is booked in the system after the count was frozen, so the shelf is ahead. Or goods are sold, taken off the shelf, and the sale is entered later, so the shelf is behind.

The 700x28c tyres show +12 ($264.00). Twelve tyres arrived on Friday afternoon, went straight to the rack, and the delivery note sat in a tray until Monday. The count on Saturday saw 30 tyres; the system, with no receipt yet, held 18. Once the receipt is booked, the system also says 30 and the variance is zero.

The test is simple: list every receipt and dispatch dated within three days either side of the count and compare against each variance. If you use delivery notes as a source, matching goods received notes to orders shows how to make the booking date and the physical date agree, and reading delivery notes automatically covers cutting the lag to hours.

A cut-off rule that prevents most timing gaps

On count day, stop receiving and dispatching for the duration of the count, or isolate anything that moves into a marked "in-flight" box with its paperwork stapled on. Take the system quantity snapshot at the moment counting starts, and write down the time. Without that snapshot you are comparing a moving system against a still shelf and no one can say which one was wrong.

After the three: what is left in the bicycle shop

Re-sort the sheet with causes applied. The raw $4,031.60 of absolute variance shrinks as follows.

Real absolute variance: $311.40, net negative $287.40 once the cage is netted off. Against $11,500 of stock that is about 2.5 per cent, and nearly all of it has a known cause that a tally sheet will fix. Two checks took about twenty minutes and removed 92 per cent of the apparent problem. The one line worth a real investigation is the two missing cassettes, $68.00 of stock that is small, easily pocketed and has no booking trail.

Let a chat assistant spot patterns while a person checks the shelf

The ratio test, the date comparison and the log comparison are all mechanical, which is why a chat assistant (ChatGPT, Claude or Gemini) is good at them. Paste the sheet, the receipts for the last fortnight and the adjustments log, then ask it to propose causes without changing any figures. Strip out supplier bank details, staff names and anything else the task does not need first.

Here is a stock count variance table, last fortnight's goods-received
list and the adjustments log for a small parts shop. For each line with
an absolute variance value above $25:
1. Say whether counted/system is a whole-number ratio (pairs, packs,
   reels) and name the likely unit of measure.
2. Check whether a receipt or sale dated within 3 days of the count
   date explains the gap.
3. Check whether an adjustment for that item exists in the log.
4. Give a most-likely cause and one question I should ask on the shelf.
Do not suggest theft unless nothing else fits. Return a table.

A typical reply (illustrative) is a table with a row per line: "Disc brake pads: ratio 2.00, probably pairs against pads, ask which unit the counter used." For the cassettes it says "No ratio, no receipt, no adjustment. Unexplained; check whether any were sold on the marketplace listing." What you fix: assistants sometimes see a ratio in a coincidence (a variance of 6 on a pack-of-6 item that is really a miscount), and they cannot know that the Monday receipt sat in a tray. Treat the table as a to-do list for a person, not a verdict.

Pairs that cancel each other out

A third pattern is not caught by looking at one line at a time. If someone picks a 3/8 inch filter drier when the order said 1/2 inch, the system is out by one on each and both numbers are wrong in opposite directions. In an HVAC contractor's van stock this looks like +6 on one line and -6 on a similar-looking part, with a net value close to zero, so a net-total check sees nothing at all.

Sort by item code or description so similar parts sit together, then scan for opposite signs of the same size. Bicycle tubes in 25c and 28c, cable in black and grey, screws that differ by one length: they are all pair candidates. When you find one, do not adjust either line. Check the last orders that used those items, because the wrong item was picked, shipped or invoiced somewhere, and a customer may be holding the wrong part.

When recounting is worth it and when it is not

Recounting everything wastes the hours you saved by ranking. Set a two-part rule and write it on the sheet: recount a line if the variance value is above $25 and the quantity differs by more than 5 per cent of the system figure. Both tests, not either. Small lines with a trivial dollar gap stay, and expensive lines with a one-unit gap get a second look because one missing cassette is $34.

When you do recount, send a different person who does not know the first figure, and have them write the location as well as the number. Recounts exist to separate a miscount from a real gap, and a second person who sees the first number tends to confirm it. Of the bicycle shop's real variances, only the cassettes clear both tests ($68.00, and 2 missing out of 9 is 22 per cent). The pedals ($18.00) and the cage ($12.00) are unexplained but sit under the $25 floor, so they wait for the next count and get a second look only if they fail again; the rest already have a cause.

What a second count looks like on the sheet

Add two columns: "Recount" and "Counter 2". If the recount equals the first count, the variance is real and goes to cause hunting. If it equals the system, the first count was a slip and the line is cleared. If it is a third figure, the line has more than one problem, usually a location split, with the same item stored on two shelves and only one counted.

A quick sum for what a variance costs you in margin

Variances cost more than their stock value, because a unit lost at cost has to be paid for out of the margin on other sales. If the shop earns a 35 per cent gross margin on parts, a $287.40 net write-off needs about $821 of extra sales just to cover it ($287.40 divided by 0.35). Quote that to your team when you explain why booking a tube takes ten seconds and is worth it.

Track the net percentage and the count of lines above your threshold over time. A shop whose threshold-breaking lines fall from 10 to 4 across three counts is fixing causes; a shop whose count stays at 10 is adjusting symptoms. If you want a routine that counts the lines that carry the value more often than the rest, a cycle count plan by ABC class sets that up, and running a stocktake with a phone scanner removes most transcription slips from the counting itself.

The adjustment you should not make until the end

Posting every variance back to the system on the day is the most tempting shortcut and the most damaging. It zeroes the list, so the next count starts clean, and it erases the evidence that would have shown you which routine is broken. The better order is: rank, find causes, fix the process (a tally sheet, a UoM setting, a receipt tray rule), then post adjustments with reason codes such as "UoM correction", "unbooked workshop use", "timing" and "unexplained". After three counts the reason codes themselves become the report: if "unexplained" is the largest bucket, the problem is in the shop, not the paperwork.

Finish by writing one line per cause on the sheet saying who owns the fix and by when. A variance list with owners attached is the thing people actually act on, and it is the document that makes the next count shorter. If you want the same discipline applied to your sales-stock link, finding dead stock and slow movers uses the same sheet to show which lines never move at all, and any reorder point you set later is only as good as the counts behind it.

Questions that come up after a count

How big a variance is acceptable after a count?

There is no universal figure, so set your own from value rather than percentage. Many small shops pick a dollar floor (say $25 a line) and a percentage floor (say 5 per cent of the line's quantity) and recount only lines that break both. Judge the net total against total stock value over several counts, and watch the trend more than any single figure.

Should I adjust the system straight to the physical count?

Not before you have looked at the biggest variances. Adjusting first wipes out the evidence, and a unit-of-measure or timing error will simply come back next month. Investigate the lines above your threshold, correct the cause, then post the adjustment with a reason code so the history shows why the number moved.

Can AI find the cause of a variance for me?

It can sort and suggest, not decide. Give a chat assistant the variance table, the recent receipts and the adjustments log, and it will flag ratio patterns, late bookings and opposite-sign pairs quickly. A person still walks to the shelf, because only someone standing there can see a damaged box or a misplaced bin.

How often should a small shop count stock?

Count the few lines that carry most of the value more often than the rest, as a small rolling routine, and do a full count once or twice a year. A short weekly count of the top lines usually finds problems while the paperwork is still fresh enough to trace.

Further reads

• AI for Ecommerce Inventory Management: Reorder Points and Stockouts — Once counts are trustworthy, set reorder points from them.
• Amazon FBA Reimbursement: Spot Lost and Damaged Stock Using AI — For stock held at a marketplace warehouse, claim what went missing.
• How to Sync Stock Across Shopify, Amazon and eBay Automatically — Stops multichannel sales drifting away from your count.
• Why AI Stock Forecasts Go Wrong: 8 Mistakes Small Businesses Make — Bad counts quietly poison forecasts; see what else does.
• MRO Inventory Management: AI-Set Minimums for Maintenance Spares — Spares behave differently from sale stock when you count them.
• Daily Production Report: Summarise Counts, Scrap and Stops With AI — A source of scrap figures to feed your unbooked-scrap check.
• Boutique Inventory Management: AI for Size Runs and Reorders — Size-run reorder rules, a worked AI forecast for one dress in two colours, and a markdown ladder with margin floors for a small boutique.
• Coffee Shop Inventory With AI: Ordering Milk, Beans and Pastries — Turn till data into par levels for milk, beans and pastries, keep a waste log, and have AI draft the supplier order for you to check and send.
• Food Truck Inventory Management: Build Tomorrow's Prep List — A week of sales exports, a recipe matrix, par levels and waste notes turned into a Friday prep list, with the prompt that writes it each evening.
• Hardware Store Inventory Management: SKU Clean-Up With AI — Export, de-duplicate and categorise a hardware store's SKUs, then set reorder points from real demand and let AI audit the numbers before you load them.
• Pizza Shop Inventory: Plan Dough and Toppings From Weekend Orders — Turn eight weeks of orders into dough balls, cheese and topping quantities for Friday to Sunday, then tune the plan from a simple waste log.
• Restaurant Inventory Management With AI: Par Levels and Orders — A weekly count sheet, a par for every item and an order draft for your supplier, built with AI from your own counts and POS sales, plus when paid software pays.
• Game Store Inventory Management: Preorders and Trade-Ins With AI — Three ledgers for a games shop: a preorder deposit log, a singles pricing rule and a trade-in buy list, with AI drafting listings and customer replies.
• Music Store Inventory Management: Used Gear and Trade-Ins With AI — One row per instrument, serials stored as text, a trade-in pricing sum and AI-drafted condition notes and listings, all in a spreadsheet a small shop can run.
• AI Tools for Warehousing: Which Suit a Small Warehouse or 3PL? — Stock counts, pick errors and slotting: when a spreadsheet and a chat assistant is enough and when a warehouse system's AI add-on pays.
• ISO 9001 Documentation: Hire a Consultant or Draft It With AI? — Which ISO 9001 jobs AI can draft for a small manufacturer, which ones need a consultant, and a costed worked example of the split.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Microsoft Support pages for the ABS and ROUND functions, checked November 2026; all counts, costs and variances in the examples are illustrative.