All projects
Public data setFinancial analysis
Profitability Analysis by Segment and Category
Close to 10,000 retail order lines turned into a decision-ready profitability report with a Power Pivot data model, KPI tracking and interactive dashboards.
Sep 2023ExcelPower QueryPower PivotDAX measuresDashboards
Open the live dashboard- 9,994
- order lines
- $2.30m
- sales analyzed
- 12.5%
- profit margin
- 3
- dashboards
Profitability Analysis by Segment and CategoryReal data
1Problem
Sales data sat in raw transaction form. It showed what was sold, but not which products, customer segments or locations actually made money, or how each sales manager performed against target.
2Data
- Sample Superstore retail data set (public training data): 9,994 order lines from 2014 to 2017.
- Orders, returns and regional managers tables, plus a shipping cost table per state.
3Approach
- 01Understood the data, then cleaned it in Power Query.
- 02Added calculated columns such as COGS, discount value and shipping time.
- 03Built a Power Pivot data model with measures: Revenue, COGS, Profit and margin.
- 04Wrote the business questions first: customer loyalty, strengths and weak points, performance and customer experience.
- 05Answered each question with pivot tables, then designed three dashboards: distribution, sales and KPIs.
- 06Built a KPI model that compares each regional manager against target.
4Key insights
- Total sales of $2.30m produced $286k profit, a 12.5% margin.
- Furniture sells almost as much as Technology ($742k vs $836k) but earns only $18k profit against $145k: a 2.5% margin against 17.4%.
- Tables lose $17.7k and Bookcases lose $3.5k, while Copiers are the most profitable sub-category at $55.6k.
- The Consumer segment brings 51% of sales ($1.16m). California and New York are the two largest states.
- Standard Class is used for 60% of order lines and takes 5 days on average, against 2 days for First Class.
- Sales grew 51% from 2014 to 2017, from $484k to $733k.
5Result
- A decision-ready profitability report instead of a transaction list.
- Clear view of profit and margin by product, segment and location.
- Insights that support pricing and discount decisions, starting with the loss-making furniture lines.
