Variance Analysis in Excel — Free Template & Step-by-Step Guide

Variance analysis is the engine behind every management accounts pack, board report, and FP&A review. This tutorial walks through building a complete variance analysis model in Excel from scratch: budget vs actual, month-over-month, year-over-year, and the full price-volume-mix decomposition. No add-ins, no macros. Every formula explained.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is variance analysis?

Variance analysis is the process of comparing a planned (budget or prior period) figure against an actual result, quantifying the difference, and diagnosing why that gap exists. It is one of the most fundamental techniques in financial planning and analysis (FP&A), management accounting, and operations management.

At its simplest, a variance is just subtraction: Actual minus Budget. But the discipline goes much further than arithmetic. Good variance analysis answers three questions: how large is the gap? (magnitude), is it good or bad? (direction and sign convention), and what caused it? (decomposition).

The sign convention is the first thing that trips people up. In revenue contexts, a positive variance — actual exceeds budget — is favourable (F). In cost contexts, a positive variance — actual costs exceed budget — is adverse (A). Many Excel models get this wrong because they use a single conditional formatting rule across the whole P&L without accounting for the direction flip between revenue and cost lines.

There are four main types of variance you will encounter in finance and operations work:

• Budget vs Actual variance — the most common type. Compares this period’s actual result against the budget set at the start of the year. Used in management accounts packs, board reporting, and departmental reviews.
• Period-over-period variance — compares this period against the prior period (month-on-month, MoM) or the same period last year (year-on-year, YoY). Useful when a budget is unavailable or when seasonality makes budget comparison less meaningful.
• Price variance — isolates the revenue or cost impact of selling (or buying) at a different price than planned, holding volume constant. Critical in commercial and procurement reviews.
• Volume variance — isolates the impact of selling (or using) a different quantity than planned, holding price constant. Reveals whether a revenue shortfall is a commercial problem (price) or a capacity/demand problem (volume).
• Mix variance — the most advanced type. When you sell a different combination of products than planned, your average revenue per unit shifts even if individual prices and total volumes are on target. Mix variance captures this effect.

In finance contexts, variance analysis typically operates at P&L line level: revenue, gross margin, operating expenditure, EBITDA. The audience is the CFO, board, or budget holders. In operations contexts, variance analysis goes deeper: materials price variances, labour efficiency variances, overhead absorption variances. The audience is plant managers and procurement teams.

This tutorial focuses on the finance and commercial use case — the kind of variance model you would build for a monthly management accounts pack or a revenue bridge presentation. The formulas are identical in both contexts; only the line item labels change.

Excel remains the tool of choice for variance analysis in most organisations below enterprise scale. It is flexible enough to handle non-standard P&L structures, quick to update with monthly actuals, and easy to share without specialist software. The main limitations — manual data entry, formula fragility at scale, and limited drill-through capability — are covered in the final section.

Types of variance covered in this tutorial

1. Budget vs Actual variance

The simplest and most common form. You have a budget (set at the start of the year, quarter, or month) and you compare it to what actually happened. The output is an absolute variance (£ or $) and a percentage variance. This is the backbone of monthly management accounts.

Example: Revenue budgeted at £500,000, actual £473,000. Variance = −£27,000 (adverse, 5.4% below budget).

2. Period-over-period variance (MoM, YoY)

Instead of comparing against a forward-looking budget, you compare against a historical period. Month-on-month (MoM) reveals recent trend changes. Year-on-year (YoY) strips out seasonality by comparing the same calendar period. Both use the same formula; only the reference column changes.

YoY is particularly important for businesses with strong seasonality (retail, hospitality, travel) where comparing November to October is meaningless but comparing November to November last year is very meaningful.

3. Price-Volume-Mix variance (3-way split)

The most powerful decomposition for revenue analysis. When revenue is below budget, is it because you charged less, sold less, or sold a less profitable mix? The three-way split answers that question precisely. The three components must sum to the total revenue variance — this acts as a built-in reconciliation check. Covered in full detail in the Price-Volume-Mix section below.

1.Step 1 — Set up the data structure

Open a new Excel workbook and create a sheet named Variance Report. Set up the following column structure starting at column B (leave column A for row labels):

