Is a second shift enough for the peak week, or does it take a second line?
A line making 2,000 a day against a peak week of 11,000 units. The sheet says the line falls 200 short. Across 20,000 futures the line as it is covers the peak in 34 in 100 runs, and 95% odds takes a second shift at 2,500 a day.
Operations Starter Monte Carlo Pro engine
After you install, this is the model to open.
What capacity gives 95% odds of covering peak demand?
- 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.
What it does
One production line rated at 2,000 units a day, a peak week of 11,000 units over six working days, and uptime of 90% once changeovers and breakdowns are taken out. As typed the line makes 10,800 in the week and falls 200 short. Adding a shift takes it to 2,500 a day and 13,500 for the week, 2,500 to spare, and a second line at 3,000 a day makes 16,200, 5,200 to spare.
On those numbers the shift is plenty and the second line is waste. Click Run with peak demand and uptime as three-point estimates, 9,000, 11,000 and 15,000 units for the demand and 0.80, 0.90 and 0.96 for the uptime. The seed is set to 7 and the trials to 20,000 so your first run reproduces these figures exactly. Spare capacity on the line as it is comes back with a mean of -598.6 units and a median of -518.4, and the line covers the peak week in 34 in 100 runs.
With the extra shift, spare capacity has a mean of 2,081.7 and a median of 2,156.1, the fifth percentile is 1.2 units, and the shift covers the peak week in 95 in 100 runs, so 2,500 a day is the capacity with 95% odds of covering peak demand. The second line covers it in 100 in 100 runs with a median spare of 4,834.4, and whether the last five points of odds are worth a line is the question the sheet could not ask.
The three candidates share one draw of demand and one draw of uptime in every trial, so the difference between their readings is the capacity alone. The tornado on the shift reading puts peak demand first at -0.93, carrying 87% of the spread, and uptime second at 0.35, so the forecast matters far more than the maintenance schedule. Second run: if the orders for the peak week are already in hand, narrow the demand draw in the Risk Analysis panel to 10,500, 11,000 and 12,000 and rerun.
The odds on the line as it is move first, because a firm order book is worth most to the option with no slack. What the model cannot tell you: demand and uptime are each drawn once and held for the whole week, so a breakdown on the busiest day is not in the range; a unit short is counted, not priced, so it says nothing about which orders are late or what late costs; the extra shift is assumed to run at the same uptime as the day shift; and nothing here is the cost of the shift or the line.
To make it yours, put in your own rated capacity, the peak week from the order book as a low, likely and high, and the uptime your line actually records, and set the same two ranges on the draws in the Risk Analysis panel.
The model
It arrives on a tab called Template: Capacity With 95% Odds, carrying these columns:
- Peak week demand (units)
- 11000
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 much should you order when demand is uncertain?Inventory Order Quantity (Newsvendor)
- Should you hold your price, or cut first?Price War: Hold or Cut?
- Which tasks actually set your launch date?Launch Plan: Find the Critical Path
- Which risks actually deserve the money?Project Risk Register, Priced
- Which office wins once you price the commute?Where Should the Office Be?
- Should you announce your format before they do?Standards War: Which Format to Back?
Every model like this one, and the method behind them: Monte Carlo simulation.