Is a second supplier worth paying for?
Put in the failure odds and the cost of a second source and get back whether pre-qualifying pays. Pre-qualifying a second source costs money every year and saves money almost never. Run both versions and the case for doing it turns out to have nothing to do with the average.
Operations Advanced Monte Carlo Pro engine
After you install, this is the model to open.
What If the Single Source Fails?
- 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
- Years with no exposure
- 92% of 20,000 trials cost exactly $0
- Expected exposure
- $175,698 a year, but the mean is a poor summary of a switch
- The tail
- $2.0M P95, and the worst trial reached $3.6M
- Pre-qualifying now
- $27,898 expected yearly loss that cuts the P95 to $655K
One component, one supplier, and no second source qualified. If they fail, three things cost money: the margin on the output you cannot make while a replacement is qualified, the expedite premium you pay in the gap, and the qualification program itself, which you now run under pressure. All three sit behind an 8% chance of failing at all, so nothing happens in most years.
Click Run on the 20,000 trials and the seed the template loads with. Exposure is exactly zero in 92 of every 100 trials. The mean is $175,698, the P95 is $2,019,409 and the worst trial reached $3,642,654, which is more than the plant makes in four months. The tornado is worth a glance even though it says something obvious: the failure switch scores 0.999 and the other three inputs score essentially nothing, because in nine years out of ten nothing they describe ever happens.
That is what a switch does to a model, and it is why the average is such a poor summary here. Now do the second run, and it is the point of the whole template. Pre-qualify the second source now: set the weeks to qualify to a PERT of 2, 3 and 5, because a qualified alternate is a purchase order and not a program, and move the qualification program cost out of the bracket the failure switch multiplies, so it is paid every year whether or not anything happens.
Rerun on the same seed. The mean exposure goes up, from $175,698 to $202,230. Pre-qualification loses $26,532 a year in expectation, and the no-exposure-at-all flag goes from 0.92 to zero, because now every year costs something. Read the tail instead and it is a different decision: the P95 falls from $2,019,409 to $655,046, and the worst trial in twenty thousand falls from $3,642,654 to $988,244.
That is what buying insurance looks like from the inside. You expect to lose money on it, every year, and you buy it anyway because the year you do not survive is not in the average. If your business could absorb a $2M hit without breaking a covenant or missing a customer commitment, the mean is the right lens and you should not pre-qualify.
If it could not, the P95 is the right lens and the $26,532 is the premium. What this cannot tell you is the reputational cost of missing a customer for four months, which for a tier-one supplier is often larger than everything in this sheet. To adapt it, put your own weekly contribution margin in, set the weeks to qualify from what your quality team says a real qualification takes rather than from the process document, and set the covered share honestly from how much output your buffer would carry in week six, not week one.
The model
It arrives on a tab called Template: What If the Single Source Fails:
| The single source fails this year (1 = yes) | 0 |
| Weeks to qualify a second source | 20 |
| Contribution margin per week of lost output ($) | 185000 |
| Share of output covered from stock and alternates | 0.55 |
| Margin lost during the qualification gap ($) | 0 |
| Expedite, re-tool and freight premium if it happens ($) | 240000 |
| Qualification program cost ($) | 160000 |
| Total exposure this year ($) | 0 |
| The year carries no exposure at all (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
- 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?
- What does an unreliable supplier cost you in stock?Safety Stock for a Plant That Cannot Stop
- Is the line running on a cycle nobody scheduled?Does Our Output Repeat on a Cycle?
- Is a flat daily plan good enough?The Season Repeats: Forecast It
- Is your contingency big enough if delivery slips?What If the Supplier Misses?
Every model like this one, and the method behind them: Monte Carlo simulation.