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!

 

[Letterman]

Unfortunately, I'm having trouble responding to private messages on this forum. The forum is telling me I have met my quota for messages, which is funny because I have sent none and only received one. Anyway, for the forum user who was interested in finding out how I solved the problem.... I found that by testing for zero price on either Actual or Budget, I could force Price Variance to zero which would then allow the Mix Variance to re-calculate. I then ran several different sets of data through the model and the variances started to make a lot more sense and were easier to explain. I hope this helps.

 

[jsnelson]

The Excel sheet you have linked.. Could you modify the the formulas in cells Q, R, and S and show those formulas on here for you how made the formulas work? Thanks!

 

[jsnelson]

Letterman,
I tried to message you but couldn't. Are you able to post on here the formulas you made?

 

[Letterman]

Yes. Happy to help. Working 16 hour days doing 2017 budgets. I have to recreate the changes so may take a day or two to respond. Sorry for the delay but things are a bit nuts for me at the moment...

 

[Letterman]

Good news, caught an unexpected break and had time to recreate the formulas. Here you go:

Q3: =IF(OR(L3=0,K3=0),0,K3*I3)
R3: =IF(OR(L3=0,K3=0),0,H3*(K3-L3)-S3)
S3: =IF(OR(L3=0,K3=0),C3,K7*(H3-H7)*(N3-O3))

Hope this helps!

 

[Letterman]

I found a couple of tweaks:

Anything in the formula referencing row 7 should be anchored. ie: $K$7

Also, for S3, i recommend the following change: (Notice addition of the "-D3")

