This project analyzes financial performance using a structured P&L dataset to identify profitability drivers, cost inefficiencies, and business risks across multiple business lines.
A multi-sector company in the Sports & Health industry aims to:
- Maintain sustainable profitability
- Optimize cost structure (COGS & Opex)
- Allocate resources efficiently
The company experienced significant profit volatility, especially a sharp decline in mid-year performance.
Profit dropped significantly despite relatively stable cost levels.
Key questions:
- What caused the mid-year profit decline?
- Which segments are underperforming?
- Is the company scaling efficiently with revenue?
- Designed a star schema
- Fact table: 580 financial transactions
- Dimension tables: Calendar, Group, Subgroup, KPI, Ratios
- Structured data to support full P&L analysis (Revenue → Net Profit)
- Cleaned and validated raw data
- Joined fact and dimension tables
- Aggregated metrics by time, business line, and cost category
- Calculated key metrics:
- Revenue, COGS, Opex, Profit
- Profit margins and cost ratios
- Analyzed revenue, expense, and profit over time
- Identified seasonality: Q1 peak → Q3 trough → Q4 recovery
- Compared revenue decline vs cost reduction
- Evaluated cost rigidity and operating leverage
- Compared performance across:
- Sportswear (main contributor)
- Equipment (declining trend)
- Nutrition (low efficiency)
- Analyzed:
- COGS: Labor, Materials, Shipping
- Opex: Marketing, Payroll, R&D
- Identified key cost drivers impacting profit
- Evaluated:
- Opex ratio
- Gross & Net margins
- ROI on Marketing, R&D, Payroll
- Profit dropped 87% due to revenue decline and cost rigidity
- Costs remained largely fixed despite falling revenue
- Nutrition segment showed low ROI and inefficient spending
- Revenue highly concentrated in Sales (~80%) → concentration risk
- Q4 recovery shows strong operating leverage potential
- Reallocate resources from low-ROI segments (Nutrition)
- Optimize Opex (Marketing, Payroll)
- Improve cost flexibility to scale with revenue
- Diversify revenue streams to reduce dependency
Identified key drivers behind an 87% profit decline and proposed strategies to improve margin potential from 8% to 30%+.
- SQL (data cleaning, transformation, aggregation)
- Data Modeling (Star Schema)
- Power BI (dashboard & visualization)
- Financial Analysis (P&L, margins, cost structure)
