How to run a budget vs actual variance analysis with Copilot in Excel

Budget vs actual variance analysis is the monthly exercise in which a finance team compares what the business planned to earn and spend with what it recorded in the ledger, measures each difference in currency and in percent, decides which differences are large enough to explain, and writes the commentary that goes to budget owners and management. The arithmetic is simple. The work that takes time is the rest: classifying each variance as favourable or unfavourable with the right sign convention for revenue and cost lines, splitting revenue and cost of sales into price and volume effects, applying a materiality threshold consistently, and turning the numbers into explanations that a department head can confirm or correct.

Microsoft's support documentation for Copilot in Excel, checked on November 1, 2026, lists a Microsoft-created skill called variance-analysis among the accounting skills available to Copilot. According to that page, the skill compares actual results with budgets, forecasts or prior periods, explains differences by price, volume, mix, rate or timing, calculates dollar and percentage variances, creates waterfall and bridge charts, and drafts explanations of significant or unexplained changes for review. The same page states that the accounting skills are rolling out to the Microsoft Frontier program, so a team outside Frontier can run the same analysis with ordinary prompts in Copilot in Excel, while a team inside the program can call the skill by name.

The method below uses a small illustrative profit and loss statement, the prompts a controller or FP&A analyst would give Copilot, and the correct results computed independently, so the reader can check every number Copilot returns before any of it reaches a management pack. In the example, total operating expenses land exactly on budget while three individual lines miss by between 5% and 22%, a pattern that a summary-level review would not catch.

··········

WHAT A BUDGET VS ACTUAL VARIANCE ANALYSIS PRODUCES.

A complete monthly variance analysis has four outputs. The first is a line-by-line table with budget, actual, the variance in currency, the variance in percent of budget, and a favourable or unfavourable label. The second is a set of subtotals, usually gross profit and EBITDA or operating profit, with the same measures and with margins, because a revenue shortfall and a cost overrun can offset each other at the line level and still move the margin. The third is a decomposition of the largest lines into drivers: for revenue, the volume effect of selling more or fewer units at the budgeted price and the price effect of selling the actual units at a different price; for cost of sales, the volume effect and the unit-cost or rate effect. The fourth is the written commentary, limited to the variances that pass a materiality threshold the team sets in advance.

The sign convention is the most frequent source of error in manual and AI-assisted work alike. Variance is usually calculated as actual minus budget. For revenue and gross profit a positive variance is favourable; for costs a positive variance is unfavourable, because the business spent more than planned. A table that labels every positive number as favourable will misreport every cost overrun, and the mistake is easy to miss when the table looks tidy. Readers who want the management-accounting background on why cost variances are tracked and how they feed performance reviews will find it in the site's article on using variance analysis to control business costs.

The materiality threshold should be written down before Copilot is asked anything. A common form is a dual test: a line is flagged when the absolute variance reaches a currency amount or when the percentage variance reaches a set level. The dual test catches large lines with small percentages and small lines with large percentages, and it removes the temptation to adjust the threshold after seeing which lines would need an explanation.

··········

COPILOT IN EXCEL REQUIREMENTS FOR VARIANCE ANALYSIS.

Microsoft documents a set of requirements that determine whether Copilot can work on the variance workbook at all. The frequently asked questions page for Copilot in Excel lists the eligible licences, the supported platforms and modes, and two workbook conditions that affect finance files in particular: Copilot editing is only supported when calculation options are set to Automatic, and Strict Open XML Spreadsheet files are not supported, so the file should be saved as a standard .xlsx workbook. Organisations that require check-out before editing files in SharePoint should note that Copilot in Excel on Windows or Mac does not work for files from such a site, while Excel for the web still does.

........

Item	What Microsoft documents (checked November 1, 2026)
Eligible licences	Microsoft 365 Personal or Family with an AI credits plan, Microsoft 365 Premium, a commercial Microsoft Copilot subscription, or an eligible business or enterprise Microsoft 365 or Office 365 subscription
Platforms	Excel on Windows, Mac, the web, iPad, iPhone and Android
Edit, chat and plan modes	Available on Windows, Mac and the web
Calculation setting	Copilot editing requires calculation options set to Automatic
File format	Strict Open XML not supported; use .xlsx
SharePoint check-out sites	Not supported on Windows or Mac; Excel for the web works
variance-analysis skill	Accounting skills rolling out to the Microsoft Frontier program
Skill invocation	Type @ followed by the skill name in the prompt

........

The support pages consulted do not publish a row or cell limit for the data Copilot reads, so a team working with a full general ledger extract should test with its own file size rather than assume one. The format guidance for Copilot in Excel recommends naming the columns or ranges Copilot should work with, and it warns that Copilot makes direct changes to the workbook, with undo and earlier versions available to revert them. For a variance file that is shared with budget owners, the safer practice is to run Copilot on a copy or on a dedicated analysis sheet and keep the imported budget and actual figures on a locked source sheet.