Add a header row at row 1 with column labels. Put the period label (e.g. May 2026) in cell B1. Freeze row 1 and column A (View → Freeze Panes → Freeze First Row, then repeat for the column). This keeps labels visible as you scroll across multi-month reports.

If you are building a multi-month report, repeat the four columns (Actual, Budget, Variance, Variance %) for each month. A typical full-year report will have columns B through W (4 columns × 6 months, or adjust for your reporting period). Use a named range or structured table to make the formulas easier to read.

2.Step 2 — Calculate absolute variance

In column D (Variance), enter the following formula in D2 (the Revenue row):

This is Actual minus Budget. Copy this formula down to all P&L line rows. Do not hard-code any values — all variance cells should be formula-driven so the model updates automatically when you paste in new actuals each month.

The sign interpretation depends on the line item type:

Many analysts add a helper column that appends “F” or “A” to the variance using an IF formula. For a revenue line in D2:

This displays the variance as 27,000 A or 8,500 F — a format common in UK management accounts. For cost lines, flip the condition: IF(D2<=0," F"," A").

3.Step 3 — Calculate percentage variance

In column E (Variance %), enter in E2:

Always wrap the denominator in ABS(). This is critical. If the budget value is negative (e.g. a budgeted loss of −£50,000), dividing by a negative number reverses the sign of the percentage, making a favourable outcome look adverse. ABS() prevents this.

Format column E as a percentage with one decimal place (e.g. 5.4%). If the budget figure could ever be zero (a line item with zero budget that received actual spend), protect against the divide-by-zero error:

Alternatively, use IFERROR to suppress the error silently:

