One illustrative quarter's budget-versus-actual report, with costs, losses, a zero budget and a line that changes sign.
When the budget is negative, divide the variance by the absolute value of the budget: variance % = (actual − budget) / ABS(budget), with costs and losses stored as negative numbers so that a positive variance always means favourable. A segment budgeted to lose 800,000 that loses 500,000 is then 37.5 per cent favourable; the plain formula shows minus 37.5 per cent and reports an improvement as a deterioration. When the budget is zero or the sign changes, no percentage is meaningful: report "n/m" and the amount.
Worked in full in Financial Planning and Analysis by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
A company reviews a quarter against budget. Its report holds income lines, cost lines, a finance cost, an FX line budgeted at zero, and three segments, one of which was budgeted to lose money and another that changed from a loss to a profit. All figures are illustrative and stored in profit sign: income positive, costs and losses negative.
| Line | Budget | Actual | Variance | Plain % | ABS % |
|---|---|---|---|---|---|
| Revenue | 12,000,000 | 12,600,000 | 600,000 | 5.0% | 5.0% F |
| Cost of sales | -7,200,000 | -7,700,000 | -500,000 | 6.9% | -6.9% U |
| Operating expenses | -4,300,000 | -4,150,000 | 150,000 | -3.5% | 3.5% F |
| EBIT | 500,000 | 750,000 | 250,000 | 50.0% | 50.0% F |
| Net finance cost | -200,000 | -260,000 | -60,000 | 30.0% | -30.0% U |
| FX gain or loss | 0 | -40,000 | -40,000 | #DIV/0! | n/m |
Look at the plain column. Every negative-budget line has the wrong sign: the cost overrun on cost of sales reads +6.9 per cent, as if it were good news, and the saving on operating expenses reads minus 3.5 per cent. On a report that colours positive numbers green, the overrun turns green and the saving red.
Choose one convention and print it at the top of the report. The cleanest is profit sign throughout, with variance defined as actual minus budget. A positive variance then always increases profit, on every line, and the variances add down the page: 600,000 − 500,000 + 150,000 = 250,000, the EBIT variance.
The alternative, costs entered as positives, works only if the favourable variance on cost lines is computed the other way round, budget minus actual. For operating expenses that is 4,300,000 − 4,150,000 = 150,000 favourable, 3.49 per cent. Mixing the two conventions in one report is the most common source of sign errors in management packs.
Variance % = (actual − budget) / ABS(budget)
Cost of sales: (−7,700,000 − (−7,200,000)) / 7,200,000 = −500,000 / 7,200,000 = −6.94%, unfavourable
Excel: =IF(OR(B2=0,SIGN(B2)<>SIGN(C2)),"n/m",(C2-B2)/ABS(B2))
Dividing by the absolute value keeps the sign of the variance itself, which under the profit-sign convention already says whether the line helped or hurt. The size of the percentage is unchanged; only its sign is corrected.
| Segment | Budget | Actual | Variance | Plain % | ABS % | % of budget revenue |
|---|---|---|---|---|---|---|
| Segment A | 1,400,000 | 1,100,000 | -300,000 | -21.4% | -21.4% U | -2.50% |
| Segment B | -800,000 | -500,000 | 300,000 | -37.5% | 37.5% F | 2.50% |
| Segment C | -100,000 | 150,000 | 250,000 | -250.0% | n/m | 2.08% |
| Group EBIT | 500,000 | 750,000 | 250,000 | 50.0% | 50.0% F |
Segment B is the case the ABS formula exists for: a smaller loss is an improvement, 37.5 per cent of the budgeted loss. Segment C is different. It moved from a 100,000 loss to a 150,000 profit, and the ABS formula would print 250.0 per cent. That number depends entirely on how close the budget happened to be to zero: a budgeted loss of 10,000 with the same actual would show more than 1,000 per cent for a smaller improvement. The rule is to report "n/m" (not meaningful) when the budget is zero, when actual and budget have opposite signs, and, by a threshold the team agrees, when the budget is too small to carry a percentage, for example below 100,000.
For n/m lines, give the reader a scale another way: the absolute variance, or the variance as a share of budget revenue. Segment C's 250,000 is 2.08 per cent of budget revenue, comparable with segment A's -2.50 and segment B's 2.50 per cent. The FX line's -40,000 is -0.33 per cent.
The usual error is the plain formula, (actual − budget) / budget, copied down an entire report. It is right for revenue and positive profit lines and wrong for every cost, every loss and every negative budget, which means the sign of roughly half the report is reversed. Conditional formatting then colours it confidently. A second error is fixing the sign with ABS but forgetting the n/m rule, so a segment that crossed break-even appears as the largest percentage on the page. Both make a variance pack harder to read than the raw numbers.
Variance percentages answer only the first question. Which comparison to make, budget, prior year, forecast or plan, matters more, and the volume effect has to be removed before the percentage means anything; see how to calculate a flexible budget variance. The free workbook for this case holds one quarter against all five comparisons the book uses, each with its sign and its check.
=IF(OR(B2=0,SIGN(B2)<>SIGN(C2)),"n/m",(C2-B2)/ABS(B2)), with the budget in B and the actual in C, costs and losses entered as negatives. A budget of -800,000 against an actual of -500,000 returns 37.5 per cent, favourable. A budget of -100,000 against +150,000 returns n/m.
Because the base is not a meaningful scale for the change. Segment C was budgeted to lose 100,000 and made 150,000: an improvement of 250,000, which divided by 100,000 gives 250.0 per cent. A budgeted loss of 10,000 with the same actual would have given 1,600 per cent for a smaller improvement. Report the absolute variance, or express it against revenue: 2.08 per cent of budget revenue.
It depends on the sign convention, which is why reports must state one. If costs are stored as negatives and variance is actual less budget, a positive variance is always favourable: operating expenses at -4,150,000 against -4,300,000 give +150,000. If costs are stored as positives, favourable must be computed as budget less actual.
This article is one calculation from Financial Planning and Analysis. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: corporate finance, valuation and markets → · All 1340 articles →
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 workbook stays free either way.