Variance analysis compares each line of the accounts against a prior period or budget and explains why it moved. This page shows the arithmetic on a worked example, a two-part materiality rule for deciding which lines deserve attention, and how to name the driver behind each movement so the commentary explains it.
Download
forecraft-variance-analysis-template.csv is a blank template with the formula notes in the header and columns for driver, explanation, owner, and due date.
The two calculations
For any line, comparing a current period against a prior period:
Variance = Current − Prior Variance % = Variance ÷ ABS(Prior) × 100
Divide by ABS(Prior), the prior figure without its sign. If the prior figure is negative (a contra-revenue line, or a credit sitting in an expense category), dividing by the signed value flips the sign of the percentage and makes an increase look like a decrease.
A worked example
The figures below are illustrative and invented for this example. They are not a customer, a case study, or an industry benchmark.
| Category | Prior | Current | Variance | Variance % | Flagged |
|---|---|---|---|---|---|
| Revenue | 120,000 | 132,000 | +12,000 | +10.0% | Yes |
| Cost of goods sold | 48,000 | 56,100 | +8,100 | +16.9% | Yes |
| Payroll | 41,000 | 41,400 | +400 | +1.0% | No |
| Software and subscriptions | 3,200 | 5,900 | +2,700 | +84.4% | Yes |
| Marketing | 9,000 | 6,300 | −2,700 | −30.0% | Yes |
| Professional fees | 1,500 | 1,530 | +30 | +2.0% | No |
The materiality rule used above
A line is flagged when either test is met:
- the absolute variance is at least 2,000 (a fixed amount you set per client), or
- the variance is at least 10 percent of the prior figure.
Each test catches movements the other one misses.
A large base hides big amounts. Payroll moved +400, under one percent, and is rightly ignored here. On a bigger payroll the same percentage could be a large sum, which only the absolute test catches.
A small base hides big percentages. Software rose by 2,700, small against revenue of 132,000 and only just over the 2,000 fixed amount, but an 84 percent increase on a normally stable line. With the fixed amount set any higher, only the percentage test would catch it. A jump like that usually means an annual renewal posted in one month, a new tool, or a duplicate subscription.
Professional fees rose by 30, or 2 percent. That is below both thresholds, so it is left alone. Leaving a line unflagged is a decision to make on purpose: when every line is flagged, the reader cannot tell which ones matter.
Naming the driver
A variance table that stops at the number is only half the work. Each flagged line needs a driver, and there are only five useful categories:
| Driver | Means | Example from the table above |
|---|---|---|
| Volume | More or fewer units, customers, or hours | Revenue +12,000 alongside cost of goods sold +8,100 |
| Price | The same volume at a different rate | A supplier increase inside cost of goods sold |
| Timing | Cost landed in this period but relates to several | Software +2,700 from an annual renewal |
| One-off | Genuinely non-recurring | A campaign that did not repeat, reducing marketing |
| Correction | A coding or cut-off error | Anything traced back to the wrong category |
If the driver is correction, stop and fix the books before the pack goes out. If it is timing, say so explicitly, otherwise the reader assumes a permanent step change and plans around a cost that will not recur.
A variance table that stops at the number is only half the work.
Checking gross margin as well as gross profit
Revenue rose 10.0 percent and cost of goods sold rose 16.9 percent in the example. Both look healthy in isolation, but the ratio moved:
Prior gross margin = (120,000 − 48,000) ÷ 120,000 = 60.0% Current gross margin = (132,000 − 56,100) ÷ 132,000 = 57.5%
A 2.5 point margin decline in a growing month is the most important line in this table, and it appears nowhere in the variance column. Ratios have to be checked separately from movements.
Doing this automatically
Forecraft runs this comparison across every line, ranks what moved, and drafts the explanation with the source row attached, so you can trace any figure back to the file it came from.
Related notes
- How to write monthly financial commentary covers turning a flagged variance into a sentence.
- Monthly close checklist shows where variance analysis sits in the month.
- CFO management pack template covers the section this table feeds.
- All journal notes