Excel guide · 4 min read
How to calculate the stock-to-sales ratio in Excel
=C2/D2. Womenswear started October with 172,000 of stock at retail and sold 44,800, a ratio of 3.84: it carried almost four months of sales at the start of the month.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Month | Department | BOM stock (retail) | Sales (retail) | Stock-to-sales | Department | Womenswear | |
| 2 | Sep 1, 2026 | Womenswear | 168000 | 41500 | 4.0481927711 | Month | Oct 1, 2026 | |
| 3 | Sep 1, 2026 | Menswear | 96000 | 22800 | 4.2105263158 | Planned ratio | 3.2 | |
| 4 | Sep 1, 2026 | Accessories | 54000 | 18200 | 2.967032967 | Planned sales | 52000 | |
| 5 | Oct 1, 2026 | Womenswear | 172000 | 44800 | 3.8392857143 | |||
| 6 | Oct 1, 2026 | Menswear | 101000 | 23900 | 4.2259414226 | |||
| 7 | Oct 1, 2026 | Accessories | 57500 | 19600 | 2.9336734694 | |||
| 8 | Nov 1, 2026 | Womenswear | 181000 | 49200 | 3.6788617886 | |||
| 9 | Nov 1, 2026 | Menswear | 104500 | 27100 | 3.8560885609 | |||
| 10 | Nov 1, 2026 | Accessories | 63000 | 26400 | 2.3863636364 |
Download this sheet as an Excel practice file (.xlsx)
What the ratio means and the data you need
The stock-to-sales ratio is a retail planning measure: how many months of this month's sales you're holding at the start of the month. A ratio of 4 means the opening stock is four times what you'll sell that month. It's used month by month because sales swing with the season and the right amount of stock swings with them.
Keep both figures at retail value (selling price), which is how most retail plans are built. Mixing stock at cost with sales at retail makes the ratio look much lower than it is. The sample log in A:D has one row per department per month: the month start date, the department, BOM stock at retail and the month's sales at retail. E2 is simply =C2/D2, filled down.
Result: 3.8392857143 shows as 3.84 with Number format
3.84 for Womenswear in October: 172,000 of opening stock against 44,800 of sales.
Pick a department and month with SUMIFS
For a report that someone can change, put the department in H1 and the month start date in H2, and add up the matching rows with SUMIFS, once for stock and once for sales. If your log has several rows per department and month (one per store or sub-class, say), SUMIFS adds them all before dividing, which is what you want.
The month criterion must be a real date that matches the dates in column A. A month typed as text, like "Oct", won't match dates, and the formula returns #DIV/0! because the sales total is 0.
Result: 3.8392857143 shows as 3.84 with Number format
3.84 for the department in H1 (Womenswear) and the month in H2 (October 2026).
How do I get the ratio for the whole store?
Drop the department criterion and divide total stock by total sales for the month. Don't average the department ratios: Accessories sells much faster than Menswear, and a simple average gives every department the same weight.
Result: 3.7429218573 shows as 3.74 with Number format
3.74 for the store in October: 330,500 of stock against 88,300 of sales. The average of the three department ratios would say 3.67.
Compare with a planned ratio
Plans usually set a target ratio for each department and month. Put it in an input cell (H3, 3.2 here) and compare. The clearest comparison is in money: how much more or less stock you had than the planned ratio called for, which is BOM stock - planned ratio x sales.
Womenswear opened October with 28,640 more stock than a 3.2 ratio needed for the sales it made. That's not automatically bad, since a department building up for a strong November needs it, but it's the number to explain.
Result: 28640
28,640: October's opening stock was that much above what a 3.2 ratio on 44,800 of sales needed.
Plan the stock you need for a month
Turn the ratio around to plan ahead: planned BOM stock = planned sales x planned ratio. With 52,000 of planned December sales in H4 and a ratio of 3.2, Womenswear should start December with 166,400 of stock at retail.
This is the planned beginning-of-month figure that feeds an open-to-buy plan: next month's BOM is this month's planned end-of-month stock, and the gap between the plan and what's on hand and on order is what's left to buy. See open-to-buy in Excel.
Result: 166400
166,400 of stock at retail needed at the start of December for 52,000 of planned sales.
How it relates to inventory turnover
Turnover and the stock-to-sales ratio measure the same thing from opposite sides. Over several months, 12 divided by (total BOM stock / total sales) gives the annualised turnover at retail, and it's exactly the same number as the period's sales / average BOM stock, scaled up to a year.
For Womenswear over September to November: total stock 521,000 against total sales 135,500 is a ratio of 3.85, and 12 / 3.85 = 3.12 turns a year. A ratio of 3 all year would mean about 4 turns. Bear in mind this uses BOM stock as the average, so it's a planning approximation, and the turnover in your accounts is normally at cost; see inventory turnover in Excel for that version.
Result: 3.1209213052 shows as 3.12 with Number format
3.12: Womenswear's September to November pace works out at about 3.1 turns a year at retail.
Common mistakes
Stock at cost, sales at retail
Use the same basis for both, normally retail. Mixing them understates the ratio by the markup.
Using end-of-month stock
The standard ratio uses beginning-of-month stock. If you only have month ends, last month's end is this month's beginning.
Averaging department ratios
Total stock / total sales for the group. A simple average gives a small department the same weight as a big one.
Months typed as text
Use real dates (the 1st of each month) in the log and in the criteria cell so SUMIFS can match them.
FAQ
What is the formula for stock-to-sales ratio?
Beginning-of-month stock divided by sales for the month, both at retail. In Excel: =C2/D2.
What is a good stock-to-sales ratio?
It varies by category and season. Fast-selling categories run lower ratios than slow ones, and most departments need a higher ratio ahead of their peak. Compare with your own plan and last year rather than a general benchmark.
How do I calculate the stock I need for next month?
Multiply planned sales for the month by the planned stock-to-sales ratio. 52,000 of sales x 3.2 = 166,400 of beginning-of-month stock at retail.
Is the stock-to-sales ratio the same as inventory-to-sales?
They're related. Published inventory-to-sales ratios compare inventory with a month's sales too, but definitions differ, for example end-of-month stock at cost. Check which definition a report uses before comparing.
Functions used
Related guides
- Excel formulas for inventory: stock balance, reorder and value
- How to build a retail sales plan by month in Excel
- How to calculate open-to-buy in Excel
- How to calculate weeks of supply in Excel
- How to calculate inventory turnover in Excel
- ABC inventory analysis in Excel
Updated