Project: Financial KPI Dashboard
Build a financial dashboard for tracking revenue, expenses, profit margins, and budget variance.
Project Overview
| Aspect | Details |
|---|---|
| Objective | Monitor financial health, track budgets, analyze profitability |
| Audience | CFO, finance team, department heads |
| Data Sources | GL transactions, budgets, forecasts, cost centers |
| Key Metrics | Revenue, COGS, Gross Profit, Net Profit, Budget Variance |
| Refresh | Daily or weekly |
Dashboard Pages
Page 1: Financial Summary • KPI Cards: Revenue, Gross Profit, Net Profit, Profit Margin % • Revenue vs Budget (combination chart: bars + line) • Monthly P&L waterfall chart • YTD vs Prior YTD comparison • Expense breakdown (treemap)
Page 2: Revenue Analysis • Revenue by Product/Service line (stacked column) • Revenue by Customer Segment • Monthly revenue trend with forecast line • Top 10 customers by revenue (table with sparklines) • Revenue growth rate (line chart)
Page 3: Expense & Budget Tracking • Budget vs Actual by Department (clustered bar) • Variance analysis (positive/negative indicators) • Expense categories breakdown (donut) • Monthly expense trend • Department cost center drill-through
Page 4: Cash Flow & Forecasting • Cash flow waterfall (inflows and outflows) • Accounts receivable aging (stacked bar) • Forecast vs Actual trend • Working capital metrics
Key DAX Measures
Revenue = SUM(Financials[Revenue])
COGS = SUM(Financials[CostOfGoodsSold])
Gross Profit = [Revenue] - [COGS]
Gross Margin % = DIVIDE([Gross Profit], [Revenue])
Net Profit = [Gross Profit] - SUM(Financials[OperatingExpenses])
Net Margin % = DIVIDE([Net Profit], [Revenue])
Budget Variance =
VAR Actual = [Revenue]
VAR Budget = SUM(Budget[Amount])
RETURN Actual - Budget
Budget Variance % = DIVIDE([Budget Variance], SUM(Budget[Amount]))
YTD Revenue = TOTALYTD([Revenue], Dates[Date])
Prior YTD Revenue =
CALCULATE(
TOTALYTD([Revenue], Dates[Date]),
SAMEPERIODLASTYEAR(Dates[Date])
)Data Model
Dates ─── Financials (Fact) ─── Accounts (Dimension)
─── Departments (Dimension)
─── CostCenters (Dimension)
─── Budget (Fact)
─── Forecast (Fact)Features to Implement
• ✅ Waterfall chart for P&L breakdown • ✅ Budget vs Actual variance with conditional formatting • ✅ YTD calculations using time intelligence • ✅ Drill-through from department summary to detail • ✅ Bookmarks for monthly/quarterly/yearly views • ✅ What-if parameters for scenario analysis • ✅ Report page tooltip showing cost center details • ✅ Export to PDF for board presentations
Skills Applied
Financial DAX measures, time intelligence (YTD, prior year), waterfall charts, what-if parameters, bookmarks, conditional formatting