Excel Variance Techniques

Check out the image below for a simple example.

Often people use a different variance calculation for revenue versus costs. We can standardise the variance formula it with a simple trick.

Column A has -1 for revenue and 1 for costs. This helper column makes it easy to standardise the variance calculation. The formula for cell E2 is:

=(D2-C2)*A2

This shows a negative value for variances that reduce profit (bad – unfavourable), and a positive variance for variances that increase profit (good – favourable).

Now if your budget values are always shown as a positive value as in the image above the variance calculation is easy.

=IF(D2=0,0,E2/D2)

Negative budget values

In the image below we have included Work in Progress (WIP) movement which could be positive or negative.

Check out row 15. The variance amount is negative but the variance % is positive. This is caused when the budget value is negative, and the variance is negative.

We don’t need another IF function to get this right, we just need the ABS function which removes negatives.

In the image below column G has an alternative variance %.

The formula in cell G2 is.  

=IF(D2=0,0,E2/ABS(D2))

The ABS function removes negative values.

Row 15 is in column G is correct.

By making sure the budget is always positive using the ABS function, the sign of the variance amount determines by sign of the variance %.