Role Playbooks3 min read

Use Copilot in Excel to Explain a Budget Variance

Copilot can surface trends and outliers. Finance still needs a method that separates arithmetic, business drivers and hypotheses before an explanation reaches management.

Bokili EditorialPublished August 12, 2026· Verified August 12, 2026

A budget-to-actual variance split into volume, price, mix and timing drivers before human review

A budget variance is a number. A variance explanation is a causal claim. Copilot in Excel can help find trends, outliers, formulas, charts and PivotTables, but a plausible paragraph about why actuals missed budget is not evidence that the cause has been established.

The safest finance workflow separates three layers: reproduce the gap, decompose it into measurable drivers, and label anything that still needs business confirmation. The AI accelerates the analysis; the workbook remains the auditable record.

Never start with “Why did we miss budget?”

Begin with calculations and slices. Ask for a narrative only after the drivers have been quantified.

Use the DRIVER method

DRIVER

Define

State the metric, period, comparison baseline, currency and materiality threshold.

Reconcile

Recalculate budget, actual and variance from the underlying rows.

Isolate

Split the gap across relevant dimensions such as product, region, channel or cost centre.

Validate

Test the leading drivers with formulas, filters or PivotTables.

Explain

Separate measured facts from hypotheses that require an operational owner.

Review

Check totals, signs, units and wording before the narrative is circulated.

Prepare the workbook before prompting

Analysis-ready data

One header row with unambiguous column names.
Consistent dates, currencies, units and signs.
Separate columns for budget and actual values.
Relevant dimensions such as product, region and channel.
No hidden subtotal rows mixed with transactional data.
A saved copy or version history before direct edits.

Microsoft states that Copilot in Excel uses editable workbook features such as tables, formulas, charts and PivotTables. It also recommends naming the columns to analyse because specific questions produce more useful results. Use that precision deliberately.

Ask for analysis in stages

A reviewable variance workflow

1
Reproduce the headline
Ask Copilot to calculate budget, actual, absolute variance and percentage variance for the defined period.
2
Rank contributions
Request a PivotTable or table showing how much each chosen dimension contributes to the total gap.
3
Test the top drivers
Drill into the largest contributors and compare price, volume, mix or timing where the data supports those measures.
4
Flag unexplained residuals
Calculate how much of the total variance remains outside the identified drivers.
5
Draft a two-layer narrative
Write measured findings first and operational hypotheses second, with named follow-up questions.

Reading is a start. Practice makes it stick.

Start learning

Worked example: revenue is €240k below budget

	Measured in workbook	Needs business confirmation
Volume	€150k adverse, concentrated in two regions	Was demand lower, or were orders delayed?
Price	€40k adverse from discount variance	Were discounts planned, negotiated or miscoded?
Mix	€30k adverse from lower premium-product share	Did customer preference change?
Timing	€20k adverse from invoices posted after period end	Is this a temporary cut-off effect?

The table supports a disciplined management message: the workbook explains the size and location of the gap; commercial and accounting owners explain the underlying events. Copilot should not merge those two kinds of knowledge.

A staged prompt

Investigate before narrating

Using columns Period, Region, Product, Units, Net Price, Budget Revenue and Actual Revenue: (1) reconcile total budget, actual and variance for Q2; (2) create a PivotTable ranking each region and product by contribution to the total variance; (3) test volume, price and mix effects where the columns support them; (4) show any unexplained residual; (5) list questions for business owners. Do not infer causes that are not present in the workbook.

Measured finding: two regions account for 62% of the adverse variance. Hypothesis to confirm: order timing may explain part of the gap. Required evidence: open-order report and invoice cut-off review.

Request tables and calculations before prose, and keep hypotheses visibly separate.

Review the result like finance work

Before it reaches management

Totals reconcile to the approved source.
Favourable and adverse signs are consistent.
Percentages use the correct denominator.
No correlation is described as a cause.
Hypotheses have owners and required evidence.
The narrative states the unexplained residual.

Explain one variance

Copy a small, non-sensitive budget-versus-actual table into a test workbook.
Define one metric, period and materiality threshold.
Ask Copilot to reconcile and rank contributions.
Verify the largest contribution with a formula or PivotTable.
Write one fact and one clearly labelled hypothesis.

The practical takeaway

Copilot can shorten the path from rows to candidate insights. Finance creates value by preserving the path from headline to calculation to business explanation. Use DRIVER to make that path visible, and practise each step until prompting, checking and communicating become one controlled workflow. Bokili’s short missions are designed for exactly that kind of repeated workplace judgement.