Build an Inventory Turnover and Days on Hand Report

Inventory is cash sitting on shelves. Too little and orders cannot be fulfilled; too much and money is tied up, storage costs rise and stock goes out of date. Two measures summarise the balance. Inventory turnover — cost of goods sold over a year divided by the average value of stock held — says how many times the stock was sold and replaced; higher means leaner. Days on hand — how many days the current stock would last at the current rate of use — says how long today's stock will sit before it is consumed. Both are simple ratios, and both are usually wrong in hand-built spreadsheets because they use a single year-end stock figure instead of an average, or annual usage instead of recent usage. This page computes them properly per item from monthly stock snapshots and usage, classifies items as fast, slow-moving or dead, values the cash tied up in each class, summarises by category against targets and writes the report to Excel. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The workbook holds an item list with category and unit cost, twelve months of usage per item (units issued to sales or production) and month-end stock quantities per item, October 2025 to September 2026. Valuing everything at a single standard unit cost keeps the example clear; with costed movements the same calculations apply to values directly.

Step 1 — Annual COGS and average inventory per item

import pandas as pd

items = pd.read_excel("inventory.xlsx", sheet_name="Items").set_index("SKU")
usage = pd.read_excel("inventory.xlsx", sheet_name="Usage")
stock = pd.read_excel("inventory.xlsx", sheet_name="Month-end stock")

use = usage.pivot_table(index="SKU", columns="Month", values="Qty used", aggfunc="sum")
onhand = stock.pivot_table(index="SKU", columns="Month", values="Qty on hand", aggfunc="sum")
r = items.copy()
r["Annual COGS"] = use.sum(axis=1) * r["Unit cost"]
r["Average inventory"] = onhand.mean(axis=1) * r["Unit cost"]
r["Stock now"] = onhand.iloc[:, -1] * r["Unit cost"]
r["Turnover"] = (r["Annual COGS"] / r["Average inventory"]).where(r["Average inventory"] > 0)
print(r[["Category", "Annual COGS", "Average inventory", "Stock now", "Turnover"]].round(1).to_string())

Turnover uses the average of the twelve month-end values rather than the year-end figure, because stock levels swing — an item replenished just before the year end would look slow-moving on a single snapshot. Annual cost of goods sold is a year's usage at cost. The result reads directly: most items turn over four to five times a year, roughly every ten to twelve weeks, while the bracket K-510 turns over barely a tenth of a time and two items — the tool T-610 and the packaging P-710 — not at all: they were not used once in twelve months.

Step 2 — Days on hand from recent usage

Turnover looks backwards over the whole year. Days on hand looks forward: how long will today's stock last at the rate it is being used now?

recent_daily = use.iloc[:, -3:].sum(axis=1) / 91                     # last three months, about 91 days
r["Daily usage (units)"] = recent_daily
r["Days on hand"] = (onhand.iloc[:, -1] / recent_daily).where(recent_daily > 0)
months_since_use = use.apply(lambda row: (row[::-1] > 0).values.argmax() if (row > 0).any() else len(row), axis=1)
r["Months since last use"] = months_since_use

def stock_class(x):
    if x["Stock now"] == 0:
        return "No stock"
    if x["Months since last use"] >= 6:
        return "Dead"
    if x["Days on hand"] > 180:
        return "Slow-moving"
    return "Healthy" if x["Days on hand"] >= 14 else "Low cover"

r["Class"] = r.apply(stock_class, axis=1)
print(r[["Stock now", "Days on hand", "Months since last use", "Class"]].round(0).to_string())

Using the last three months' usage, rather than the year's, makes days on hand respond to change: an item whose demand has picked up recently shows fewer days of cover than its annual average would suggest. Items are then classified with simple, explainable rules: no use for six months or more is dead stock, more than 180 days of cover is slow-moving, under two weeks is low cover and worth checking against lead times, and the rest is healthy. K-510 is an interesting case — unused for nine months and then used again — so it is no longer dead, but at its recent rate of use the stock on hand would last about 1,400 days, nearly four years, which makes it the clearest slow-moving item. The two items never used are dead.

Step 3 — Cash tied up, by class and category

by_class = r.groupby("Class")["Stock now"].agg(["count", "sum"]).rename(columns={"count": "Items", "sum": "Value"})
by_class["Share"] = by_class["Value"] / by_class["Value"].sum()
print(by_class.round(2).to_string())