Microsoft's October 29, 2026 announcement of the expanded finance capabilities adds two points that bear on control. In Excel, users can optionally create a chat-history record of what Copilot changed and why, and Copilot can summarise recent activity, show when changes were made and by whom, and reverse individual edits. The broader launch, including the Finance plugin for Cowork, is covered in the site's report on Microsoft's Copilot for finance with accounting skills in Excel.

··········

THE ILLUSTRATIVE BUDGET VS ACTUAL DATASET.

The example uses one month of a small product company, with figures in US dollars. The data is illustrative and was built for this exercise. It should sit in an Excel table, here named PnL_Oct, with one row per line item and four columns: Line, Type (Revenue or Cost), Budget and Actual. A second small table, Drivers_Oct, holds the unit data behind revenue and cost of goods sold.

........

Line	Type	Budget	Actual
Revenue	Revenue	500,000	498,200
Cost of goods sold	Cost	220,000	220,900
Salaries and benefits	Cost	120,000	126,500
Marketing	Cost	40,000	31,200
Rent	Cost	25,000	25,000
Software subscriptions	Cost	12,000	14,300

........

........

Driver	Budget	Actual
Units sold	10,000	9,400
Average selling price	50.00	53.00
Average unit cost	22.00	23.50

........

The materiality rule for the example is a dual test: a line is flagged for commentary when the absolute variance is at least $5,000 or the percentage variance is at least 10% of budget. The Type column carries the sign convention explicitly, which removes the need for Copilot to infer from the line name whether a positive variance is good or bad.

··········

PROMPTS FOR THE VARIANCE ANALYSIS IN COPILOT.

The prompts below are written for Copilot in Excel and state the table names, the formula for each measure, the sign convention and the threshold. Stating the rules in the prompt makes the output easier to check, because any deviation from the stated rule is an error rather than a judgement call. Teams with access to the Frontier accounting skills can start the first prompt with @variance-analysis; the rest of the wording stays the same.

Using the table PnL_Oct, add four columns: Variance = Actual - Budget,
Variance % = Variance / Budget, Fav/Unfav, and Flag.
For rows where Type = "Revenue", a positive variance is Favourable.
For rows where Type = "Cost", a positive variance is Unfavourable.
A zero variance is labelled "On budget".
Flag = "Yes" when ABS(Variance) >= 5000 or ABS(Variance %) >= 10%.
Use Excel formulas in the new columns, not typed values.

Below PnL_Oct, add Gross profit (Revenue - Cost of goods sold),
Total operating expenses (all Cost rows except Cost of goods sold) and
EBITDA (Gross profit - Total operating expenses) for Budget and Actual,
with Variance, Variance % and margin as a percentage of revenue.

Using Drivers_Oct, split the revenue variance into a volume effect
(Actual units - Budget units) x Budget price and a price effect
(Actual price - Budget price) x Actual units. Split the cost of goods sold
variance into a volume effect (Actual units - Budget units) x Budget unit cost
and a rate effect (Actual unit cost - Budget unit cost) x Actual units.
Show that each pair of effects adds up to the total line variance.

For each row with Flag = "Yes", draft a two-sentence explanation for the
budget owner. State the amount and percentage, and mark every cause as
"to be confirmed" unless it is supported by data in this workbook.

The last prompt is the control step. Copilot has no access to the reasons behind a payroll overrun or a delayed campaign unless those reasons are in the workbook or in connected content the user can access, so any cause it writes without that support is a hypothesis. Labelling it as such keeps the draft commentary from being forwarded as fact.

··········

EXPECTED RESULTS FOR THE BUDGET VS ACTUAL EXAMPLE.

The tables below are the correct answers, calculated outside Excel, against which Copilot's columns can be compared cell by cell. Variance is actual minus budget, and percentages are rounded to one decimal place.

........

Line	Variance	Variance %	Fav/Unfav	Flag
Revenue	-1,800	-0.4%	Unfavourable	No
Cost of goods sold	+900	+0.4%	Unfavourable	No
Salaries and benefits	+6,500	+5.4%	Unfavourable	Yes
Marketing	-8,800	-22.0%	Favourable	Yes
Rent	0	0.0%	On budget	No
Software subscriptions	+2,300	+19.2%	Unfavourable	Yes

........

Three lines pass the threshold, each for a different reason. Salaries and benefits is flagged on the amount test, marketing passes both tests, and software subscriptions is flagged only on the percentage test, with a variance of $2,300 that a currency-only threshold would have ignored.

........

Subtotal	Budget	Actual	Variance	Variance %	Margin budget	Margin actual
Gross profit	280,000	277,300	-2,700	-1.0%	56.0%	55.7%
Total operating expenses	197,000	197,000	0	0.0%	n/a	n/a
EBITDA	83,000	80,300	-2,700	-3.3%	16.6%	16.1%

........

Total operating expenses are exactly on budget at $197,000 because the $8,800 marketing underspend offsets the $6,500 salaries overrun and the $2,300 software overrun. The whole EBITDA shortfall of $2,700 therefore comes from gross profit. A review that stopped at the operating expense subtotal would conclude that cost control was on plan, while the line detail shows two overruns funded by a marketing saving that may only be a timing difference.

........

