How often does this store miss its number?
One store, one year, built the way a store manager thinks: footfall, conversion, basket. Rent and payroll do not move, so the bottom line is about seven times as volatile as the top line, and the simulation shows exactly what that costs.
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 Intermediate Monte Carlo Pro engine
After you install, this is the model to open.
Will This Store Make Its Year?
- 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
- On the sheet
- $82,969 contribution against a $75,000 target
- Misses the target
- 45% of simulated years: nearly a coin flip
- Loses money outright
- 9.6% of years, about one in ten
- The swing
- -$20K to +$198K P5 to P95 contribution
One store, one year, built the way a store manager actually thinks about it: 3,200 people through the door a week, 22% of them buy, and they spend $34.50. That is $1,262,976 of sales, $580,969 of gross profit, and after $498,000 of rent, payroll and running costs the store contributes $82,969 against a $75,000 target. On the face of it the store makes its year.
Click Run with footfall, conversion and basket on ranges and the answer is a coin flip: contribution averages about $86,000, the median is about $83,000, and the miss flag comes back at about 0.45. The fifth percentile is about negative $19,000, so roughly one year in eleven the store does not merely miss the target, it loses money. Here is the number worth understanding, because it is the whole reason a store profit and loss behaves the way it does.
Sales run from about $1,041,000 at the fifth percentile to about $1,517,000 at the ninety-fifth, a span of about 38% of the median. Contribution runs from about negative $19,000 to about $200,000, a span of about 260% of its median. In relative terms the bottom line is roughly seven times as volatile as the top line, and that is not a modeling artifact, it is arithmetic: the store keeps 46 cents of every sales dollar and gives $498,000 back before it earns anything, so contribution is a 6.6% margin sitting on top of a $1.26M line.
Second run, and it is the one to have ready before somebody asks. Take footfall down 10%, to a most likely 2,880 a week with the range moved with it, and rerun. Mean contribution falls from about $86,000 to about $28,000. A tenth off the door count takes two thirds off the value of the store, which is the sentence to bring to a conversation about a bus route moving or the neighboring unit going dark.
The tornado ranks footfall first, basket second, conversion third, and that is worth knowing precisely because the two levers a store manager actually holds are the last two. What this cannot tell you: it has no seasonality, so a store that makes its year in six weeks of December and a store that trades evenly look identical here, and it assumes payroll is fixed when in practice hours get cut as trade falls, which softens the downside the model shows.
No correlations are set either; if your own data says a busy week comes with a smaller basket, add a negative pair on footfall and basket in the Correlations panel and rerun. To point it at your own store, replace the door count, the till conversion and the basket from your last 52 weeks, and put the real lease and payroll figures into the two cost lines that carry them.
The model
It arrives on a tab called Template: Will This Store Make Its Year:
| Footfall a week (people through the door) | 3200 |
| Conversion rate (share who buy) | 0.22 |
| Average basket ($) | 34.5 |
| Weeks trading | 52 |
| Sales for the year ($) | 1,262,976 |
| Gross margin | 0.46 |
| Gross profit ($) | 580,969 |
| Rent, rates and service charge ($ a year) | 168000 |
| Payroll including on costs ($ a year) | 268000 |
| Other store costs: utilities, cleaning, security, consumables ($ a year) | 62000 |
| Store contribution ($) | 82,969 |
| Contribution target ($) | 75000 |
| Misses the target (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
- Did the change work, or do your lines just differ?Did the New Process Actually Help?
- How many tickets before the event breaks even?Will the Event Make Money?
- What on-time rate can you actually promise?Will We Hit the Delivery Promise?
- The higher price lost. Did it make more money?Two Prices, One Answer
- What should you reserve against a recall?What Would a Recall Cost?
- Book the same room again or take the bigger one?Should We Run It Again Next Year?
Every model like this one, and the method behind them: Monte Carlo simulation.