Project Case Study · Financial Sales Analytics

The Profit Paradox

700 rows. 5 segments. 5 countries. 6 products. One uncomfortable question: where is profit actually being made — and where is it being quietly lost? The answers challenged every assumption about revenue, discounts and efficiency.

Dataset

Financial Sample — Microsoft Power BI

Records

700 transactions · 2013–2014

Pipeline

PySpark · Bronze → Silver → Gold

Deliverables

Databricks Pipeline · Python Viz · Power BI

Scroll

01 — The Brief

Problem & Approach

Most financial reports answer the same question: how much did we sell? This project asked a harder one — where is profit actually being made, and where is it being quietly lost? Starting with a single Excel file of 700 financial sales transactions covering 5 customer segments, 5 countries and 6 products across 2013 and 2014, the goal was to move beyond revenue and expose the real profit story underneath.

The dataset contained everything needed to go deep: gross sales, discounts applied, cost of goods sold, actual sales revenue and profit — all at the transaction level. The question was whether the segments generating the most revenue were actually the most profitable, whether discounts were helping or hurting, and which products were hiding efficiency under low volume numbers.

The approach was a full end-to-end pipeline built in Azure Databricks — Bronze layer preserving the raw data, Silver layer cleaning and enriching with three calculated columns (profit margin %, discount impact £ and cost ratio %), and five Gold aggregation tables each designed to answer a specific business question.

Python visualisations were built directly in Databricks notebooks using Matplotlib and Seaborn to surface findings before they reached the Power BI dashboard. The final deliverable was a 5-page interactive Power BI dashboard with 7 custom DAX measures — built on the Silver table to preserve full transaction-level flexibility.

Azure Databricks PySpark Python Power BI DAX Pandas Medallion Architecture Excel Power Query

02 — Data Engineering

Bronze → Silver → Gold — The Full Pipeline

Built entirely in Azure Databricks using PySpark and Python. The pipeline ran from raw Excel file through full profiling, cleaning, feature engineering and aggregation — producing five purpose-built Gold tables that each answered a specific business question, plus a Silver table connected directly to Power BI for full dashboard flexibility.

Bronze

Raw Ingestion

Pandas → PySpark

Excel file uploaded to Databricks raw_files volume
Loaded via pandas (PySpark cannot read Excel directly)
Converted to Spark DataFrame
Raw data preserved exactly — no transformations
Full data profile run: types, nulls, duplicates, unique values
53 nulls in discount_band confirmed as valid "no discount" — not missing data

Silver

Clean & Enrich

PySpark · Delta Lake

All 16 columns renamed to lowercase with underscores
Date column fixed from timestamp to date type
3 calculated columns added: profit_margin_pct, discount_impact, cost_ratio_pct
Saved as silver_financial_sales Delta table
Connected directly to Power BI for full transaction-level analysis

Gold

5 Aggregation Tables

PySpark · groupBy · agg

gold_profitability_by_segment — revealed Enterprise loss of -£614,545
gold_profitability_by_country — revealed France beats USA on profit
gold_profitability_by_product — revealed Carretera efficiency advantage
gold_discount_analysis — revealed 37.3% vs 24.9% margin gap by discount band
gold_monthly_trend — revealed seasonal peak pattern towards year end

Report

Python Viz + Power BI

Matplotlib · Seaborn · Power BI

5 Python charts built in Databricks notebook
Conditional colouring — red bars for negative profit (Enterprise)
Power BI connected to silver_financial_sales Delta table
7 DAX measures in dedicated _Measures table
5-page interactive dashboard with segment, country, product, discount and date slicers

Databricks catalog showing financial_sales schema with 5 Gold tables and 1 Silver table

Databricks Catalog — financial_sales schema

The financial_sales schema contains 6 tables: one Silver table (silver_financial_sales) holding the full cleaned and enriched transaction dataset, and five Gold tables — gold_discount_analysis, gold_monthly_trend, gold_profitability_by_country, gold_profitability_by_product and gold_profitability_by_segment — each built to answer one specific business question. Raw data is stored in the raw_files volume.

03 — At a Glance

The Numbers That Tell the Story

$119M

Total Sales

Across 5 countries & 5 segments

$17M

Total Profit

27.9% average profit margin

$9M

Total Discounts Given

From $128M gross to $119M net

($614K)

Enterprise Profit

The only loss-making segment

29.9%

Carretera Margin

Most efficient product — undersold

Critical Finding — The Loss-Making Segment

($614,545)

Enterprise segment profit — the only segment actively destroying value.

While Government generated $11.4M in profit and Small Business $4.1M, Enterprise recorded a net loss of $614,545. Every Enterprise sale costs more than it brings in. This is not a marginal underperformer — it is a segment where the business is paying to serve customers. The data raises an urgent question: what is driving Enterprise costs above revenue, and is the current pricing and discount strategy appropriate for this segment?

04 — The Dashboard

Power BI — 5-Page Interactive Dashboard

