Math Is Fun Forum

GenLee

Guest

How to calculate attribution of % variance knowing absolute values?

Hi everyone,

Can you please help me as this is definitely a simple calculation, but I cannot figure it out no matter how hard I try and it`s driving me nuts?

I want to calculate attribution of % variance knowing absolute values` variances in nominator and denominator. How do I do that?

E.g. I want to be able to explain margin % variance between a margin of 80% (80 / 100) in period 1 and 82% (90.2 / 110) in period 2. I know absolute values that comprise the variance between 80 and 90.2 and absolute values that contribute to the change from 100 to 110, but how do I get this to translate to %`s that make up 2% variance between two periods? It`s not just a simple ratio of contributing factors to (110 - 100 = 10), is it?

Thank you!!!

Bob

Administrator

Registered: 2010-06-20

Posts: 10,143

Re: How to calculate attribution of % variance knowing absolute values?

hi GenLee

Welcome to the forum.

I am not fully understanding your question but you might find this helpful:

https://en.wikipedia.org/wiki/Pooled_variance

Any calculation on two variances has got to depend on the population / sample size.

If that's not what you want, perhaps you could make up and post a small data set (n=5 say) that shows what you are after.

Bob

---

Children are not defined by school ...........The Fonz
You cannot teach a man anything;  you can only help him find it within himself..........Galileo Galilei
Sometimes I deliberately make mistakes, just to test you!  …………….Bob

GenLee

Member

Registered: 2015-08-07

Posts: 6

Re: How to calculate attribution of % variance knowing absolute values?

Hi Bob,

Let me explain this in a bit more detail. Basically I'm working on an economics problem where I'm calculating a margin % rate (margin / revenue) for two periods:
period 1 = 80 margin / 100 revenue = 80%
period 2 = 90.2 margin / 110 revenue = 82%

