Will the new store pay for its fit-out?
A new store is a fixed commitment against a sales number nobody has yet proved. The model returns the range and, more usefully, the size of the landlord contribution that would make the bad case stop being a loss.
Operations Intermediate Monte Carlo Pro engine
After you install, this is the model to open.
Is the New Store Worth Opening?
- 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
- Median NPV
- $176K the point estimate said $254K
- P5, the bad case
- -$110K the landlord contribution that would fix it
- Does not pay back
- 17% of 20,000 simulated leases
- Year one
- 30% of openings go backwards before they ramp
A five year lease, $340,000 of fit out and fixtures, and a site assessment that says $1,150,000 of sales in a mature year. At a 45% margin that is $517,500 of gross profit against $310,000 a year of rent, payroll and running costs, ramping from 70% of mature sales in the first year to full by the third. Net present value $254,130, and the store gets signed.
Click Run with the mature sales, the margin and the first-year ramp on ranges. Net present value averages about $167,700 with a median of about $176,000, a fifth percentile of about a $109,700 loss and a ninety-fifth of about $423,300, and the does-not-pay-back flag comes back at about 0.16. First-year cash flow averages about $28,200 and its fifth percentile is about a $50,100 loss, so about 30 in 100 openings spend their first year going backwards before they start.
The tornado is unusually lopsided: mature sales rank at about 0.91 against the margin at about 0.31 and the ramp at about 0.20. Almost the entire risk of this decision is the one number the site assessment produced, so the money is better spent on a better assessment than on anything else in the sheet. Now the useful part, which is a negotiating number rather than a verdict.
The fifth percentile is about a $109,700 loss. Ask the landlord for a $110,000 contribution to the fit out and the bad case stops being a loss: the same store, the same forecast, the same lease, and the fifth percentile outcome is now break even. That is a specific thing to ask for and a specific reason to ask for it. The second run tests what property teams usually ask for instead, which is a break clause.
Shorten the grid to three years and run it again: net present value falls hard and the chance of not paying back rises sharply, because the fit out is not recovered until the fourth year. The flexibility everybody asks for is worth a large negative number here, and knowing that changes which of the two things you spend your negotiating capital on.
What the model cannot tell you: it has no cannibalisation, so if the new site takes trade from a store you already own the real number is lower, and it has no dilapidations or exit cost at the end of the lease. To make it yours, put your own fit out quote and heads of terms in, take the mature sales from your two closest comparable stores rather than from the assessment alone, and set the first-year ramp from your own last three openings.
The model
It arrives on a tab called Template: The New Store, carrying these columns:
- Fit out, fixtures and opening stock ($)
- 340000
with the model computed beside the data:
| Gross profit in a mature year ($) | 517,500 |
| Store cash flow ($) | 52,250 |
| Present value ($) | 47,500 |
| Present value over the lease ($) | 594,129.6 |
| Net present value ($) | 254,129.6 |
| The store does not pay back over the lease (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 many stores does your pilot really need?How Big Does the Pilot Have to Be?
- Should you drop the slow line?Should We Drop This Line?
- Is a random draw a fair way to split the weekends?Assign the Shifts Without Arguing
- How much of the opening date is just waiting?What Sets the Opening Date?
- Should you make more of your most profitable product?What Should We Make This Week?
- How few people can cover a seven-day week?How Few People Can Staff the Week?
Every model like this one, and the method behind them: Monte Carlo simulation.