KPI Variance Dashboard in Excel for Actual vs Budget

A monthly reporting pack answers two questions about the same number: how far the result sits from the plan, and which parts of the business moved. A pack built as a flat list of accounts answers neither question well, because the reader has to do the comparison on the page and the movement that matters is buried among lines that shifted by a rounding error.

A KPI variance dashboard puts the comparison first. Each row carries a KPI, the actual figure, the budget, a saved pre-close forecast, the variance in dollars and in percent, the prior month and year-to-date comparisons, a trend line and a flag that says whether the movement needs attention. The rows hold a stable order so a reader finds a line in the same place every month, and the flags are few enough that the reader starts with them.

Building one is a set of decisions made before the first formula. Which KPIs earn a row. How the sign convention works so that a cost overrun and a revenue shortfall both read as adverse. What size of variance is worth a flag. Where the data comes from on refresh, and how the dashboard proves it agrees with the ledger.

Choosing the KPI Set Before the Layout

Start with the set, because the layout follows from it. A starting set of six to ten financial KPIs keeps the pack manageable, and the set should stay stable enough that the same rows appear every month.

The core financial set covers the profit and loss, the balance sheet and cash. Revenue, gross margin percentage, operating expenses by department, EBITDA, the cash balance, cash conversion and debtor days answer most of what a board asks. Cash conversion needs a stated definition, and operating cash flow divided by EBITDA is one possible definition. State the period and treatment of interest and tax, and suppress the ratio when EBITDA is zero or too small for a useful comparison.

Two or three operating metrics sit alongside the financial set, and the choice follows the industry. A services business tracks billable utilisation and the average rate per hour. A retailer tracks transactions per store per day and average basket size. A manufacturer tracks units produced and scrap rate. A trades business tracks jobs completed and quote conversion.

The test for a KPI is whether a named person can change it. A metric with no owner becomes decoration on the page, and it still costs a refresh cycle every month.

The Dashboard Layout and the Columns That Earn Their Place

One row per KPI, one column per comparison, and no merged cells. The table below carries an illustrative worked month for September 2026, so the flags have something to talk about.

Three value columns give three comparisons. Actual against budget measures performance against the plan. Actual against the forecast issued before the period closed measures forecasting accuracy. A current forecast that already contains the actual result cannot provide that test. Budget against forecast shows how far the expectation has already moved from the plan, which is the comparison that prompts a conversation with a lender.

The remaining columns carry context. Prior month variance and year-to-date variance stop a reader reacting to a single month in isolation, and the year-to-date column matters most on cost lines, because an overspend that repeats is a run rate. The trend column holds a sparkline over the last twelve months of the KPI. Set common minimum and maximum axes only within comparable groups with the same unit and sensible magnitude. Dollars, ratios and days need separate groups. Individual scales show each KPI’s direction, but their slopes cannot be used to compare movement sizes across rows. Microsoft documents the sparkline axis options. The flag column holds the only judgement on the page.

A Sign Convention That Handles Revenue and Cost

The arithmetic problem in a variance dashboard is that favourable means different things on different lines. Revenue above budget is good. Operating expense above budget is bad. A dashboard showing the raw difference produces a column where a plus sign is good at the top and a plus sign is bad further down the page, and the reader carries the burden of remembering which is which.

First normalise source amounts so revenue and ordinary expenses are reported as positive values, as in the example. Reversals and credits reduce those balances. Applying a lower-is-better sign directly to expenses stored as negatives would reverse the intended result. ABS(Budget) fixes the denominator. Source signs still need to be normalised.

Carry an account type and a sign column. The account type reads Revenue, Cost, Margin, Days or Cash, and the sign column holds 1 where a higher figure is better and negative 1 where a lower figure is better. Revenue, gross profit, margin, EBITDA and the cash balance sit at 1. Operating expense, cost of goods, debtor days and any account where less is better sit at negative 1.

