Join Rudolph Rosenberg for an in-depth discussion in this video Performing a price/mix analysis, part of Financial Analysis: Analyzing the Bottom Line with Excel.
So what we want to do now is perform what we call a price mix analysis. This is an analysis that enables us to differentiate what part of our margin evolution has been driven by the changes in mix from our product or volumes of sales by product, and which part of it has been driven by the changes in gross margin percentage by product. So let's start with the mix element of that analysis. The mix element is to analyze solely the volume changes and their influence on the total margin.
To do so, we'll take our 2014 volumes and apply to those the gross margin percentages of 2013. So that pretty much will calculate what would have been 2014 if we had only revenue changes but no margin changes. Let's do that now. So here let me calculate equal B10 which is the revenue for 2014, multiplied by D5 which is the margin for 2013.
Let me press Enter, and let me drag that formula for each of our products. Let me format this by clicking on the comma style format, and let me calculate the total margin that's resulting from that sum of those four calculations. Let's press Enter. Now, let me calculate the margin percentage over the total revenue for 2014. So that's =F9 divided by B9, which is the revenue for 2014. Let's press Enter.
And let me format this as a percentage. Let me add a decimal as well. So what you can see here is that if we had only volume changes for our products but no gross margin changes, we would have been generating in 2014 80.4% margin. So not very different from what we're generating right now. Let's now put at the bottom here below price and mix, and let's calculate what's the influence of each one of those.
So let's start with mix. Let's just calculate the difference between our newly calculated gross margin percent compared to the 2013 gross margin percent. So let's equal our 80.4% minus the 82.4%. And let's press Enter. That means that the mix element of our changes from one year to the next has been driving a decrease of 1.9 percentage points, out of the total 2.9 percentage points that we have seen as a decline from one year to the next.
Let's now calculate the price element, which is equal to the total change we have seen within both years, so 2014 gross margin minus 2013 gross margin. Let's press Enter now. And let's take this result and remove from it the difference we have seen from the mix element. And let's press Enter. And the result is that from a price perspective, 0.7 percentage points decline has been generated, and from a mixed perspective 1.9 percentage point.
So our price mix analysis tells us here that most of our decline in margin comes from mix. So that means from changes in volumes of sales from one product to the next. The next question we'll want to answer is, okay, but which products and what was the influence of each product in the decline? To do so, we need to calculate what was the influence of each product in the generation of the overall gross margin for that year. So let's start with 2013. So let's click here, and calculate C5, which is the gross margin of high-end pen, divided by the total gross margin for 2013.
And let's press Enter. Let's put that in a percentage format with a decimal, and let's drag that on all products. Let's now do the same for 2014. Let's get that equal C10 divided by the total gross margin for 2014. Let's press Enter, and let's drag that down on all of our products. Let's now compare 2014 to 2013. So for high-end pen let's type equal, our resulting calculation minus the same calculation for the previous year and let's press Enter and drag this down on all of our four products.
What you can see here is that high-end pens have been contributing much more to our total gross margin in 2014 than in 2013. And as you can see, this is our lowest margin product. So that means that because we've been selling much more high-end pens, which are less profitable, overall our margin has been going down. At the same time, you can see that the influence of regular pens, sturdy pen, and ultra-thin pen has reduced over the course of the two years.
So to summarize, the conclusion of this analysis is that the main reason for the margin decline between 2013 and 2014 is because the mix of product has been switching from regular pen, sturdy pen and ultra-thin pen towards high-end pen which is much less profitable. That could be something to investigate to rebalance the margin in the other direction.
Also check out the companion course, Financial Analysis: Analyzing the Top Line with Excel.
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Finding data points
- Prepping data
- Calculating standard cost and gross margins
- Analyzing overall gross margin performance
- Analyzing individual and overall expenses