Inventory 19 min read Intermediate

Fashion Size & Color Performance Matrix in Excel: Stop Dead Inventory

You ordered 100 shirts: 40 Small, 30 Medium, 20 Large, 10 XL. Medium sold out, XL sits forever. Build a size/color matrix in Excel so you buy the right mix every time.

Platforms: Shopify WooCommerce
Fashion Size & Color Performance Matrix in Excel: Stop Dead Inventory

Why Fashion Sellers Drown in Dead Inventory

The classic mistake: You launch a new dress. Manufacturer's minimum: 100 units. You guess at sizes:
  • 20 XS (20%)
  • 30 S (30%)
  • 30 M (30%)
  • 20 L (20%)
What actually sells in first 90 days:
  • XS: 18 sold (90% sell-through) ✓
  • S: 30 sold (100% sell-through) - stockout!
  • M: 22 sold (73% sell-through)
  • L: 8 sold (40% sell-through) - dead stock
You ended with:
  • 50 units sold total
  • Lost sales: ~10 S you didn't have
  • Dead inventory: 12 L you'll markdown
  • Cash tied up: $240 in dead L sizes (at $20 COGS)
The fix: Historical size/color matrices tell you the ACTUAL demand curve before you place orders. This guide shows you how to build matrices that save you thousands in markdowns.

The Fashion-Specific Data You Need

Export from Shopify/WooCommerce:

Product-level data:
  • Style name
  • SKU (base SKU + variant)
  • Size (XS, S, M, L, XL, XXL)
  • Color
  • Units ordered (purchase order quantity)
  • Units sold (order line items)
  • Units remaining
  • Date first available
  • Date last sold

Calculate These Metrics:

1. Sell-Through Rate: 
Sell-Through % = (Units Sold / Units Ordered) × 100
2. Days to Sell Through: 
Days to Sell Through = Date Last Sold - Date First Available
3. Sell-Through Velocity: 
Units per Day = Units Sold / Days Available
4. Size Mix Actual vs Planned: 
Actual % = Units Sold by Size / Total Units Sold
Planned % = Units Ordered by Size / Total Units Ordered
Variance = Actual - Planned

Build Your Size/Color Performance Matrix

1

Export Product Variant Sales Data

Shopify: Products → Export → Include all variants. Ensure columns for: Title, Variant SKU, Option1 (Size), Option2 (Color), Variant Inventory, Variant Sales Count. Last 12 months for seasonal patterns.

Export Product Variant Sales Data
2

Clean and Normalize Size/Color Labels

Standardize inconsistent labels: 'Small' → 'S', 'small' → 'S', 'SM' → 'S'. Same for colors: 'Navy Blue' → 'Navy', 'navy' → 'Navy'. Use Find & Replace or formulas: =UPPER(LEFT(A2,1)).

Clean and Normalize Size/Color Labels
3

Calculate Sell-Through Rate per Variant

Add column: Sell_Through = (Units_Sold / Units_Ordered) × 100. This shows which size/color combos sold well (>80%) vs dead stock (<50%).

Calculate Sell-Through Rate per Variant
4

Create Size Performance Pivot Table

Pivot: Rows = Size, Values = SUM(Units_Sold), AVERAGE(Sell_Through_%), COUNT(Styles). Sort by units sold descending. This reveals your size curve: which sizes sell most.

Create Size Performance Pivot Table
5

Create Color Performance Pivot Table

Pivot: Rows = Color, Columns = Month (if seasonal trends), Values = SUM(Units_Sold), AVERAGE(Sell_Through_%). Sort by sell-through descending. Shows hot colors vs duds.

Create Color Performance Pivot Table
6

Build Size × Color Matrix

Pivot: Rows = Size, Columns = Color, Values = AVERAGE(Sell_Through_%). This 2D matrix shows which size+color combos are winners. Example: S+Black = 95%, L+Yellow = 12%.

Build Size × Color Matrix
7

Calculate Optimal Size Mix %

For each size, calculate: Optimal % = Units Sold of Size / Total Units Sold. This is your target size mix for future orders. Compare to what manufacturer offers (often flat 25% each).

Calculate Optimal Size Mix %
8

Add Conditional Formatting for Alerts

In matrix, format cells: Green if >80% sell-through (winners), Yellow if 50-80% (acceptable), Red if <50% (avoid this combo). Instantly see which variants to avoid.

Add Conditional Formatting for Alerts
9

Create Buy Plan Template

When placing new order: Total units needed × Optimal size % = Units per size. Example: 200 units × 35% S = 70 S. Rounds to nearest case pack size if manufacturer requires it.

