Shrinkage is inventory lost to theft, damage, and error — the gap between book stock and what’s actually on the shelf. Measured as the lost value over sales (or over book inventory).
The example
$5k lost on $250k sales.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Inventory loss | 5000 |
| 3 | Sales | 250000 → 2.0% |
The formula
The formula:
How it works
How it works:
- Book inventory is what your records say you have; the physical count is what’s really there.
- The difference is the shrinkage loss — theft, damage, miscounts, supplier shortfalls.
- Divide the loss by sales for the standard shrink rate (industry average is ~1–2%).
- Dividing by book inventory instead gives shrinkage as a share of stock value.
Track the trend, not just the number. A single shrink rate is hard to judge; plotting it by period or store reveals problems. A spike at one location or after a process change points straight to the cause — conditional formatting on a store-by-period grid makes outliers jump out.
Try it: interactive demo
Inventory loss and sales.
Variations
Loss amount
Book vs count:
Of book value
Share of stock:
In units
Unit shrink:
Pitfalls & errors
Pick the base. Shrink over sales vs over inventory give different rates — state which.
Value vs units. Measure in dollars for finance, units for operations.
Count accuracy. A bad physical count fakes shrinkage — reconcile before alarming.
Practice workbook
Frequently asked questions
How do I calculate inventory shrinkage in Excel?
What's a normal shrinkage rate?
Should I measure shrink over sales or inventory?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class