Free Excel templates

A free budget vs actual variance analysis template in Excel, with one quarter read five ways

Actual against budget, prior year, latest forecast, preceding quarter and underwriting plan, with the quarterly phasing, the nine months, the reforecast year and the cost of the budget cycle: five linked sheets, one fictional company, no macros.

The file computes the variance of one quarter's revenue, in money and in per cent, against five different comparators. Calderwood Instruments closes its third quarter at 216,340,000. Against the budget of 210,000,000 that is 6,340,000 favourable, 3.02%; against the underwriting plan of 232,500,000 it is 16,160,000 adverse, -6.95%. Two readings are favourable, three adverse, and the spread between the best and the worst reading is 10.66 points, or 23,900,000. Every reading is a formula, and every one is correct: each answers a different question.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Budget_vs_Actual_Variance_Analysis.xlsx (17 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This file is one worked case from Financial Planning and Analysis. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon →

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 19 paperbacks by Julian R. Sterling on markets, funds and compliance, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Five sheets, in the order the calculation runs. Inputs are blue type and sit on the Inputs sheet (plus the quarterly phasing and the budget-cycle estimates, also in blue); the yellow cell is the actual every comparison is made against. Amounts carry no currency, as in the book.

Inputs
The third-quarter actual revenue and the five comparators: budget, prior year same quarter, latest forecast (July), preceding quarter and underwriting plan, each with the question it answers.
Comparisons
The quarter read five ways: base, actual, variance and per cent for each comparator, then the best and worst readings, the spread between them, the count of favourable readings, the forecast gap on the actual as base, and a test of the sequential reading against the seasonal step.
Phasing
Budget, underwriting plan, prior year and actual quarter by quarter, so you see where the budget and the plan diverge across the year. Then the nine months and the full year on the reforecast against both documents.
Budget Cost
What the budget cycle costs: people, working days and loaded rate, as a share of budget EBIT, per million of revenue planned, and against the third quarter's EBIT variance.
Checks
Every figure the book prints set against what the workbook computes, with a verdict on each of the 35 lines.

What the worked quarter returns

MeasureValue
Third-quarter actual revenue216,340,000
Against budget (210,000,000)+6,340,000 (3.02%)
Against prior year, same quarter (218,900,000)-2,560,000 (-1.17%)
Against latest forecast, July (221,000,000)-4,660,000 (-2.11%)
Against preceding quarter (208,600,000)+7,740,000 (3.71%)
Against underwriting plan (232,500,000)-16,160,000 (-6.95%)
Spread, best reading to worst10.66 points (23,900,000)
Nine months, actual less the plan-64,160,000 (9.43% short of the plan)
Full year on the reforecast852,340,000 (12,340,000 above budget, 77,660,000 below plan)
Cost of the budget cycle475,540 (0.51% of budget EBIT)

Nothing about the quarter changes between the best reading and the worst: only the comparator does. The budget gives the second-best reading and the plan the worst, because this year's budget of 840,000,000 sits 90,000,000 below a plan of 930,000,000 approved by the same board. The sequential gain of 3.71% falls between the budget's own step from the second quarter to the third (2.44%) and last year's (4.74%), so it reads as a direction, not a measurement. State the comparator beside every variance, and when two comparators disagree in sign, report both.

How to use it on your own case

  1. Open Inputs and overwrite the blue cells: your actual for the period and the five comparators you hold.
  2. Read Comparisons from the top. Check the sign and the base of each per cent before you quote it.
  3. Look at the spread line. If the best and worst readings sit far apart, the choice of comparator is the finding.
  4. On Phasing, enter your budget, plan and prior year by quarter to see where the documents diverge across the year, not only in the period you are reporting.
  5. Change the headcount, days and loaded rate on Budget Cost to price your own budget cycle.
  6. Keep the Checks sheet: once you change inputs it stops matching the book, which is expected. Use its structure for your own tie-outs.

Questions people ask about it

Is this variance analysis template really free?

Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Does it use macros or circular references?

No macros and no circular reference. Every variance is the actual less its base, every per cent is that variance over the base, so the file recalculates in Excel, LibreOffice or Google Sheets as it is.

Which base should a variance percentage use?

State it, whichever you choose. The July forecast misses the actual by 4,660,000: that is -2.11% on the forecast and 2.15% on the actual. Same gap, two bases, two numbers. The Comparisons sheet prints both.

Can I use it for a real company?

You can reuse the structure. Calderwood Instruments and every figure in the file are fictional, and nothing here is investment or accounting advice.

The rest of the files

This template is one of the companion files for Financial Planning and Analysis: the full set adds the price, volume and mix workbook, the segment contribution and allocation workbook and the forecast accuracy workbook. Free, like this one.

Open the companion files →

Worked questions from the same book

Short articles that each answer one question with the arithmetic shown, on the same ground as this model.

Other free templates

This template comes from Financial Planning and Analysis. The book is on Amazon.

If this book helped — or didn’t — a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.