Effect of Revenue and Gross Profit Margin on Profit Variance

Question

I am trying to calculate the effect of the Revenue variance and the Gross Margin % variance on the Gross profit variance. The linked image walks through the two different scenarios. Which one of the two is correct?

Calculations walkthrough

Answer 1

Both are correct (but you seem to have made an error in the formulas by switching 2 values)!

For sceanario 1, the formula for the Gross Margin effect should be "$(AC6-AC5)*AB6$" (you multiplied with $AB5$). Only that way you are getting "$-120,000$" in $AE11$.

Similiarly in scenario 2, the forumla for the Gross Margin effect should be "$(AC6-AC5)*AB5$" (you multiplied with $AB6$). Only that way you are getting "$-100,000$" in $AE16$.

To see why both ways are correct, note that 2 values in your calculations have changed: Revenue and Gross Margin. You can 'simulate' that by first changing one of the two values, and calculate the difference to the previous Gross Profit, then change the other value, and again calculate the difference in Gross Profit. If you add those 2 differences, you get the overall difference in Gross Profits.

Look at the following picture:

You want to go from the top left part (Budget) to the bottom right part (Actual). The changing of values can go two ways: You change Revenue first, Gross Margin second (red path), or you can chnage the Gross Margin first, Revenue second (blue path).

Both paths lead via an immediate point I called 'Mixed', because it contains values from both the Budget and the Actual data set.

Your scenario 1 corresponds to to the red path. Your "Revenue effect" calculates the change in Gross Profit when you go from the Budget cell to the Mixed cell below it. Then, your "Gross Margin effect" calculates the change in Gross Profit when you go from that Mixed cell to the Actual sell. Notice that for this to be correct it is important that you use the $1.2$ Million as Revenue, because that is the constant value in that change.

Your scenario 2 corresponds to the blue path. Your "Gross Margin effect" calculates the change to Gross Profit when you move from the Budget cell to the Mixed cell to the right of it. Then your "Revenue Margin" effect calculates the change to Gross Profit when you move from that Mixed cell to the Actual cell below it.

---

Maybe your are more comfortable with a little math than following different paths. I'll repeat the formulas you used here (corrected as mentioned in the beginning).

Scenrio 1: $$\begin{eqnarray} \text{Revenue Effect} & = &(\color{red}{AB6}-AB5)\times \color{red}{AC5}\\ \text{Gross Margin Effect} & = &(AC6-\color{blue}{AC5})\times \color{blue}{AB6}\\ \end{eqnarray} $$

Scenrio 2: $$\begin{eqnarray} \text{Revenue Effect} & = &(AB6-\color{red}{AB5})\times \color{red}{AC6}\\ \text{Gross Margin Effect} & = &(\color{blue}{AC6}-AC5)\times \color{blue}{AB5}\\ \end{eqnarray} $$

Note the colored variables in each scenario (only look at one scenario at a time). They represent, when you multiply to remove the parentheses, the same product of values, with different signs. It's about $AB6 \times AC5$ in the first scenario and $AB5 \times AC6$ in the second.

So in each scenario, when you add up the Revenue Effect and the Gross Margin effect, the terms from multiplying the colored terms cancel each other:

Scenario 1:

$$\begin{eqnarray} \text{Revenue Effect} + \text{Gross Margin Effect} & = &(\color{red}{AB6}-AB5)\times \color{red}{AC5} + (AC6-\color{blue}{AC5})\times \color{blue}{AB6}\\ & = & (\color{red}{AB6}\times\color{red}{AC5} - AB5\times AC5) + (AC6\times AB6 - \color{blue}{AC5}\times\color{blue}{AB6})\\ & = & AC6\times AB6 - AB5\times AC5 \end{eqnarray} $$

If you do it for scenario 2, you get

$$\begin{eqnarray} \text{Revenue Effect} + \text{Gross Margin Effect} & = &(AB6-\color{red}{AB5})\times \color{red}{AC6} + (\color{blue}{AC6}-AC5)\times \color{blue}{AB5}\\ & = & (AB6\times AC6 - \color{red}{AB5}\times \color{red}{AC6}) + (\color{blue}{AC6}\times \color{blue}{AB5} - AC5\times AB5)\\ & = & AC6\times AB6 - AB5\times AC5 \end{eqnarray} $$

Both sums give the same result, and it is what is expetced: The difference between the Actual Gross Profit ($AC6\times AC6 = 360,000$) and the budgeted Gross Profit ($AC5\times AC5 = 400,000$).