Budget vs Actual Variance Analysis

Every management team wants the same thing at month end: a single table showing what was planned versus what actually happened, and exactly where the gap is. This is called variance analysis, and it is the core of every CFO dashboard, board pack, and quarterly business review.

Variance analysis is more nuanced than it sounds. Revenue can miss budget because fewer clients paid (volume variance) or because clients paid less on average (price/mix variance). A good variance report separates these drivers rather than showing a single "you missed by £X" number. This lesson walks through building variance analysis SQL step by step — from simple actuals aggregation through to year-over-year comparisons and account manager league tables.

Why Variance Analysis Matters

• Finance teams use it to explain the business to the board and investors
• Sales teams use it to understand which accounts or segments are underperforming
• Operations use it to identify months where revenue was strong but collection rate fell (billing vs. cash issue)
• Leadership uses it to decide whether shortfalls require corrective action or are explained by known factors (lost contract, seasonal dip, payment timing)

The same SQL pattern — actual vs. target with a CASE statement labelling the status — powers almost every KPI dashboard you'll ever build.

Dataset Context

-- invoices: id, client_id, issue_date, due_date, total_amount, status
-- payments: id, invoice_id, payment_date, amount
-- clients: id, name, industry, account_manager
-- Assume budget_targets table: month, category, target_revenue

Step 1 — Actual Revenue by Month and Industry

Before you can compare actuals to a budget, you need a clean actuals table. This query calculates total revenue collected (from the payments table, not just invoices) broken down by month and client industry segment. The distinction between "billed" and "collected" matters: an invoice issued in March may only be paid in May. For cash-basis reporting, you want the payment date; for accrual-basis, you want the invoice date.

This step uses only paid invoices (WHERE i.status = 'paid') to ensure we're measuring money that actually moved, not just invoices sent.

WITH actuals AS (
  SELECT
    DATE_TRUNC('month', i.issue_date) AS month,
    c.industry,
    SUM(p.amount) AS actual_revenue,
    COUNT(DISTINCT i.invoice_id) AS invoices_paid,
    COUNT(DISTINCT i.client_id) AS paying_clients
  FROM invoices i
  JOIN payments p ON p.invoice_id = i.invoice_id
  JOIN clients c ON c.client_id = i.client_id
  WHERE i.status = 'paid'
  GROUP BY DATE_TRUNC('month', i.issue_date), c.industry
)
SELECT * FROM actuals ORDER BY month, industry;

What This Returns

Step 2 — Month-over-Month Growth Rate

Once you have actuals by month, the next most useful metric is growth rate — how does this month compare to last month? This query uses the LAG() window function to bring the previous month's collected revenue forward, then calculates the percentage change.

It also includes a collection rate column (collected / billed) which is a completely different metric from growth. You can have strong growth in billed revenue while your collection rate is declining — meaning you're invoicing more but actually receiving less of it.

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', issue_date) AS month,
    SUM(total_amount) AS billed,
    SUM(CASE WHEN status = 'paid' THEN total_amount ELSE 0 END) AS collected
  FROM invoices
  GROUP BY DATE_TRUNC('month', issue_date)
),
with_growth AS (
  SELECT
    month,
    billed,
    collected,
    ROUND(collected::numeric / NULLIF(billed, 0) * 100, 1) AS collection_rate_pct,
    LAG(collected) OVER (ORDER BY month) AS prev_collected,
    ROUND(
      (collected - LAG(collected) OVER (ORDER BY month))::numeric
      / NULLIF(LAG(collected) OVER (ORDER BY month), 0) * 100, 1
    ) AS mom_growth_pct
  FROM monthly
)
SELECT * FROM with_growth ORDER BY month;

What This Returns