Skip to content
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
Profit, $kTechnology145Office Supplies122Furniture18
This project uses a public or training data set, so the numbers on this page are the real results of the analysis.

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

  1. 01Understood the data, then cleaned it in Power Query.
  2. 02Added calculated columns such as COGS, discount value and shipping time.
  3. 03Built a Power Pivot data model with measures: Revenue, COGS, Profit and margin.
  4. 04Wrote the business questions first: customer loyalty, strengths and weak points, performance and customer experience.
  5. 05Answered each question with pivot tables, then designed three dashboards: distribution, sales and KPIs.
  6. 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.
Chat on WhatsApp