Handling a 0 (Zero) for Price, Volume and Mix Analysis

[Letterman]

I'm new as a member to this forum but have admired the contributions from members for some time as a reader.

I have a question on Price, Volume and Mix (PVM) calculations with the nuance regarding introduction of a 0 (Zero) into the analysis.

Most of the PVM analysis and the corresponding work I have found on the internet (example here: https://community.qlik.com/thread/47625 ) seems to have some difficulty properly categorizing variances involving a zero. (Spreadsheet here: https://community.qlik.com/servlet/JiveServlet/download/619221-128866/Price%20Volume%20Mix%20Variance%20.xlsx)

For example, the PVM analysis usually works great as long as there are numbers available for Budget and for Actuals relative to units and sales. However when we sell a product that we didn't forecast, the formula insists on calling that variance "Price" instead of "Mix". This results from the "Price" variance formula taking the Actual price (which has a value) minus the Budget price (which is zero) and multiplying it by the actual units.

In my view, this "muddies" the water and makes it difficult to explain to a CEO or VP/GM the "why" behind the variance. In their minds, selling something we didn't forecast is clearly a "Mix" issue and has nothing to do with "Price" but it seems to be accepted practice to allow the formulas to dump the variance into the "Price" bucket.

This gets even more interesting when you do Price, Volume, Mix based on standard cost instead of Gross Sales. The PVM formula says the variance is a "Price" variance which would suggest that the standard cost changed when we know full well that the standards have not changed or been rolled recently. So the CEO or VP/GM would not agree with the analysis that suggests the difference is due to "Price"... and I agree with them. It isn't correct.

I'm also wondering if the same issue exists with the typical "Volume" variance calculations. It seems to me that volume should only include the portion of the variance that truly had no effect on contribution margin %. A 2007 Mr. Excel forum thread found here has links to an offsite website that seems to touch on that a little as well. http://www.mrexcel.com/forum/excel-questions/537105-volume-mix-price-cost-analysis-help.html

I'm curious to see if other Mr. Excel forum users have run into a similar "definition" problems between Price, Volume or Mix and how it may have been handled relative to the effect of a 0 (Zero) in either budget or actual, units or sales? Is there a more accurate, yet simple way to accommodate the zero?

I personally added an "if" statement which tests for zero budgeted units and if a zero is found, I dump the variance into Mix. Is that technically correct?

Thanks!

 

[Aloisius]

Dear Enigma1984,

I appreciate your continued interest in this important topic; I see that it fascinates you as it has continuously interested me. It would be great to sit down together and be able to go over its' details; it is limiting in this forum in that we cannot share/post our Excel spreadsheets and I would be banned (quite appropriately!) if I posted an applicable website link. That is indeed a shame, isn't it? You might want to Bing search the web for "Volume and Mix Analysis" for a more detailed discussion of this engrossing topic. I am still lost in the sea of Excel cell calculations that have been presented here.

Explicit in my recommendations is that "Volume and Mix" differential analysis calculations are completely separate from any considerations of Price, Cost or Fx variances. In theory, V&M should only be carried out after ALL Price, Cost, Fx, etc., etc. variances have been completely eliminated (a difficult task to be sure). V&M, however, can be carried out completely separate from other variance calculations and can be carried out just as soon as actual (physical) volumes have been determined; that can be very important when one faces the time constraint pressures of "the real world". V&M analysis is absolutely correct, even when it is handled "all by itself". The results of V&M analysis do not change, regardless of what Price, Cost, Fx, etc., etc. variance analyses ultimately contribute to some "total" solution.

About the "Unbudgeted Product" situation. I do not feel that (your rather clever) "IF" statements are needed. The "Unbudgeted Product" can be assumed to exist (at least mathematically) in the "Budget" weighted average. It is simply that its' budgeted mix % can logically be assumed = 0%. I think that this obviates the need for an "IF" conditional test.

What I am saying here is that there is a mathematical identity implicit in any V&M analysis:

Actual Product n Profit Rate === Budget Product n Profit Rate

This not only simplifies the calculations, it seems to make logical sense. It simply says that Product n was originally included in the Budget but it had a 0% mix . . . in other words, it had absolutely NO impact on the Budget's weighted average Profit Rate even though it (correctly) impacted the Actual's weighted average Profit Rate. As a result of this, the profit rate effect of the unbudgeted product winds up ONLY in mix variance . . . and that is the ONLY place that it should appear . . . it absolutely does NOT belong in price variance!

I fear that I have taken "a lot of words" to describe what can be so simply expressed in the beauty of an Excel spreadsheet. Hope this helps in your journey; Good Luck!


Aloisius

 

[Enigma1984]

Dear Enigma1984,

I appreciate your continued interest in this important topic; I see that it fascinates you as it has continuously interested me. It would be great to sit down together and be able to go over its' details; it is limiting in this forum in that we cannot share/post our Excel spreadsheets and I would be banned (quite appropriately!) if I posted an applicable website link. That is indeed a shame, isn't it? You might want to Bing search the web for "Volume and Mix Analysis" for a more detailed discussion of this engrossing topic. I am still lost in the sea of Excel cell calculations that have been presented here.

Explicit in my recommendations is that "Volume and Mix" differential analysis calculations are completely separate from any considerations of Price, Cost or Fx variances. In theory, V&M should only be carried out after ALL Price, Cost, Fx, etc., etc. variances have been completely eliminated (a difficult task to be sure). V&M, however, can be carried out completely separate from other variance calculations and can be carried out just as soon as actual (physical) volumes have been determined; that can be very important when one faces the time constraint pressures of "the real world". V&M analysis is absolutely correct, even when it is handled "all by itself". The results of V&M analysis do not change, regardless of what Price, Cost, Fx, etc., etc. variance analyses ultimately contribute to some "total" solution.

About the "Unbudgeted Product" situation. I do not feel that (your rather clever) "IF" statements are needed. The "Unbudgeted Product" can be assumed to exist (at least mathematically) in the "Budget" weighted average. It is simply that its' budgeted mix % can logically be assumed = 0%. I think that this obviates the need for an "IF" conditional test.

What I am saying here is that there is a mathematical identity implicit in any V&M analysis:

Actual Product n Profit Rate === Budget Product n Profit Rate

This not only simplifies the calculations, it seems to make logical sense. It simply says that Product n was originally included in the Budget but it had a 0% mix . . . in other words, it had absolutely NO impact on the Budget's weighted average Profit Rate even though it (correctly) impacted the Actual's weighted average Profit Rate. As a result of this, the profit rate effect of the unbudgeted product winds up ONLY in mix variance . . . and that is the ONLY place that it should appear . . . it absolutely does NOT belong in price variance!

I fear that I have taken "a lot of words" to describe what can be so simply expressed in the beauty of an Excel spreadsheet. Hope this helps in your journey; Good Luck!


Aloisius

Aloisius,

Sorry for bodering you but im lost and I really want to implement this analysis in my new company. Before in my previous company I was doing only price, quantity and Fx impact on revenue side and COGS side separtely, but now here in new company it is important to do also and mix impact. Now i want to calculate price, quantity and mix effect.

 

[Aloisius]

Complicated multi-faceted question. Wrestled with it for many years but Excel can provide a complete answer. See the above site for a complete analysis of the question . . . and, most importantly, Excel worksheet examples which are easy to follow.


​

 

[Akali]

Hi guys,
Do you Know how to implement FX rate in variance analysis? In current model im calculating price, volume and mix effects and now i want to add FX rate but im not sure how to set formula. Original rate is Croatian kunas (HRK) but bord want to see numbers in EUR.