Is your contingency big enough if delivery slips?
A late delivery costs you twice: the sales the season never makes, and the markdown on the stock that arrives too late to sell at full price. Price both and the contingency you set from the plan turns out to be the wrong size.
Operations Intermediate Monte Carlo Pro engine
After you install, this is the model to open.
What If the Supplier Misses?
- 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
- Chance it eats the contingency
- 70% of 20,000 trials clear the $60,000 held
- Expected shock cost
- $86,142 lost margin, markdowns and expediting together
- A bad season
- $160,788 the P95
- The sheet's point estimate
- $61,615 the near miss that makes $60K feel about right
One seasonal range, 45,000 units at $22 of gross margin, and a supplier who is usually but not always on time. The model carries two costs and most buying sheets carry only the first. Units that arrive too late to sell in the season are margin you never earn. Units that arrive late but still sell get marked down to move, which is margin you earn less of.
The split between the two uses the simplest defensible rule: three weeks late in a thirteen-week season means roughly three thirteenths of that stock never sells. At the typed values the sheet reads $61,615 of shock cost against a $60,000 contingency, which is the near miss that makes a buyer feel the contingency is about right. Click Run on 20,000 trials and the answer is not $61,615.
The mean cost of the shock is $86,656, the median is $80,487, P90 is $142,466 and P95 is $161,852. Read the second output before anything else: the $60,000 contingency is eaten in 70.6% of seasons. A contingency set from the plan case is not a contingency, it is a coin flip that lands badly 70 times in 100, and the reason is that the plan case sits near the bottom of the range rather than in the middle of it.
To size it properly, take the P80 of $119,065, which holds in 80 of every 100 seasons. Now the second run, which is the one that pays for itself. Change the on-time share to a tight PERT of 0.88, 0.95 and 1.00, which is what a dual-sourced or contractually penalised supplier actually looks like, leave everything else alone, and rerun: the mean falls from $86,656 to $44,608, the P80 falls from $119,065 to $55,194, and the contingency breach rate falls from 70.6% to 12.2%.
That $42,000 of mean cost is the most you should be willing to pay a second supplier or a penalty clause, per season, and it is a number you can put in front of the supplier. What this cannot tell you is whether the lost sales come back later. If your customer waits rather than buying elsewhere, the lost margin overstates the damage and the markdown understates it.
To adapt it, put your own units and margin in, set the on-time share and the weeks late from your supplier scorecard rather than from the contract, and put the contingency you are really holding in.
The model
It arrives on a tab called Template: Supply Shock:
| Units planned for the season | 45000 |
| Gross margin per unit ($) | 22 |
| Share of the order that lands on time | 0.9 |
| Weeks the rest of the order is late | 3 |
| Selling season, in weeks | 13 |
| Units on the late part of the order | 4,500 |
| Share of those units the season never sells | 0.2308 |
| Units lost to the delay | 1,038.5 |
| Margin lost to the delay ($) | 22,846.2 |
| Markdown per unit taken on the late stock that does sell ($) | 6 |
| Markdown cost ($) | 20,769.2 |
| Any of the order is late (1 = yes) | 1 |
| Expediting, re-plan and inbound premium if it is late ($) | 18000 |
| Total cost of the supply shock ($) | 61,615.4 |
plus 2 more rows on the sheet.
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 many tickets must you sell to break even?What Ticket Price Covers the Room?
- Is it the venue or the time of year?Venue and Month: What Drives Attendance?
- What does outsourcing cost once you price the risks?Outsource or Keep It In House?
- Are three of your regions really scoring the same?Do the Regions Rate Us Differently?
- The season looks fine on pace. Is it really?Will the Season Fill?
- Should weekends be staffed like any other day?Weekday and Weekend Are Not the Same Business
Every model like this one, and the method behind them: Monte Carlo simulation.