How to calculate inventory turnover in Excel

The turnover formula: COGS / average inventory

Turnover answers "how many times did we sell through our average stock this year?". The top of the fraction is cost of goods sold, the bottom is the stock you were carrying, both valued at cost. The data here is a kitchenware shop's year by category: inventory on January 1 and December 31, the year's COGS and the year's net sales.

The simplest average inventory is the opening and closing balance added and halved, which is what AVERAGE(B2:C2) does. Cookware started at 48,000, ended at 52,000, so it carried 50,000 on average and sold 210,000 at cost through it.

Days of inventory (DIO)

Most people find days easier to act on than a ratio. Days inventory outstanding is the period's days divided by turnover, or the same thing written the other way round: average inventory divided by COGS, times the days.

Keep the number of days in an input cell (H1 here) rather than typing 365 into every formula. For a quarter, put that quarter's COGS, its opening and closing stock and its day count (90 to 92) in the same places, and every formula still works. Some companies use 360 days by convention; whatever you pick, use it every time so periods compare.

Knives come out at 162.2 days and linens at 202.8 days: the stock you buy today sits on the shelf for roughly five to seven months before it sells.

Turnover for the whole store: sum first, then divide

For the total, add up COGS and the inventory balances, then divide. Do not average the six category ratios. AVERAGE(D2:D7/((B2:B7+C2:C7)/2)) gives 4.71, because the tiny gift-set line with its turnover of 11 counts as much as small appliances, which carry a third of the stock.

The correct figure weights each category by how much inventory it actually holds. The store as a whole works out to 84.4 days of inventory.

Seasonal stock: average more than two balances

Opening plus closing, halved, is fine when stock levels are steady. A shop that builds stock before the holidays is not steady. Here the total stock jumped to 310,000 at the end of September, a peak that the January and December balances never see.

If you have month-end or quarter-end balances, average all of them. J2:K6 holds the five quarter-end totals, from the 2024 year-end through the 2025 year-end. Including both year-ends keeps the start and the end of the period in the average. Using only K2 and K6 gives back the 4.32 from the two-point method.

The result is lower, and the days rise from 84.4 to 90.4. That is the more honest figure: for part of the year you were carrying far more stock than the year-end snapshots show.

Flag slow movers against your own target

A "good" turnover depends entirely on what you sell, so there is no number to hard-code. Put your target in an input cell (H2 holds 3 here, an example, not a benchmark) and compare each line to it. Then you change one cell to rerun the whole review.

To list every slow category at once in Excel 365 or 2021, =FILTER(A2:A7,D2:D7/((B2:B7+C2:C7)/2)<$H$2) spills Knives and Linens, and =SUMPRODUCT(--(D2:D7/((B2:B7+C2:C7)/2)<$H$2)) counts them: 2.

Read a very high ratio with care too. Gift sets turn 11 times, but their stock fell from 9,000 to 3,000. A high turnover from running the shelf nearly empty can mean lost sales, not efficiency.

Which version to use

One product or category over a stable year: COGS over the average of opening and closing stock. Seasonal business or a big change in stock mid-year: average month-end or quarter-end balances. A total across categories: sum COGS and the balances first, never average the ratios. Talking to a buyer or planner: show days of inventory alongside the ratio, since "160 days" lands faster than "2.25".

For days of cover on individual SKUs from recent sales (how long current stock will last at today's sell rate), see the inventory formulas guide. That is a forward-looking figure; turnover and DIO look back over a whole period.