Hey just wanted to thank you for illustrating this calculation, it was really helpful for me. For the final formula would it be better to add the price and cost delta impact together. Since the change in GM rate is a change in Price and Cost. So instead of price, volume, mix...it would be GM rate, volume, mix. GM^TY - GM^LY = V^LY(Delta P - Delta C) + Delta V*(Price^LY - Cost^LY) + Delta V(Delta P - Delta C) Instead if lumping the cost impact into mix it should be a rate impact like price.
Very good video! Quick question in the DAX for Perfect Price Volume Mix (PVM) Calculation Power BI Tutorial video (posted Dec 20) - you mentioned that the price impact = Volume TY * (Price TY - Price LY) but here you mention that the price impact = Volume LY * (Price TY - Price LY). Why here is different ?
Please use TY as calculating base. Then, your Last Year Price equals to Pty-ΔP, where ΔP=Pty-Ply. Therefore, you got a new result align with triditional one. GMty-GMly =(Pty*Vty-Cty*Vty)-((Pty-ΔP)*(Vty-ΔV)-(Cty-ΔC)*( (Vty-ΔV)) =(Pty*Vty-Cty*Vty)-(Pty*Vty-Pty*ΔV-ΔP*Vty+ΔP*ΔV-(Cty*Vty- Cty*ΔV-ΔC*Vty+ΔC*ΔV)) =(Pty*Vty-Cty*Vty)-(Pty*Vty-Pty*ΔV-ΔP*Vty+ΔP*ΔV-Cty*Vty+ Cty*ΔV+ΔC*Vty-ΔC*ΔV) =Pty*Vty-Cty*Vty- Pty*Vty+Pty*ΔV+ΔP*Vty-ΔP*ΔV+Cty*Vty- Cty*ΔV-ΔC*Vty+ΔC*ΔV =+Pty*ΔV+ΔP*Vty-ΔP*ΔV - Cty*ΔV-ΔC*Vty+ΔC*ΔV =ΔP*Vty+ΔV*(Pty-Cty)+ ΔV*(ΔC-ΔP)- ΔC*Vty If there was any mistakes, please correct me as I am now using this refined formula for daily analusis
Great explanation, thank you very much!!! I work with similar product groups (only different cost/price structure/GM), divided into sub-categories further into items. A few questions: 1. Level of granularity within the category, down to item level summarized to total category? The lower down you go in the item hierarchy, the bigger chance of hitting 0 (discontinued vs. new item) due to small variations between items. My PCVM shows completely different on mid-level and item-level. 2. Mix between the categories, is that taken into account, or is the variation only by product category summarized together? The variation between high level product groups (ex. Premium -> Basic) is crucial for the overall GM. 3. Cost impact, I would like to know how much of the GM has been affected by cost savings/increases. My main target is to break down the GM change (increase/decrease in %-points) in to sales price change, material cost change, work cost change and mix between product groups in a easily graspable diagram
Can you explain how this would work with margin %s where the volume and price number bridge the previous periods margins to the current period. Take Geberit’s bridge in their presentation as an example
Great explanation. Thank you. Can you please explain the formula to calculate as Percentage? for example, I am trying to build a Bridge from Prior Year GM% of 20% to Current Year GM% of 15%...I am trying to understand how to bridge that in % for Price and Rate.
@@KnowledgeBankPro Yes, I follow that volume should not have impact when it comes to % point bridge. Looking forward to it and just in time to help explain my year end performance! Thanks again.
@@KnowledgeBankPro Hi! Did you happen to have a chance to make this update video yet. I know it would be super helpful to me as well as many others id presume. Thanks again for your video!
Have you addressed this somewhere? I have been looking for this answer for quite some time and would appreciate any direction toward finding a way to apply your formulas to a GM% bridge. The absolute $ margin math works great, but we are looking for a way to apply it to the GM%. Thanks!
Hi :) Thank you so much for this great course. Do you think you could explain us how you compute it with discounts and new product as show in our chart at the begining ? many thanks for sharing your knowledges
Thank you for the video. It was very helpful. I have built out a Gross Margin Variance analysis in Excel using your formulas. I applied the formulas to a specific category (Parts) and I get a number for the Price Impact on all Parts. I then applied the formulas to all the specific products in the Parts category. The sum of the price impact for each specific product does not tie to the result when the formula is applied to the entire category. I do not have access to Power BI. Can this be resolved in Excel?
there is an updated PVM tutorial here ua-cam.com/video/te5kKFqrB8U/v-deo.html and i go through the waterfall chart here ua-cam.com/video/BfACOEiPK8M/v-deo.html
Hey just wanted to thank you for illustrating this calculation, it was really helpful for me.
For the final formula would it be better to add the price and cost delta impact together. Since the change in GM rate is a change in Price and Cost. So instead of price, volume, mix...it would be GM rate, volume, mix.
GM^TY - GM^LY = V^LY(Delta P - Delta C) + Delta V*(Price^LY - Cost^LY) + Delta V(Delta P - Delta C)
Instead if lumping the cost impact into mix it should be a rate impact like price.
Kay M you are welcome! I am working on another video that will take a different approach to PVM for Revenue and GM, so please stay tuned
KnowledgeBank when will this happen?
@@martintibbie5511 happened last month: ua-cam.com/video/te5kKFqrB8U/v-deo.html
Very good video! Quick question in the DAX for Perfect Price Volume Mix (PVM) Calculation Power BI Tutorial video (posted Dec 20) - you mentioned that the price impact = Volume TY * (Price TY - Price LY) but here you mention that the price impact = Volume LY * (Price TY - Price LY). Why here is different ?
Please use TY as calculating base. Then, your Last Year Price equals to Pty-ΔP, where ΔP=Pty-Ply. Therefore, you got a new result align with triditional one.
GMty-GMly
=(Pty*Vty-Cty*Vty)-((Pty-ΔP)*(Vty-ΔV)-(Cty-ΔC)*( (Vty-ΔV))
=(Pty*Vty-Cty*Vty)-(Pty*Vty-Pty*ΔV-ΔP*Vty+ΔP*ΔV-(Cty*Vty- Cty*ΔV-ΔC*Vty+ΔC*ΔV))
=(Pty*Vty-Cty*Vty)-(Pty*Vty-Pty*ΔV-ΔP*Vty+ΔP*ΔV-Cty*Vty+ Cty*ΔV+ΔC*Vty-ΔC*ΔV)
=Pty*Vty-Cty*Vty- Pty*Vty+Pty*ΔV+ΔP*Vty-ΔP*ΔV+Cty*Vty- Cty*ΔV-ΔC*Vty+ΔC*ΔV
=+Pty*ΔV+ΔP*Vty-ΔP*ΔV - Cty*ΔV-ΔC*Vty+ΔC*ΔV
=ΔP*Vty+ΔV*(Pty-Cty)+ ΔV*(ΔC-ΔP)- ΔC*Vty
If there was any mistakes, please correct me as I am now using this refined formula for daily analusis
Great explanation, thank you very much!!!
I work with similar product groups (only different cost/price structure/GM), divided into sub-categories further into items.
A few questions:
1. Level of granularity within the category, down to item level summarized to total category? The lower down you go in the item hierarchy, the bigger chance of hitting 0 (discontinued vs. new item) due to small variations between items. My PCVM shows completely different on mid-level and item-level.
2. Mix between the categories, is that taken into account, or is the variation only by product category summarized together? The variation between high level product groups (ex. Premium -> Basic) is crucial for the overall GM.
3. Cost impact, I would like to know how much of the GM has been affected by cost savings/increases.
My main target is to break down the GM change (increase/decrease in %-points) in to sales price change, material cost change, work cost change and mix between product groups in a easily graspable diagram
ok, lots of questions, I have done a bunch more videos on PVM, but I would recommend this and go from there ua-cam.com/video/te5kKFqrB8U/v-deo.html
for the margin bridge, might be best to split cost separately right ? its a bit confusing to put cost element inside the mix bar
Can you explain how this would work with margin %s where the volume and price number bridge the previous periods margins to the current period. Take Geberit’s bridge in their presentation as an example
i can and i will, but after we have moved everything to a new location on our website: obvience.com .. please give us a few weeks
How do i do this but for the margin %? I can get the $ amounts but not % margin change
PVM topic will get a refresher in the next few weeks. We are also working on free courses and labs that will be available on our website: obvience.com
Great explanation. Thank you. Can you please explain the formula to calculate as Percentage? for example, I am trying to build a Bridge from Prior Year GM% of 20% to Current Year GM% of 15%...I am trying to understand how to bridge that in % for Price and Rate.
I should address this in a future video... just be aware that the Volume impact does not contribute to the GM%
@@KnowledgeBankPro Yes, I follow that volume should not have impact when it comes to % point bridge. Looking forward to it and just in time to help explain my year end performance! Thanks again.
@@KnowledgeBankPro Hi! Did you happen to have a chance to make this update video yet. I know it would be super helpful to me as well as many others id presume. Thanks again for your video!
Have you addressed this somewhere? I have been looking for this answer for quite some time and would appreciate any direction toward finding a way to apply your formulas to a GM% bridge. The absolute $ margin math works great, but we are looking for a way to apply it to the GM%. Thanks!
Same question as everyone else....need a video on GM% !!!
Hi :) Thank you so much for this great course. Do you think you could explain us how you compute it with discounts and new product as show in our chart at the begining ? many thanks for sharing your knowledges
i cover new and discontinued products approach here: ua-cam.com/video/vh8zJYRCMUc/v-deo.html
Thank you for the video. It was very helpful. I have built out a Gross Margin Variance analysis in Excel using your formulas. I applied the formulas to a specific category (Parts) and I get a number for the Price Impact on all Parts. I then applied the formulas to all the specific products in the Parts category. The sum of the price impact for each specific product does not tie to the result when the formula is applied to the entire category. I do not have access to Power BI. Can this be resolved in Excel?
you have done a few more videos on PVM, latest can be found here: ua-cam.com/video/vh8zJYRCMUc/v-deo.html maybe you can find some inspiration there
Is it possible to Quantify the price volume or mix impact to percent impact of GM%?
it is, just keep in mind that volume does not drive GM%, so it should be taken out of the calc
If we do a Gross Margin PVM in percentage terms, is it not true that we should have no volume impact?
that's right, volume component of PVM should not affect the GM% number
great tutorial, except that I can not use measures in my waterfall chart. Your guidance is required. Thanks.
there is an updated PVM tutorial here ua-cam.com/video/te5kKFqrB8U/v-deo.html and i go through the waterfall chart here ua-cam.com/video/BfACOEiPK8M/v-deo.html
Hi, How to see the variance in percentage of gross margin?
volume portion of the PVM in GM should not be considered as part of GM% change
Hi sir, thanks for this tutorial. Kindly help me answer the questions that I posted on your blog
thanks in advance.
Let me check
Cost should be a separate item in the GM waterfall. it should not be part of mix as shown in this video...
Will you share this example power BI file as well?
I am working on a comprehensive PVM tutorial, that will cover two major approaches to PVM calculation. Hoping to finish it in the a week or two