TARGET_TURNS = {"Fasteners": 6, "Brackets": 4, "Tools": 2, "Packaging": 8}
cat = r.groupby("Category").agg(COGS=("Annual COGS", "sum"), Avg_inventory=("Average inventory", "sum"), Stock_now=("Stock now", "sum"))
cat["Turnover"] = cat["COGS"] / cat["Avg_inventory"]
cat["Target"] = cat.index.map(TARGET_TURNS)
cat["Excess vs target"] = (cat["Avg_inventory"] - cat["COGS"] / cat["Target"]).clip(lower=0)
print(cat.round(1).to_string())

Valuing stock by class turns ratios into money: how much of today's stock value is dead or slow-moving, and therefore a candidate for a write-down, a clearance sale or simply not reordering. The category view compares actual turnover with a target per category — packaging should turn fast, tools slowly — and converts the gap into money: the stock a category would hold if it met its target turnover on the same cost of sales, subtracted from what it actually holds on average, is the cash that better stock control would release. That number is what makes an inventory review worth a manager's time. Here dead and slow-moving stock make up almost 30% of today's stock value, about 8,700, and packaging is furthest from its target, turning 2.7 times against a target of 8 — roughly 5,800 more stock than the target implies, much of it in the unused P-710. Tools already beat their target and need no action.

Reordering logic for the healthy and low-cover items is covered in Build an Inventory Reorder Report.

Step 4 — Write the report

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

path = "inventory_turnover_2026-09.xlsx"
cols = ["Category", "Unit cost", "Annual COGS", "Average inventory", "Stock now", "Turnover", "Days on hand", "Months since last use", "Class"]
with pd.ExcelWriter(path, engine="openpyxl") as xw:
    r[cols].sort_values(["Class", "Stock now"], ascending=[True, False]).to_excel(xw, sheet_name="Items")
    by_class.to_excel(xw, sheet_name="By class")
    cat.to_excel(xw, sheet_name="By category")
wb = load_workbook(path)
ws = wb["Items"]
fills = {"Dead": "FFC7CE", "Slow-moving": "FFEB9C", "Low cover": "DDEBF7"}
class_col = [c.value for c in ws[1]].index("Class") + 1
for row in ws.iter_rows(min_row=2):
    colour = fills.get(row[class_col - 1].value)
    if colour:
        for cell in row:
            cell.fill = PatternFill("solid", fgColor=colour)
    for cell in row[2:8]:
        cell.number_format = "#,##0.0" if cell.column in (7, 8) else "#,##0"
wb.save(path)
print(wb.sheetnames)

The item sheet is sorted by class and then by value, so the dead and slow-moving stock with the most money in it is at the top of its section, shaded by class. The class and category sheets carry the money summaries. Run monthly, the report also shows movement between classes — an item drifting from healthy to slow-moving is cheaper to deal with than one that has already become dead.

Getting the inputs right

Two details make the difference between a turnover report people trust and one they argue with. First, value usage and stock on the same basis: if stock is valued at standard cost, cost of goods sold must be too, or the ratio mixes two price levels. Second, decide what counts as usage. Sales to customers obviously do; transfers between warehouses do not, because the item has not left the business; scrap and write-offs arguably do not either, because they would flatter the turnover of items that are being thrown away. Record the definition on the report. When stock levels are only available at year end, an average of opening and closing stock is a poor but common substitute; twelve monthly snapshots, as here, are much better and usually easy to obtain from the stock system's month-end valuation report.

Common pitfalls

• Year-end stock instead of an average. Use monthly snapshots for turnover.
• Annual usage for days on hand. Use recent usage so cover reflects current demand.
• Inconsistent valuation. Value COGS and stock on the same cost basis.
• Counting transfers as usage. They inflate turnover without selling anything.
• Ratios without money. Value the slow and dead stock and the excess against target.

Conclusion

Compute inventory turnover per item as a year's cost of goods sold over the average of monthly stock values, and days on hand as current stock over recent daily usage. Classify items as dead, slow-moving, low cover or healthy with simple rules, value the stock in each class, compare category turnover with targets to find the cash tied up, and write an item sheet sorted by class and value with category and class summaries.

Frequently asked questions

How do I calculate inventory turnover in pandas? Divide a year's cost of goods sold by the average inventory value, using the mean of the monthly stock values rather than a single year-end figure.

How do I calculate days on hand? Divide current stock quantity by recent average daily usage, for example the last three months' usage divided by about 91 days.

What counts as dead stock? A common rule is stock with no usage for six months or more; choose a period that fits the business and apply it consistently.

How do I show how much cash is tied up in excess stock? Compare average inventory with the inventory a target turnover would imply, cost of goods sold divided by the target, and report the positive difference.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build an Inventory Reorder Report — what to order next.
• Calculate Weighted Average Cost Inventory Valuation — valuing the stock.
• Run an ABC-XYZ Inventory Classification — classifying by value and variability.