Excel guide · 6 min read
How to calculate weeks of supply in Excel
=B2/AVERAGE(H2:K2). Wrap it in IF so a SKU with no recent sales shows a label instead of #DIV/0!: =IF(AVERAGE(H2:K2)=0,"No sales",B2/AVERAGE(H2:K2)). The Merino sweater has 96 units and sells 18 a week, so it has about 5.3 weeks left.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 | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SKU | On hand | On order | W1 | W2 | W3 | W4 | W5 | W6 | W7 | W8 | Avg 4 wks |
| 2 | Merino crew sweater | 96 | 0 | 14 | 12 | 15 | 13 | 16 | 18 | 17 | 21 | 18 |
| 3 | Wool beanie | 210 | 60 | 9 | 11 | 10 | 14 | 19 | 24 | 28 | 33 | 26 |
| 4 | Linen shirt | 340 | 0 | 22 | 18 | 15 | 12 | 9 | 8 | 6 | 5 | 7 |
| 5 | Leather belt | 58 | 24 | 7 | 6 | 8 | 7 | 6 | 7 | 8 | 6 | 6.75 |
| 6 | Rain jacket | 41 | 0 | 5 | 7 | 6 | 9 | 11 | 10 | 12 | 13 | 11.5 |
| 7 | Canvas tote | 75 | 0 | 4 | 3 | 2 | 1 | 0 | 0 | 0 | 0 | 0 |
| 8 | Total | 820 | 84 | 69.25 |
Download this sheet as an Excel practice file (.xlsx)
The layout: one row per SKU, one column per week
Weeks of supply answers a simple question: if sales carry on at the current pace, how many weeks until this item runs out? It's the per-item version of days of inventory, and it's the number buyers and store managers actually look at when deciding what to chase and what to stop ordering.
The sample has six SKUs. B holds units on hand today, C units already on order, and D:K the units sold in each of the last eight weeks, oldest on the left (W1) and the latest full week on the right (W8). If your sales come as a transaction log instead, build the weekly grid with SUMIFS on the SKU and the week start date, one column per week.
Use full weeks only. Including the current week, which is only partly over, drags the average down and makes every SKU look better stocked than it is.
The weeks of supply formula
Average the last four weeks of sales in L2 with =AVERAGE(H2:K2), then divide on-hand units by that rate. Four weeks is a common window: long enough to smooth out one odd week, short enough to follow a trend. Eight weeks suits slow sellers where a single sale moves the average a lot.
The sweater sold 16, 18, 17 and 21 in the last four weeks, an average of 18 a week. 96 units / 18 is 5.33 weeks. Multiply by 7 if your team thinks in days: about 37 days of supply.
Result: 5.3333333333 shows as 5.3 with Number format
5.33 weeks for the Merino crew sweater: 96 on hand at 18 a week.
Can I average the last 4 weeks without retyping the range every week?
In Excel 365, TAKE with a negative number takes columns from the right end of a range, so you can extend the range when you add a new week and the formula still reads the latest four. In older versions, insert the new week column inside the range so the reference grows, or keep a fixed 4-column block that you paste over each week.
Result: 5.3333333333
5.33 weeks again: TAKE(D2:K2,,-4) returns the last four week columns, H2:K2.
SKUs with no sales: the #DIV/0! guard
The canvas tote hasn't sold a unit in four weeks, so its average is 0 and a plain division returns #DIV/0!. That error also breaks any SUM or AVERAGE that touches the column. The IF in M7 returns "No sales" instead. IFERROR(B7/L7,"No sales") does the same job in a shorter formula, but it would also hide other errors, like text typed into a sales cell, so the explicit test is safer.
Don't treat "No sales" as infinite supply and move on. 75 totes that don't sell are cash sitting on a shelf. Give them their own flag so someone looks at them: a markdown, a move to another store, or a check that the item is actually on the floor and scanning.
Result: No sales
No sales: the tote sold 0 units in weeks 5 to 8, so there's no rate to divide by.
Flag reorder and overstock against min and max weeks
Put your targets in input cells, not inside the formula: minimum 4 weeks in Q1 and maximum 10 weeks in Q2 here. The flag checks the no-sales case first, then compares weeks of supply with the two limits. Lock the input cells with $ so the formula fills down.
The rain jacket has 41 units at 11.5 a week, only 3.6 weeks, so it shows Reorder. The linen shirt has 340 units selling 7 a week: 48.6 weeks, well past the 10-week maximum, so it shows Overstock. Your own min and max depend on lead time and how often you order; they're business decisions, so keep them where anyone can change them. The same min and max idea in units, with an order quantity that tops stock back up to the max, is in Excel formulas for inventory.
Result: Reorder
Reorder: the rain jacket has 3.57 weeks of supply, under the 4-week minimum.
Should stock on order count?
Decide and label it. On-hand weeks tell you whether you'll run out before the next delivery; on-hand plus on-order tells you whether you've already ordered enough. The leather belt is OK at 8.6 weeks on hand, but with 24 more on order it reaches 12.1 weeks, over the maximum. Many teams show both columns.
Result: 12.148148148 shows as 12.1 with Number format
12.1 weeks for the belt including the 24 units on order.
Why the averaging window matters: trends and seasons
The wool beanie is ramping up for winter: 9, 11, 10, 14 a month ago and 19, 24, 28, 33 in the last four weeks. On the last four weeks it has 8.1 weeks of supply. Average all eight weeks and it looks like 11.4 weeks, and on the first four weeks alone it would have been 19.1 weeks. The longer window is a step behind the trend, so it says you have more cover than you do just when demand is climbing.
It cuts the other way for the linen shirt, which is fading at the end of summer. The eight-week average (11.875 a week) shows 28.6 weeks; the last four weeks (7 a week) show 48.6 weeks. That's why a yearly average is a poor rate for anything seasonal: it spreads the peak across the quiet months and hides both the stock-out risk going into the season and the leftover stock coming out of it. For seasonal items, compare with the same weeks last year, or use the planned sales for the coming weeks as the rate instead.
Result: 11.351351351 shows as 11.4 with Number format
11.35 weeks for the beanie using all eight weeks, against 8.08 weeks on the last four.
Weeks of supply for a whole category
For a department or the whole store, divide total units on hand by total average weekly sales. Don't average the SKU results: one slow item with 48 weeks of supply pulls a simple average way up. Here the total is 820 units against 69.25 a week, 11.8 weeks, while the average of the five SKU figures is 14.8 weeks.
Totals in units only make sense for similar items. Across a mixed category, use stock and sales at cost or at retail value instead of units, so a pallet of socks doesn't count the same as a coat.
Result: 11.841155235 shows as 11.8 with Number format
11.84 weeks of supply across the six SKUs: 820 units on hand at 69.25 units a week.
Common mistakes
Including the current, unfinished week
Average full weeks only. A partial week of sales lowers the rate and inflates weeks of supply.
Averaging weeks of supply across SKUs
Divide total stock by total weekly sales. A simple average lets one slow seller distort the whole category.
Letting #DIV/0! spread through the sheet
Test for a zero average with IF and return a label, then flag those SKUs for review instead of ignoring them.
Using a year's average for seasonal items
A long window lags the trend. Use the last 4 weeks, last year's same weeks, or planned sales for the weeks ahead.
FAQ
What is the formula for weeks of supply?
Units on hand divided by average weekly units sold. In Excel: =B2/AVERAGE(H2:K2), where H2:K2 holds the last four weeks of sales.
How do I convert weeks of supply to days of supply?
Multiply by 7, or divide on-hand units by average daily sales. 5.33 weeks is about 37 days.
What is a good weeks of supply?
It depends on your lead time, how often you reorder and how steady demand is. Set a minimum and maximum in input cells that fit your business and flag SKUs outside them, rather than chasing a single number.
What's the difference between weeks of supply and inventory turnover?
Turnover is a period ratio (cost of goods sold / average inventory), usually for a year. Weeks of supply looks forward from today's stock at the current sales rate. 52 / weeks of supply gives a rough annualised turnover if the rate holds. See inventory turnover in Excel.
Functions used
AVERAGEMean of numbersIFPicks a result from a testIFERRORFallback when a formula errorsTAKEFirst or last rows, spillsSUMAdds numbersSUMIFSAdds with several conditions
Related guides
- How to calculate inventory carrying cost in Excel
- ABC inventory analysis in Excel
- How to calculate safety stock and reorder point in Excel
- How to calculate the stock-to-sales ratio in Excel
- Excel formulas for inventory: stock balance, reorder and value
- Excel formulas cheat sheet: the 25 everyone should know
Updated