👗
Fashion Retail Analytics
“RM 24,000 trapped in unsold inventory — and it was all the same 2 sizes. The data showed exactly which to stop ordering.”
Sell-Through RateDead Stock DetectionMarkdown ImpactInventory TurnoverSize-Level Analysis
450
SKUs Analysed
18
Dead Stock SKUs
RM 24k
Trapped Value
32%
Discount Revenue
-8.9%
Margin Erosion
1
Step 1 — Data Extraction
450 active SKUs were exported from the inventory management system and POS covering Women, Men, and Accessories across Physical Store and Online channels.
Raw export — 10 sample SKU records
| SKU | Category | Sub | Size | Channel | Units In | Units Sold | Orig Price | Discount |
|---|---|---|---|---|---|---|---|---|
| SKU-1000 | Women | Dress | M | Physical | 120 | 96 | RM 189 | 0% |
| SKU-1001 | Women | Dress | XS | Online | 80 | 9 | RM 189 | 0% |
| SKU-1002 | Men | Shirt | L | Physical | 150 | 118 | RM 145 | 0% |
| SKU-1003 | Men | Shirt | XXL | Online | 95 | 16 | RM 145 | 30% |
| SKU-1004 | Accessories | Bag | M | Physical | 60 | 48 | RM 220 | 0% |
| SKU-1005 | Women | Jeans | XS | Online | 75 | 8 | RM 210 | 40% |
| SKU-1006 | Men | Jacket | L | Physical | 88 | 71 | RM 340 | 0% |
| SKU-1007 | Accessories | Watch | S | Online | 45 | 12 | RM 480 | 25% |
| SKU-1008 | Women | Blouse | M | Physical | 110 | 87 | RM 128 | 0% |
| SKU-1009 | Men | Polo | XXL | Physical | 90 | 14 | RM 118 | 40% |
2
Step 2 — Data Cleaning
Issues found and resolved
| Issue | Count | Action | Result |
|---|---|---|---|
| Duplicate SKU entries | 12 | Removed — kept latest | 438 clean records |
| Units Sold > Units Received | 4 | Capped at Units Received | Corrected |
| Blank discount column | 89 | Set to 0% — confirmed full-price | Imputed |
| Negative revenue records | 2 | Removed — returns error | Dropped |
| Blank Channel field | 15 | Set to Physical Store | Imputed |
3
Step 3 — Transform: VLOOKUP + Calculated Fields + Pivot
VLOOKUP joined the COGS reference table to each SKU. Four retail metrics were calculated per row. A Pivot Table grouped by Size x Category to surface the sell-through pattern.
Calculated fields added
| Metric | Formula | Purpose |
|---|---|---|
| Sell-Through Rate | Units Sold / Units Received | How much stock actually sold |
| Dead Stock Flag | IF(Sell-Through < 20%, 1, 0) | Flags non-moving inventory |
| Actual Revenue | Units Sold x Sell Price | Revenue after discount |
| Gross Margin % | (Revenue - COGS) / Revenue x 100 | Profitability per SKU |
Pivot Output — Sell-Through % by Size x Category
This is what the management chart was built from
| Size | Women | Men | Accessories | Avg | Dead Stock? |
|---|---|---|---|---|---|
| XS | 14% | 12% | 18% | 14.7% | 🔴 YES |
| S | 38% | 41% | 52% | 43.7% | 🟡 Below Target |
| M | 68% | 71% | 65% | 68.0% | 🟢 Strong |
| L | 72% | 69% | 67% | 69.3% | 🟢 Strong |
| XL | 44% | 48% | 42% | 44.7% | 🟡 Below Target |
| XXL | 16% | 19% | 14% | 16.3% | 🔴 YES |
🔍 KEY FINDING
M and L sizes sell at 68–69% yet represent only 35% of total units ordered. XS and XXL fall below the 20% dead-stock threshold across all 3 categories. RM 24,197 is locked in non-moving XS/XXL units — cash that should be going to M/L restocking.
4
Step 4 — Analysis
48.2%
Avg Sell-Through
Target: 65%
32%
Revenue from Discounts
-8.9%
Margin Erosion
RM 24k
Dead Stock Value
Markdown impact by category
| Category | Full-Price Revenue | Post-Discount Revenue | Margin Before | Margin After | Erosion |
|---|---|---|---|---|---|
| Women | RM 68,420 | RM 58,180 | 52.3% | 44.1% | -8.2% |
| Men | RM 54,890 | RM 48,340 | 49.8% | 41.9% | -7.9% |
| Accessories | RM 38,200 | RM 30,920 | 58.4% | 47.6% | -10.8% |
| TOTAL | RM 161,510 | RM 137,440 | 53.4% | 44.5% | -8.9% |
🔍 KEY FINDING
30% of all revenue came from discounted items, reducing gross margin by 8.9%. Accessories suffered most — XS/XXL Accessories SKUs were discounted repeatedly to force clearance, destroying margin on the highest-pricing-power category.
5
Step 5 — Visualisation
Sell-Through Rate by Size & Category
Below 20% dotted line = dead stock
Dead Stock SKU Count by Category
18 SKUs total with sell-through below 20%
Full-Price vs Post-Discount Revenue
Gap = revenue lost to markdowns
Sell-Through Rate by Size and Category
Grouped bar — M/L at 68-69%, XS/XXL below 20%. Visual mandate to stop over-ordering extreme sizes.
Dead Inventory Count by Category
Bar — 18 dead SKUs, Accessories worst with 8. Tells management exactly where the problem is.
Markdown Impact: Full Price vs Discounted
Side-by-side bars — RM 24k lost to discounting. Drives markdown policy tightening conversation.
6
Step 6 — Report to Management
Actions submitted from this analysis
| Finding | Action Required | Owner | Deadline | Expected Impact |
|---|---|---|---|---|
| XS/XXL dead stock RM 24k | Freeze all XS/XXL reorders immediately | Procurement Manager | This week | Stops new dead stock |
| XS/XXL unsold units | 40% markdown clearance campaign | Retail Manager | This weekend | Recover ~RM 14k |
| M/L underordered vs demand | Shift order ratio to 50% M/L from 35% | Procurement Manager | Next order cycle | Sell-through 48% to 62% |
| Markdown eroding margin | Enforce Week 6/8 markdown trigger policy | Operations Director | 2 weeks | Stop unplanned discounting |
✅ WHY MANAGEMENT MUST ACT
- Cash flow: RM 24,197 tied in XS/XXL cannot be reinvested in M/L. Every week they sit, recovery value drops further.
- Margin decay: Currently at 38% avg discount. Trending toward 50%+ after week 8. The clearance window closes in 3 weeks as Q2 arrives.
- Next season risk: Without a procurement ratio change now, the same problem repeats next quarter with fresh stock in the same wrong proportions.