Does the new capacity pay if growth stops?
A capacity investment does not earn on the demand you have, it earns on the demand you have no room for. That makes it worth almost nothing if growth stops and a great deal if it holds, and an average forecast hides exactly that.
Words on this sheet
- Contribution: What is left of the income after the costs that come with it, before the fixed costs are paid.
Operations Advanced Monte Carlo Pro engine
After you install, this is the model to open.
Should We Add the Second Line?
- 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
- Expected NPV
- $651K median $635K across 20,000 futures
- Does not pay
- 27% of futures; the P5 loses $1.02M
- Year-eight use
- 299K units of the 420K the line can make
- If growth stops
- -$870K mean NPV, and 89% of futures lose
The first line makes 480,000 units a year and next year's order book says 610,000. A second line adds 420,000 units of capacity for $1,400,000 of capital and $380,000 a year to run. Read the line headed Extra units the second line sells before anything else, because it is the whole investment: the second line earns nothing on the 480,000 units the first line already makes, only on the 130,000 that will not fit, growing to about 322,700 by year eight and stopping there, because once demand passes 900,000 the second line is full too.
At the plan growth of 4% that is $2,427,764 of present value and a net present value of $1,027,764. Click Run with demand, growth and contribution all on ranges. Net present value averages about $651,000 with a median of about $635,000, a P5 of about minus $1,022,000 and a P95 of about $2,360,000, and the does-not-pay flag averages 0.27. More than 25 in 100 cases lose money, and the worst twentieth loses about three quarters of the capital.
The locked third output is worth adding: extra units in year eight average about 299,000 against the 420,000 the line can make, and the line is full in about 14 of every 100 futures. You are buying a line that spends most of its life mostly idle, and it is still worth about $651,000, because it earns in exactly the years you could not have served the customer at all.
The second run is not a sensitivity, it is the decision. Everything here rests on growth continuing, so set the growth input to a PERT of minus 0.02, 0.00 and 0.02, which is the flat market case, and run it again. Net present value averages about minus $872,000, the median about minus $861,000, and the chance of not paying goes from 0.27 to 0.89.
There is no version of flat demand in which this investment is a good idea, because the second line still costs $380,000 a year to stand there. The whole case is the growth assumption, and the tornado agrees: demand next year and growth sit together at the top at about 0.68 each, and contribution per unit is a distant third at about 0.23.
Two things the model cannot do. It treats this as now or never, when waiting a year and deciding again with better information is a genuinely different and often better option, which is a decision tree question rather than a simulation one. And it assumes no competitor adds capacity, so the demand you are buying room for is demand nobody else serves.
To make it yours, put your own capital quote and running cost into the assumption block, set the growth range from your own order book history rather than from the strategic plan, and change the added capacity if the line you are quoting is a different size.
The model
It arrives on a tab called Template: Should We Add the Second Line, carrying these columns:
- Capital cost of the second line ($)
- 1400000
with the model computed beside the data:
| Demand (units) | 610,000 |
| Extra units the second line sells | 130,000 |
| Net cash flow ($) | 153,000 |
| Present value ($) | 137,837.8 |
| Present value of the second line ($) | 2,427,763.9 |
| Net present value ($) | 1,027,763.9 |
| The second line does not pay (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 plan beat the deadline?Will We Be Ready for Peak?
- Could your sample keep checking the same head?Pull the Units to Inspect
- What repeats in your numbers that nobody planned?Find the Cycle You Did Not Know Was There
- Should you buy more than you expect to sell?How Much Stock, When Demand Is a Guess
- One store takes more. Real, or a good fortnight?Are These Two Stores Really Different?
- How few units tell you the batch average?How Many Units Do We Have to Check?
Every model like this one, and the method behind them: Monte Carlo simulation.