The IFERROR version is cleaner for a finished report but hides potential data quality issues. In a working model, IF(C2=0,"N/A",... is more transparent because it flags zero-budget lines explicitly.

4.Step 4 — Add conditional formatting

Conditional formatting is what transforms a variance column from a wall of numbers into something a reader can parse in seconds. The challenge in a P&L model is that the colour logic is opposite for revenue and cost lines.

For revenue and profit lines (rows 2, 4, 5 in our example)

Select the Variance cells for these rows (e.g. D2, D4, D5). Go to Home → Conditional Formatting → New Rule → Format only cells that contain. Set up two rules:

• Cell value greater than 0 → Green fill (#d4edda), dark green text (#155724)
• Cell value less than 0 → Red fill (#f8d7da), dark red text (#721c24)

For cost lines (row 3 in our example)

Select the Variance cells for cost rows (e.g. D3). Apply the same rules but inverted:

• Cell value less than or equal to 0 → Green fill (costs came in under budget)
• Cell value greater than 0 → Red fill (costs came in over budget)

Apply the same conditional formatting rules to the Variance % column (E) using the same logic.

5.Step 5 — Build a variance waterfall chart

A waterfall (bridge) chart is the standard way to visualise how individual variance components add up to a total. It shows a starting value (e.g. Budget EBITDA), then a series of positive and negative bars representing each line item’s contribution, ending at Actual EBITDA.

Using Excel 2016+ built-in waterfall

Excel 2016 and Microsoft 365 include a native Waterfall chart type. Set up a two-column helper table with the variance components:

Select both columns, go to Insert → Charts → Waterfall. Right-click the first bar (Budget EBITDA) and select Set as total. Do the same for the last bar (Actual EBITDA). Excel will render the intermediate bars as floating segments above the baseline.

Colour coding the waterfall

Double-click any bar to open the Format Data Point pane. Set positive variance bars to green (#22c55e) and negative variance bars to red (#ef4444). Keep the total bars in your brand colour or neutral grey. This makes the bridge chart immediately interpretable: how much green vs red, and which line items drove the gap.

For Excel 2013 or earlier

Build a stacked bar chart manually. You need three series: an invisible base, a positive variance series, and a negative variance series. The base series floats each bar to the correct position; format it with no fill and no border. This is more work but produces the same result.

6.Step 6 — Add period-over-period comparison columns

Alongside your budget vs actual view, most management reports also include a prior period comparison. Add a second block of columns to the right of your budget vs actual section:

The MoM variance formula in H2:

The MoM variance % formula in I2:

The YoY variance in K2:

The YoY variance % in L2:

Apply the same conditional formatting rules from Step 4 to these new variance columns. For a multi-month report, the Current Month and Prior Month columns reference the relevant monthly actuals columns rather than fixed values — use INDEX/MATCH or OFFSET to pull the correct month dynamically based on a period selector.

Price-Volume-Mix variance (advanced)

Price-Volume-Mix (PVM) analysis is the standard decomposition used by FP&A teams to explain revenue variances to commercial leadership. When revenue is £27,000 below budget, management wants to know: did we charge less per unit (price), sell fewer units (volume), or sell a worse mix of products (mix)? PVM answers this precisely.

You need a product-level breakdown with four fields per product: Actual Price, Budget Price, Actual Volume, and Budget Volume.

Price variance formula

Price variance measures the revenue impact of selling at a different price, holding volume at actuals (so we are asking: “how much more/less revenue did the price change alone generate?”).

In Excel, with Actual Price in B2, Budget Price in C2, Actual Volume in D2:

For Product A: (48.50 − 50.00) × 4,200 = −£6,300 (adverse — sold at a lower price than planned)

Volume variance formula

Volume variance measures the revenue impact of selling a different total quantity, holding price at budget rates. We hold price at budget because we want to isolate volume as the only variable.

In Excel, with Budget Price in C2, Actual Volume in D2, Budget Volume in E2:

For Product A: (4,200 − 4,000) × 50.00 = +£10,000 (favourable — sold more units than planned)

Mix variance formula

Mix variance is the residual. It captures the revenue impact of selling a different proportion of each product within the total volume. Even if total volume and individual prices are exactly on budget, shifting customers from high-price products to low-price products depresses average revenue per unit — and that impact is mix variance.

Where Actual Mix % = Actual Volume for this product ÷ Total Actual Volume across all products, and Budget Mix % = Budget Volume for this product ÷ Total Budget Volume across all products.

In Excel, assuming total actual volume is in a cell named TotalActualVol and total budget volume in TotalBudgetVol:

Reconciliation check

The three components must sum to the total revenue variance. Add a reconciliation row at the bottom:

Compare this to your total revenue variance from the budget vs actual sheet. If they match, your PVM decomposition is correct. If not, check for rounding errors in the mix percentage calculation — use more decimal places or avoid percentage rounding until the final display step.

This output tells a clear story: volume was strong (+£10,000 favourable), but the pricing discount (−£6,300) and the unfavourable product mix shift (−£4,200) more than offset it, leaving a small net adverse revenue variance. Without PVM analysis, all you would know is that revenue was £500 below budget — which tells you almost nothing about what to do next.

Common mistakes in Excel variance analysis

• Wrong sign convention on cost lines. Applying a single “positive = green” rule to the whole P&L turns adverse cost variances green. Always apply separate conditional formatting rules to revenue rows and cost rows. If in doubt, add a column that explicitly labels each variance “F” or “A” using an IF formula.
• Dividing by zero or near-zero budgets. A line item with a £0 or £1 budget that receives £50,000 of actual spend will show a meaningless variance percentage (e.g. 5,000,000%). Use IF(C2=0,"N/A",...) to handle zero-budget lines explicitly. Consider whether these lines should have a budget allocated at all.
• Mixing absolute and relative cell references in copied formulas. When you copy a percentage formula across multiple months, the denominator must lock to the correct budget column for each month. Use $ appropriately. A common error is copying =B2/C2 across three months and ending up with =D2/E2 (prior month actual divided by prior month budget) instead of =D2/$C2 (current actual divided by original budget).
• Referencing live pivot table cells directly. If your variance model pulls from a pivot table, any pivot refresh that changes the layout (e.g. a new row appearing due to a new cost code) will silently shift your formula references. Always paste pivot output as Values into your variance model, then refresh manually each period.
• Forgetting to update the period label. Sounds trivial, but a board pack with “April 2026” in the header when the data is May is embarrassing. Lock the period label to a single cell (e.g. a named range ReportingMonth) that you update once per period, and reference it in all headers with =ReportingMonth.
• Conflating price and mix variance in a two-way split. Some analysts only decompose into Price and Volume, grouping mix into volume. This overstates the volume story and understates the mix story. If you have multi-product revenue, always do the full three-way PVM split for an accurate picture.