Excel Lesson 49 – Finance Dashboard | Dataplexa

Lesson 49 · Final Projects Project

Finance Dashboard Project

Finance dashboards live or die on one question: are we on budget? Everything else — the P&L summary, the expense breakdown, the trend line — is context for that answer. This project builds a complete Finance Performance Dashboard: a P&L summary pulling from a multi-year actuals table, a budget vs actuals variance analysis with conditional formatting, an expense category breakdown chart, and a year and department slicer that updates everything simultaneously. The data model uses Power Pivot with DAX measures for variance, variance percentage, and a running YTD total. Every component is wired together so a single slicer click gives you the full picture for any department or time period in under a second.

Dashboard Blueprint

Finance Dashboard — Layout Preview

£4.82M

Total Revenue

£3.21M

Total Costs

£1.61M

Net Profit

33.4%

Profit Margin

Budget vs Actuals by Month

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Budget

Under budget

Over budget

Expense Breakdown

Payroll

48%

Property

16%

Marketing

13%

Tech

10%

Other

13%

P&L Summary — Budget vs Actuals vs Variance

Line Item	Budget	Actual	Variance	Var %
Revenue	£4.60M	£4.82M	+£220K	+4.8%
Total Costs	£3.00M	£3.21M	-£210K	-7.0%
Net Profit	£1.60M	£1.61M	+£10K	+0.6%
Profit Margin	34.8%	33.4%	-1.4pp	—

Year:

2025 2024 2023

Dept:

All Sales Ops IT

Dark theme · KPI strip · grouped column chart · expense breakdown · P&L table · Year + Department slicers

The Data Model

Table 1: Actuals (fact table — one row per cost/revenue line per month)
  Columns: LineID, Year, Month, MonthNum, Department, Category,
           LineItem, Type (Revenue / Cost), BudgetAmount, ActualAmount

Table 2: DimDate (Date dimension — same as Lesson 48)
  Columns: Date, Year, Month, MonthNum, Quarter, MonthYear

Table 3: DimDept (Department lookup — same as Lesson 48)
  Columns: Department, DeptCode, DeptHead, CostCentre, RegionGroup

Relationships in Power Pivot:
  Actuals[Year]       → DimDate[Year]           (many-to-one on Year)
  Actuals[Department] → DimDept[Department]     (many-to-one)

Sheet structure:
  Raw_Actuals  — CSV loaded via Power Query
  DimDate      — date dimension
  DimDept      — department lookup
  PT_Helper    — all five PivotTables (hidden before sharing)
  Dashboard    — the finished output

DAX Measures — Finance Metric Library

Create a measures table named "Finance KPIs":

-- Core revenue and cost --

Total Revenue =
  CALCULATE(SUM(Actuals[ActualAmount]), Actuals[Type] = "Revenue")

Total Costs =
  CALCULATE(SUM(Actuals[ActualAmount]), Actuals[Type] = "Cost")

Net Profit =
  [Total Revenue] - [Total Costs]

Profit Margin =
  DIVIDE([Net Profit], [Total Revenue], 0)

-- Budget comparisons --

Budget Revenue =
  CALCULATE(SUM(Actuals[BudgetAmount]), Actuals[Type] = "Revenue")

Budget Costs =
  CALCULATE(SUM(Actuals[BudgetAmount]), Actuals[Type] = "Cost")

Budget Profit =
  [Budget Revenue] - [Budget Costs]

-- Variance (Actual minus Budget) --
-- Revenue: positive = favourable · Costs: positive = adverse --

Revenue Variance =
  [Total Revenue] - [Budget Revenue]

Revenue Variance % =
  DIVIDE([Revenue Variance], [Budget Revenue], 0)

Cost Variance =
  [Total Costs] - [Budget Costs]

Cost Variance % =
  DIVIDE([Cost Variance], [Budget Costs], 0)

Profit Variance =
  [Net Profit] - [Budget Profit]

Profit Variance % =
  DIVIDE([Profit Variance], [Budget Profit], 0)

-- YTD running total --

YTD Revenue =
  CALCULATE(
    [Total Revenue],
    FILTER(
      ALL(DimDate[MonthNum]),
      DimDate[MonthNum] <= MAX(DimDate[MonthNum])
    )
  )

-- Prior year --

Prior Year Revenue =
  CALCULATE(
    [Total Revenue],
    FILTER(ALL(DimDate[Year]),     DimDate[Year]     = MAX(DimDate[Year]) - 1),
    FILTER(ALL(DimDate[MonthNum]), DimDate[MonthNum] <= MAX(DimDate[MonthNum]))
  )

YoY Revenue Growth =
  DIVIDE([Total Revenue] - [Prior Year Revenue], [Prior Year Revenue], 0)

-- Expense category share --

Category Expense Share =
  DIVIDE(
    CALCULATE(SUM(Actuals[ActualAmount]), Actuals[Type] = "Cost"),
    CALCULATE(SUM(Actuals[ActualAmount]), ALL(Actuals[Category]), Actuals[Type] = "Cost"),
    0
  )

Total Revenue: £4.82M · Total Costs: £3.21M · Net Profit: £1.61M · Profit Margin: 33.4% · Revenue Variance: +£220K (+4.8%) · Cost Variance: +£210K (+7.0%) · YoY Revenue Growth: +8.3%

Variance Sign Convention — Getting It Right

Variance in finance has a sign convention that trips up almost every first-time dashboard builder. For revenue, a positive variance is good — you earned more than planned. For costs, a positive variance is bad — you spent more than budgeted. Your conditional formatting must reflect this asymmetry, otherwise a cost overrun gets highlighted green and an analyst raises an eyebrow.

