5-Step Guide to Optimizing a 1000-SKU Assortment
Managing a portfolio of 1000 SKUs is a complex but essential responsibility for category managers, commercial leads, and brand teams in FMCG and retail environments. A well-optimized assortment ensures the right products are in the right place, at the right price, and delivering the right profit. However, bloated or misaligned assortments can lead to inefficiencies, lower margins, and reduced customer satisfaction.
This guide outlines a structured 5-step approach to improve your assortment strategy, focusing on sales volume, distribution, and margin contribution. You will also find practical ChatGPT prompts, visual analysis tools, and FMCG-relevant thinking to guide your decision-making on assortment depth (variety within categories) and width (breadth across categories).
Step 1: Data Collection & Cleaning
Objective: Build a robust dataset to ensure reliable analysis.
Tasks To Do:
Collect sales performance data (units sold, revenue per SKU, gross margin).
Compile distribution data by channel, customer, and region.
Integrate promotional activity logs, price levels, and customer segment data.
Clean the dataset by eliminating duplicates, filling missing values, and adjusting for anomalies (e.g., stockouts, promotional spikes).
Normalize timeframes (e.g., last 12 months, YTD) to ensure comparability.
ChatGPT Prompt Example: "Give me a Python script to clean SKU-level sales data and flag anomalies based on z-score."
Step 2: Performance Segmentation
Objective: Classify SKUs based on their contribution to key metrics.
Tasks To Do:
Perform Pareto analysis to identify top performers (typically 20% of SKUs driving 80% of revenue).
Calculate contribution per SKU to volume, profit, and distribution reach.
Segment SKUs into four strategic buckets:
A: High Volume, High Margin, High Distribution (Core Growth)
B: High Margin, Low Volume (Profit Boosters)
C: High Volume, Low Margin (Traffic Builders)
D: Low in all metrics (Rationalization Candidates)
Use filters or visual dashboards to drill into regional or channel-specific performance.
ChatGPT Prompt Example: "Create a bubble chart in Excel mapping SKU volume (x-axis), margin (y-axis), and bubble size by distribution."
Step 3: Cross-Dimensional Analysis
Objective: Identify strategic opportunities and hidden inefficiencies.
Tasks To Do:
Build a 3D matrix or bubble chart crossing margin, volume, and distribution.
Assess whether some SKUs are cannibalizing others.
Identify white space (underserved high-potential segments).
Check consistency of SKU performance across regions and customers.
Quadrant Graph Example:
X-Axis: Volume
Y-Axis: Margin
Bubble Size: Distribution
ChatGPT Prompt Example: "How can I cluster SKUs into strategic quadrants using K-Means based on volume, margin, and distribution?"
Step 4: Optimization Recommendations
Objective: Provide guidance on how to adjust the assortment.
Tasks To Do:
Recommend delisting or repositioning SKUs in the bottom 20% of profitability and distribution.
Identify duplication within the assortment—too many similar formats, pack sizes, or variants.
Propose expansion in segments with strong growth or unmet demand.
Use benchmarks and historic data to defend decisions.
ChatGPT Prompt Example: "Suggest optimization recommendations for underperforming SKUs based on cluster logic and sales contribution."
Step 5: Simulation & Execution
Objective: Model impact and align with business teams for roll-out.
Tasks To Do:
Create what-if scenarios: e.g., impact on revenue and margin of cutting 200 SKUs.
Model volume redistribution to retained SKUs.
Define action plans per customer/channel.
Align cross-functionally with supply chain, trade marketing, and sales.
Define and track key KPIs post-execution.
ChatGPT Prompt Example: "Build a forecasting model to simulate margin impact of removing 200 SKUs and redistributing volume across top performers."
Visual Tools & Graphs to Support Analysis
Recommended Visuals:
Pareto Charts: Cumulative revenue vs. SKUs.
Bubble Charts: Volume vs. Margin with distribution as bubble size.
Matrix Heatmaps: Category vs. Margin/Volume.
Quadrant Maps:
High Volume / High Margin (Stars)
Low Volume / High Margin (Niche)
High Volume / Low Margin (Workhorses)
Low Volume / Low Margin (Eliminate or Reposition)
ChatGPT Prompt Example: "Give me a Tableau dashboard layout to visualize SKU performance across 3 KPIs with interactive filters."
Final Advice
Use this method iteratively. First pass for quick wins, second pass for long-term strategy. Align findings with your commercial calendar and supply realities.