I have covered Price Volume Mix (PVM) analysis for Revenue Variance in the earlier article. I recommend that you take a look at it first because PVM for Revenue variance analysis is much easier to understand as there are fewer moving parts to think about in the calculations.

Assuming that you are now well versed in the PVM for Revenue analysis, let’s jump in to PVM for Gross Margin analysis. Again, I will save you a lot of reading time by having only the essentials of the material in this post and coving most of the content in a YouTube video.

I mentioned before that PVM for Gross Margin is a lot more complicated than PVM for Revenue. The reason it is more complicated is because Revenue is a result of two variables, Volume and Price, whereas to do a similar analysis for Gross Margin we also have to account for Unit Cost.

Let us start with basic definitions and assumptions. Let us say that we will be analyzing Gross Margin This Year (or Actual) and comparing it to Gross Margin Last Year (or Target)

GM^{TY} |
– Gross Margin This Year |

GM^{LY} |
– Gross Margin Last Year |

R^{TY} |
– Revenue This Year |

R^{LY} |
– Revenue Last Year |

COGS^{TY} |
– Cost of Goods Sold This Year |

COGS^{LY} |
– Cost of Goods Last Year |

V^{TY} |
– Volume This Year |

V^{LY} |
– Volume Last Year |

P^{TY} |
– Price This Year |

P^{LY} |
– Price Last Year |

C^{TY} |
– Unit Cost This Year |

C^{LY} |
– Unit Cost Last Year |

Our goal is to arrive at a formula where Gross Margin variance (R^{TY} – R^{LY}) (I will explain all buckets of the PVM in my video) is represented by

GM^{TY} – GM^{LY} = Price^{Impact} + Volume^{Impact} + Mix^{Impact}

In addition, our goal is to implement the calculation in such a way that the Price^{Impact} of the Revenue PVM is the same as Price^{Impact }in the Gross Margin PVM.

Let us begin!

if GM^{TY }= R^{TY }– COGS^{TY }= P^{TY}*V^{TY} – C^{TY}*V^{TY}

and GM^{LY }= R^{LY }– COGS^{LY }= P^{LY}*V^{LY} – C^{LY}*V^{LY}

then GM^{TY} – GM^{LY} = P^{TY}*V^{TY} – C^{TY}*V^{TY }– (P^{LY}*V^{LY} – C^{LY}*V^{LY})

if ΔP = P^{TY} – P^{LY} (change in *price*) and ΔV = V^{TY} – V^{LY }(change in *volume*) and ΔC = C^{TY} – C^{LY }(change in *unit cost*)

then GM^{TY} – GM^{LY} = (P^{LY} + ΔP)*(V^{LY} + ΔV) – (C^{LY} + ΔC)*(V^{LY} + ΔV) – (P^{LY}*V^{LY} – C^{LY}*V^{LY})

or GM^{TY} – GM^{LY} = P^{LY}*V^{LY} + ΔP*V^{LY} + P^{LY}*ΔV + ΔP*ΔV – C^{LY}* V^{LY} – ΔC*V^{LY} – C^{LY}*ΔV – ΔC*ΔV – P^{LY}*V^{LY} + C^{LY}*V^{LY}^{
}

items in RED cancel each other out

so GM^{TY} – GM^{LY} = ΔP*V^{LY}^{
}+ P^{LY}*ΔV + ΔP*ΔV– ΔC*V^{LY} – C^{LY}*ΔV – ΔC*ΔV

or GM^{TY} – GM^{LY }= ΔP*V^{LY} + ΔV*(P^{LY}– C^{LY}) + ΔV*(ΔP – ΔC) – ΔC*V^{LY}^{
}

From the formula above, we can now define our Gross Margin PVM calculations as the following:

Price |
= ΔP*V^{LY} |

Volume |
= ΔV*(P^{LY}– C^{LY}) |

Mix |
= ΔV*(ΔP – ΔC) – ΔC*V^{LY} |

Hi, thank you for this interesting article.

Is there any chance you could share with us an example power bi file?

Thank you so much!

Kind regards,

Ronald

Amazing …. if you share the same power BI file, use full to us.

Hi, can we have formula for gross margin ratio variance ? I mean the ration not the absolute amount.

I am working on a very comprehensive tutorial with a sample file to cover two ways of implementing PVM for Revenue and Gross Margin. Unfortunately, I am not moving as fast as I would like, but I am hoping to finish it in the next couple of weeks, so you will be able to look in the file and see all the logic there

Do you have a sample file that you could share with me?

Not yet, sorry

making progress on this, the new video is available here: https://businessintelligist.com/2020/11/20/the-definitive-guide-to-price-volume-mix-calculation-pvm-in-power-bi-theory/ and in the next video I will be providing a sample data set with all the calcs

Hi biZ-i!

First of all thank you for sharing your knowledge! I am struggling with a mix calculation were I don’t have a variation in Cost or Price but yes in volume and mix. With the formula that you are using if my variation in price and cost are =0 the result of the mix impact will be 0 but the mix may be indeed changing and generating an impact. how can I calculate this impact in this situation?

Thanks a lot for your time and effort!

Bests,

Fran

I am working on a video to address it but it’s taking me a while to finish it… Hopefully in the next couple of weeks

This is great! How do we calculate volume and Price impact for a single product Year on year. There is no mix impact there.

Great explanation and good model to work with. However, I’m used to showing the cost/price squeeze, i.e. the company’s capability to pass on any purchase cost variances to their customers. On the margin PVM walk I would therefore show the net of your price impact and (part of) the mix element.

The cost/price squeeze would then show as (ΔP-ΔC)*VLY, whilst your mix is ΔV*(ΔP – ΔC).

Hey Could you please please share the PowerBI file?

Hey, thanks for the article, is amazing! it really piqued mi interest.

I am gonna post one thing that helped me understand your article and might helped someone else, which was:

the second term of Mix impact ‘– ΔC*VLY’ seemed out of place, so I took it to Price Impact making that ‘ΔP*VLY – ΔC*VLY’ == VLY*((PTY-CTY)-(PLY-CLY)) == VLY(GMTY-GMLY)

Arriving then at

‘Price’ impact: VLY(GMTY-GMLY) == VLY*ΔGM

Vol Impact:ΔV*(GMLY)

‘Mix’ : ΔV*(ΔP – ΔC) == ΔV*(GMTY-GMLY) == ΔV* ΔGM

You can immediately see the analogy on your Price Vol Mix for revenue…

Once again, thanks a lot. Your explanation is extremely clear and easy to follow (took me more than a couple of hours)

As for people saying this is right or wrong… if you follow the math you will end with the same result. And i understand that other approaches might satisfy your need, however in any case that does not mean this is wrong !

hi, you may want to check out my latest post on PVM found here: https://businessintelligist.com/2020/11/20/the-definitive-guide-to-price-volume-mix-calculation-pvm-in-power-bi-theory/

Is it possible to separate the cost impact? Having price, cost, volume and mix would be really useful.

I will be uploading a new video on Revenue PVM today or tomorrow and after that I will tackle the GM PVM again in more detail