Create Buy Plan Template

Typical Size Curves by Product Category

Women's Tops (T-shirts, Blouses, Sweaters)

Typical size mix:
  • XS: 10%
  • S: 30%
  • M: 35%
  • L: 20%
  • XL: 5%
Peak: Medium

Women's Dresses

Typical size mix:
  • XS: 15%
  • S: 35%
  • M: 30%
  • L: 15%
  • XL: 5%
Peak: Small (dresses run larger than tops)

Men's T-shirts

Typical size mix:
  • S: 15%
  • M: 30%
  • L: 35%
  • XL: 15%
  • XXL: 5%
Peak: Large

Men's Button-Downs

Typical size mix:
  • S: 10%
  • M: 25%
  • L: 35%
  • XL: 20%
  • XXL: 10%
Peak: Large (more XL/XXL demand than casual wear)

Kids Clothing

Varies dramatically by age range:
  • Toddler (2-4T): Peaks at 3T
  • Kids (5-12): Peaks at 8/10
  • Youth (13-16): Peaks at 14/16
Critical: Kids sizes sell through FAST at peak age, slow at edges.

Color Performance: The Seasonal Matrix

Year-Round Safe Colors (80%+ sell-through)

1. Black: Universal, all seasons 2. White/Cream: Summer peak, decent year-round 3. Navy: Professional, stable demand 4. Gray/Heather Gray: Athleisure, year-round 5. Denim Blue: Casual, stable

Seasonal Colors (High sell-through in season, dead outside)

Spring (Mar-May):
  • Pastels: Light pink, mint, lavender
  • Coral, peach
  • Light florals
Summer (Jun-Aug):
  • Bright colors: Yellow, orange, bright pink
  • Turquoise, aqua
  • Bold florals
Fall (Sep-Nov):
  • Earth tones: Rust, burgundy, olive
  • Mustard, burnt orange
  • Browns and caramels
Winter (Dec-Feb):
  • Jewel tones: Emerald, ruby, sapphire
  • Deep burgundy, forest green
  • Metallics (for holidays)

Risky Colors (Often <50% sell-through)

  • Neon colors (except specific athletic brands)
  • Unusual colors (chartreuse, magenta)
  • Trendy colors past their peak (millennial pink in 2025)

Excel Formula to Flag Seasonal Colors:

=IF(AND(Color="Pastel Pink", MONTH(Today())>=3, MONTH(Today())<=5), "Buy Now", 
   IF(AND(Color="Pastel Pink", MONTH(Today())>=9), "High Risk", 
   "Neutral"))
Product Type Peak Size Size Spread Color Strategy Seasonal?
Women's Dresses S Concentrated (70% S/M) Safe colors + 1 seasonal High
Women's Tops M Balanced (65% S/M/L) Mix safe + seasonal Medium
Men's Casual L Balanced (70% M/L/XL) Safe colors dominant Low
Men's Formal L Wide spread (30% XL/XXL) Conservative only None
Kids Clothing Varies Age-dependent Bright colors OK High
Athleisure M Balanced Heather/Black + accent Low

Advanced Analysis: Size × Color × Style Performance

The 3D Matrix Problem:

Size performance varies by: 1. Size (S, M, L) 2. Color (Black, Navy, Red) 3. Style (Fitted vs Relaxed, V-neck vs Crew) Example patterns:
  • Fitted styles: Peak at S (60% of sales)
  • Relaxed styles: Peak at M/L (70% of sales)
  • Black fitted: 95% sell-through
  • Yellow relaxed: 30% sell-through

Excel Approach: Segmented Matrices

Create separate size/color matrices for:
  • Fitted styles only
  • Relaxed styles only
  • Dress shirts vs casual shirts
  • Each product category
Why: One "average" matrix hides critical patterns.

Real Example: Athletic Leggings

Overall size mix looks like:
  • S: 30%, M: 35%, L: 25%, XL: 10%
But when you segment by style: High-waisted leggings:
  • S: 45%, M: 35%, L: 15%, XL: 5%
Standard-rise leggings:
  • S: 20%, M: 35%, L: 30%, XL: 15%
Using overall average would create dead stock in both segments.

The Buy Planning Spreadsheet: From Matrix to Purchase Order

Step 1: Determine Total Units Needed

Methods: A. Historical velocity: 
Monthly_Velocity = Units_Sold_Last_3_Months / 3
Reorder_Qty = Monthly_Velocity × Months_of_Cover_Desired
B. Manufacturer minimums:
  • You need 100 minimum to place order
  • Or: 200 units to hit price break tier
