To begin with, this is a rather technical post and not an easy read. So you should only go through it if you came across the problem and have a need to solve it. OK, let’s get to it.
Say you have a total margin of 40% and last year that was 30%. You want to show to which elements the +10% change is attributable to by breaking down the variance into effects. The challenge here is the complexity coming from having changes in absolute and margin %, changes in product mix and changes in revenue all baked into the margin % variance.
Let’s take this scenario as an example: the company sells different chocolate products, the dark chocolate, the milk chocolate and specialties (truffles, candy…). The results are something like this:
You notice that all margin elements have changed: revenue, absolute margin, margin % and product mix. You can, of course, make some observations, such as “dark chocolate, high margin segment has gone down in sales, however the % margin has improved”, but it will be hard to say whether the negative effect from lower sales is larger than the positive effect from higher margins, and by how much.
The methodology below is one way of getting to the answer, but sure not the only one. I like to use it because it’s rather simple and allows me to say something about each segment (or driver) individually. Also I checked the math and it makes sense to me.
Step 1. Break down the margin into a revenue effect and a margin effect
These effects are rather hard to tell a story with as they are, the main reason we do this is because they help in the rest of the calculation. The sum of margin change effect + revenue change effect gives the margin variance.
Step 2. Set up a margin table like the one below
You can easily calculate the margin effect for each segment. The revenue effect calculation by segment is different though. It is expressed as the % share of the segment out of total revenue, which is then multiplied with the total revenue effect (in our example, the 2.7%).
For our example, such a table would look like the one below:
What we use out of this table is the last column, the % margin effect, everything else is a means to an end, so the focus should not be on understanding individual margin or revenue effects. It is possible, of course, but is not very useful. The reason why individual effects do not help us is because they show what would the effect be if nothing else, but that one element changes. For example, if the absolute margin of dark chocolate does not change and neither does anything else, then the margin effect is zero. If the revenue decreases by 1000 and the absolute margin stays the same, then the effect is +3%. Makes sense, but like I said, is not very useful in telling the story.
If we look at the last column though, the story begins to unfold: “the +10% margin increase is the result of profitability improvement on both the milk chocolate segment (+8%) and dark chocolate (+3%), whereas the lower margin on specialties contributes negatively (-1%). Although the mix effect does not surface specifically, it is still embedded into the margin % effect (for example lower revenue on milk chocolate with the same margin will show positive).
One other thing which I like about this method is that it also works with a breakdown which is not “segment-like”. For example, a change in material cost which does not have a revenue element attributable to it. Which brings us to the next step.
Step 3. Select those items which are most relevant to the story
For example, let’s say there has been a drop in the price of cocoa beans, which reduced the cost by 10%. We can add that as a separate item and take out cocoa bean effect from the absolute margin of each segment. It would look something like this:
Adding the cocoa beans price effect is quite revealing for the story because it brings insight into that part of the variance which is the result of an external factor. It also deepens the understanding of sensitivity to such factors, something like: “10% change in cocoa bean price has a 4% margin effect, all else equal”.
So, rather simple method, the math adds up (there is no “residual”) and it allows for a segmentation which can be adapted to most situations.