Connected to the silver_financial_sales Delta table in Databricks. Seven custom DAX measures — Total Sales, Total Profit, Total Gross Sales, Total Discounts, Total COGS, Avg Profit Margin % and Total Units Sold — power every visual across all five pages. Dynamic slicers for Segment, Country, Product, Discount Band and Date allow any combination of filtering.

Executive Summary dashboard showing KPI cards: Total Sales $119M, Total Profit $17M, Total COGS $102M, Avg Profit Margin 27.9%, Total Units Sold 1.13M

Page 1 — Executive Summary · KPIs & Slicers 01 / 05

Profitability Analysis showing Government leading profit at $11.4M and Enterprise in loss

Page 2 — Profitability Analysis · Where is Money Being Made and Lost? 02 / 05

Product Analysis showing Paseo leading total profit, Carretera leading profit margin

Page 3 — Product Analysis · Volume vs Efficiency 03 / 05

Discount Analysis showing profit margin declining from 37.3% at no discount to 24.9% at high discount

Page 4 — Discount Analysis · Are Discounts Helping or Hurting? 04 / 05

Trend Analysis showing monthly profit and sales trends from September 2013 to December 2014

Page 5 — Trend Analysis · Is the Business Growing? 05 / 05

05 — Python Visualisation

Charts Built in Databricks Notebook

Before building the Power BI dashboard, key findings were visualised directly in the Databricks notebook using Matplotlib and Seaborn. Conditional colouring was used deliberately — red bars for negative profit made Enterprise's loss immediately visible without needing to read a number.

Python chart showing profit by segment with Enterprise in red indicating loss

Chart 1 — Profit by Segment (red = loss) + Avg Profit Margin % by Segment

Python chart showing monthly profit and sales trends comparing 2013 and 2014

Chart 4 — Monthly Profit & Sales Trend — 2013 vs 2014 comparison

06 — Key Findings

Five Findings That Challenge the Obvious

Finding 01

Enterprise is losing money

Despite generating sales activity, Enterprise recorded -£614,545 profit — the only segment with negative profit. Every Enterprise transaction costs more than it brings in, making it a value-destroying segment that demands urgent investigation into pricing and discount strategy.

Finding 02

Highest revenue ≠ highest profit

USA generates the highest gross revenue but France generates the most profit. France sold more units at similar discount levels — demonstrating that volume efficiency, not just revenue size, determines profitability. A business focused only on revenue would miss this entirely.

Finding 03

Carretera is the hidden star

Carretera has the highest profit margin (29.9%) and lowest cost ratio (70%) — the most efficient product in the portfolio. Yet it generates the least total profit ($1.8M) because it sells the lowest volume. It is not a weak product — it is an undersold one. The opportunity: targeted marketing to convert efficiency into volume.

Finding 04

Discounts are a volume game — not a margin game

Profit margin drops from 37.3% (no discount) to 24.9% (high discount). Yet Government — the most discounted segment — is the most profitable because volume absorbs the margin sacrifice. The sweet spot is Low discount, generating the most total profit at $6.2M. Discounting only makes sense when the volume return justifies the margin cost.

Finding 05

October is the profit peak

Monthly analysis shows a clear seasonal pattern with profit peaking in October across both years combined. Sales and profit generally move together but not perfectly — the product mix sold each month and discount levels applied create monthly variation independent of raw sales volume.

Finding 06

Government profits despite heavy discounting

Government receives $3.9M in discounts — the most of any segment — yet generates $11.4M in profit. This is the volume paradox in action: discounting works when unit volumes are high enough to compensate. Government's bulk purchasing power absorbs the margin sacrifice and still produces the business's largest profit contribution.

07 — Recommendations

What the Business Should Do Next

Investigate Enterprise immediately

A loss-making segment cannot be left unaddressed. The business needs to understand whether Enterprise losses are driven by pricing too low, discounting too heavily, high service costs or low volume. Until this is understood, every Enterprise sale is destroying value.

Market Carretera aggressively

Carretera is the most cost-efficient product (lowest production cost, highest margin at 29.9%) but sells the least. A targeted marketing push would increase total profit without proportionally increasing costs — making it the single highest-leverage growth opportunity in the product range.

Make discounting strategic, not habitual

Discounts should be seasonal, segment-specific and volume-justified. Before applying a discount, the business should calculate whether the expected volume increase covers the margin sacrifice. High discounts (24.9% margin) should only be used when guaranteed volume returns justify the cost.

Investigate VTT and Velo production costs

Both VTT and Velo carry a 74% cost ratio — meaning 74 cents of every sales dollar goes to production. If costs cannot be reduced, the strategy must shift to volume growth to compensate. Understanding whether these costs are controllable is a critical business decision.

Plan for October — capitalise on the seasonal peak

October consistently delivers the highest profit across both years. The business should plan its highest-margin product pushes and marketing campaigns ahead of this window to maximise the seasonal uplift rather than simply benefiting from it passively.

Track France as a profitability model

France generates the most profit despite not having the highest revenue. Understanding what France does differently — product mix, discount levels, segment focus — could be a blueprint for improving profitability in lower-performing countries like Mexico.