Every variance on the page is then multiplied by the sign, which gives one rule for the whole dashboard. A positive signed variance is favourable and a negative signed variance is adverse, wherever the line sits in the profit and loss. The sign column lives in the mapping table that links ledger accounts to KPI rows, so a new account inherits the convention the moment it is mapped.

Materiality Thresholds and the Three Test Formulas

Two materiality floors keep attention on meaningful movements.

Apply a floor in the KPI's own unit and a floor in percent of the base. The unit floor suits revenue, gross profit, opex, EBITDA and cash, and it stops a trivial movement on a small line raising a flag. The percentage floor stops a large dollar movement on a large base taking attention it has not earned, and it complements the unit floor. Both tests can be used for ratios and day counts, provided the units are defined consistently.

A variance raises a flag only when it clears both floors, so the harder of the two tests governs the outcome. With a unit floor of 10,000 and a percentage floor of 5%, revenue 36,000 above budget at 2.4% stays quiet, while a 52,000 cash shortfall at 10.0% flags. Ratio and day-count KPIs carry the unit floor in their own measure, which is half a percentage point for a margin and two days for a debtor count.

1. Variance in the KPI unit, signed so favourable is positive
   = (Actual - Budget) * Sign

2. Variance in percent, on a base that survives a zero or negative budget
   = IF(ABS(Budget) = 0, "n/a", IF(ABS(Budget) < MinBase, "n/a", (Actual - Budget) / ABS(Budget) * Sign))

3. Flag, with a separate review route for zero or small bases
   = IF(OR(ABS(Budget) = 0, ABS(Budget) < MinBase),
        IF(ABS(Actual - Budget) >= UnitFloor, "Review base", ""),
        IF(AND(ABS(Actual - Budget) >= UnitFloor,
               ABS(Actual - Budget) / ABS(Budget) >= PercentFloor),
           IF((Actual - Budget) * Sign > 0, "Favourable", "Adverse"), ""))

Use positive MinBase and UnitFloor values in each KPI’s own unit, with PercentFloor entered as a decimal such as 0.05. The outer IF routes zero and small budgets away from division in the flag as well as the displayed percentage. Review base needs its own amber formatting and an owner. A margin floor of half a percentage point is 0.005 when margin is stored as a decimal. The margin row is calculated from unrounded gross profit divided by revenue; displayed values are rounded. Prior-month and YTD comparisons are illustrative context and require their own period data.

Flags, Conditional Formatting and the Tie Check

Conditional formatting on the flag column uses formula-based rules rather than cell-value rules, so the colour follows the label the formula produced and survives a change to the thresholds.

Adverse flag:     =$K8="Adverse"                        red fill, dark red text
Favourable flag:  =$K8="Favourable"                     green fill, dark green text
Repeat adverse:   =AND($K8="Adverse",$I8<-0.05)         bold text, left border

The third rule catches the persistent problem, where a line is adverse in the month and adverse year to date as well, which tells the reader that the movement is a run rate. The references assume columns A:K follow the displayed table. Add the adverse red fill to the repeat rule as well as its bold text and border, then place it above the plain adverse rule with Stop If True. Guard the YTD percentage with the same base policy. The 5% repeat threshold is illustrative and can be set per KPI.

Dashboards lose trust when their totals stop agreeing with the ledger, so a tie check runs every month before the pack is issued. Reconcile additive actual measures to the ledger or bank source for the same period and scope. Reconcile budget to the approved budget version and forecast to the selected forecast snapshot. Check departments as well as totals, including unmapped and duplicate records. Do not sum overlapping KPI rows such as total opex and wages. Recompute ratios and day counts from their reconciled underlying balances; they cannot be added back to a trial balance.

The tie check proves the dashboard agrees with the ledger. The bridge proves the movements add up, and it is what turns a flag into a conversation. The example below explains the September EBITDA result with the figures from the KPI table.

Gross profit moved 11,000 on volume, price realisation and direct cost, operating expenses moved 18,000 adverse on wages and 9,000 favourable on other lines, and the net position leaves EBITDA 2,000 above budget. The wages row inside it is the flagged line, at 18,000 adverse and 12.0% of its budget.

