How to use the stock count variance calculator
- Paste one line per item: item, system quantity, counted quantity and unit cost, separated by commas or tabs. A first line of headings is skipped.
- Set a tolerance, the percent of the system quantity by which a count may differ before a line is flagged.
- Read the net variance, the shortages and overages, the shortage as a share of the system value, and the flag on each line.
- Copy the table or download the CSV. Recount the flagged lines before you change any records.
Formula
difference = counted quantity - system quantity
value of each side = quantity x unit cost, in whole cents
variance in value = counted value - system value; shortages are the negative lines
flag when |difference| / system quantity > tolerance
A shortage is negative and shown in parentheses. Values are rounded to the cent per line before they are added, so the net variance always equals counted value minus system value.
Six items
The sample has six items. The system holds $1,793.60 of stock and the count finds $1,693.65, so the net variance is ($99.95). Four lines are short: 2 units of the first item ($9.00), 6 of the third ($19.50), 12 of the fifth ($9.60) and all 15 of the last ($149.85). One line is over by 4 units ($88.00).
The shortages add up to $187.95, which is 10.48% of the system value, and the overage of $88.00 brings the net to ($99.95). With a tolerance of 5%, four lines are flagged: the third (8% short), the fourth (40% over), the fifth (6% short) and the last (100% short).
Reading shortages and overages
A shortage means you counted fewer than the system says, and an overage means more. Both matter. An overage on one item often goes with a shortage on another, for example when two similar items were mixed up, so look at them together before treating a shortage as loss.
The shortage as a share of the system value is a rough size of the problem. It does not say why the difference exists. Timing, unrecorded movements, damaged goods, theft and miscounts all produce the same numbers.
Counting well
Count when stock is not moving, or record movements during the count. Flag lines for a recount rather than adjusting records straight away, and recount the highest value lines first. A tolerance in percent suits fast-moving items, while a small number of high-value items may need a tight check whatever their percentage.
Keep your item list with the Inventory List Maker, find the effect on cost of goods with the COGS Calculator, and track down an unexplained amount with the Subset Sum Reconciliation Finder.
Assumptions and limits
- Quantities and costs must be 0 or more and are read as decimals, so half units are fine.
- A line with no system quantity is flagged if anything was counted, because a percentage cannot be taken.
- Up to 500 lines are read at once.
- Costs are per unit in one currency. Taxes, write-down rules and valuation methods are not modeled.
- Nothing typed here leaves your browser.
Frequently asked questions
Why are shortages in parentheses?
Negative amounts are shown in parentheses so that a loss stands out from a gain and cannot be mistaken for a positive figure when the table is read quickly or printed.
What does the tolerance do?
It flags lines whose difference is bigger than that percent of the system quantity. It does not change any totals. Set it higher for noisy counts and lower for items where every unit counts.
How is the shrinkage share worked out?
It is the total of the negative value differences, taken as a positive number, divided by the total system value. Overages do not offset it in this figure.
Can I paste from a spreadsheet?
Yes. Copy four columns and paste them in. Tabs between the cells are read the same way as commas, and a first row of headings is skipped automatically.