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.
I would also be interested to see this same analysis in respect of Gross Profit. Some sort of Bridge from budget to actual. Thanks again for this very clear way of seeing through what at times appear very complex
Thank you Maurice. I take note of the suggestion for future posts. This analysis can be applied to sales, purchases, and profits.
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.
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.
Hi Jose, would it be possible if you can share the file with added New dimension? Thank you so much for your helpful explanation.
Hello Hang,
Please reach me at my e-mail address or LinkedIn.
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://thecostelement.com/understanding-the-components-of-the-sales-bridge-price-volume-mix-analysis/
Hi Jose,
Thanks for the explanation.
When performing the analysis, I was asked why the volume effect of a particular item is negative despite selling more than planned?
I explained the impact is actually in the mix effect but it did not resonate. What would be your recommendation in explaining to wider team?
In addition, what level of details would you recommend in performing this analysis as the more detail I go, the higher the mix impact it seems? e.g. Customer-product code level? Country-product group….
Thanks
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 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.
Hi Jose,
Is it possible for the same bridge to be used on margin price, volume, mix analysis?
I would like to account for delisted and new sku’s when comparing margin in the current year to the prior year.
Many Thanks
Hello Marie.
Certainly you can apply this for margin. You just need to make couple of adjustments: replace price by unitary margins and budget by last year Then, apply the same logic to obtain your analysis.
Hello, your video is very useful!
If I understand correctly, the closer the % total between Budget and Actual, the smaller mix impact we will have?
I have done this model for my company but I found the mix impact is always much larger than volume impact, which my boss really doesn’t like. Since we sell different product to different customers (e.g. Product A to Nancy, Product A to Billy …) and you see that we also consider customer mix, it has a very small chance that the % will be the same between Budget and Actual.
Would you have any suggestions that could reduce the mix impact and increase volume impact in the model? Many thanks!
Hello Mag.
I get your point. Indeed, I have often faced the same situation, having the same product for different clients treated as a different products. I have a question, do you have a different price for each customer?
hi jose,
Great example – do you have an idea how I can add more dimensions? we sell multiple products in multiple channels at different price (wholesale / retail) – would like to be able to split mix effects in a channel mix and product mix. Have you ever done this?
Hello José.
Thank you for a detailed post.
On reviewing March Actual vs. March Budget, you have actual sales, but it is not budgeted. Likewise, budgeted sales but no actual sale. How do you account for this in the calculations?
Hello DJ,
Isolate that impact in a fourth effect, often known as New Effect.
Thanks for this article! This is the most comprehensive article I have read as now since you use not just define the effects clearly but also calculate the effect in a direct way. In my previous experiences, people usually just calculate the mix effect via indirect way Mix Effect=Total Effect-Price Effect-Volume Effect. Besides, when I calculated the effect by the base level instead of the higher hierarchy, I had been requested to use an inaccurate way to calculate price effect by the new quantity and overlooked the mix effect. With your article, I can justify my calculation and rationale if meeting the same controversies.
That’s cool Alice.
Hi,
thanks for this model – it’s simple and intuitive, BUT I’ve come across the following problem:
if in Actuals, we sell a new Product that wasn’t planned in Budget, the variance shows up as price effect. Or rather, there are two scenarios possible:
1- if the product existed in budget with a price but no units, the variance is correctly reported as Mix effect.
2- if the product didn’t have a price, the variance is incorrectly reported as price effect.
The problem is that it’s in the nature of Actuals that new items turn up that weren’t budgeted (or sold in prior year, if it’s a yr-o-yr bridge). So in most cases, the underlying database won’t show a price in the budget/prior yr comparison column.
Any ideas how this could be addressed?
Hello Eckhard.
These sales must be isolated in a fourth component. We can call it “New effect”.
Simple example makes it easy to follow, I have used this in explaining to business how they can use these features in our newly developed Margin Bridge Analyser tool. Using PowerBI makes it easy for users to filter on specifics to come to the best business decisions. How would you propose to take this to the next level? What other analysis tools can be of value?
A few come to mind Henrik 🙂 Great idea to build reports based on this using PowerBI.
I use this model to analyze transportation costs versus a budget and/or prior periods, Volume, price, and mix impact transportation cost in an identical manner. I am also struggling with the “new” item concept. In other words, a lane is shipped that is not budgeted. Can you send me the logic for dealing with “new”? Thanks in advance….
Hello Joe,
All unbudgeted products must be allocated into the “New” component. Contact me if you need further help so I can send you a file.
Hi, thank you for this great explanation. When dealing with 2+ dimensions for mix (for ex. product, region, etc), what is a more appropriate way to create the analysis to capture the price and mix effect? Build separate tables by product only, Region only and get the Price/Qty/Mix view for each aggregate dimension. Build one big table with all dimensions and get the PVM and if needed then add them to get to the variation attributable to price, to mix.
Thank you so much for the clear explanation.
It helped me a lot in my analysis.
Greetings from Brazil. =)
I am glad to hear that Thiago 🙂
Hello,
Thanks for the analysis. Question on the volume part of volume, mix and price:
– isn’t it counter intuitive that volume impact of T-red is negative (-1000) whereas the actual T-red volume is higher than budgeted?
Hello Andy,
I understand your point, and certainly it may look like this. However, you need to think on the “role” of the mix component. Did you see this other post?
Hi, very clear explanation in your blog!
A question: what if a product portfolio’s products have completely different measurements (e.g. Prod A is sold by boxes, Prod B is sold by pieces), or different products are sold at drastically different scales (e.g. Prod A is sold by millions, Prod B is only sold by a dozens), how do you adjust the volume and mix calculations?
Good question Ken.
It doesn’t matter. You can still use the model. The column “quantity” doesn’t necessarily have to be expressed in the same base unit for all products, nor do they need to be of the same type.
However, you just need to be consistent with the way you did your budget and obviously use the same base units in actual and budget for the same product.
Hi José,
Very insidefull video and excel. Thanks for this.
I think there is one thing i dont get in your model is, if i change volume for only 1 product in the actuals compared to plan, leaving the remaining Budget/Acutal consistant, why should volume impacts all products?
Thanks a lot for you explainantion in advance,
Mike
Hi Mike, thank you.
Because you are changing the mix, and volume and mix are interdependent.
Hi Jose,
thanks for sharing. Could you share the template including the new / non-budgeted references as well please?
Thanks in advance
Hello Robert, sure, please contact me at my e-mail or LinkedIn.
Thank you very much! Love the bridge with product family.
Thank you Andy!
Test 3
Test 5