Superstore Profitability Analysis

01 — Objective

Where does a profitable store quietly lose money?

On paper, Superstore is in good shape: $2.30M in sales and a 12.5% margin. But one overall margin can hide a lot. Some products sell well and earn almost nothing, and some regions work harder for less.

This project works down from the totals to find what is responsible, and what the store could change. It uses four years of order data and Python, and every figure on this page comes from a named section of one notebook.

Which categories and products drive profit, and which lose money?
What separates the profitable parts of the business from the unprofitable ones?
Where, and when, should the business act to protect its profit?

02 — Data

9,994 order lines, four years, one question

The dataset is the Superstore sales file: 21 columns covering orders, customers, locations, products, sales, quantity, discount and profit. Each row is one order line, meaning one product on one order. A customer who buys a chair, a table and paper in one purchase creates one order and three order lines.

9,994order lines5,009orders793customers49states

The file has no duplicate rows and no missing values. The two date columns were stored as text, so they were converted to real dates (day first) before any year or month analysis. There is no cost column: the profit figure is already sales minus cost, so margin is profit divided by sales.

03 — Method

One pattern, repeated: group, calculate, print, plot

Nearly every section of the notebook splits the data into groups, calculates a measure for each group, prints the table and draws a chart with the values written on the bars. That keeps each finding easy to check. A few short snippets from the notebook appear through this page.

Clean and check
Load the CSV, convert both date columns, then check for duplicates, missing values and data types.
Group and measure
Total sales, total profit and margin by category, sub-category, region, state and customer segment.
Test the discounts
Average profit at each discount level, discounts grouped into bands, and the correlation between discount and profit.
Isolate the losses
Pull out every loss-making line, trace the losses to sub-categories, then examine Tables alone by discount, region and year.
Check the clock and the customer
Yearly and monthly trends, then segment and shipping mode, to rule things in or out.

PythonSections 2 and 3 · load the data, convert the dates

df = pd.read_csv("Superstore.csv", encoding="latin1")
df["Order Date"] = pd.to_datetime(df["Order Date"], dayfirst=True)

04 — Key Findings

Furniture sells like the others and earns almost nothing

Finding 1 · Category

A 2.5% margin against 17% for everything else

Furniture brings in $742K, 32% of all sales, but only 6% of profit. Technology and Office Supplies earn margins of 17.4% and 17.0%. The next step was to look inside Furniture.

PythonSection 6 · sales, profit and margin by category

category = df.groupby("Category").agg(
    Sales=("Sales", "sum"),
    Profit=("Profit", "sum")
)
category["Margin %"] = category["Profit"] / category["Sales"] * 100

Bar chart of sales and profit by category with values on the bars — Notebook section 6 · Sales vs profit by category ($K)

2.5%Furniture profit margin
~17%Margin on Technology and Office Supplies

Finding 2 · Sub-category

Three sub-categories lose money, and Tables lose the most

Tables lose $17.7K on $207K of sales. Bookcases (-$3.5K) and Supplies (-$1.2K) also lose, and Machines barely break even at a 1.8% margin. Copiers earn the most per line ($818), but from only 68 lines, so they are not a typical order.

Horizontal bar chart of profit by sub-category in thousands of dollars — Notebook section 7 · Profit by sub-category ($K)

-$17.7KTables
-$3.5KBookcases
-$1.2KSupplies

Finding 3 · Discounts

Past a 20% discount, the store starts losing money

Average profit per order line is positive from 0% to 20% and negative from 30% upward. Grouped into bands, lines with no discount earned $321K. The three bands above 20% lost a combined $135K, and every line above 40% lost money. Discount and profit have a correlation of -0.22: a moderate link, not proof of cause.

Bar chart of average profit per order line at each discount level — Notebook section 8 · Average profit per order line by discount, all products ($)

PythonSection 8 · grouping discounts into bands

df["Discount Band"] = pd.cut(
    df["Discount"],
    bins=[-0.01, 0, 0.2, 0.4, 0.6, 1.0],
    labels=["0%", "1-20%", "21-40%", "41-60%", "61-80%"]
)

Bar chart of total profit by discount band in thousands of dollars — Notebook section 8 · Total profit by discount band ($K)

+$321KProfit on lines with no discount
-$135KLost on lines discounted above 20%

Try it: pick a discount level

Average profit per order line at that discount. Tables are shown next to all products.

All products-

Tables only-

Finding 4 · Loss-making lines

One order line in five loses money, and the losses are not only Tables

1,871 lines (18.7%) lose money, a combined $156K. That wipes out over a third of the $443K that profitable lines earn. Binders (-$38.5K), Tables (-$32.4K) and Machines (-$30.1K) lose the most on their loss-making lines. Binders stay profitable overall because their winning lines outweigh the losers; Tables do not.

Horizontal bar chart of losses from loss-making order lines by sub-category — Notebook section 9 · Losses from loss-making order lines ($K)

The sub-categories that discount most tell the same story. Machines, Tables and Bookcases are three of the four most-discounted sub-categories and three of the four lowest-margin ones. Binders, discounted most of all, is the exception because of its volume.

Sub-category	Avg discount	Margin	Order lines
Binders	37%	14.9%	1,523
Machines	31%	1.8%	115
Tables	26%	-8.6%	319
Bookcases	21%	-3.0%	228

Notebook section 18 · the four most-discounted sub-categories.

05 — Tables Spotlight

Profitable at full price, losing from 30% off

Tables were the biggest loss, so they got their own section. The question was whether the product itself is the problem, or the way it is sold.

