📊 Project Overview — What You Will Build
In this project, you will create a complete P&L (Profit & Loss) Statement Financial Dashboard in Power BI using a real dataset. You will analyze Revenue, Discounts, COGS, Gross Profit, OPEX, EBITDA, and Net Profit—along with Budget vs Actual variance and Branch-wise performance.
🔍 Key Metrics You Will Build
- Revenue, Net Revenue & Discount Impact
- COGS & Gross Profit
- Gross Margin % (GM%)
- Operating Expenses (OPEX)
- EBITDA (Operating Profit)
- Net Profit (Final Profit)
- Budget vs Actual (Revenue, OPEX, Profit)
- Branch-wise & Category-wise Profitability
📂 Dataset Used in This Project
This project uses a structured P&L dataset containing Orders, Product Costs, OPEX (Operating Expenses), and Budget tables. You can open or download the dataset using the link below:
📥 Open Dataset (Google Sheets)Key Performance Metrics
The dashboard tracks essential financial metrics that help analyze performance, profitability, and spending across the organization. These metrics give a clear picture of revenue generation, cost efficiency, and operational strength.
Total Revenue
Total income generated from all sales after discounts.
Cost of Goods Sold (COGS)
Total direct cost of producing or purchasing the sold items.
Gross Profit
Profit earned after subtracting COGS from Revenue.
Gross Margin (%)
Percentage of profit retained after covering direct costs.
Operating Expenses (OPEX)
Monthly expenses across departments such as HR, Marketing, IT, and Admin.
EBITDA
Earnings before interest, tax, depreciation & amortization — core operating profit.
Net Profit
Final profit after subtracting all direct and operating expenses.
Budget vs Actual Variance
Deviation between planned targets and real performance.
Data Processing & Structure
The dataset is already prepared and cleaned for direct use in the dashboard. Below is a concise description of each table, its columns, and the minimal processing performed so the data is ready for aggregation and visualization.
Tables & Columns
- OrderID, OrderDate, Year, Month — row-level time keys
- ProductID, ProductName, Category — product dimensions
- Branch, Region — location dimensions
- Quantity, UnitPrice, DiscountPct — pricing & volume
- Sales — revenue after discount (UnitPrice × Qty × (1 – DiscountPct))
- UnitCost, COGS — cost per unit and total cost (UnitCost × Qty)
- GrossProfit — Sales − COGS
- ProductID, ProductName, Category
- UnitPrice, UnitCost — standard list price & cost
- StandardMarginPct — benchmark margin (UnitPrice − UnitCost) ÷ UnitPrice
- Month (YYYY-MM), Branch, Department
- OPEX_Amount — monthly expense per department & branch
- Month, Branch
- Budget_Revenue, Budget_OPEX — planned targets for variance comparison
Processing Summary
- Dates standardized (OrderDate → YYYY-MM for monthly grouping).
- Numeric fields validated and corrected (Sales, COGS, UnitCost, Quantities).
- Discount values normalized to decimals (e.g., 5% → 0.05).
- Sales, COGS and GrossProfit recalculated where inconsistencies were found.
- OPEX and Budget aligned to the same monthly grain for easy joins.
Aggregation logic (for dashboard)
- Monthly Revenue: sum(Sales) grouped by Month (and Branch if filtered).
- Monthly COGS / GP: sum(COGS) and sum(GrossProfit) grouped by Month.
- Total OPEX: sum(OPEX_Amount) grouped by Month & Department (or Branch).
- EBITDA / Net Profit: GrossProfit − Total OPEX (per month / branch).
- Budget Variance: Actual (Revenue/OPEX) − Budget (Revenue/OPEX) by Month & Branch.
Visual Insights
The dashboard contains concise, actionable visuals that surface revenue trends, cost structure, margin performance, and operational expenses — enabling quick decision-making at product and branch level.
KPI Summary
At-a-glance cards displaying Total Revenue, Gross Profit, Gross Margin %, Total OPEX, EBITDA and Net Profit for the selected period and branch.
Monthly Revenue Trend
Line chart showing monthly revenue movement — useful for spotting seasonality, spikes and dips.
Monthly Gross Profit Trend
Line chart for gross profit over time to reveal margin trends independent of revenue scale.
P&L Matrix
Dynamic matrix (rows = P&L lines, columns = months) showing Revenue → Discounts → Net Revenue → COGS → Gross Profit → OPEX → EBITDA → Net Profit.
OPEX Breakdown
Stacked view of operating expenses by department & month, highlighting major cost drivers (Rent, HR, Marketing, IT, Admin).
Product Margin Comparison
Comparison of each product’s benchmark margin vs actual margin to identify margin leakage and discount impact.
Budget vs Actual
Side-by-side bars for budgeted vs actual revenue and OPEX, with variance figures to highlight gaps.
Branch Performance
Branch-level ranking for revenue and net profit, enabling quick identification of top and underperforming locations.
Waterfall Summary
Waterfall visualization showing movement from Revenue down to Net Profit (discounts, COGS, OPEX effects clearly shown).
This chart visualizes monthly revenue trends to highlight seasonal peaks and troughs. Use it for forecasting, budgeting, and P&L insights.
This chart visualizes monthly gross profit to help identify months with higher margins and months that need cost or pricing interventions.
Values aggregated from the OPEX_Monthly sheet for the year 2025. If you’d like a different year or different grouping (e.g., combine Admin+HR as “Salaries”), tell me and I’ll update the block.
/mnt/data/pnl_project_dataset_rectified1.xlsx
Higher % indicates better gross margin (Gross Profit ÷ Sales). Use this chart to prioritise high-margin SKUs and review low-margin products for pricing or cost improvements.
- Responsive bars adapt to screen size.
- Product names truncate on small screens for readability.
- Hover or tap to see exact margin values.
/mnt/data/pnl_project_dataset_rectified1.xlsx → Orders
- Total Sales: —
- Avg GP Margin: —
- Best Sales Branch: —
- Best Margin Branch: —
Hover/tap on bars for exact Sales (INR) and on the line points for GP Margin. Want per-branch drilldown (monthly trend) or branch filter? Ask and I’ll add it.
/mnt/data/pnl_project_dataset_rectified1.xlsx → Budget (Budget_OPEX) & Orders (Sales)
- Total Budget: —
- Total Actual Sales: —
- Budget vs Actual Variance: —
- Best Month (Actual): —
Hover/tap to see exact values. Want this chart as percentage of budget (Actual/Budget) or a cumulative budget vs actual chart? I can add it.
/mnt/data/pnl_project_dataset_rectified1.xlsx
- Total Sales—
- COGS—
- Gross Profit—
- Operating Expenses (OPEX)—
- Operating Profit—
Notes: Operating Profit is negative for 2025 (operating loss). Tell me if you want:
- Drilldown by month or branch
- Include taxes/interest if present in data
- Export this summary CSV
/mnt/data/pnl_pl_matrix_2025.csv| Branch | Orders_Count | Sales | COGS | GrossProfit | OPEX_Amount | Operating_Profit | GP_Margin_pct | OP_Margin_pct |
|---|---|---|---|---|---|---|---|---|
| West-01 | 104 | ₹235,803.76 | ₹47,951.30 | ₹187,852.47 | ₹137,649.02 | ₹50,203.45 | 79.66% | 21.29% |
| North-01 | 95 | ₹201,039.87 | ₹40,224.46 | ₹160,815.41 | ₹153,302.52 | ₹7,512.89 | 79.99% | 3.74% |
| South-01 | 76 | ₹168,265.51 | ₹35,434.91 | ₹132,830.60 | ₹45,233.98 | ₹87,596.62 | 78.94% | 52.06% |
| East-01 | 72 | ₹168,151.86 | ₹34,067.73 | ₹134,084.86 | ₹(0.00) | ₹134,084.86 | 79.74% | 79.74% |
| TOTAL (2025) | 347 | ₹773,260.99 | ₹157,678.40 | ₹615,583.34 | ₹336,185.52 | ₹279,397.82 | 79.60% | 36.14% |
Key Insights
- Revenue grows strongly in H2 — July to December show the highest sales (See Section 5).
- Gross margins remain high (~79%) across months and products (Sections 6 & 8).
- OPEX is the biggest pressure point — Marketing, Sales & Rent dominate expenses (Section 7).
- Branch performance varies — West-01 leads in Sales, while OP differs due to OPEX (Section 9).
- Waterfall reveals an operating loss when OPEX is fully allocated (Section 11).
Conclusion
The business shows strong revenues and excellent product margins, but high OPEX spending and allocation inconsistencies are the main risk. Optimising OPEX and fixing allocation logic are critical to restoring profitability.
Recommendations
- Reconcile OPEX allocation (Waterfall vs P&L matrix mismatch).
- Reduce OPEX — cut low-ROI Marketing & Sales spend; review Rent/IT costs.
- Focus on high-margin SKUs and review pricing/costs for underperforming items.
- Improve branch efficiency — replicate West-01 practices, reduce branch-level wastage.
- Refine Budgeting — add Actual/Budget variance and set realistic monthly targets.