Inventory 16 min read Beginner

Inventory Turnover & Days of Supply in Excel: Know When to Restock

Your $50K of inventory is sitting for 180 days before selling. That's $50K you can't use for 6 months. Calculate turnover rate and days of supply in Excel to free up cash and prevent stockouts.

Platforms: Shopify WooCommerce
Inventory Turnover & Days of Supply in Excel: Know When to Restock

The $50K Cash Flow Problem

Your inventory situation:
  • $50,000 invested in inventory
  • Inventory sits 180 days before selling
  • Annual sales: $100,000
What this means:
  • Your cash is locked up for 6 months
  • You can only "turn" that $50K twice per year
  • Inventory turnover: 2× (very slow)
If you improved turnover to 30 days:
  • Same $100K annual sales
  • Only $8,333 inventory needed at any time
  • You free up $41,667 in cash
  • That cash can: pay down debt, buy better products, fund marketing
This is why turnover matters more than total sales. This guide teaches you to calculate, track, and optimize inventory turnover and days of supply.

The Two Key Metrics

Metric 1: Inventory Turnover Rate

What it measures: How many times per year you sell and replace your entire inventory. Formula: 
Inventory Turnover = Cost of Goods Sold (COGS) / Average Inventory Value
Example:
  • Annual COGS: $120,000
  • Average inventory value: $20,000
  • Turnover: $120,000 / $20,000 = 6×
You sell and replace inventory 6 times per year (every 60 days). Excel formula: 
=SUMIFS(COGS, Date, ">=1/1/2024", Date, "<=12/31/2024") / 
 AVERAGE(Inventory_Value_Monthly_Snapshots)

Metric 2: Days of Supply (Inventory Days)

What it measures: How many days until you run out of stock at current sales rate. Formula: 
Days of Supply = (Current Inventory Units / Average Daily Sales)
Or from turnover: 
Days of Supply = 365 / Inventory Turnover Rate
Example:
  • Current inventory: 200 units
  • Average daily sales: 5 units/day
  • Days of supply: 200 / 5 = 40 days
Excel formula: 
=Current_Units / (SUM(Units_Sold_Last_90_Days) / 90)
At current rate, you'll stock out in 40 days.

Calculate Turnover & Days of Supply in Excel

1

Export Inventory Snapshot

From Shopify/WooCommerce: Export current inventory with SKU, Product Name, Units On Hand, Cost per Unit. Calculate total inventory value: Units × Cost. This is your starting point.

Export Inventory Snapshot
2

Export Sales Velocity Data (90 Days)

Export orders from last 90 days: Date, SKU, Quantity Sold. Group by SKU and sum quantities. This gives units sold per SKU in 90-day period.

Export Sales Velocity Data (90 Days)
3

Calculate Average Daily Sales per SKU

Formula: =SUMIFS(Quantity, SKU, A2) / 90. This gives average units sold per day for each SKU. Some SKUs: 0.5/day (slow), others: 10/day (fast).

Calculate Average Daily Sales per SKU
4

Calculate Days of Supply per SKU

Formula: =Units_On_Hand / Avg_Daily_Sales. Shows days until stockout. Example: 50 units / 2/day = 25 days supply. Flag <30 days (reorder soon).

Calculate Days of Supply per SKU
5

Calculate Inventory Turnover per SKU

Formula: =365 / Days_Of_Supply or =(Units_Sold_Last_Year × COGS) / Avg_Inventory_Value. Shows how many times you turn each SKU annually.

Calculate Inventory Turnover per SKU
6

Flag Slow Movers & Fast Movers

Conditional formatting: Red if Turnover <4× or Days Supply >90 (slow movers). Yellow if Turnover >15× or Days Supply <15 (risk stockout). Green if 4-15× (healthy).

Flag Slow Movers & Fast Movers
7

Create Turnover Summary Dashboard

Pivot table: Product Category in Rows, AVG(Turnover) in Values. Shows which categories move fast vs slow. Add chart showing top 20 SKUs by $ tied up (slow movers costing most).

Create Turnover Summary Dashboard
Turnover Rate Days of Supply Classification Action Cash Impact
<2× >180 days Dead Stock Liquidate immediately High cash lockup
2-4× 90-180 days Slow Mover Reduce quantity, markdown Medium cash lockup
4-8× 45-90 days Moderate Maintain current levels Balanced
8-12× 30-45 days Healthy Optimal turnover Good cash flow
12-15× 24-30 days Fast Mover Increase safety stock Excellent turnover
>15× <24 days Very Fast Risk of stockout Monitor closely

Turnover Benchmarks by Industry

E-commerce/Retail Categories:

