Purpose
By the end of this lesson, you will be able to build a variance report comparing actual spending against budget, and flag meaningful deviations.
Lesson Explanation
A variance is the difference between actual and budgeted figures: =B2-C2 (actual minus budget) gives the dollar variance; a positive result means overspending, a negative result means underspending, assuming B is actual and C is budget.
Percentage variance is often more useful for comparing across line items of very different sizes: a $5,000 overrun means something very different on a $10,000 budget line versus a $2,000,000 budget line. =(B2-C2)/C2 expresses the variance as a percentage of the original budget.
Not every variance deserves equal attention – a small percentage variance on a huge budget line can represent more actual dollars at stake than a large percentage variance on a tiny line item. A useful variance report often flags line items using BOTH a dollar threshold AND a percentage threshold, catching both kinds of meaningful deviation.
A formula flagging a line item for review if it exceeds either threshold: =IF(OR(ABS(B2-C2)>10000,ABS((B2-C2)/C2)>0.15),”Review”,”OK”) flags any line that’s off by more than $10,000 in absolute dollars, OR more than 15% in percentage terms – catching genuinely significant variances of either type.
Practice Questions
1. Actual spending for a department is in cell B2 ($52,000) and the budgeted amount is in C2 ($48,000). Write a formula calculating the dollar variance.
View Answer
=B2-C2 (which would return $4,000, indicating overspending).
2. Using the same B2 and C2 values, write a formula calculating the variance as a percentage of budget.
View Answer
=(B2-C2)/C2 (which would return approximately 8.3%).
3. A $5,000 overrun occurs on a budget line of $10,000, and a separate $5,000 overrun occurs on a budget line of $2,000,000. Are these two overruns equally significant? Why or why not, based on this lesson?
View Answer
No – the first represents a 50% variance (highly significant relative to that budget line), while the second represents just a 0.25% variance (comparatively minor) – the same dollar amount means very different things depending on the base.
4. Why does this lesson recommend flagging variances using BOTH a dollar threshold AND a percentage threshold, rather than just one or the other?
View Answer
A small percentage variance on a very large budget line can represent more actual dollars at stake than a large percentage variance on a small line item – using both thresholds catches genuinely significant deviations of either type.
5. Write a formula that flags a line item as “Review” if its dollar variance exceeds $5,000 in either direction (over or under budget), and “OK” otherwise, assuming actual is B2 and budget is C2.
View Answer
=IF(ABS(B2-C2)>5000,”Review”,”OK”)6. What does the ABS function do within a variance-flagging formula, and why is it necessary here?
View Answer
ABS returns the absolute value, removing the negative sign; it’s necessary because both significant overspending (positive variance) and significant underspending (negative variance) should be flagged, not just one direction.
7. A department has a variance of -20% (significantly under budget). Does this lesson’s flagging approach treat underspending the same as overspending, or does it only flag overspending?
View Answer
The ABS-based approach treats both directions the same, flagging significant underspending just as it would flag significant overspending, since both represent a meaningful deviation from plan worth investigating.
8. Write a formula flagging a line item as “Review” if it exceeds EITHER a $10,000 dollar variance OR a 20% percentage variance, using B2 for actual and C2 for budget.
View Answer
=IF(OR(ABS(B2-C2)>10000,ABS((B2-C2)/C2)>0.2),”Review”,”OK”)9. A budget line shows a variance of exactly $8,000 on a $500,000 budget (a 1.6% variance). Using a $10,000 dollar threshold AND a 15% percentage threshold (flagging on either condition), would this line be flagged for review?
View Answer
No – $8,000 is below the $10,000 dollar threshold, and 1.6% is well below the 15% percentage threshold, so neither condition is met.
10. A small department with a $15,000 total budget shows a $3,000 overrun (a 20% variance). Using the same thresholds ($10,000 dollar / 15% percentage), would this be flagged?
View Answer
Yes – while $3,000 is below the dollar threshold, the 20% variance exceeds the 15% percentage threshold, so the OR condition would flag it for review.
11. Why might using only a percentage threshold (with no dollar threshold) risk missing genuinely important variances in a large budget?
View Answer
A very large budget line could have a small percentage variance that still represents an enormous number of actual dollars, which a percentage-only threshold might fail to flag as significant.
12. A finance team wants to review every line item with a variance, regardless of size, rather than using any threshold at all. What tradeoff does this lesson’s thresholding approach help avoid?
View Answer
Reviewing every single variance regardless of significance would waste time on genuinely trivial deviations; thresholds help focus review effort specifically on variances large enough (in dollars or percentage) to actually matter.