Inventory spreadsheets: the inventory spreadsheet template that does the arithmetic
- Variance in units (negative is a shortfall)
- -86
- What the shortfall cost you to replace
- $1,075
- Count accuracy, percent of expected found
- 96.4
- Units at which this stock hits its reorder point
- 480
The arithmetic on these sheets is your own count: units, unit cost and reorder point in, variance and value out. Nothing is estimated and no industry average is applied to your numbers. Where a page states an outside figure it names the document it came from and links to it.
The figures above are a worked example (-86) from a typical parts room. Change any input and the count updates as you type.
Download the Inventory spreadsheet template worked example (CSV)
This sheet is the stock count itself. Enter each line you hold with what the record expected, what you actually counted and what a unit costs you, and it works the variance line by line, totals what the gap is worth at your own costs, and flags every line that has fallen under the reorder point you set. It is the inventory spreadsheet template most people build badly in a hurry, with the arithmetic already in it and nothing to set up.
Stock counts: what people ask before the first one
Why does it ask for expected as well as counted? Because a count on its own is just a number, and the useful figure is the difference. Expected against counted is what tells you whether stock is walking, whether receiving is being recorded, or whether the reorder point is simply set wrong. A sheet that only holds the counted figure cannot answer any of those.
What unit cost should I put in? What you pay for one, not what you sell it for. The variance value this sheet gives you is what the gap cost your business to replace, which is the figure worth acting on. If you want the retail value of missing stock that is a different sum and a different conversation.
Can I use this for a first count when I have no expected figures? Yes. Leave expected blank on the first pass and the sheet just totals what you counted and what it is worth, which is exactly what an opening count is. From the second count onward the previous count becomes your expected, and the variance starts doing real work.
Where the constants in this tool come from
NIST SP 800-53 Rev. 5, control CM-8: System Component Inventory.
CIS Critical Security Controls v8, Control 1: Inventory and Control of Enterprise Assets.