Retail Supply Chain Sales & Profitability Analytics

Business problem

A retailer sees healthy sales growth, but that does not say whether the growth is profitable. The questions behind this report were: where does profit come from, how much do discounts and returns erode it, and how concentrated is performance across categories, customer segments and salespeople?

Dataset

The data is a public retail order table with customers, products, sales, profit, discount, return status, geography and salesperson. After preparation the fact table holds 9,994 rows.

Limitations to keep in mind:

• It is a public sample dataset, so findings describe this data and are not evidence about any real business.
• It has no product cost breakdown, so profit is taken as given.
• Discount and profit are observed together in historical transactions. The data does not show what would have happened under a different discount policy.

Analytical workflow

1. Profiling in Python (pandas). Dimensions, data types, descriptive statistics, missing values and duplicate checks, and validation of the fields the report depends on.
2. Preparation and modelling in Power BI. Power Query transformations, then a star schema with a FactSales table and dimensions for date, customer, salesperson, geography and product.
3. Model validation. 9,994 fact rows, 0 null geography keys, 0 null salesperson keys, and 5 relationships created and checked.
4. DAX measure layer. Core KPIs, year-over-year growth, discount, returns and segment measures, documented with formulas in the repository.
5. Dashboard. Three pages that move from executive summary to profitability drivers to people and segments.

Business insights

Growth was uneven. Sales fell about 2.8% in 2015, then grew 29.5% in 2016 and 20.4% in 2017.

Heavy discounting is where profit disappears. Margin is positive up to the 11-20% band (11.6%) and negative from the 21-30% band (-10.0%). It gets steadily worse as discounts deepen.

Category margins differ sharply. Technology (17.4%) and Office Supplies (17.0%) earn healthy margins. Furniture earns 2.5% on similar sales volume.

Sales are concentrated in a few salespeople. The top two account for roughly 61% of sales.

What a stakeholder could do with this: review the approval process for discounts above 20%, look at Furniture pricing and discounting specifically, and check dependence on the two leading salespeople. These are suggestions for investigation. Nothing here was tested or implemented.

Methodology and technical decisions

• Star schema keeps measures simple and fast, and prevents double-counting when slicing by segment, year or salesperson.
• A DAX measure layer keeps definitions in one place. Each measure is documented so a reviewer can check how a number is calculated.
• The headline discount metric is defined precisely. Profit Above 20% Discount is the total profit of transactions where the Discount field is greater than 0.20 (CALCULATE([Total Profit], FactSales[Discount] > 0.20)). The dashboard shows it as a share of total profit, which is -47.27%. Sales Above 20% Discount % is the same filter applied to sales (15.79%).
• Two different return figures appear on the dashboard. Returned sales are 7.86% of sales value. The separate Return Rate card (5.91%) is defined in the DAX documentation and is not the same measure.

Results and limitations

Verified in the repository and dashboard: 9,994 rows; $2.297M sales; $286.4K profit; 12.47% margin; 5,009 orders; 7.86% returned sales; 15.79% of sales above the 20% discount threshold; profit on those sales of -$135,376.06.

Limitations:

• The negative-profit finding is an association in one dataset. It does not prove that capping discounts would raise profit, because lower discounts could also lower volume.
• Discounted transactions may differ in product mix, so part of the effect could come from what was sold and not from the discount itself.
• The report is a static .pbix file and PDF export. There is no live published dashboard.