The business case says yes. What are the odds?
Put in the machine cost, the saving per unit and the volume range and get back the odds the purchase pays. A $480,000 machine that saves 85 cents a unit, justified on the volume in the plan and the availability in the brochure. Put honest ranges on both and a business case with $78,000 of headroom becomes a coin flip.
Operations Intermediate Monte Carlo Pro engine
After you install, this is the model to open.
Is the New Machine Worth It?
- 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 committee case
- $77,823 NPV at the plan volume and availability
- Simulated NPV, mean
- $9,560 P5 -$130,187 · P95 $146,994
- Does not pay
- 46% of 10,000 trials
- Payback, median
- 4.8 years · P90 6.2, past the 4.9-year breakeven
A $480,000 machine that takes 85 cents out of every unit. At the plan volume of 190,000 units, 92% availability and $34,000 a year of maintenance, it saves $114,580 a year, which over seven years at a 10% discount rate is $557,823 of present value against a $480,000 cost. Net present value $77,823, payback 4.19 years, and the case passes.
Click Run with volume, availability and the saving per unit all on ranges. Net present value averages about $9,600 with a median of about $9,000, a P5 of about negative $130,200 and a P95 of about $147,000, and the does not pay flag comes back at 0.46. Payback, the third output, has a median of 4.78 years and a P90 of 6.16 years, both inside the seven-year life in the committee paper, not past it.
What is past it is the discount rate: the payback that actually breaks even is 4.87 years, the annuity factor, because net present value hits zero at exactly the payback that equals it. The median trial clears that line by about a month, which is the coin flip the does not pay flag is reporting, and it is a coin flip payback itself cannot see, because payback never discounts.
Nothing dishonest was done to the business case. It was built on the plan volume and the supplier availability figure, each of which is the top of a range rather than the middle of one, and multiplying two optimistic numbers produced a third. The tornado ranks the saving per unit first at about 0.69, volume second at about 0.65 and availability third at about 0.24, and that ordering decides what to go and check.
The second run is a real decision with a price on it. Buy the response-time maintenance contract, which is what lifts the availability floor from 0.80 to 0.88 and the most likely figure from 0.92 to 0.93, and rerun: net present value rises from about $9,600 to about $23,900 and the chance of the machine not paying falls from 0.46 to 0.40.
That is worth about $14,400 of present value, so if the contract costs less than that across seven years it pays for itself. Now do the run that matters more. The saving per unit is a study, not a measurement. Drop its most likely value from 85 cents to 75, leaving the rest of the range alone, and rerun: net present value goes to about negative $17,500 and the chance of not paying rises from 0.46 to 0.59.
Ten cents on a single estimate is the difference between a case that squeaks through and one that does not, which tells you exactly which number to go and verify on the shop floor before the paper is signed. What the model cannot tell you: it assumes the volume is there for all seven years, so it is a machine question with a demand assumption buried in it, and it has no residual value and no disposal cost.
To make it yours, put your own quotation in, take the availability from a machine of the same type already on your floor rather than from the brochure, and measure the saving per unit on a sample rather than estimating it.
The model
It arrives on a tab called Template: Is the New Machine Worth It:
| Machine, installation, commissioning and training ($) | 480000 |
| Units a year the machine will run | 190000 |
| Cost saving per unit ($) | 0.85 |
| Machine availability, share of planned running time | 0.92 |
| Units actually produced on it a year | 174,800 |
| Annual saving before maintenance ($) | 148,580 |
| Maintenance and consumables a year ($) | 34000 |
| Net annual saving ($) | 114,580 |
| Life (years) | 7 |
| Discount rate | 0.1 |
| Annuity factor | 4.868 |
| Present value of the savings ($) | 557,823.4 |
| Net present value ($) | 77,823.4 |
| The machine does not pay (1 = yes) | 0 |
plus 1 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 far short could the season finish?Will the Season Hit Its Number?
- Which of your peak-season fixes actually pay?What Could Go Wrong at Peak?
- Tool up or keep paying the supplier?Make It or Buy It?
- Is the round your driver does now the shortest?Which Order of Stops Makes the Shortest Delivery Round?
- How much does it cost to pull the event now?What If It Has to Be Cancelled?
- Will the new store pay for its fit-out?Is the New Store Worth Opening?
Every model like this one, and the method behind them: Monte Carlo simulation.