Will your shrink budget survive one bad incident?
Everyday shrink is a rate you can almost predict. Organized theft is a rare event with a long tail. Run both together and a budget that holds on average turns out to fail more than a third of the time.
Operations Intermediate Monte Carlo Pro engine
After you install, this is the model to open.
What Is Shrinkage Really Costing?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
This one runs on a Pro engine, and every free install includes five full-quality runs on your own numbers, shared across all five Pro engines rather than five for each. After that, Pro is $199/year.
The answer
- Shrink, mean year
- $105,953 under the $110,000 budget
- Budget breached
- 37% of 20,000 simulated years
- P95 year
- $181,078 worst trial $412,374
- Median year
- $98,445 no organized incident in 45% of years
Two kinds of shrink in one sheet, because they behave nothing alike and the budget has to carry both. Everyday shrink is a rate: theft, damage and admin error running steadily against sales, and three years of stock counts put it somewhere between 0.6% and 1.9% on this $6.8M estate. Organized theft is a count. Four incidents in the last five years is an average of 0.8 a year, and the incident distribution here spreads that count over zero to five incidents with the odds a rate of 0.8 implies: nothing at all in about 45 of every 100 years, one incident in about 36, two or more in about 19.
Each incident costs between $9,000 and $95,000 depending on whether it is a van at the back door or a single high-value line. At the typed values the sheet reads $100,800 of shrink, or 1.48% of sales, against a $110,000 budget, which looks comfortably set. Click Run on 20,000 trials. The mean total is about $106,000, still under the $110,000 budget, and that is the sentence that gets shrink under-budgeted every year.
Read the over-budget output instead: the budget is breached in about 37 of every 100 years. The median is about $98,500, the ninetieth percentile about $156,000 and the ninety-fifth about $180,000, and the worst trials run close to $400,000. The distribution is not symmetric around its mean and cannot be, because the incident count is zero in nearly half of all years and every incident that does happen drags the total to the right.
A budget set at the mean of a right-skewed cost fails far more often than half the time, and that is the general lesson here rather than anything about retail. Second run, and it is the one that tells you where to spend. Take the organized theft off its distributions entirely and budget it as one average incident, a count of 1 at $26,000, leaving only the everyday rate uncertain.
The mean barely moves, from about $106,000 to about $104,000, but the ninety-fifth percentile collapses from about $180,000 to about $133,000. The tail belongs almost entirely to the organized incidents, which means the money spent on guards, cameras and back-door discipline buys tail rather than average, and it should be argued for on that basis rather than on an expected saving that will always look thin.
Two honest limitations. The organized term multiplies a random count by a single random average loss rather than drawing a separate severity for each incident, so it understates the spread in the years with two or three. And the everyday rate is applied to sales, so a bad shrink year and a bad sales year interact in a way this sheet does not model.
To adapt it, put your own sales and rate in from your stock count history, set the incident odds from your own incidents over the last five years, and take the severity range from your incident log.
The model
It arrives on a tab called Template: Shrinkage:
| Annual sales at retail ($) | 6800000 |
| Everyday shrink rate (theft, damage, admin error) | 0.011 |
| Everyday shrink cost ($) | 74,800 |
| Organized theft incidents this year | 1 |
| Average loss per organized incident ($) | 26000 |
| Organized theft cost ($) | 26,000 |
| Total shrink cost ($) | 100,800 |
| Shrink as a share of sales | 0.01482 |
| Shrink budget for the year ($) | 110000 |
| Shrink runs over budget (1 = yes) | 0 |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- How often does this store miss its number?Will This Store Make Its Year?
- Did the change work, or do your lines just differ?Did the New Process Actually Help?
- How many tickets before the event breaks even?Will the Event Make Money?
- What on-time rate can you actually promise?Will We Hit the Delivery Promise?
- The higher price lost. Did it make more money?Two Prices, One Answer
- What should you reserve against a recall?What Would a Recall Cost?
Every model like this one, and the method behind them: Monte Carlo simulation.