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:
```excel
=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:
```excel
=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
-
3
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.
-
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).
-
3
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).
-
3
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.
-
3
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).
-
3
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).
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
```excel
=SUMIFS(Revenue, SKU, A2)
```
Step 2: Calculate cumulative % of total revenue
```excel
=SUM($B$2:B2) / SUM($B$2:$B$500)
```
Step 3: Classify
```excel
=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)
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:
```excel
=SUMIFS(COGS, Month, ">=4", Month, "<=8") /
AVERAGE(IF(MONTH(Date)>=4, IF(MONTH(Date)<=8, Inventory_Value)))
```
(Array formula for in-season turnover)
Stop manually tracking inventory turnover. Get automated reports showing turnover rates, days of supply, slow movers, and reorder recommendations for every SKU.
Get Inventory AnalyticsWorks with Shopify, WooCommerce inventory data
Common Mistakes to Avoid
Learn from these common pitfalls to ensure success
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.
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.
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.
Ignoring seasonality in turnover calculations
Solution: Calculate in-season and off-season turnover separately. Annual average hides critical patterns for seasonal products.
How to Verify Your Numbers
Ensure accuracy with these verification steps
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 (<30 days supply) are flagged for reorder in next week
Slow movers (>90 days supply) are flagged for markdown/liquidation
Frequently Asked Questions
What's a good inventory turnover rate for e-commerce?
Can inventory turnover be too high?
How do I calculate turnover for a new product with no history?
Should I use units or dollars for inventory calculations?
What if my supplier has a 60-day lead time but I want 30 days supply?
Ready to Transform Your Reporting?
Start with a free report and see exactly what you'll get every time.