Stock Take & Variance Sheet

What this tool does

CM8-80 is a working stock take sheet. You record what the system said, what was physically counted and what one unit costs; the sheet works out the variance on every line, values it, keeps the net and gross totals separate, measures how accurate the count was, and builds a recount list of the lines whose variance is too big to accept without a second look.

Everything runs inside this single file. There is no account, no upload and no network request of any kind, so your stock quantities, costs and locations never leave the computer you are using.

Before you count

A stock take is only as good as its cut-off. Before anyone starts counting, stop movements in the area being counted, post everything that has already happened — receipts, issues, transfers, despatches — and take the system quantities after that posting. A count taken against stale system figures produces variances that are really just unposted paperwork, and you will spend the week investigating transactions that were never wrong.

Decide the unit cost basis before you start and use it consistently: last purchase price, standard cost or weighted average all give defensible answers, but mixing them across lines makes the totals meaningless. Write the basis in the report notes so the reader knows what the values mean.

Count in pairs where you can — one counting, one writing — and count blind: the counter should not see the system quantity before counting, because a counter who knows the expected answer tends to find it.

Recording a counted line

One line is one item counted at one location on one date. The same SKU counted in two locations is two lines — that is what lets the location chart show you where the problems are.

• System quantity — what the stock system said at the cut-off, in the line's unit.
• Counted quantity — what was physically found. Enter what was counted, not what you think the answer should be.
• Unit cost — cost per unit in your currency, on the basis you chose above. Every valued figure on this sheet multiplies by it, so a wrong cost makes a wrong variance value.
• Recount done — tick it once a line has been counted a second time. Ticking it removes the line from the recount list whatever its variance, so only tick it when the recount has genuinely happened.

The sheet refuses a few combinations that are almost always data-entry mistakes: a loss reason (damage, theft, wastage) on a line that counted more than the system, “found stock” on a line that counted less, and any reason other than “no variance” on a line that matches exactly.

The variance figures

Each line carries three computed figures, all derived from the two quantities and the unit cost:

A positive variance is an overage — more stock than the system shows. A negative variance is a shortage. Both are errors in the records: an overage is not good news, it means something was received, returned or produced without being booked, and somewhere else a matching shortage may be waiting.

Where the system quantity is zero the variance percentage is shown as “—” rather than a number, because a percentage of nothing has no meaning — any count against a zero base would be an infinite percentage. The line still carries its full variance quantity and value, and still appears in every total and chart.

Net versus gross — do not mix them

The sheet reports two totals, and they answer different questions:

The net figure is what your stock valuation moves by when the adjustments are posted — overs and shorts offset each other. The gross figure measures how wrong the records are: a store that is over by 500 in one aisle and short by 500 in the next has a net variance of zero and a gross variance of 1,000, and pretending that store is accurate because the net is zero is how record-keeping problems stay hidden. Quote both, and label which is which — the printed report and the per-category table keep them in separate columns for exactly that reason.

Count accuracy

The accuracy tile measures what proportion of lines came out within your tolerance:

A line counts as within tolerance when its variance percentage is no bigger than the acceptable variance % you set in Settings (in either direction). A line with a system quantity of zero cannot have a percentage, so it is treated as accurate only when the count was also zero — four found against a system zero is a miss, not a rounding difference.

The figure is a proportion of lines, deliberately. Averaging the percentage variances of the lines themselves would add percentages with different bases — 2 % of 300 metres and 2 % of 9 panels are not the same kind of thing — and a single line with a tiny base would swamp the average. A count of lines within tolerance has one base and one meaning.

Recounts and the threshold

Set the recount threshold to the variance value below which you will accept a first count without question. Any line whose variance value is beyond it — in either direction — appears on the recount list until its recount box is ticked. The right threshold depends on your stock value and your patience: too low and you recount half the warehouse, too high and a real loss sails through.

Recount before you investigate. A large variance is a miscount more often than it is a theft, and a second count by a different person settles the question in minutes. Only when the recount confirms the variance is it worth pulling transaction histories.

Variance reasons

The reason field turns a list of numbers into something you can act on. The donut chart counts lines by reason, and the pattern matters more than any single line: a count dominated by admin errors points at booking discipline — goods in without paperwork, issues that never reach the system. Repeated wastage on the same items suggests the system should book part-units. Theft suspected concentrated in one location is a security question, not a counting question. And a large slice of unknown is fine on the day of the count and a failure a fortnight later — the point of recording reasons is to drive the investigation that replaces them.

“Found stock” deserves the same attention as a loss. Stock the system does not know about was unavailable to sell, may have been re-ordered unnecessarily, and its purchase sits unmatched somewhere in the accounts.

Units, and what must never be added up

Each line carries its unit of measure — each, kilograms, litres, metres, boxes or packs — and every quantity on that line is read in that unit. Quantities are never added across lines anywhere in this sheet, because 12 metres of rod plus 5.5 litres of fluid is not a number. Only values are totalled, because multiplying each quantity by its unit cost puts every line in the same currency. If a total quantity for a group of like items would be useful to you, export the CSV and sum the lines that genuinely share a unit.

Be careful with boxes and packs: a system that holds singles while the counter writes boxes produces a spectacular false variance. The unit on the line must match the unit both quantities are expressed in.

Printing and sharing

Print Report produces a report from whatever the current filter shows: the headline tiles including both net and gross totals, the four charts, the recount list, the per-category summary, the full count register and your closing notes. Print to PDF to circulate it.

The scope line under the title states the filter in force. Clear the filters before issuing anything described as the full count, and say in the notes which cost basis the values use. The date filter is useful when the file holds more than one count — filter to one count date and the figures describe that count alone.

Saving your work

Count lines, settings and the report header are written to this browser's local storage as you type, and the toolbar shows the time of the last save. That storage belongs to one browser on one computer: another browser, a private window, a second machine or a clean-up tool that clears site data will not have it.

Treat Export .json as the real save — one file containing everything, which Import .json restores anywhere. Export CSV gives you every filtered line with its computed variance columns for spreadsheet work. Reset asks twice, then erases everything this tool has stored. There is no undo.

Accuracy & disclaimer

This sheet calculates from what you enter. It cannot tell whether the system quantity was taken at a clean cut-off, whether the count was careful, or whether the unit cost is on the basis your accounts expect — and every valued figure depends on all three.

It is a working record and a calculation aid, not an inventory valuation for your accounts, not evidence of theft, and not a stock adjustment. Post adjustments through your stock system's own process, with whatever approval your business requires, and keep the exported report with the adjustment as the audit trail.