Every month, FP&A teams compare what happened with the plan. The basic layout gives each line four columns: budget, actual, variance and variance %.
- Variance = actual - budget, and variance % = variance / budget.
- A variance is favorable (F) when it helps profit: revenue above budget, or costs below it. It's unfavorable (U) when it hurts: revenue below budget, or costs above it.
The same +10 can be good or bad depending on the line, so the flag needs to know whether the line is revenue or cost. A Type column and a nested IF handle it:
=IF(B5="Revenue",IF(E5>=0,"F","U"),IF(E5<=0,"F","U"))
Then explain the big variances. For revenue, split the variance into volume (selling more or fewer units, valued at budget price) and price (selling at a different price than planned):
- volume variance = (actual units - budget units) x budget price
- price variance = (actual price - budget price) x actual units
The two add up exactly to the total revenue variance. Producing the numbers is quick. Explaining the two or three biggest variances is the slow part, and it's the part managers read.