allanninal.dev/build

Part 3 of 7 · Stock count reconciler series ~8 min read

How a variance gets found

Once the count is clean, the actual reconciliation is arithmetic. For each counted line the engine looks up what the system of record says should be there, subtracts one from the other, and works out three numbers: the gap in units, the gap as a percentage of the system quantity, and the value of the gap in pounds. Then it applies a single rule — the materiality tolerance — to decide whether this gap is worth a manager’s time or whether it just gets logged and moved past. This post walks one real line through that engine, with the numbers, and shows why every bit of it is plain code with no model anywhere near the decision.

Key takeaways

  • Each counted line is joined to the system quantity from the snapshot in stkr-inventory.
  • The gap is computed three ways: units, percent of the system quantity, and value at unit cost.
  • One rule — the materiality tolerance — sorts every line into in agreement, minor variance, or material variance.
  • Counted-not-in-system and in-system-not-counted are handled explicitly, not dropped.
  • All of it is plain Python. No model is anywhere near the number or the decision.

The join

Reconciliation starts with a join. For each clean counted line from the capture — SKU, location, counted quantity — the engine looks up the matching row in stkr-inventory and reads the system quantity and the unit cost. The lookup is against the snapshot taken when the count session opened, not the live system, so a sale that rings through mid-count can’t move the target underneath the arithmetic. Counting against a moving number is how you get variances that aren’t real.

Three things can come back from the join, and each is handled on purpose:

  • Counted, and in the system. The normal case — both numbers exist, and the engine computes the gap.
  • Counted, but not in the system. A tin of paint on the shelf that the system has never heard of. There’s no system quantity to subtract from, so this is its own kind of variance: an unrecorded item, treated as a material gap of the full counted quantity and flagged for sign-off.
  • In the system, but not counted. A SKU the snapshot expected at this location that no line came in for. If the session covered that SKU’s area, a blank is meaningful — it may be a true zero on the shelf — so it’s surfaced for the manager rather than ignored. If the SKU was simply out of this session’s scope, it’s left alone.

The three numbers

For a line that exists on both sides, the engine computes the gap three ways, because a single number lies in two different directions:

  • Units. counted − system. The raw gap. Negative is a shortage; positive is an overage.
  • Percent. (counted − system) / system. The same gap relative to how much there should be. Twelve units missing from a stock of 20 is a different problem from twelve missing from a stock of 5,000.
  • Value. (counted − system) × unit cost. The gap in money. This is what actually hits the books, and it’s what makes a small unit gap on an expensive item matter and a big unit gap on a cheap one not.

All three travel with the variance from here on, because the manager in Part 5 needs all three to judge it and the model in Part 4 needs them to suggest a cause.

A real line, with the numbers

Take SKU BOLT-M6-50 — M6 bolts, 50 mm, stocked loose in the fixings aisle. The system snapshot says there should be 480. The Saturday team counted 432. Unit cost is £0.35.

One counted line walked through the variance engine to a material-variance verdict A left-to-right worked example for SKU BOLT-M6-50. On the left, two stacked input boxes: "Counted: 432" and "System: 480 (unit cost £0.35)". Both feed a central compute box, "Variance engine (stkr-variance), plain Python", which shows three computed lines: gap in units equals 432 minus 480 equals minus 48; gap in percent equals minus 48 divided by 480 equals minus 10 percent; gap in value equals minus 48 times £0.35 equals minus £16.80. An arrow leaves the compute box to a diamond decision labelled "Material? exceeds 2 units AND 1 percent, or £20 value". From the diamond, three branches fan out to three outcome boxes: top, "In agreement" (within tolerance, no adjustment); middle, "Minor variance" (logged only, no adjustment); bottom, "Material variance" (queued for sign-off, on to cause). The minus 48 unit, minus 10 percent line takes the bottom branch to "Material variance" because it exceeds both the 2-unit and the 1-percent thresholds. A note at the bottom reads: every number here is plain code — the model is not consulted until a gap is already proven material. Counted: 432 BOLT-M6-50 System: 480 unit cost £0.35 Variance engine stkr-variance · plain Python units: 432 − 480 = −48 pct: −48 / 480 = −10% value: −48 × 0.35 = −£16.80 Material? >2 units & 1%, or £20 In agreement Minor (logged) Material (queued) −48, −10% Every number here is plain code — the model isn’t consulted until a gap is already proven material.
Fig 3. One line through the engine. Counted 432 against a system 480 gives −48 units, −10%, and −£16.80. That clears both the unit and the percent thresholds, so the line is a material variance and goes on to cause suggestion. A line inside tolerance would be logged and left.

The engine runs the three sums: 432 − 480 = −48 units, which is −48 / 480 = −10%, worth −48 × £0.35 = −£16.80. Now the tolerance: a gap is material if it exceeds 2 units and 1% of the system quantity, or if its value exceeds £20. This gap is 48 units (well past 2) and 10% (well past 1%), so it trips the unit-and-percent test. It’s a material variance, and it goes on to Part 4 for a likely cause. Had the team counted 479 — a gap of one unit, 0.2%, 35 pence — it would have been in agreement: logged, no adjustment, no manager’s time spent. Had they counted 476 — four units, 0.8%, £1.40 — it would clear the unit test but not the percent test, so it’s a minor variance: recorded for the trend, but nothing queued.

The tolerance is the whole game

The materiality tolerance is the one knob that decides how much of a manager’s attention this system spends. Set it too tight and every count buries them in £1 variances; set it too loose and real shrinkage hides under the threshold. That’s exactly why it lives in the rules doc, not the code: a business can tune it — tighter on high-value lines, looser on bulk consumables — without a deploy. The default of “2 units and 1%, or £20” is a starting point, not a law.

The combined test — units and percent — is deliberate. A unit-only rule flags trivial gaps on huge stocks; a percent-only rule flags noise on tiny ones. Requiring both keeps the flag where it belongs: a gap that’s big enough to notice and big enough to matter relative to what should be there. The value test sits beside them as an override, so a two-unit gap on a £200 item still gets caught even though it fails the percent test.

Why there’s no model here

Nothing in this post involves Bedrock, and that’s the point. Subtraction, division, and a threshold comparison are things code does perfectly, cheaply, and identically every time. A model asked to “find the variances” would be slower, cost money per line, and — worst of all — might give a different answer on a re-run, which is intolerable for a number that feeds the books. The model earns its place in the next post, where the question stops being “how big is the gap” (arithmetic) and becomes “what most likely caused it” (judgement over messy signals). The reconciliation stays deterministic; the model only ever labels a number the code has already nailed down.

Why this shape

  • The join is against the count-time snapshot, so a mid-count sale can’t fabricate a variance.
  • The gap is computed three ways because units, percent, and value each tell the manager something the others don’t.
  • One tolerance rule, in a doc, decides materiality — tunable without a deploy.
  • Counted-not-in-system and in-system-not-counted are first-class outcomes, not dropped rows.
  • It’s all plain Python. The number that feeds the books is never produced by a model.

The gap is now proven and sized. The next post answers the harder question: of the four likely causes, which one does the evidence point to — and how the system asks a model that without ever letting it touch the stock.

All posts