Variance Sign Convention — Finance Standard

Line Type	Formula	Positive means	Colour	Example
Revenue	Actual − Budget	Favourable ✓	Green	+£220K = earned more than planned
Revenue	Actual − Budget	Adverse ✗	Red	−£80K = fell short of target
Cost	Actual − Budget	Adverse ✗	Red	+£210K = overspent against budget
Cost	Actual − Budget	Favourable ✓	Green	−£50K = came in under budget

Conditional formatting rules must be written separately for Revenue rows and Cost rows

PivotTables on the PT_Helper Sheet

PivotTable 1: KPI Cards source
  Rows:    (none)
  Values:  Total Revenue, Total Costs, Net Profit, Profit Margin
  Purpose: Single row; GETPIVOTDATA feeds KPI cards on Dashboard

PivotTable 2: Budget vs Actuals by Month (grouped column chart)
  Rows:    MonthNum, Month (DimDate — sort Month by MonthNum)
  Values:  Budget Revenue, Total Revenue
  Filters: Year slicer

PivotTable 3: Expense Breakdown (horizontal bar chart)
  Rows:    Category (filter Actuals[Type] = "Cost")
  Values:  Total Costs, Category Expense Share
  Sort:    Total Costs descending

PivotTable 4: P&L Summary Table (displayed directly on Dashboard)
  Rows:    LineItem, Type
  Values:  Budget Revenue, Total Revenue, Revenue Variance, Revenue Variance %,
           Budget Costs, Total Costs, Cost Variance, Cost Variance %
  Layout:  Tabular, subtotals off, grand totals off
  Note:    Lock the worksheet after setup so users cannot collapse rows

PivotTable 5: YTD Revenue Line Chart
  Rows:    MonthNum, Month
  Values:  YTD Revenue, Prior Year Revenue
  Filters: Year
  Purpose: Current vs prior year running total line chart

The Colour-Coded Budget Chart — No VBA Required

A standard grouped column chart cannot colour individual bars conditionally based on whether they are above or below budget. The workaround is to split the Actual series into two helper measures and stack them — one for months where actual is under budget (green), one for months where actual is over budget (red).

Actual Under Budget =
  IF([Total Revenue] <= [Budget Revenue], [Total Revenue], 0)

Actual Over Budget =
  IF([Total Revenue] > [Budget Revenue], [Total Revenue], 0)

Chart setup:
  Three series: Budget Revenue · Actual Under Budget · Actual Over Budget
  Budget series:               blue (#1d4ed8), 50% transparency
  Actual Under Budget series:  green (#15803d), full opacity
  Actual Over Budget series:   red (#dc2626), full opacity

  Under budget month → Under Budget bar visible, Over Budget = 0 (hidden)
  Over budget month  → Over Budget bar visible, Under Budget = 0 (hidden)

Result: one visible Actual bar per month — green or red based on budget performance

Jan–Mar: green · Apr–May: red (over budget) · Jun–Jul: green · Sep: red · Oct–Dec: green — matches the dashboard preview chart above

The YTD Running Total — How It Works

YTD Revenue =
  CALCULATE(
    [Total Revenue],
    FILTER(
      ALL(DimDate[MonthNum]),
      DimDate[MonthNum] <= MAX(DimDate[MonthNum])
    )
  )

How it evaluates month by month:
  Jan (MonthNum=1):  MAX=1 → sums Jan only        → £320K
  Feb (MonthNum=2):  MAX=2 → sums Jan+Feb          → £670K
  Mar (MonthNum=3):  MAX=3 → sums Jan+Feb+Mar      → £1.08M
  ...continues cumulatively through December

ALL() is critical:
  Without ALL(), the existing filter context limits DimDate[MonthNum] to the
  current row's month only — the cumulative sum never builds across earlier months.

Prior year YTD for comparison:
  Prior Year Revenue =
    CALCULATE(
      [Total Revenue],
      FILTER(ALL(DimDate[Year]),     DimDate[Year]     = MAX(DimDate[Year]) - 1),
      FILTER(ALL(DimDate[MonthNum]), DimDate[MonthNum] <= MAX(DimDate[MonthNum]))
    )

💡 Teacher's Note

Three things determine whether a finance dashboard gets used or ignored. First, the variance sign convention — get it wrong and you will hear about it immediately from anyone with a finance background. Second, the P&L table layout — Finance directors read P&L reports in a specific order: Revenue, then Gross Profit, then Operating Costs, then EBITDA, then Net Profit. Match that structure and use Tabular layout in the PivotTable so it reads like a real P&L. Third, slicer response time — if the dashboard takes more than two seconds to update, people will stop using it. Keep the data model lean: only load the columns you need, and push calculated columns into Power Query rather than Power Pivot wherever possible.

🠐 Practice

Q1. The Cost Variance for Total Costs is +£210K (actuals exceeded budget). The conditional formatting colours this red. Write the rule condition and explain why a positive cost variance is coloured red rather than green.

Q2. The YTD Revenue measure uses ALL(DimDate[MonthNum]) inside the FILTER. What breaks if you remove the ALL() and write FILTER(DimDate[MonthNum], ...) instead?

Q3. To create a colour-coded budget chart without VBA, the lesson splits the Actual series into two helper measures. Name them and describe what each contains.

Next up — The Excel Capstone Project, where you receive a real-world design brief and build a complete workbook from scratch using every skill from across this course — Power Query, Power Pivot, DAX, dashboards, and automation.

← Previous Course Index Next →

Excel Course