Analyzing / interpreting individual customer impact on consolidated margin % (sample data included)
Hey Guys,
A big part of my new role is to analyze what is driving a consolidated GM% variance versus forecast. So in the sample data I provided, my goal would be to tell my stakeholders that the 4.2% increase in margin % is driven by X Y and Z.
One approach that I've been using thus far to determine line-by-line impact on the consolidated margin % has been to take the gross margin ($) for each customer (or product, or division, etc, depending on what story I am trying to tell) and divide by the consolidated sales to get my "bottom up margin" or "contribution to the margin". I'm not sure what this would be called. But in essence it describes what each customer "contributes" to the consolidated margin %. So in my sample actuals, Customer A has 22 gross margin, the consolidated sales are 430 - 22/430 = 7.3%; Customer A's "contribution to the margin" is 7.3%.
I calculate this for the actuals and forecast, and use the delta to guide my analysis. I.e. in my sample data I would say that we are +4.2% consolidated gross margin versus forecast. 1.9% driven by customer C, 1.5% by customer A, etc.
Does looking at margin % variance this way make sense? I am having some doubts now but I feel like I'm so close. Let's look at Customer A specifically. In total our consolidated sales are down -128 vs FC. Customer A's sales are +50 vs FC, however despite higher sales volume, margin $ is lower and thus so is margin %. I understand that Customer A's gross margin is higher as a portion of consolidated sales vs FC, however this is driven by the fact that overall volume is down. Right? I am tempted to describe this as "+1.5% driven by Customer A volume", but I feel like it doesn't make sense to say they are driving a 1.5% margin increase to forecast when they have less margin $ and margin % than forecasted.
What do you think of this approach? How would you interpret Customer A? Any other tips for analyzing impacts on a consolidated margin %?