← All projects

Retail · Profitability

Global Superstore: where profit is lost

The business sells $12.64M a year but keeps only $1.47M as profit. Where is the money being lost, and what should management change first?

Tools: Power BI, DAX, Power Query Data: Global Superstore, public dataset (Kaggle) Size: 51,000 order lines, 24 columns
Profit leakage dashboard built in Power BI
Profit leakage page: which sub-categories, regions and products lose money.

Key numbers

11.6%overall profit margin ($1.47M on $12.64M sales)
30%of orders lose money
$920Klost on unprofitable orders
-51%margin on orders discounted over 30%

Why I picked this question

When I worked in sales, discounts were the easiest way to close a deal. Nobody on the sales side saw what they did to profit afterwards. I wanted to build the report a CEO would need to see that: not just how much we sell, but which sales actually make money.

How I approached it

  1. Cleaned and shaped the data in Power Query.
  2. Wrote DAX measures for profit margin, loss orders, total loss and profit by discount band.
  3. Built a 4-page Power BI report: an overview, customer and shipping performance, profit leakage, and discount strategy.
  4. Summarised the findings in a short presentation written for a CEO.

What I found

1. Heavy discounts turn sales into losses

DiscountSalesProfitMargin
No discount$6,992,411$1,770,69525.32%
0 to 10%$1,962,619$338,18917.23%
10 to 20%$1,757,261$173,2559.86%
20 to 30%$382,555-$21,156-5.53%
Over 30%$1,547,656-$793,526-51.27%

Margin drops with every discount band. Past 20% the business loses money, and orders discounted over 30% lose about half of what they sell. Taken together, discounted orders lost $303K overall.

2. Losses are concentrated, not spread evenly

By product: Tables lose the most ($144K), followed by Bookcases ($101K), Phones ($96K) and Chairs ($96K). Furniture is the worst category at $370K in losses, and it also gets the highest average discount (16.8%).

By region: Central loses the most ($202K), then South ($157K) and EMEA ($123K).

3. Where the profit comes from

The Consumer segment brings in $749K profit, more than Corporate ($441K) and Home Office ($277K). Standard Class shipping earns the most profit ($891K). Faster shipping didn't show a clear link to higher profit.

What I would recommend

  1. Cap discounts at 20% and require manager approval above that. This is where orders start losing money.
  2. Review pricing and discounts on Tables and Bookcases before cutting them. They drive the biggest losses.
  3. Start with Central and South regions, where the losses are largest, and check what discounts are being given there.

What I'd look at next

The data doesn't include product costs or the reason each discount was given. With those, I could separate discounts that win repeat customers from ones that just give margin away.

More from the report

Discount strategy page in Power BI
Discount strategy: sales, profit and margin by discount band.
Customer and operational performance page in Power BI
Customer and operational performance: profit by segment and shipping method.
View files on GitHub
← All projects Next project: Vrinda Store →

Let's talk

Open to remote Business Analyst, Data Analyst, BI and Sales or Revenue Operations roles. The fastest way to reach me is by email.

Email me LinkedIn