Drill-Down From a Flagged Line to a Transaction

A flag shows the reader where to look. The drill-down shows them why. The path runs from the KPI row to the department that caused it, from the department to the account, and from the account to the individual transactions, with each step filtering the same fact table.

Department level, from the flagged KPI to the department that moved
=SUMIFS(Actuals[Amount],Actuals[KPI],$B$8,Actuals[Dept],$H$3,Actuals[Period],$C$2)

Account level, from the department to the account
=SUMIFS(Actuals[Amount],Actuals[Dept],$H$3,Actuals[Account],$B$18,Actuals[Period],$C$2)

Transaction level, every line behind the account
=FILTER(Actuals,(Actuals[Dept]=$H$3)*(Actuals[Account]=$B$18)*(Actuals[Period]=$C$2),"No matching transactions")

These examples use a separate drill-down sheet where B8 holds the selected KPI, H3 the department, B18 the account and C2 the period. Actuals is an actual-only worksheet table with transaction details and Amount. FILTER returns complete matching rows, including amounts, for reconciliation. Use a PivotTable or table filters where FILTER is unavailable.

A department panel beside the KPI table gives the first step at a glance, with one SUMIFS per department against the flagged KPI. The account and transaction steps sit on a second sheet, so the reader who wants detail moves one click away from the page the board sees.

Keep the same mappings, filters and reporting signs at every level, then reconcile the detail total to the selected KPI. If scenarios share one fact table, add a Scenario criterion to every SUMIFS and FILTER, together with a version criterion where snapshots coexist. Budget and forecast drill-downs lead to the relevant allocations or driver calculations; they may have no underlying transactions. Ratio KPIs need their numerator and denominator detail separately.

Refreshing the Dashboard in Power Query

The refresh decides whether the dashboard is trusted. A workbook refreshed by hand, with an analyst editing links and re-pasting exports, produces a monthly argument about the numbers and eventually gets abandoned.

Power Query moves the refresh into a defined pipeline. The ledger export and the payroll export arrive as queries. The month columns are unpivoted into a fact table of account, department, period and amount, so actuals and every comparison share one shape. The account mapping table is merged in, which is where the account type and the sign column arrive. A second query reads the budget file, and a third holds the driver assumptions behind the forecast.

Refreshing becomes one click with a deterministic result, because the same exports produce the same fact table every time. For these SUMIFS and FILTER examples, load the query output to a worksheet table named Actuals. A Data Model-only connection is not a worksheet range for these references. For larger models, use measures and PivotTables over the Data Model and rebuild the drill-down around that route. Check refresh errors and source totals before issuing the pack.

Mistakes That Make a Variance Dashboard Untrusted

• KPIs redefined each month. Changing a definition mid-year breaks every comparison on the page. Fix the set for the financial year, and record a definition change with a restated comparative.
• Inconsistent cost signs or small bases. Normalise expenses to the reporting convention before applying the direction sign. Use ABS(Budget) as the denominator, suppress percentages on zero or small bases and route material movements to review.
• Budget and forecast mixed in one column. A column labelled Budget that holds the latest forecast for the remaining months cannot be explained in a variance conversation. Keep each scenario in its own column and show the movement between them.
• A dashboard refreshed by hand. The refresh is the part of the process that fails under pressure, and it fails quietly. Put it in a query and make it one click.
• Flags nobody owns. A flag with no name against it becomes background noise within two months. Each flagged KPI row carries the person who will explain it.
• No tie check. A dashboard that cannot prove it agrees with the ledger gets audited in the meeting, in front of the board.

Keep KPI definitions, scenario versions and reporting signs stable so each month’s comparisons remain meaningful.

Related reading

Use variance analysis to investigate the movements behind the flags. Our guide to turning business KPIs into insights covers interpretation, while Excel dashboards with slicers explains interactive filtering.