Overview
This session covers using Excel pivot tables to analyze sales data across multiple SKUs, distribution centers, and time periods. The analysis identifies top-performing products and applies the Pareto Principle to understand which items drive the majority of sales volume.
Pivot Table Setup
To analyze sales by SKU without manual filtering, use Excel's pivot table feature to aggregate and view data across different dimensions.
Basic configuration steps:
| Component | Action | Result |
|---|
| Data Selection | Select entire dataset | Prepares all records for analysis |
| Insert | Insert > Pivot Table | Opens pivot table interface |
| Rows | Drag SKU column to Rows field | Creates row for each unique SKU |
| Values | Drag Sales column to Values field | Aggregates sales data per SKU |
| Function | Select Sum in Value Field Settings | Calculates total units sold per SKU |
- Pivot tables automatically aggregate data across all dates and cities
- Row labels display all 30 SKU codes alphabetically by default
- Value field settings allow different calculations: Sum, Count, Max, Average
Sales Analysis Process
- Cross-verification: M01 showed 909 units (matching manual filter result)
- Grand total across all SKUs: 7,438 units
- Top three SKUs (F01, M01, L01) each contributed ~12% of total volume
- F01 led with 914 units sold across three distribution centers over 15 days
Sorting computed columns:
- Pivot tables don't allow direct sorting on computed values
- Copy pivot table results and paste as values in new location
- Sort pasted data by Sum of Sales (largest to smallest)
Pareto Principle Application
The Pareto Principle states that 80% of outcomes come from 20% of causes, commonly observed in sales distributions.
- Expected pattern: 20% of products generate 80% of sales
- Observed result: 11 out of 30 SKUs (37%) contributed 80% of volume
- Top 3 SKUs alone represented 33% of total sales
- Pattern less pronounced with only 30 items; larger catalogs show clearer 80/20 splits
Cumulative analysis:
- Calculate cumulative volume: add each SKU's contribution to running total
- Convert to percentage of grand total to identify cutoff points
- Approximately 15 SKUs reached 80% threshold in this dataset
Next Steps
- Repeat identical analysis for revenue data (requested by CFO)
- Compare volume leaders with revenue leaders to identify mismatches
- Determine if top volume SKUs also drive highest revenue
- Use findings to focus on dominant items for inventory and sales strategy
Key Terms & Definitions
- SKU (Stock Keeping Unit): Unique product identifier used to track inventory
- Pivot Table: Excel tool for summarizing and analyzing large datasets across multiple dimensions
- Distribution Center (DC): Warehouse location from which products are shipped
- Pareto Principle: 80/20 rule stating that roughly 80% of effects come from 20% of causes
- Computed Column: Calculated field in pivot table (sum, average, count) rather than raw data