Excel guide · 4 min read
How to calculate inventory shrinkage in Excel
=C2-D2 gives shrink dollars and =(C2-D2)/C2 gives shrink as a percentage of book inventory. Most retailers report it as a percentage of sales instead: total shrink divided by sales since the last count, by store with SUMIFS.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Store | Category | Book inventory | Counted | Sales since last count | Shrink $ | Store | Identified cause | Amount | Count | ||
| 2 | North | Grocery | 48200 | 47350 | 212000 | 850 | North | Damaged | 320 | Oct 2025 | ||
| 3 | North | Health & beauty | 22600 | 21890 | 64000 | 710 | North | Vendor short-ship | 180 | Apr 2026 | ||
| 4 | North | Household | 18400 | 18260 | 51000 | 140 | South | Damaged | 410 | Oct 2026 | ||
| 5 | South | Grocery | 51900 | 50620 | 236000 | 1280 | South | Expired | 260 | |||
| 6 | South | Health & beauty | 24100 | 22980 | 70000 | 1120 | South | Paperwork error | 150 | |||
| 7 | South | Household | 17800 | 17690 | 49000 | 110 |
Download this sheet as an Excel practice file (.xlsx)
Set up the count results
The sheet above is a two-store chain after its October stock count. Book inventory (C) is what the system says should be on hand; Counted (D) is what the count found; Sales (E) covers the six months since the last count. Column F is the shrink, =C2-D2, copied down.
Pick one valuation and use it everywhere. Here every column is at retail (selling price), which is what most store reports use because the sales figure is at retail too. If your counts are at cost, either convert or divide by cost of goods sold instead of sales. Dividing cost-based shrink by retail sales makes shrink look smaller than it is.
Shrink % for one line
Shrink as a share of book inventory tells you how far off the records were for that line. Format the result as a percentage with two decimals, because shrink numbers are small. Book inventory is the calculated stock (opening + received - sold); Excel formulas for inventory shows how to build it, including a running balance from an in and out log.
Result: 0.017634854772 shows as 1.76% with Percent format
North Grocery is $850 short on $48,200 of book stock: 1.76%. South Health & beauty, row 6, is far worse at 4.65%.
Shrink % of sales, by store and by category
Shrink as a percentage of sales is the figure people compare between stores, between years and against industry surveys, because sales scale with the size of the store. Use SUMIFS on both the shrink and the sales so the ratio is weighted correctly, rather than averaging the line percentages.
Result: 0.0070704225352 shows as 0.71% with Percent format
0.71% for South ($2,510 of shrink on $355,000 of sales). North is 0.52%, and the chain overall is 0.62% ($4,210 on $682,000).
Which category is the problem?
Swap the store column for the category column. Small, high-value items usually stand out.
Result: 0.013656716418 shows as 1.37% with Percent format
1.37% for Health & beauty across both stores, against 0.48% for Grocery. That's where to look first.
Separate identified causes from unexplained shrink
Not all shrink is a mystery. When you investigate a count you usually find some of it: damaged stock thrown out without a write-off, a supplier delivery that was short, out-of-date product, a receiving paperwork error. Log each one with its store, a reason and the amount (H:J above), then subtract them from the total. What's left is the unexplained shrink, the part nobody can account for yet.
The reason column pays off over time. =SUMIFS(J2:J6,I2:I6,"Damaged") shows $730 of damage across both stores, which is a handling or storage fix, not a security one.
Result: 1200
$1,200 of North's $1,700 shrink is unexplained. South's is $1,690 of $2,510. Overall, 68.6% of the shrink has no identified cause.
Track it count to count
One count is a snapshot; the trend is what tells you whether a change worked. Keep one row per count with shrink % of sales per store (L:N above). The latest row is a live formula, =SUMIFS($F$2:$F$7,$A$2:$A$7,N1)/SUMIFS($E$2:$E$7,$A$2:$A$7,N1), so it updates when you correct a count. Older rows are pasted as values so they don't change when this period's data is replaced.
Subtract the previous count to see the direction. Show it in percentage points, not percent: going from 0.63% to 0.71% is 0.08 points.
Result: 0.00077042253521 shows as 0.08% with Percent format
South is up 0.08 percentage points since April and has risen at every count. North fell 0.06 points to 0.52%. A line chart of L1:N4 makes this obvious in a meeting.
Make the numbers trustworthy
- Compare like periods. Sales must cover exactly the time since the last count, or the percentage is meaningless.
- Cut off receiving and sales at the count time. A delivery received but not booked looks like overage; one booked but still on the truck looks like shrink.
- Treat overages as a warning, not good news. A negative shrink usually means a counting or receiving error that is hiding real loss elsewhere.
- Keep the raw count sheets. Recount any line whose shrink % is far above the others before anyone draws conclusions.
- For the turnover and reorder side of the same stock, see the inventory formulas and inventory turnover guides.
Common mistakes
Averaging line percentages
South Health & beauty (1.60% of sales) and South Household (0.22%) average to 0.91%, but together they are 1.03%, because Health & beauty sells more. Divide summed shrink by summed sales with SUMIFS.
Mixing cost and retail
Shrink at cost divided by sales at retail understates it. Value inventory and sales on the same basis.
Treating all shrink as theft
Damage, expired stock, short deliveries and paperwork errors are often a large share. Log identified causes and only call the rest unexplained.
Overwriting past counts
Paste earlier results as values in a trend table so this period's data doesn't change history.
FAQ
What is the formula for inventory shrinkage?
Shrink = book (recorded) inventory - physical count. As a percentage, divide by book inventory for accuracy of records, or by sales since the last count for the figure retailers usually report.
How do I calculate shrink as a percentage of sales in Excel?
=SUMIFS(shrink,store,"South")/SUMIFS(sales,store,"South"), with sales covering the period since the last count. Format it as a percentage with two decimals.
Should I count shrink at cost or at retail?
Either works if you are consistent. Retail matches sales and is common in store reporting; cost matches your accounting value. Don't divide one by the other.
What does negative shrinkage mean?
You counted more than the records say. It usually points to a receiving or counting error, such as a delivery not yet booked, so check it before treating it as a gain.
Functions used
Related guides
- How to calculate cost of goods sold in Excel
- Excel formulas for inventory: stock balance, reorder and value
- How to calculate inventory turnover in Excel
- How to calculate sell-through rate in Excel
- How to calculate initial markup in Excel
- ABC inventory analysis in Excel
Updated