Cellwise Start free

Excel guide · 4 min read

How to calculate the stock-to-sales ratio in Excel

Quick answerStock-to-sales ratio = beginning-of-month (BOM) stock / sales for that month, both at retail value. In Excel, with BOM stock in C2 and the month's sales in D2: =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.

RowABCDEFGH
1MonthDepartmentBOM stock (retail)Sales (retail)Stock-to-salesDepartmentWomenswear
2Sep 1, 2026Womenswear168000415004.0481927711MonthOct 1, 2026
3Sep 1, 2026Menswear96000228004.2105263158Planned ratio3.2
4Sep 1, 2026Accessories54000182002.967032967Planned sales52000
5Oct 1, 2026Womenswear172000448003.8392857143
6Oct 1, 2026Menswear101000239004.2259414226
7Oct 1, 2026Accessories57500196002.9336734694
8Nov 1, 2026Womenswear181000492003.6788617886
9Nov 1, 2026Menswear104500271003.8560885609
10Nov 1, 2026Accessories63000264002.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.

=C5/D5

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.

=SUMIFS(C2:C10,B2:B10,H1,A2:A10,H2)/SUMIFS(D2:D10,B2:B10,H1,A2:A10,H2)

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.

=SUMIFS(C2:C10,A2:A10,H2)/SUMIFS(D2:D10,A2:A10,H2)

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.

=SUMIFS(C2:C10,B2:B10,H1,A2:A10,H2)-H3*SUMIFS(D2:D10,B2:B10,H1,A2:A10,H2)

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.

=H4*H3

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.

=12/(SUMIFS(C2:C10,B2:B10,H1)/SUMIFS(D2:D10,B2:B10,H1))

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

Updated