S3: =IF(OR(L3=0,K3=0),C3-D3,$K$7*(H3-$H$7)*(N3-O3)

Enjoy!

 

[Enigma1984]

I found a couple of tweaks:

Anything in the formula referencing row 7 should be anchored. ie: $K$7

Also, for S3, i recommend the following change: (Notice addition of the "-D3")

S3: =IF(OR(L3=0,K3=0),C3-D3,$K$7*(H3-$H$7)*(N3-O3)

Enjoy!

Dear Letterman,
thank you very very very much for your help! I really have the same issue and couldnt find anywhere on internet the solution.
I used your formula
Q3: =IF(OR(L3=0,K3=0),0,K3*I3)
R3: =IF(OR(L3=0,K3=0),0,H3*(K3-L3)-S3)
S3: =IF(OR(L3=0,K3=0),C3,$K$7*(H3-$H$7)*(N3-O3))
but i have problem again. I have many products and some of them are new and some of them are not any more in sales but were in last period.
When I sumup the total of each variance/effect(V,P,M) (Q7,R7,S7 cells) and sumup these totals (T7) i will get the same value as the total sales value difference is (E7). And that is ok! Mathematicaly everything is ok BUT when I analyse the logic behind totals of each effect/variance then it is not ok. Beacause my sumtotal for volume variance is negative but in real life my volumes in curent period are higher compared to previous period, how to interpret/explain that to Managment or to myself? shouldnt that be positive effect?

PLEASE PLEASE HELP!! What iam missing?

 

[Aloisius]

Hey, guys, we are making this a whole lot more complicated than it needs to be. I, too, love Excel but in this barrage of answers we are getting lost in a veritable sea of Excel cell calculations. Let's simply get back to some simple basic math and examine the problem from its' very simple basic beginnings. Remember, all we trying to do here is to compare 2 "weighted averages" (the "Actual" weighted average and the "Budget" weighted average). It's just that basic and simple math provides the solution. This is not "Rocket Science". So, getting "back to basics":
Mix variance arises from 2 factors whenever a product is considered as part of a group. The first factor is the relationship between the product's budgeted profit rate and the aggregate budgeted profit rate of the group; the budgeted profit rate of a product may be higher or lower than the budgeted profit rate of the group. Note that the profit rate referred to here is the budgeted profit rate. This relationship is "locked in" at budget finalization and never changes during the budget year (a constant value). The second factor is the relationship between the actual sales mix of the product (as part of its group) and the budgeted sales mix of the product; the actual product sales mix changes throughout the budget year but the budgeted product sales mix remains constant. Note that the only factor, which varies during the budget year, is actual product sales mix.
. BPRDiff = (Budgeted Product Profit Rate - Budgeted Group Profit Rate)
. MixVar = (Actual Product Sales Mix - Budgeted Product Sales Mix)
The effect on mix variance of the group is a direct function of total actual sales for the group (TotActGrpSls). Given these component parameters, we can measure the impact of any given product's mix variance on the group (Mix Variance = MV) as follows:
. MV = (TotActGrpSls x MixVar x BPRDiff)
We can assume some example values for the purpose of illustrating the calculation:
. TotActPrdSls = $20,000 Total Actual Product Sales
. TotActGrpSls = $100,000 Total Actual Group Sales
. TotBudPrdSls = $30,000 * Total Budget Product Sales
. TotBudGrpSls = $200,000 * Total Budget Group Sales
. Actual Product Sales Mix = (TotActPrdSls / TotActGrpSls) = .20
. Budgeted Product Sales Mix = (TotBudPrdSls / TotBudGrpSls) = .15 *

. Thus MixVar = (.20 - .15) = .05 Mix Variance
. Budgeted Product Profit Rate = .34 *
. Budgeted Group Profit Rate = .46 *

. Thus BPRDiff = (.34 - .46) = -.12 * Budgeted Profit Rate Differential
. Thus MV = ($100,000 x .05 x (-.12)) = -$600
$600 Unfavorable Mix Variance
* Constant throughout Budget Year
The only other component of the product's profit variance is volume variance (VV). Order of calculation is important; MV must be calculated before VV. VV is a simple residual calculation:
. VV = [(TotActPrdSls - TotBudPrdSls) x Budgeted Product Profit Rate] – MV
. Thus VV = [(-$10,000 x .34) - (-$600)] = [-$3,400 + $600] = -$2,800
$2,800 Unfavorable Volume Variance
As a result of the above calculations, we have determined that this product contributed an unfavorable $2,800 toward its group's volume variance and contributed an unfavorable $600 toward its group's profit rate (mix) deterioration. We have successfully split the product's profit variance of $3,400 into the volume variance (-$2,800) and mix variance (-$600) components that affect the group. The next step would be to subject each component product of the group to this analysis, splitting each product's profit variance into its component volume and mix variance contributions.
The volume variance and mix variance of the total group are summations of the corresponding variances (VV and MV) of the individual products, which comprise the group. We might have calculated VV and MV for the total group by some other method but we would not have known about the contribution of the individual component products to the total group. Performing the above calculation on a product-by-product basis gives the important advantage of knowing the sources of the volume and mix variance components of the total group; no other method provides us with such invaluable information.
. TVV = Total Volume Variance = ∑VV
. TMV = Total Mix Variance = ∑MV
Okay, now let's get back to the original question that was asked in this forum. What happens if there are actual sales for which there was no provision in the budget. To answer that, look back at the Mix Variance calculation:
. MixVar = (Actual Product Sales Mix - Budgeted Product Sales Mix)
In the case of the sale of an unbudgeted product, the formula simply becomes:
. MixVar = (Actual Product Sales Mix - 0)
In addition, most importantly, the Budgeted Product Profit Rate = Actual Profit Rate. Plugging this into our Excel spreadsheets provides the exact answer that is easy to explain to management. Simple, intuitive, no gotchas.

 

[Enigma1984]

Dear Aloisius,

Thak you for your reply!
English is not my first language so sorry if Ididnt udnerstand.
But, i have calculated mix, price and quantity variance. I used excel example from the 1st or 2nd post in this thread. Formulas are:
Mix: IF(OR(K3=0;L3=0);C3-D3;$K$7*(H3-$H$7)*(N3-O3))
Pice: IF(OR(K3=0;L3=0);0;K3*I3)
Quantity: IF(OR(K3=0;L3=0);0;H3*(K3-L3)-S3)

K3: actual quantity
L3: budget quantity
C3: actual sales value
D3: budget sales value
K7: actual total quantity
H3: budget price for product
H7: total average budget price
N3: actual Mix
O3: budget mix
I3: difference between actual and budget price by product
S3: Mix variance

Function IF and OR are here because of products which are new or products which were in budget but are not in actual.
Do you think this is ok?
Its hard when we can not attach excel file here.