CASE STUDY · RETAIL · SQL SERVER, PYTHON, POWER BI

Discount Guardrails

Discounts above 20% lost $135K over four years, almost half of the profit. A 20% cap was the best option I tested.

SALES: $2.30M
PROFIT: $286K
MARGIN: 12.5%
LOST ABOVE 20% OFF: −$135K

Overview page of the Discount Guardrails Power BI report — The overview page of the four-page Power BI report, built for the Head of Sales.

THE QUESTION

What maximum discount should each product group allow?

A US office-supply retailer took 5,009 orders between 2014 and 2017, with discounts from 0% to 80% off. The Head of Sales sets discount limits once a year. I analysed every one of its 9,994 order lines in SQL Server to give that decision a number.

FINDING 01

The line is 20%.

Up to 20% off, sales stayed profitable at an 11.9% margin. Every discount level above 20% made a net loss, and above 50% off every single sale lost money.

Bar chart of total profit at each discount level

FINDING 02

It’s the discount, not the products.

The products sold at 30–50% off make 27¢ on every $1 at full price, close to the company average. At those discounts they lost 20¢. And it was routine: 565 of 793 customers got at least one discount above 20%.

Product groups page of the Power BI report

FINDING 03

20% is the best cap I tested.

I re-priced every past order under caps from 20% to 50%. The 20% cap adds the most profit under every assumption. Tables, Storage and Supplies still lose money even at up to 20% off, so they need a price review as well.

Chart comparing extra profit under 20 to 50 percent caps

WHAT IF WE CAP AT 20%? · MODELLED

It pays, however customers react.

+$219Kif every customer still buys at the capped price.

$0Extra profit over 2014–17, with a 20% cap$219K

Cap at 20% · 8 groups

Binders, Tables, Machines, Bookcases, Appliances, Chairs, Phones, Furnishings

Review prices · 3 groups

Tables, Storage, Supplies: losing money even at up to 20% off

Fine as they are · 7 groups

Labels, Paper, Envelopes, Copiers, Fasteners, Accessories, Art

HOW I BUILT IT

SQL Server: T-SQL with CTEs, window functions and stored procedures for the scenario and monitoring logic.
Checks: 33 automated checks that reconcile totals, plus Python cross-checks against the SQL results.
Power BI: A four-page report with a what-if page and Deneb custom visuals, for a quarterly check-in.
Recommendation: Pilot the 20% cap in two regions for two quarters before rolling it out, because the real risk is losing customers.