PythonSection 10 · isolate Tables, average profit at each discount

tables = df[df["Sub-Category"] == "Tables"]
tables_discount_profit = tables.groupby("Discount")["Profit"].mean().round(0)

Bar chart of average profit per order line for Tables at each discount level — Notebook section 10 · Tables: average profit per order line by discount ($)

Discount	Order lines	Avg profit per line	Total profit
0%	72	$184	$13,276
20%	71	-$4	-$304
30%	54	-$63	-$3,402
40%	75	-$216	-$16,187
45%	11	-$227	-$2,493
50%	36	-$239	-$8,615

At full price a Tables line earns $184. At 20% it breaks even. Beyond that every step deepens the loss. Tables lines discounted 20% or less earned +$13.0K; the 176 lines discounted above 20% lost $30.7K on $90K of sales. The average Tables discount is 26%, against 15% for all other products.

+$184Per Tables line at 0% discount
-$239Per Tables line at 50% discount
-$8.1KTables loss in 2014, against about $3K a year before

Bar chart of total Tables profit by region in thousands of dollars — Notebook section 10 · Tables: total profit by region ($K)

The loss is concentrated. In the East every Tables line was discounted above 20%, the average was 37%, and the region lost $11.0K. In the West only 16% of lines were, the average was 20%, and Tables made $1.5K.

Region	Order lines	Avg discount	Total profit
East	80	37%	-$11,025
South	51	22%	-$4,623
Central	72	26%	-$3,560
West	116	20%	$1,483

06 — Where

The weakest places are the ones that discount most

The West earns a 14.9% margin on an 11% average discount. Central earns 7.9% on 24%. Furniture in Central lost $2.9K, the only region and category pair to lose money.

Bar chart of profit margin by region — Notebook section 11 · Profit margin by region (%)

Bar chart of average discount by region — Notebook section 11 · Average discount by region (%)

At state level, 10 of 49 states lose money. Texas (-$25.7K), Ohio (-$17.0K), Pennsylvania (-$15.6K) and Illinois (-$12.6K) lose $70.9K between them, at average discounts of 32% to 39%. The five most profitable states average only 0% to 7%.

Horizontal bar chart of the ten least profitable states in thousands of dollars — Notebook section 12 · The ten least profitable states ($K)

07 — When

Profit grew every year, but the best months are not the busiest

Sales dipped 2.8% in 2012, then grew 29.3% and 20.6%. Profit rose every year, from $49.5K to $93.5K. Technology drove the growth ($21.5K to $50.7K); Furniture stayed small and uneven ($5.5K, $3.0K, $7.0K, $3.0K).

Line chart of sales and profit by year — Notebook section 15 · Sales and profit by year ($K)

Line chart of profit by year for each category — Notebook section 15 · Profit by year and category ($K)

Sales peak in September, November and December. But November, the busiest month at $349K, earns only a 10.3% margin, while February, the quietest, earns the best (17.1%). The average discount is steady at 15% to 17% all year, so heavier discounting does not explain it. Product mix is a more likely cause and worth checking.

Bar chart of sales by month — Notebook section 16 · Sales by month ($K)

Bar chart of profit margin by month — Notebook section 16 · Profit margin by month (%)

08 — Ruled Out

Customer segment is not the problem

Margins are close across the three segments: Home Office earns 14.0%, Corporate 13.0% and Consumer 11.5%. Shipping mode makes little difference either, with margins of 12% to 14% across all four modes. Whatever is leaking profit is not about who buys or how it ships.

Bar chart of profit margin by customer segment — Notebook section 13 · Profit margin by customer segment (%)

09 — Recommendations

What the data supports doing

Cap discounts at 20%
Average profit is positive up to 20% and negative from 30%. Start with Tables, where capped lines earned +$13.0K and the rest lost $30.7K.
Review Central and East discount approvals
Central averages a 24% discount and the lowest margin (7.9%). East Tables averaged 37% and lost $11.0K.
Reprice Tables, Binders and Machines
The largest losses on loss-making lines: $32.4K, $38.5K and $30.1K. Binders average a 37% discount and Machines 31%.
Audit Texas, Ohio, Pennsylvania and Illinois
A combined loss of $70.9K at average discounts of 32% to 39%, against 0% to 7% in the most profitable states.

10 — Limitations

What this analysis cannot say

Association, not cause. Heavy discounts go with losses, but the data cannot prove they cause them. Discounts may be given on higher-cost or slower-moving items.
No cost data. A discount cap might reduce sales volume. The deep-discounted Tables lines brought in $90K and lost $30.7K, so profit could still rise, but that should be tested before it is assumed.
Small groups. Some discount levels have few lines (11 at 45%; 36 for Tables at 50%), so single bars are indicative.
Region and state gaps may partly reflect product mix, not only discount policy.
Four years of history. Patterns may have changed since 2014.

The sensible next step is a controlled test: cap discounts at 20% in one region for a quarter and compare margin and sales against a similar region.

11 — Project Files

Explore the full analysis

The written report goes through every finding in detail, the slides give the short version, and the notebook holds all the code.

To run the notebook, place Superstore.csv in the same folder and use Run All Cells. Python 3 with pandas and matplotlib is required.

12 — More from this portfolio

← Back to all projects

Superstore Profitability Analysis

At a glance

Where does a profitable store quietly lose money?

9,994 order lines, four years, one question

One pattern, repeated: group, calculate, print, plot

Clean and check

Group and measure

Test the discounts

Isolate the losses

Check the clock and the customer