Driver effect	Calculation	Amount
Revenue volume effect	(9,400 - 10,000) x 50.00	-30,000
Revenue price effect	(53.00 - 50.00) x 9,400	+28,200
Total revenue variance	Sum of the two effects	-1,800
COGS volume effect	(9,400 - 10,000) x 22.00	-13,200
COGS rate effect	(23.50 - 22.00) x 9,400	+14,100
Total COGS variance	Sum of the two effects	+900

........

The decomposition changes the story behind a revenue line that looked almost on budget. Units fell 6% short of plan, which cost $30,000 of revenue at budget prices, and a $3.00 higher average price recovered $28,200 of it. On the cost side, lower volume reduced cost of goods sold by $13,200, but a $1.50 higher unit cost added $14,100. In gross profit terms the three effects are a volume effect of -$16,800, a price effect of +$28,200 and a unit-cost effect of -$14,100, which add up to the -$2,700 gross profit variance. When Copilot's bridge or waterfall chart does not reconcile to these figures, the chart is wrong, regardless of how clean it looks.

··········

WHERE COPILOT VARIANCE OUTPUT NEEDS CHECKING.

Four checks catch most errors in AI-assisted variance work. The first is the sign test: every cost line with a positive variance must be labelled unfavourable, and the rent line, with no variance, must not be labelled at all. The second is the reconciliation test: the sum of the line variances for operating expenses must equal the subtotal variance, and the volume and price effects must add up to each line's total variance. The third is the formula test: the new columns should contain formulas that reference the table, such as =[@Actual]-[@Budget] for the variance and =IF([@Budget]=0,"",[@Variance]/[@Budget]) for the percentage, so that next month's data recalculates without a new prompt. Typed values in those columns mean the analysis cannot be rolled forward and should be rebuilt. The fourth is the threshold test: each Yes in the Flag column should be traceable to one of the two stated conditions.

Percentage variances on lines with a zero or very small budget need a separate rule. Dividing by zero returns an error, and dividing by a small budget produces percentages of several hundred percent that dominate a chart without carrying financial weight. The prompt should say how such lines are treated, for example by reporting the currency variance only.

Commentary needs the closest review. Microsoft's support page for the skills states that Copilot does not provide accounting, tax, audit or financial advice or professional determinations, that AI output might be inaccurate or incomplete, that users should review and verify all output, including calculations and account mappings, before relying on it or sharing financial information, and that Copilot in Excel does not replace internal controls, review or approval processes. In practice, the explanations for the salaries, marketing and software variances must come from the budget owners. Copilot can draft the sentence structure and insert the correct figures; the cause, and the decision on whether the marketing saving is permanent or a timing difference that will reverse next month, stays with the people who own the budget.

The same discipline applies when the variance analysis feeds a forward-looking view. The site's guide on building a 13-week cash flow forecast in Excel with Copilot and Claude uses the same approach of stating every rule in the prompt and checking the output against independently computed figures.

··········

DATA HANDLING FOR VARIANCE FILES IN COPILOT.

Budget and actual files contain payroll totals, supplier spend and margin information, so the data-handling terms should be checked before the first prompt. For work or school accounts, Microsoft directs users to the Microsoft Copilot data, privacy and security documentation on Microsoft Learn. In its October 29, 2026 finance announcement, Microsoft states that prompts and responses are processed within the Microsoft 365 service boundary with enterprise data protection, that Copilot works only with content each user can access and honours existing permissions and sensitivity labels, and that it does not use prompts, responses or documents to train the underlying foundation models. Users signed in with personal accounts are covered by a separate support article for Copilot in Microsoft 365 apps for home, and a finance team should not run company budget files through a personal subscription.

Permissions also shape the analysis itself. If the person running Copilot cannot open the payroll workbook, Copilot cannot use it to explain the salaries variance, and the draft commentary will lack that context. That is the intended behaviour, and it is one more reason the explanations stay with budget owners. Broader instructions on Copilot's modes and prompting in Excel are collected in the site's article on how to use Copilot in Excel.

··········

BUDGET VS ACTUAL VARIANCE ANALYSIS WITH COPILOT: PRACTICAL LIMITS.

Copilot in Excel can produce the full mechanical layer of a monthly variance analysis: variance columns built as formulas, favourable and unfavourable labels driven by a type column, subtotals with margins, price and volume decomposition, a bridge chart and draft commentary. The variance-analysis skill packages those steps for users in the Microsoft Frontier program, and ordinary prompts reach the same result elsewhere, provided the prompt states the sign convention, the formulas and the materiality rule.

What Microsoft's documentation does not settle is equally specific. The support pages consulted do not publish a size limit for the data Copilot reads, the accounting skills are still rolling out, and Microsoft's own terms place every calculation and every explanation under human review. For the illustrative month, the checks are concrete: a $2,700 EBITDA shortfall, operating expenses exactly on budget, three flagged lines, and a revenue line whose -$1,800 variance hides a -$30,000 volume effect and a +$28,200 price effect. A Copilot output that reproduces those figures with formulas, and leaves every cause marked for confirmation, is ready for the budget owners.

··········

FOLLOW US FOR MORE.

·····

DATA STUDIOS

·····

datastudios.org