Calculating inventory days (DIO): formula, Excel and a worked example

Calculating inventory days takes three numbers: opening inventory, closing inventory and cost of goods sold for the same period. Average the two inventory balances, divide by cost of goods sold and multiply by the days in the period. A distributor with average inventory of $8,800,000 and annual cost of goods sold of $52,000,000 holds 61.8 days of inventory.

What inventory days measures

Inventory days (DIO) = Average inventory / Cost of goods sold × Days in period

Average inventory = (Opening inventory + Closing inventory) / 2

DIO = Days in period / Inventory turnover

Inventory days, also called days inventory outstanding (DIO), days in inventory, or in the UK stock days, say how many days of cost of sales the inventory on hand represents. The CFA Institute's reading on financial analysis techniques lists it, as days of inventory on hand, among the major activity ratios next to inventory turnover. The two are the same information inverted: 5.9 turns a year is 365 / 5.9, about 62 days. The ratio itself, and what moves it, is covered in inventory turnover ratio; this page is the days version.

The rows you need

One row per category or site, all at cost:

Not revenue, and not inventory at selling price. Inventory is carried at cost, so the denominator must be cost too.

Worked example: five categories at a distributor

An industrial distributor, one fiscal year of 365 days, USD thousands. DIO in E2 and turns in F2:

=AVERAGE(B2:C2)/D2*365
=D2/AVERAGE(B2:C2)

filled down to row 6.

Fasteners: (1,200 + 1,400) / 2 = 1,300, and 1,300 / 9,500 × 365 = 49.9 days. Plumbing: 2,200 / 8,900 × 365 = 90.2 days. The total row sums the inventory and COGS columns and recomputes from the totals: 8,800 / 52,000 × 365 = 61.8 days, and 52,000 / 8,800 = 5.91 turns. As a check on the pair, 365 / 5.909 = 61.8.

Plumbing holds 25% of average inventory (2,200 of 8,800) but carries 17% of cost of goods sold (8,900 of 52,000), and power tools hold 32% against 25%. Those two categories pull the total up.

The check: the total is COGS-weighted

Total DIO is the COGS-weighted average of the category DIOs, not their simple average.

3,212,000 / 52,000 = 61.8 days, the same as the total row. Each product is just the category's average inventory × 365, so the weighted sum has to land on total average inventory / total COGS × 365. The simple average of the five DIOs is 61.6 days: close here by coincidence, and it has no meaning as a company figure because it gives safety's 6,600 of COGS the same weight as electrical's 14,200.

Then tie the inputs: opening inventory of 8,400 and closing inventory of 9,200 to the balance sheets and the inventory valuation reports, and 52,000 to cost of goods sold in the income statement.

Calculating inventory days in Excel

Keep the days in a cell named Days, so the same sheet works for any period:

=AVERAGE(B2:C2)/D2*Days

Set Days to 365 for a year, 90 or 91 for a quarter (the actual days), and 28 to 31 for a month. The COGS in column D must cover exactly the same period. The AVERAGE function takes a range, so for a seasonal business put the 13 month-end balances for the year in columns B to N and average them all:

=AVERAGE(B2:N2)/O2*Days

with annual COGS in column O. For the total row, sum the inventory and COGS columns first and apply the same formula to the sums, =AVERAGE(B7:C7)/D7*365, never =AVERAGE(E2:E6). The check cell, which must equal it, is:

=SUMPRODUCT(D2:D6,E2:E6)/SUM(D2:D6)

Inventory days in the cash conversion cycle

DIO is one leg of the cash conversion cycle:

Cash conversion cycle = DSO + DIO − DPO

Working capital works it from one quarter-end balance sheet: DIO of 780,000 / 1,560,000 × 90 = 45.0 days on quarter-end inventory and quarterly cost of goods sold, with days sales outstanding of 45.9 and days payable outstanding of 36.0, a cycle of 54.9 days.

Every day of DIO is a day of cost of sales held as inventory. In the five-category example a day is 52,000 / 365 = 142.5 thousand. Bringing plumbing from 90.2 to 60 days would cut its average inventory to 8,900 × 60 / 365 = 1,463, release about $737,000 of cash, and take the total from 61.8 to 56.6 days.

What is a good number

There is no universal threshold. Electrical and safety products here turn in about 44 days, plumbing in 90, and a company figure of 61.8 averages them away. Compare each category with its own history, month by month, and with distributors that sell similar goods. Mark the seasonal peaks: inventory built before a selling season raises DIO for a reason. The inventory turnover ratio page works a sourced peer figure from a 10-K, and inventory aging by site shows which inventory sits behind a high number.

Where it goes wrong

• Revenue instead of COGS. Dividing by sales understates days by the gross margin.
• Averaging category DIOs. The total comes from summed inventory and summed COGS, not the average of the category days.
• Year-end inventory only. When the year-end is a seasonal low point, DIO looks short; average monthly balances.
• Mixed valuation. Inventory at selling price against COGS at cost, or standard cost against actual.
• Goods in transit and consignment. Included in one period and not the next, they move DIO with no change in the warehouse.

Inventory days by site and category from your own inventory files

Upload the inventory valuation and the COGS ledger and Covirage's tools compute inventory days per category and site, with the total checked as the COGS-weighted average; the external AI model explains which categories move it and never does the arithmetic. See Covirage for distributors. For what goes into the denominator, see cost of goods sold.