Variance analysis template (Excel)
Last reviewed 2026-09-28
A free Excel workbook for monthly variance analysis. Enter budget and actual for each line; the 83 formulas work out the variance, the variance percentage, whether each line is favourable or unfavourable, and which ones cross your investigation threshold. A second tab splits a revenue movement into price, volume and mix.
Download the variance analysis template (.xlsx)
What is in the workbook
- Budget vs Actual: ten example lines from revenue to operating profit, with subtotals as formulas, an investigation rule you can change, and a split of the cost of goods sold variance into the part caused by lower revenue and the part caused by cost per sale.
- Price Volume Mix: three example products, with the price, volume and mix effects each calculated from its own formula and a check line that must show 0.00.
- How to use: the steps and every formula, in words.
The formulas it uses
| Column | Formula (row 5) | What it does |
|---|---|---|
| Variance | =D5-C5 | Actual minus budget |
| Variance % | =IF(C5=0,"n/a",E5/ABS(C5)) | Against the absolute budget; n/a when there is no budget |
| Favourable / unfavourable | =IF(E5=0,"On budget",IF((B5="Income")=(E5>0),"Favourable","Unfavourable")) | More income or less cost is favourable |
| Investigate? | =IF(AND(E5<>0,ABS(E5)>=$C$17,OR(C5=0,ABS(E5)>=$C$18*ABS(C5))),"Investigate","") | Both thresholds met, or a line with no budget over the amount |
The worked example in the file
Revenue came in at $112,800.00 against a budget of $120,000.00 and operating profit at $6,120.00 against $16,600.00. With the thresholds set to $1,000.00 and 5%, 6 lines are flagged: Revenue, Gross profit, Marketing, Contractors, Total operating expenses, Operating profit.
| Line | Budget | Actual | Variance | Variance % | F / U | Investigate? |
|---|---|---|---|---|---|---|
| Revenue | $120,000.00 | $112,800.00 | −$7,200.00 | −6.0% | U | Yes |
| Cost of goods sold | $48,000.00 | $46,500.00 | −$1,500.00 | −3.1% | F | |
| Gross profit | $72,000.00 | $66,300.00 | −$5,700.00 | −7.9% | U | Yes |
| Salaries | $38,000.00 | $38,000.00 | $0.00 | 0.0% | on budget | |
| Marketing | $9,000.00 | $12,150.00 | +$3,150.00 | +35.0% | U | Yes |
| Software subscriptions | $2,400.00 | $2,280.00 | −$120.00 | −5.0% | F | |
| Rent | $6,000.00 | $6,000.00 | $0.00 | 0.0% | on budget | |
| Contractors | $0.00 | $1,750.00 | +$1,750.00 | n/a | U | Yes |
| Total operating expenses | $55,400.00 | $60,180.00 | +$4,780.00 | +8.6% | U | Yes |
| Operating profit | $16,600.00 | $6,120.00 | −$10,480.00 | −63.1% | U | Yes |
On the Price Volume Mix tab, revenue moves +$18,000.00: price −$1,000.00, volume +$15,250.00 and mix +$3,750.00, with $0.00 left over. The step-by-step working is in the price volume mix example, and the reasoning behind each column of the first tab is in the budget vs actual variance guide.
How to use it
- Download the file and open it in Excel, Google Sheets or LibreOffice.
- On Budget vs Actual, type your own line names, budgets and actuals over the example. Keep the subtotal rows or change their formulas to match your chart of accounts.
- Mark each line Income or Cost in column B. That is what decides favourable or unfavourable.
- Set the investigation amount and percentage in C17 and C18.
- Write an explanation for every flagged line before the report goes out. A flag is a question, not an answer.
Try Valcenra free
A spreadsheet stops at the flag. Valcenra takes your budget and actual product detail, splits a margin movement into volume, mix, price and cost that tie to the ledger, and keeps any amount it cannot explain as an open item with an owner instead of folding it into another line.
The free workspace is one entity, two users and five stored analysis runs a month, with no card and no sales call.
Common questions
- What should a variance analysis template include?
- Budget and actual for the same period, the variance as actual minus budget, the variance percentage against the absolute budget, a favourable or unfavourable label that depends on whether the line is income or cost, and a rule for which variances get investigated. This template has all five, plus a price volume mix tab.
- How is variance percentage calculated in the template?
- Variance divided by the absolute value of the budget: =IF(C5=0,"n/a",E5/ABS(C5)). The absolute value keeps the sign meaningful when a budget is negative, and a line with no budget shows n/a rather than a percentage it does not have.
- Does the template work in Google Sheets?
- Yes. It uses only IF, ABS, AND, OR and SUM, which Google Sheets and LibreOffice support. Upload the .xlsx file to Google Drive and open it with Google Sheets.
- Why does a favourable cost variance still need a look?
- Because it may only reflect lower sales. In the example, cost of goods sold is under budget, but restated at the revenue actually earned it is over. The template shows that split on the first tab.
Illustrative figures. General information, not accounting advice. Last reviewed 2026-09-28.