Now I know that in absolute terms the margin grew 10.2 from period 1 to period 2 due to: change in price of 4 and costs reduction of 6.2 (that's given).
Same goes for revenues - an increase from 100 to 110 is driven by: change in price of 5, volume of 2 and product mix of 3 (again, that's just given).

Now I need to know how each of above 5 factors (change in price on margin, cost reduction, change in price in revenues, change in sold volumes and product mix) have contributed to the 2% increase in the margin. I.e. how much of those 2% change between 2 periods is, say due to change in price in revenues, or due to a shift in volume, etc.?

Thanks again!

Bob

Administrator

Registered: 2010-06-20

Posts: 10,143

Re: How to calculate attribution of % variance knowing absolute values?

hi GenLee

Economics.  Oh dear.  I know nothing about that, but, if no one else is going to chip in, you may be stuck with me. 

What may work is this .... you teach me some economics and I'll put in the maths. 

Here's what I've got so far:

Things I'm not clear about:

I assume volume = amount sold.  In that case I've got to insert a number or letter for that, eg.  number sold in period 1 = N

mix.  What is that?

Price.  Why is there a price factor for the sales and also for the profit ?

What I'd like to do is to update the above table to take account of actual item price and number sold.

Is that a plan ?

Bob

---

Children are not defined by school ...........The Fonz
You cannot teach a man anything;  you can only help him find it within himself..........Galileo Galilei
Sometimes I deliberately make mistakes, just to test you!  …………….Bob

GenLee

Member

Registered: 2015-08-07

Posts: 6

Re: How to calculate attribution of % variance knowing absolute values?

Hi Bob,

Sorry for delayed response: I was away for several days.

To your questions: the variances themselves, i.e. volume, mix, price, etc. are not important (or at least that's how I see it) - I could've easily named them x, y and z, etc. It's just that these are typical sources for variance in revenues and costs. My question is more broad and math-related, not economics':

Knowing items that comprise absolute variances for revenues and costs (whatever these items are!), how do I "translate" these items to the % variance in the margin?

In other words, if I know revenue for period 1 and absolute numbers that affect its change to the value in period 2 + the same for costs, how do I bridge costs / revenue ratios for two periods using those absolute variance items? Is there a way to tell that of the 2% variance (82% to 80%), e.g. x contributed, say, 0.4%; y contributed 0.5%, etc. such that the sum of those bridging items adds up to 2%?

Hope that clarifies what I'm trying to achieve here

Thanks again!

GenLee

Member

Registered: 2015-08-07

Posts: 6

Re: How to calculate attribution of % variance knowing absolute values?

I just realized that my numbers in variances don't add up, really sorry for extra confusion that might've caused

Here's a corrected and cleaned up layout of the problem:

Hopefully it all makes much more sense now. In essence, I'm trying to find out ??? numbers expressed as % changes in margin.

Bob

Administrator

Registered: 2010-06-20

Posts: 10,143

Re: How to calculate attribution of % variance knowing absolute values?

hi GenLee,

Thanks for the table.  That has helped a lot.  I've written out some formulas for what you are calculating.  But I am mystified about what you want.  It's your use of the word 'variance' that is confusing me.  In statistics a variance is a recognised word for a particular calculation on a set of data.  That's what I thought you wanted in your original post which is why I replied with a link to the variance formulas.

But it doesn't seem that was correct.  So now I'm assuming you just mean 'the amount by which something varies'.  eg.  If a number goes up from 5 to 7 then it has varied by 2 and as a percentage that is (7-5)/5 x 100 = 2/5 x 100 = 40%.

Now the revenue, costs and margin have all varied between period 1 and period 2, so we can calculate the amount these figures have varied.

But reason 1 etc appear to be the actual change between the periods so you already know the amount by which they have varied.  You could only calculate something more if you knew the reason 1 value at the start and the reason 1 value at the end.  Then you could do (end value - start value)/start value x 100 to get the percentage change.

That's all I can think of at the moment.  Sorry. 

Bob

---

Children are not defined by school ...........The Fonz
You cannot teach a man anything;  you can only help him find it within himself..........Galileo Galilei
Sometimes I deliberately make mistakes, just to test you!  …………….Bob

GenLee

Member

Registered: 2015-08-07

Posts: 6

Re: How to calculate attribution of % variance knowing absolute values?

Hi Bob,

Yep, sorry if the term "variance" was misleading. It's meant to mean simple arithmetical difference or "delta" between the numbers, not statistical variance.

As for my end result: I'm attempting to understand how much each of the reasons attributed to a 2% change in margin between two periods.

Perhaps, let me illustrate this point in terms of what I think is an incorrect approach, but that might give you a hint as to what I'm trying to achieve here:

Reason 1 contributes to the overall margin change: 5 / (90.2 - 80.0) * (82% - 80%) = 0.98%
Reason 2 contributes to the overall margin change: 2 / (90.2 - 80.0) * (82% - 80%) = 0.39%
Reason 3 contributes to the overall margin change: 3 / (90.2 - 80.0) * (82% - 80%) = 0.59%
Reason 4 contributes to the overall margin change: 0.2 / (90.2 - 80.0) * (82% - 80%) = 0.04%

The sum of all 4 adds up to 2% of the overall change in margin. But it can't be that simple, can it? For once, margin %'s are calculated using different revenue numbers for two periods in the denominators, so I'm reluctant to think that the calculation is that simple...

Bob

Administrator

Registered: 2010-06-20

Posts: 10,143

Re: How to calculate attribution of % variance knowing absolute values?

hi GenLee,

I've finally understood.  Thanks for the clarification.  Sorrry it's taken my old brain so long to get there.

So, a few seconds before I looked at your approach, I thought about how I'd do this, and I came up with exactly that, too.  So it seems you have answered your own question. 

Galileo would be very pleased.

Bob

---

Children are not defined by school ...........The Fonz
You cannot teach a man anything;  you can only help him find it within himself..........Galileo Galilei
Sometimes I deliberately make mistakes, just to test you!  …………….Bob

GenLee

Member

Registered: 2015-08-07

Posts: 6

Re: How to calculate attribution of % variance knowing absolute values?

Hi Bob,

That simple at the end of the day?

Something nags me about the fact that 82% and 80% have been calculated with a different revenue number in the denominator. Had both percentages been calculated using the same "reference point" I would've concurred, but with different denominators? For some reason it seems we're unwittingly oversimplifying the calculation here...

Bob

Administrator

Registered: 2010-06-20

Posts: 10,143

Re: How to calculate attribution of % variance knowing absolute values?

You have 5 + 2 + 3 + 0.2 = 10.2, actual change.

This give rise to a 2% change.

By adding these factors without any 'weighting', you are assuming they are equally weighted.

If you say that 2% is the percentage obtained from 10.2, then the factors have to contribute proportionally.

So the 5 is 5/10.2 of the 2% and so on.

And that is exactly the calculation you have done.

I suspect in the real world this is an oversimplification, simply because it doesn't take account of market forces, how peoples spending is varying, and that there may be several products / unit sizes contributing to the variations.

But, as soon as you decided to add 5 + 2 + 3 + 0.2, you were creating a model in which the factors are the only ones and they are equally weighted.  From that moment on the laws of proportion take over and what you are doing becomes correct for this model.

Bob

---

Children are not defined by school ...........The Fonz
You cannot teach a man anything;  you can only help him find it within himself..........Galileo Galilei
Sometimes I deliberately make mistakes, just to test you!  …………….Bob

GenLee

Member

Registered: 2015-08-07

Posts: 6

Re: How to calculate attribution of % variance knowing absolute values?

Now that you write about it this way, it starts making perfect sense

Thanks again for your patience with me, Bob!

Last edited by GenLee (2015-08-20 08:15:29)

Bob

Administrator

Registered: 2010-06-20

Posts: 10,143

Re: How to calculate attribution of % variance knowing absolute values?

You're welcome.  I'm glad we've got there.

14 days is nothing.  Some of my threads took months to reach a conclusion, like when I had to learn carpentry first, and then teach the poster how to do vectors. 

Bob

---

Children are not defined by school ...........The Fonz
You cannot teach a man anything;  you can only help him find it within himself..........Galileo Galilei
Sometimes I deliberately make mistakes, just to test you!  …………….Bob