When calculating variances between actuals and budget, you typically have a positive value representing a favourable (good) variance and a negative value for an unfavourable (bad) variance. When looking at revenue and expenses together this poses a problem for the variance calculation. The calculation needs to be different for revenue and costs. Here’s a way to use a single formula for both.
Common Technique
The image below shows a common technique which has a different formula for revenue compared to expenses.
The problem with this is that you must have the right formula on the right row. If you insert a new section you need to use the correct formula and be careful copying the variance formula.
Other techniques
You need a way to differentiate between revenue and expenses. This may be as simple as checking the first digit on the account number.
In the example below all the revenue codes start with 3 and the other expense codes start with 4 or higher.
Note the LEFT function is a text function, so it returns text, not a number. When comparing the result of the LEFT with a number you need to enclose the number within quotation marks.
If you don’t have access to the account number, you can use helper cells.
In the image below we have used helper cell in column A. We have used Hi to represent a higher number than budget is good or Lo where a lower number than budget is good. These are keyed in, but you may have a centralised table that has the descriptions with the Hi or Lo entry so that it can be looked up across multiple reports.
Alternate formula
I thought I would share a slightly different way to use the IF function with the variance calculation.
In the image below the IF function provide either 1 (revenue) or -1 (expense) and that is multiplied by the revenue variance calculation.
I am not saying this is better or worse just different. It may provide some different ideas for other formulas you require where the sign is an issue.
The examples in this post are in a file that you can download via the button below.
Please note: I reserve the right to delete comments that are offensive or off-topic.