How far short could the season finish?
A 24 store chain, 13 trading weeks and one gross profit number the whole season is judged on. Put honest ranges on weekly takings, markdown and margin and the plan stops being a target and becomes a probability with a shortfall attached.
Operations Intermediate Monte Carlo Pro engine
After you install, this is the model to open.
Will the Season Hit Its Number?
- 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
- Miss the $2.3M plan
- 62% of 20,000 simulated seasons
- Gross profit, median
- $2.22M P5 $1.80M · P95 $2.65M
- Shortfall when it misses
- $242,800 on average, against $150,513 spread over every season
- A plan the season clears
- $1.99M the 20th percentile: four seasons in five
Thirteen weeks, twenty-four stores, and one number the season is judged on. Sales per store per week, markdown and bought margin are the three things the plan holds still and the season does not, so each is a range: weekly takings from $13,500 to $22,500 with $18,500 most likely, markdown from 8% to 22%, margin from 49% to 54.5%. The sheet opens at $2,308,800 of season gross profit, $8,800 clear of the $2,300,000 plan, which is what a plan built on last season's averages always looks like.
Click Run and the plan stops being a target. Gross profit averages $2,217,806 with a median of $2,214,643, a P5 of $1,790,705 and a P95 of $2,647,319, and the miss flag comes back at 0.62. The plan is a number the season clears in about 40 of every 100 runs. The shortfall output is the part to take into the meeting: it averages $151,614 across all seasons, but across only the seasons that actually miss it averages about $245,500, and those are two different sentences.
The tornado puts sales per store first, markdown second and margin a distant third. Now do the second run, because the obvious fix is the wrong one. Tighten the markdown range to a maximum of 0.16, which is what a real markdown discipline commitment buys you, and rerun: gross profit rises to $2,275,391 and the miss flag falls only from 0.62 to 0.54.
Markdown discipline is worth about $58,000 and it does not fix a plan that is set above the season. What fixes it is the plan number, and the model hands you the candidate: the twentieth percentile of gross profit is $1,995,423, so a plan of $2,000,000 is one the season clears in 80 of every 100 years. Two things this model cannot tell you.
It treats the thirteen weeks as one draw, so it says nothing about whether a bad start predicts a bad finish. And it has no store-to-store variation at all: twenty-four stores each averaging $18,500 and one store on $40,000 with twenty-three on $17,600 are the same season here and very different businesses. To make it yours, replace sales per store per week with your own weekly average, set the markdown range from your last three seasons rather than from the budget, and put your real plan number in.
The model
It arrives on a tab called Template: Will the Season Hit Its Number:
| Weeks in the season | 13 |
| Stores trading (count) | 24 |
| Sales per store per week ($) | 18500 |
| Season sales ($) | 5,772,000 |
| Markdown taken (share of season sales) | 0.12 |
| Gross margin before markdown | 0.52 |
| Season gross profit ($) | 2,308,800 |
| Season gross profit plan ($) | 2300000 |
| Misses the plan (1 = yes) | 0 |
| Shortfall against the plan ($) | 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
- 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?
- How many stores does your pilot really need?How Big Does the Pilot Have to Be?
Every model like this one, and the method behind them: Monte Carlo simulation.