Fashion/Apparel:
  • Target: 8-12× turnover (30-45 days supply)
  • Fast fashion: 12-20× (seasonal urgency)
  • Luxury: 4-6× (slower sell-through acceptable)
Electronics/Gadgets:
  • Target: 10-15× turnover (24-36 days supply)
  • Why: Fast obsolescence, price drops
  • Slow movers risk becoming unsellable
Home Goods/Furniture:
  • Target: 4-8× turnover (45-90 days supply)
  • Why: Bulky, expensive, slower decision cycle
  • Higher holding costs justify slower turns
Beauty/Cosmetics:
  • Target: 8-12× turnover (30-45 days supply)
  • Why: Expiration dates, trend cycles
  • Dead stock becomes literally expired
Grocery/Food:
  • Target: 15-30× turnover (12-24 days supply)
  • Why: Perishable, expiration dates
  • <10× = you're throwing away expired product
Jewelry/Luxury:
  • Target: 2-4× turnover (90-180 days supply)
  • Why: High value, slow decision, unique pieces
  • Capital-intensive but acceptable

General Rules:

  • High ticket items: Slower turnover acceptable (furniture, jewelry)
  • Low margin items: Need fast turnover to make profit
  • Perishable/trendy items: Fast turnover critical
  • Commodity items: Fast turnover, minimal differentiation

The ABC Analysis: Focus on What Matters

Classify Inventory into 3 Tiers:

A Items (20% of SKUs, 80% of revenue):
  • Your best sellers
  • Can't afford stockouts
  • Target: 8-12× turnover, 30-45 days supply
  • Action: Monitor daily, maintain safety stock
B Items (30% of SKUs, 15% of revenue):
  • Moderate sellers
  • Important but not critical
  • Target: 6-10× turnover, 36-60 days supply
  • Action: Monitor weekly, lean inventory
C Items (50% of SKUs, 5% of revenue):
  • Slow sellers
  • Tie up cash, low contribution
  • Target: 4-6× turnover OR liquidate
  • Action: Reduce quantities, consider discontinuing

Excel ABC Classification:

Step 1: Sort SKUs by annual revenue 
=SUMIFS(Revenue, SKU, A2)
Step 2: Calculate cumulative % of total revenue 
=SUM($B$2:B2) / SUM($B$2:$B$500)
Step 3: Classify 
=IF(Cumulative_Percent <= 0.80, "A",
   IF(Cumulative_Percent <= 0.95, "B", "C"))
Step 4: Set turnover targets by class
  • A items: 8-12×
  • B items: 6-10×
  • C items: 4-6× or liquidate

Cash Flow Impact: The Real Cost of Slow Turnover

Example Business:

Current state:
  • $100K invested in inventory
  • 6× annual turnover (60 days supply)
  • $600K annual revenue
  • 30% gross margin = $180K gross profit
If you improve to 12× turnover (30 days supply):
  • Only $50K inventory needed (freed up $50K cash)
  • Same $600K revenue
  • Same $180K gross profit
  • But: $50K cash available for other uses

What Can You Do with $50K Freed Cash?

Option A: Pay down debt
  • $50K at 8% interest = $4K/year saved
Option B: Buy better-margin products
  • $50K in higher-margin items at 40% margin instead of 30%
  • Additional gross profit: $50K × 0.10 = $5K/year
Option C: Fund marketing
  • $50K marketing spend at 3:1 ROAS = $150K additional revenue
  • At 30% margin = $45K additional gross profit
Improving turnover from 6× to 12× is like getting a $50K interest-free loan.

The Holding Cost Formula:

Annual_Holding_Cost = Inventory_Value × Holding_Cost_Rate

Where Holding_Cost_Rate typically = 20-30% per year:

    
  
  • 10%: Cost of capital (what you could earn elsewhere)
    • 
  
  • 5%: Warehouse/storage
    • 
  
  • 3%: Insurance
    • 
  
  • 2%: Shrinkage/damage
Example:
  • $100K inventory
  • 25% holding cost rate
  • Annual holding cost: $25K
At 6× turnover (60 days supply):
  • Average inventory: $100K
  • Holding cost: $25K
  • Per-unit holding cost: $25K / (6 turns × inventory) = HIGH
At 12× turnover (30 days supply):
  • Average inventory: $50K
  • Holding cost: $12.5K
  • Savings: $12.5K/year

Action Plan: Improve Turnover & Free Up Cash

Step 1: Identify Slow Movers (<4× turnover)

Export inventory with turnover rates, sort by days of supply descending. Focus on:
  • Items with >120 days supply
  • Items with high $ value tied up (Units × COGS)
