Which category did the buying plan get wrong?
Paste the units sold per category against the planned mix and get back whether the mix is off and where. Six categories, 1,500 units sold, against the mix the buying plan assumed. The mix is wrong and it is wrong in one place: a single category is more than half the entire test statistic.
Operations Starter Statistics free
After you install, this is the model to open.
Is the Product Mix What We Planned For?
- 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 verdict
- p < 0.001 chi-square 40.16 on 5 degrees of freedom
- More than half the miss
- Outerwear 412 sold against 330 planned: 20.4 of the 40.2
- Bought into the wrong category
- 226 units 15.1% of a 1,500-unit season
- Exactly to plan
- Accessories its share of the whole statistic is 0.4
Six categories, 1,500 units sold this season, against the share of the buy each category was given. The planned-share column holds proportions rather than counts, and that is deliberate: the tool scales the expected values to your observed total and prints a line saying it did, which is what lets you paste a buying plan straight in without converting anything.
The units-the-plan-implies column repeats that arithmetic on the sheet so you can check the two agree. Click Run: chi-square comes back 40.16 on 5 degrees of freedom with a p-value of 0.00000014. The mix you sold is not the mix you bought, and no amount of rounding explains it. Now find where. The Observed versus Expected table in the report numbers the categories in the order they sit on the sheet, so the first line is outerwear.
A chi-square total is built from one contribution per category and they are almost never even: outerwear sold 412 against 330 expected, and its contribution alone is 20.4 of the 40.2, more than half the whole statistic. Denim is next at 8.1, having sold 196 against 240. Accessories contributes 0.4 and is behaving exactly as planned. So this is not a plan that is broken across the board, it is two categories moving in opposite directions.
Add the six misses up and they come to 226 units out of 1,500, which is 15.1% of the season bought into the wrong categories. Against the open-to-buy figure beside the table, $480,000 at $26 a unit, that is about 2,780 units of next season that would be placed wrongly if you repeated this plan. Second run: delete the outerwear line from both ranges and rerun.
Chi-square falls to 15.09 on 4 degrees of freedom with a p-value of 0.0045, still comfortably significant, which tells you denim needs fixing whether or not you fix outerwear. Put whole counts in the observed column and never percentages, because the test works from the number of units behind each share; 22% of 1,500 and 22% of fifteen are completely different evidence.
What it cannot tell you is why outerwear ran hot, and a category code has no opinion about the weather. To use your own season, overwrite the categories, the units sold and the planned shares, and widen both ranges to match.
The model
It arrives on a tab called Template: Is the Mix What We Planned For, carrying these columns:
- Category
- Units sold this season
- Planned share of the buy
- Units over or short
with the model computed beside the data:
| Units next season buys | 18,461.5 |
| Share of this season bought into the wrong category | 0.1507 |
| Units of next season that would go the same way | 2,781.5 |
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
- Is the overrun general or is it one bad line?Is the Event Build On Track?
- Is the rollout slowing, or does it just feel slow?Is the Rollout Where It Should Be by Now?
- Will your shrink budget survive one bad incident?What Is Shrinkage Really Costing?
- How often does this store miss its number?Will This Store Make Its Year?
- 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?
Every model like this one, and the method behind them: Statistics in Google Sheets.