How to perform a sales bridge (or price volume mix analysis)
A sales bridge (or price volume mix analysis) is a report which shows the gap between budgeted and actual sales, and the explanation for that variation. Basically, there are three type of effects or components that should be considered in order to explain the gap:
- Price effect: deviation due to apply higher or lower selling prices.
- Volume effect: variation in the turnover due to the total units sold.
- Mix effect: measures the impact in the sales amount resulting from a change in the mix of the quantities sold (% of units sold per reference over the total).
This report becomes a really effective tool when it is performed as a bottom-up analysis, as we can identify the origin of the variations at different levels: product, product family, customer, etc.
Below we can see an example of a sales bridge and how to build it in Excel. To perform the analysis, it is needed to have the list of products with sales information (quantity and price) as follows:
- Blue columns contain budget data (units, units mix (%), price and turnover).
- Green columns show the same information for actual sales.
- And finally, in the last column (deviation) we can find the gap between budget and actual turnover.
Now some calculated columns are added in order to show the cause of the deviation for each product:
- Volume effect. In our example, the units sold (125) are lower than budget (150), therefore we will have a negative volume effect. To quantify this effect by product, we need to compare budget and actual turnover at budget mix, which is calculated as the sales amount in case the company had sold the 125 units with the same mix and price than budget.
Example: volume effect for product T RED.
Step 1. In budget, the units of product T RED are 20% of the total. Therefore, the actual units at budget mix are obtained as the 20% of 125 = 25 units.
Step 2. The actual turnover at budget mix is the result of multiplying the previous units by budget price: 25 x 200 = 5.000 EUR.
Step 3. Volume effect: difference between actual turnover at budget mix and budget turnover: 5.000 – 6.000 = -1.000 EUR.
- Mix effect. This component is calculated as the difference between actual units and actual units at budget mix, multiply by the budget price.
Example: mix effect for product T RED.
Step 1. Mix effect on quantities: 40 (actual units) – 25 (actual units at budget mix) = 15 units.
Step 2. Mix effect: 15 (mix effect on quantities) x 200 (budget price) = 3.000 EUR.
- Price effect. It is calculated as the difference between actual and budget price, multiplied by the actual units. In our example, the products T RED and T GREEN has no price effect, as the budget and actual prices are exactly the same. However, T BLACK has a positive price impact.
Example: price effect for product T BLACK.
Step 1. 90 (actual price) – 80 (budget price) = 10 EUR/unit
Step 2. Price effect: 10 x 75 (actual units) = 750 EUR.
Once we have got all the effects, it is highly recommended to show the result in a waterfall graph:
You can download the excel file with the example above here
In this post we simulate different scenarios in order to evaluate how a change in volume and/or price impact over the total sales, and understand better the meaning of every component.
Thanks for this great and simple example
Thank you for visiting my blog!
Such a great example! I know so many people who will love this template. BTW: some companies will also benefit to see the exchange rate impact, or a PVMX analysis.
You are fully right. I will take into account your suggestion. I will update the post or create a new one including the exchange rate effect.
Jose
Great file and example. Following up on the above comment by Marco. I would appreciate it if you could create a post on this with multiple currencies – GBP,USD,EUR and JPY.
Great example! so practical and easy to apply!
Hello Moises, thank you for your comment and visiting my blog.
Thank you for this great analysis example!
May I ask for some clarification? In your excel model, you also have a sheet for analysis with product family. I am wondering if the product family analysis could/should be constructed by referring to Product family specific numbers? So in a way that when we calculate actual and budget “% Total” (Share of total units) we would refer to total of product family instead of the full total? Same question applies to “Actual units at budget mix”. So to calculate that we multiplied “% total” (referring to share of a product in a product family) and Actual Unit total (again referring to total units in product family).
So to give an example if we analyse Flip-flops we would calculate totals for products belonging to this category. Also if we calculate “% total” for DANY-flip-flops would be percentage of total flip-flops.
This question popped to my mind as it didn’t make completely intuitive sense to me that in Flip-flop category both products increase in units from Budget to Actuals but the product family still has a negative volume effect in the analysis.
Hello Henri. I will try to answer your question.
It depends. If your objective is to perform a global analysis of the company sales and how the different references impact in your result, you should do it by taking all references (I mean, “% Total” should be calculated based on the sum of all references -as I did in the Excel-). This is the most commmon approach, as companies normally want to maximize their total sales. By doing partial analysis per”family”, the effects by material will change and the analysis will not show the correct “behaviour” of each reference for your business.
On the other hand, in my opinion the analysis split by “family” may be useful only if it exists a specific department within the company in charge of a that “family”, and they would like to perform an isolated analysis for that concrete “family”, but that is all. It is more accurate if you build a table like the one created in rows 13-17, by agregating the results by family.
Regarding the intuitive sense of the analysis, I agree with you. Sometimes it is hard to understand a negative “volume effect” when we are selling more units than budget for that family or product. However, if you did not watch it yet, I recommend you to check the video out I uploaded in another related post. You will watch how “mix effect” plays in this game, and it “absorbs” the impact we expect (intuitively) to have in volume.
https://undercontrolling.com/understanding-the-components-of-the-sales-bridge-price-volume-mix-analysis/
Hi
Is there way taht you can send me the file for price volume mix analysis
Hi Carlos. I just sent the file to your e-mail.
Is it possible to ever run the calculation and only have 2 of the variables? Meaning, could I run this and only see price and volume impact with the mix impact = zero? I don’t think it is but am not sure. Thank you!
Hello Tom,
It is possible. You can calculate volume and price as follows:
– Volume effect: (Actual units – Budget units) * Actual price
– Price effect (same formula as the model proposed): (Actual price – Budget price) * Budget units
Actually, by applying this way of calculation, it is equal to sum Vol effect + Mix effect in the model explained in the Excel.
Hope I answered your question. If not, let me know.
Thank you, Jose! Very helpful. As a business manager, this analysis is extremely helpful in understanding what is truly going on in my business. If sales are down, I can look at the data and tell if I’m losing volume to a competitor or dropping price to protect my business.
Thank you!
Thank you for reading my blog.
Glad i
Hi José,
lookin at the above, it feels better to me if you would apply for price effect the actual units iso the budget units;
this allows to sum the mix impact and volume impact in the “volume effect”, while the price efffect remains the same. (which is the same formula as the model proposed).
Thanks,
Anton
Hi Anton,
I do not think I understand your point. Price effect is calculated with actual units.
Hi Jose,
I think the standard in cost accounting accepted by AICPA and IMA is the following:
Volume effect : (actual unit – budget units ) * budget price
Price effect: (actual price – budget price) * actual units
If you disagree or have learned it differently, would you mind quoting the source material?
Hello KT,
I do not agree or disagree. In your formula of “volume effect”, you are simply omitting the “split” between mix and volume effect, and considering both as only one.
The proof is, if in my model, you sum the volume and mix effect of each product, you will get the volume effect according to your formula.
e.g. T RED — Volume effect according to you: (40 – 30) x 200 = 2.000.
Which is equal to volume effect (-1000) + mix effect (3.000).
Hi, Thank you for conveying your knowledge.
One question please. This exercise can be used to know the sales variation between the previous month and the current month?
Hello,
Yes. You would only have to replace budget data by previous month data.
In this sense, you could also analyze the variation of the current month vs. the same month last year.
Hope it helps.
Hi All, How you are dealing with the case if the BU was at 0. In this way, if I calculate with the same formulas all of my Skus with 0 will become with price Effect.
Thanks!
Hello Maria,
If I understood you well, you are considering 0 quantity budget for one (or more) references, right? In that case the model proposed in the Excel is not enough.
When having non-budgeted references, the template has to be “improved” and introduce a new effect, commonly called “New”. That “New” effect will collect the impact due to references you did not consider in budget (higher sales). If you are interested, contact me in my e-mail (j.raulgarcia@yahoo.es) and I will send you a new file which will consider this casuistry, which is quite common in many business.
Thank you for proposing this issue, because it is pretty interesting.
what about “new effect” or new items, how do you present this in the model.
Hello April,
You will need to adjust the excel, including a condition which classify as a new effect all products with budget unit 0.
Hello,
Is there any edition working with excel 2013″
Many thanks
Hello Greg,
Do you mean the graph? I do not think show, at this kind of graph was introduced in 2016 version.
I guess the table works well in any version.
HI Jose
Thanks for such a nice explanation of the mix- I have 2 questions please. In regards to the
1, Mix Effect:
If I have only 1 product and my actual quantity will be obviously the same, so does that mean that I will not have a mix effect?
2, if my volume effect is negative and my price effect is positive, what can I deduce from that ?
Thanks
Dana
Hello Dana,
Sorry for the delay in my answer.
1. Yes, exactly.
2. I cannot answer this question without knowing if the total variance is positive or negative. Maybe you could apply the price elasticity of demand theory to get your conclusions. Did you sold less units because you increased your price? As a result, your turnover was higher or lower than budget? If it was lower, maybe you should consider a decrease of price.
Hi
Thank you for sharing – this is very helpful and insightful!
Is there a way to segregate product and customer mix impact on profitability for companies with >1000 products/customers? Would love to hear your thoughts!
Hello Mag,
Yes. The model does not change if you have 3 or 1000 products.
Hello,
I am so happy to have found your website. This tools is very helpful!
Can you tell me what version of excel this file was built in? When I download it the chart is broken and lets me know I’m in the wrong version. Or alternatively if you could send me the component of the chart that would be very helpful in confirming I’ve rebuilt it properly.
Thanks so much,
KC
Excel 2016. If you have an older one, the graph will not work.
Hey – can you send me the file, this seems super helpful and easier to understand
Hello Fadi,
You have the link at the end of the post.
Thank you for the great example.
In your example above, the mix effect is a product mix effect.
How would the calculations look if you had 2 mix effects (say product mix and channel (wholesale vs retail) mix)?
Is this something you could potentially expand on our clarify? Thanks!
Hi Jose,
do you share your e-mail? I can not find it on this webpage?
Best regards,
Hello Boris,
You can find it in About me section.
Hi,
Great piece!
I was wondering if you have a template that includes variances in COGS, Sales all together and its impact on profit margin?
Can you share with me please?
Thank you
Hello Wodny,
Apologies for the late answers.
Very interesting question. You can apply the same calculation method for COGS. For example, if you use cotton as a raw material, you will need put the cost per unit in price. Nothing changes for units.
To extend the analysis to margin, you can sum the effects of sales and COGS. E.g. Margin volume effect = sales volume effect – COGS volume effect.
However, in price effect, I would only recommend to do that if the price variation in sales it is related with the price variation in COGS. E.g.: the sale price was higher than budget because the cotton price increased. On the contrary, if the price variation in sales and COGS are not related, you should wonder if it makes sense to merge both.
Hi, thank you for this great example. I have a question regarding a “New effect”. If budget price and budgeted units is 0, in your calculation it will be price effect. I would call it volume effect. Maybe you can send me a new file which will consider this casuistry with explanation.
Thank you
martin.ganz1@icloud.com
Hello Martin,
Apologies for the delay in my answer. The new effect is not considered in my model. You will need to adjust it and include a condition which isolate in mix effect the variation for non budgeted references in order to avoid the error you mention (in any case it is price effect as you well said).
I’m not quite sure how to interpret your approach reagarding volume impact. Volume for T black has no change in Actual vs Planned however a volume variance is shown.
The formula for volume that makes sense to me is: (Act Volume – Planned Volume) * Planned Price….
Hello Christian,
This formula matches with the volume effect + mix effect in my model. In your formula you put both effects together. We need to go further and understand the meaning of mix effect. Maybe you can check my other post about this topic:
https://undercontrolling.com/understanding-the-components-of-the-sales-bridge-price-volume-mix-analysis/
Hi, thanks for your sharing.
I would like to ask: I can not make a waterfall graph like you did. The turnover is not total in graph but become the increase. How can i do like you?
Hello Alvin,
I guess you already solved your problem. If not, try to double clic in the turnover bar, and in the graph options, in series option, you should enable “set as a total”.
Hi Jose Raul – Let me start with how much I enjoyed reading your article. The whole concept is written with such simplicity and it is so easy to comprehend. I have dropped you an email on j.raulgarcia@yahoo.es with some questions that you would be able to help. Would really appreciate if you can respond to it.
Thanks!
Thanks, very useful.
How can I add in the analysis also the variation of the Customer ?
For example, if I have Product A and B sold by Customer X and Y, i would love to see also the changing in the mix of customers .
Thank you for support !
Hello,
First of all, I guess you know you need to have in separate lines each product and each customer combination.
After that, have you tried to totalize the effects by customer?
Hey José,
I’m actually trying to figure out some variation on this, but I’m completely stuck. I’m calculating revenue by amount of members, price per hour and amount of hours. I’m trying to calculate the revenue impact by each driver. How would I approach this? It’s not P*Q*mix, but rather P*Q*hours?
Regards,
Robert
Hello Robert,
Apologies for the late answer, maybe you already solved the issue.
The model considers two variants, price and quantity. Mix is an effect (result), not a variant of the equation, so you cannot replace hours by mix as you suggested.
Maybe you can consider the people as your finished goods. Then the number of hours is Q, the rate as a P. If you have additional members that you did not budgeted, they can be considered as a “new effect” (which is not included in my model).
Thanks for this! Was really helpful to perform the analysis here at our company in Belgium.
Great insights. Thanks a lot.
Hope to see more of these interesting controlling blogposts in the future?
Thank you very much Thomas.
I hope so. Recently I could not dedicate much time to the blog due to a new professional project, but soon I will try to write about this and other topics.
Hello,
thanks for your reply. I managed to do the same thing with office 2013 with a “manual” process.