C. Budget-constrained: 
Max_Units = Available_Budget / Unit_COGS

Step 2: Apply Optimal Size Mix

Formula for each size: 
=Total_Units × Size_Optimal_Percent
Example:
  • Total order: 200 units
  • S optimal: 35%
  • S order qty: 200 × 0.35 = 70 units

Step 3: Round to Case Pack Requirements

Manufacturers often require multiples of:
  • 6 units per size/color combo
  • 12 units per size
  • 24 units per inner carton
Formula: 
=CEILING(Calculated_Qty, Case_Pack_Size)
Example: 70 S calculated, 12-pack required → 72 S ordered (6 packs)

Step 4: Allocate Colors within Each Size

Use color performance matrix: If ordering 72 S, and color matrix shows:
  • Black: 40% of S sales
  • Navy: 30% of S sales
  • Gray: 20% of S sales
  • Red: 10% of S sales
Allocation:
  • Black S: 72 × 0.40 = 29 → round to 30
  • Navy S: 72 × 0.30 = 22 → round to 24 (case pack)
  • Gray S: 72 × 0.20 = 14 → round to 12 (don't over-order risky)
  • Red S: 72 × 0.10 = 7 → round to 6 (minimum for testing)
Total: 72 S (matches size allocation)

The 80/20 Rule for Fashion

In fashion, 20% of size/color combos drive 80% of sales. Black/Navy in your top 2 sizes = safe winners. Everything else is risk. Use matrix to minimize the risky 80% of variants.

Markdown Decision Matrix: When to Liquidate

30-60-90 Day Sell-Through Benchmarks:

30 days:
  • >50% sell-through: Winner, reorder
  • 30-50%: Acceptable, monitor
  • <30%: Risky, no reorder
60 days:
  • >70% sell-through: Good product
  • 50-70%: Acceptable
  • <50%: Dead stock, markdown soon
90 days:
  • >80% sell-through: Success
  • 60-80%: Acceptable
  • <60%: Markdown immediately

Excel Markdown Calculator:

Input:
  • Units remaining
  • Original retail price
  • COGS per unit
  • Target markdown % (25%, 50%, 75%)
Calculate: 
Markdown_Price = Original_Price × (1 - Markdown_%)
Revenue_at_Markdown = Units_Remaining × Markdown_Price
Total_Cost = Units_Remaining × COGS
Profit_Loss = Revenue_at_Markdown - Total_Cost
Decision Matrix:
Sell-Through Days Available Action Markdown %
<40% 90+ Clearance 50-75%
40-60% 90+ Markdown 25-50%
60-80% 90+ Hold 0-25%
>80% Any Reorder 0%

Common Mistakes to Avoid

Mistake: Using manufacturer's suggested size mix instead of your actual sell-through data
Solution: Manufacturers suggest flat 25% each size for their convenience. Your customers don't buy that way. Use YOUR historical size curve, not theirs.
Mistake: Ordering seasonal colors outside their season
Solution: Pastels ordered in October will sit until March. Order seasonal colors 2-3 months before their peak season, liquidate at end of season.
Mistake: Not segmenting size curves by product category
Solution: Dresses, tops, pants all have different size curves. One 'overall' mix creates dead stock in all categories. Segment by category.
Mistake: Ignoring case pack requirements in buy plan
Solution: If calculated 37 M but case packs are 12, you must order 36 or 48. Build rounding into your Excel formulas: =CEILING(qty, 12).

Verification Checklist

  • Your size mix sums to 100% (no rounding errors creating 103% or 97%)
  • Top 2 sizes represent 60-70% of total order (concentrated demand is normal)
  • Safe colors (black, navy, white) are 50%+ of color mix (risk management)
  • Your sell-through rate for past orders matches benchmarks: 80%+ at 90 days
  • When you apply optimal size mix to next order, units per size change from flat distribution

Frequently Asked Questions

Use industry benchmarks for your product category as starting point. After first order, track sell-through for 60 days and adjust next order. You'll have real data in 2-3 order cycles.
Treat as separate size curve. Plus sizes often have DIFFERENT optimal mix than standard sizes. Build separate matrix if you offer both.
Same principle: track sell-through separately. Petite/tall typically 10-15% of total volume. Don't over-order based on enthusiasm - data wins.
Yes, but don't eliminate entirely. Use 80/20: 80% of order in safe colors, 20% in seasonal/trendy for testing. Cap risk exposure.
Quarterly minimum. Annually for stable categories. After any major fit change or new body type targeting (e.g., maternity, plus size expansion).