Home›Blog› The 7 Inventory KPIs Every Manager Should Track in Excel

Most inventory teams track quantity on hand and not much else. But the managers who prevent stockouts, reduce carrying costs, and keep operations running smoothly track a handful of calculated metrics that tell a much richer story. Here are the 7 that matter most — and the Excel formulas to calculate them.

1. Inventory Turnover Ratio

What it tells you: How many times you sell through your entire inventory per year. Higher is generally better — it means your capital isn't sitting idle on shelves.

=COGS / Average Inventory Value =SUM(AnnualCOGS) / AVERAGE(BeginningInventory, EndingInventory)

Target: Depends heavily on industry. Grocery = 12–15x. Manufacturing = 4–6x. Specialty retail = 2–4x.

2. Days of Inventory Outstanding (DIO)

What it tells you: How many days of sales your current stock can cover. Essential for planning orders and avoiding stockouts.

=(Qty on Hand / Average Daily Sales) =C2 / (AnnualSales/365)

Target: Enough to cover your lead time plus safety stock. Typically 30–60 days for most businesses.

3. Stockout Rate

What it tells you: The percentage of time items are out of stock. Every stockout is a lost sale and a damaged customer relationship.

=(Stockout Incidents / Total Order Lines) * 100 =COUNTIF(StatusCol,"Out of Stock") / COUNTA(SKUCol) * 100

Target: Below 2% for most operations.

4. Carrying Cost of Inventory

What it tells you: The true cost of holding inventory — warehousing, insurance, obsolescence, and capital cost. Usually 20–30% of inventory value per year.

= Inventory Value * Carrying Cost Rate = SUMPRODUCT(QtyCol, UnitCostCol) * 0.25

Target: Minimize by reducing slow-moving stock and optimizing reorder quantities.

5. Fill Rate

What it tells you: The percentage of orders you can fulfill immediately from available stock.

=(Orders Fulfilled from Stock / Total Orders) * 100 =(TotalOrders - Backorders) / TotalOrders * 100

Target: 95%+ for most operations. High-service businesses target 98%+.

6. Inventory Accuracy

What it tells you: How closely your system inventory matches your physical inventory. Low accuracy leads to phantom stockouts and ordering mistakes.

=(Items with Correct Count / Total Items Counted) * 100

Target: 95%+ after cycle counts. World-class operations achieve 99%+.

7. Slow-Moving Inventory %

What it tells you: The percentage of your inventory that hasn't moved in 90+ days. Slow-moving stock ties up cash and warehouse space.

=COUNTIF(AgingBucket,"90+ Days") / COUNTA(SKUCol) * 100

Target: Below 10% of total SKUs. Review and act on slow movers quarterly.

💡 Don't track all 7 at once if you're starting out. Pick the 2-3 that address your biggest current pain point and build from there.

FAQ

How often should I calculate these KPIs? ▼
Days of inventory and stockout rate should be checked daily or weekly. Turnover ratio and carrying cost are typically reviewed monthly. Inventory accuracy is measured after each cycle count.
Where do I get the data for these formulas? ▼
Most of the inputs come from three sources: your inventory system (quantities), your accounting system (COGS, costs), and your order management system (orders, backorders). If you don't have a system, a well-maintained Excel tracker is your starting point.

📊 Get the Operations KPI Report Template

Free download — no sign-up required.

⬇ Download Free Template

Related Articles

Dashboard

Build a Warehouse KPI Dashboard from Scratch

Inventory

How to Build a Low Stock Alert in Excel

Inventory

How to Calculate Reorder Points for Every SKU