Example findings:
  • 15 SKUs with >120 days supply
  • Total $ tied up: $28,000 (28% of inventory!)

Step 2: Liquidate or Markdown Slow Movers

Decision matrix:
Days Supply $ Value Action Timeline
>180 days Any 50-75% markdown, liquidate Immediate
120-180 days >$1K 25-50% markdown 30 days
90-120 days >$2K 10-25% markdown 60 days
60-90 days >$5K Monitor, don't reorder 90 days

Step 3: Reduce Order Quantities for Moderate Slow Movers

Example:
  • SKU currently: 100 units on hand, 60 days supply
  • Target: 30 days supply
  • Average daily sales: 1.67 units
  • New reorder quantity: 1.67 × 30 = 50 units (instead of 100)

Step 4: Increase Safety Stock for Fast Movers

Example:
  • SKU currently: 50 units on hand, 10 days supply
  • Risk of stockout
  • Average daily sales: 5 units
  • New target: 30 days supply = 150 units
  • Add 100 units to next order

Step 5: Negotiate Better Terms with Suppliers

Options:
  • Smaller minimum order quantities (MOQ)
  • Shorter lead times (reduce safety stock needed)
  • Consignment (they hold inventory, you pay when sold)
  • Just-in-time delivery (weekly instead of monthly)

The Stockout vs Overstock Tightrope

Too little inventory = stockouts = lost sales. Too much inventory = cash tied up = can't grow. Target: 30-45 days supply for most SKUs. Adjust based on lead time and sales variability.

Seasonal Adjustment: Fashion/Holiday Products

The Seasonal Turnover Problem:

Example: Holiday decor
  • Oct-Dec: 30× turnover (flying off shelves)
  • Jan-Sep: 0.5× turnover (dead)
  • Annual average: 8× turnover (looks healthy but misleading)

Seasonal Turnover Formula:

Calculate turnover separately for:
  • In-season months
  • Off-season months
Example: Summer apparel
  • Apr-Aug (5 months): 15× annualized turnover = 24 days supply
  • Sep-Mar (7 months): 3× annualized turnover = 120 days supply
Strategy:
  • Stock up in February for March-August demand
  • Liquidate by September (70% sell-through target)
  • Minimal inventory September-February

Excel Seasonal Turnover:

=SUMIFS(COGS, Month, ">=4", Month, "<=8") / 
 AVERAGE(IF(MONTH(Date)>=4, IF(MONTH(Date)<=8, Inventory_Value)))
(Array formula for in-season turnover)

Common Mistakes to Avoid

Mistake: Using beginning inventory instead of average inventory in turnover calculation
Solution: Turnover = COGS / AVERAGE inventory. Use average of 12 monthly snapshots, or (Beginning + Ending) / 2 minimum. Beginning only overstates turnover.
Mistake: Calculating turnover on revenue instead of COGS
Solution: Use COGS (what you paid), not revenue (what customer paid). Revenue overstates turnover by your margin amount. Industry standard is COGS.
Mistake: Treating all SKUs the same (one target turnover rate)
Solution: A items need faster turnover than C items. Fashion needs faster than furniture. Set category-specific targets, not one-size-fits-all.
Mistake: Ignoring seasonality in turnover calculations
Solution: Calculate in-season and off-season turnover separately. Annual average hides critical patterns for seasonal products.

Verification Checklist

  • Overall turnover rate = Total COGS / Average Total Inventory Value matches your calculation
  • Days of supply × Daily sales rate = Units on hand (validates formula)
  • Sum of all SKU inventory values = Your total inventory value (no missing SKUs)
  • Fast movers (&lt;30 days supply) are flagged for reorder in next week
  • Slow movers (&gt;90 days supply) are flagged for markdown/liquidation

Frequently Asked Questions

6-12× annually (30-60 days supply) for most categories. Fashion: 8-12×. Electronics: 10-15×. Home goods: 4-8×. Depends on margin, perishability, and capital constraints.
Yes! &gt;15× turnover (&lt;24 days supply) means you're constantly risking stockouts. You lose sales when out of stock. Balance is key: fast enough to free cash, slow enough to avoid stockouts.
Use comparable product's turnover as proxy. After 30 days, calculate actual rate. After 90 days, you have reliable data. Start conservative (45-60 days supply) until proven.
Dollars for overall turnover rate (COGS / Inventory Value). Units for days of supply per SKU. Both have uses - dollars shows cash impact, units shows stockout risk.
You need 90 days worth of inventory: 30 days to sell + 60 days to reorder. Longer lead times force higher inventory. Negotiate shorter lead times or find faster suppliers.