What on-time rate can you actually promise?
Four handovers, each of them running between 92% and 99%, and one promise that depends on all four. The multiplication is the whole lesson, and the model returns the number you should have promised instead.
Words on this sheet
- Order line: One item on an order. An order for three different products is three lines.
Operations Advanced Monte Carlo Pro engine
After you install, this is the model to open.
Will We Hit the Delivery Promise?
- 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
- The promise
- 95% on time in full, written into the contract
- What four good steps deliver
- 87.9% mean OTIF: four stages at 95-98% multiply, not average
- Months that miss
- 100% every one of 20,000 simulated months falls short
- A promise you would keep
- 84% the P5; even the best month only reached 94.4%
Four steps stand between an order line and a happy customer, and the business reports all four separately: stock available 96.5%, picked inside the cut off 98%, carrier on the day 95.5%, shipped complete 98.5%. Every one of those numbers is a number somebody is proud of. Multiply them and the line arrives on time and in full in 89 of every 100 months (88.96%).
Sales promised 95%. Click Run with all four steps on PERT ranges and the result is blunt in a way that is worth seeing rather than being told: across ten thousand simulated months the on time in full rate averages 87.9%, the median is 87.9%, the P10 is 85.1% and the P90 is 90.6%. The miss flag against the 95% promise comes back at 1.000, or a hair under it on a lucky run: not one of those ten thousand months reached the promise, and the best of them stops somewhere around 94.5%.
There is a ceiling, and it is worth knowing where it is. Every one of the four steps at the very top of its range in the same month gives 96.7%, so the promise is not impossible, it is what a perfect month looks like. That is not a bad month, it is a promise that cannot be kept by a process shaped like this one, and no amount of expediting closes a six point gap that is arithmetic.
The model also hands you the number sales should have taken to the customer. Read the P10 off the report: 85%, which is the level the process holds in 90 of every 100 months. Now the second run, and pick the step the tornado names rather than the one that gets talked about. The tornado puts the carrier first, stock availability second, picking third and completeness last.
Change the carrier to a PERT of 0.955, 0.975 and 0.99, which is a premium service rather than a standard one, and rerun: the mean rises to 90.0% and the P10 to 87.8%. Buying the best carrier available moves you from 88% to 90% and still leaves you five points short of the promise, which tells you the promise has to change, not the carrier.
What this model cannot do is show you a correlated failure: it treats the four steps as independent, and the day the warehouse system goes down all four go together, so the real bad tail is worse than the P10 here. If your own history shows that, add correlation pairs in the Correlations panel and rerun. To make it yours, replace the four shares with the numbers your own systems report, set the promised rate to the service level you have actually signed, and use the P10 as the number you offer next time.
The model
It arrives on a tab called Template: Will We Hit the Delivery Promise:
| Order lines in the month | 480 |
| Share of lines with stock available at pick | 0.965 |
| Share picked and packed inside the cut off | 0.98 |
| Share delivered by the carrier on the promised day | 0.955 |
| Share complete with no short ship, given stock was available | 0.985 |
| On time in full rate per line | 0.8896 |
| Lines on time and in full (lines) | 427 |
| On time in full promised to the customer | 0.95 |
| Lines needed to hold the promise (lines) | 456 |
| Misses the promise this month (1 = yes) | 1 |
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
- 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?
- Book the same room again or take the bigger one?Should We Run It Again Next Year?
- Is a second supplier worth paying for?What If the Single Source Fails?
- Is your worst supplier really worse than the rest?Are These Three Suppliers Really Different?
- Does this delivery note match a supplier on file?Which Rows in Two Vendor Lists Are the Same Supplier?
Every model like this one, and the method behind them: Monte Carlo simulation.