How much cash does the bigger buy tie up?
One seasonal buy, three demand cases, saved and compared. A weak season costs less gross profit than you would guess and more cash than you would like, and the markdown price is why.
Operations Starter Scenarios free
After you install, this is the model to open.
Which Season Plan Should the Chain Commit To?
- 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.
The answer
- The bigger buy, day one
- $253,000 cash out for the strong season, against $198,000
- What it buys
- +$110,090 gross profit over the normal season, if strong shows up
- If the season is weak
- $158,400 gross profit at 55% sell-through and a deeper markdown
- The spread
- $158K to $330K gross profit across the three scenario columns
A seasonal buy has to be committed before anybody knows what the season is, so the only honest way to look at it is three seasons at once. The thing that makes this a scenario rather than a sweep is that the markdown price moves with the season: in a weak season your stock is not the only stock being marked down, so the residual clears at $22 instead of $27, and in a strong season it clears at $32.
That is why the markdown sits in the assumption block with the others rather than being fixed. Save the assumption block as Normal, then copy the Weak column over those five assumptions and save that, then the Strong column, then build the summary. Normal returns $417,960 of revenue and $219,960 of gross profit at a 52.6 percent margin. Weak returns $356,400 and $158,400 at 44.4 percent.
Strong, on a bigger 11,500-unit buy, returns $583,050 and $330,050 at 56.6 percent. The comparison people expect is that a weak season is a disaster. It is not: gross profit falls by $61,560, about 28 percent, which is survivable. The number that should worry you is the Units at markdown line. Normal ends the season with 2,520 units on the rack and weak ends with 4,050, and in the weak case those 4,050 units sell for exactly what they cost.
That is not lost profit, it is a season of floor space and working capital producing nothing at all. And the strong case, which looks the best of the three, needs $253,000 of cash on day one against the normal case's $198,000, at the moment you know least about what is coming. Save a fourth case to see what that really means: the strong buy of 11,500 units, but a weak season's 55 percent sell-through and $22 markdown.
It returns $455,400 of revenue and $202,400 of gross profit, which is still more than the weak case on the smaller buy, so the profit line is not where the strong buy hurts you. It leaves 5,175 units on the rack against 4,050, on $55,000 more cash committed at the start. That is the real shape of the risk, and it is a stockroom and a bank balance rather than a profit and loss account.
What the model does not tell you is how likely each season is, and it treats sell-through as a single number rather than as something you find out gradually and could react to. Put your own units, prices and unit cost in, and if you can reorder mid-season, split the buy into two blocks and save a case for each.
The model
It arrives on a tab called Template: Which Season Plan Should the Chain Commit To?, carrying these columns:
- This season
- Normal
- Weak
- Strong
with the model computed beside the data:
| Units at full price | 6,480 |
| Units at markdown | 2,520 |
| Revenue ($) | 417,960 |
| Cost of the buy ($) | 198,000 |
| Gross profit ($) | 219,960 |
| Gross margin | 0.5263 |
| Cash out on day one ($) | 198,000 |
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
- Are bookings growing, or is that the zigzag?Smooth the Booking Curve
- Can you spot the bad batch before inspection does?Predict the Defect Before It Ships
- Does the new capacity pay if growth stops?Should We Add the Second Line?
- 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
Every model like this one, and the method behind them: What-if analysis in Google Sheets.