Mathematics Stack Exchange is a question and answer site for people studying math at any level and professionals in related fields. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to determine the weighted average impact of one customer's change in rate on the total change in effective rate.

Let's say I have two customers and two time periods:

2010:
Customer 1 Revenue: 8
Customer 1 Quantity: 2
Customer 1 Rate: 4 (Rev / Qty)

Customer 2 Revenue: 21
Customer 2 Quantity: 6
Customer 2 Rate: 3.5 (Rev / Qty)

Total Revenue: 29
Total Quantity: 8
Effective Rate: 3.625 (Rev / Qty)

and time period 2:

2011:
Customer 1 Revenue: 11
Customer 1 Quantity: 3
Customer 1 Rate: 3.7 (Rev / Qty)

Customer 2 Revenue: 22
Customer 2 Quantity: 7
Customer 2 Rate: 3.14 (Rev / Qty)

Total Revenue: 33
Total Quantity: 10
Effective Rate: 3.3 (Rev / Qty)

Using these two time periods, I can calculate the change from 2010 to 2011 for Customer 1:

Revenue: 37.5% (11/8 - 1)
Quantity: 50% (3/2 - 1)
Rate: -8.3% (3.7/4 - 1)

Revenue Change = Quantity Change + Rate Change + Cross Product Term
.375 = .5 + -0.083 + .5 * -0.083

Likewise, for Customer 2:

Revenue: 4.76% (22/21 - 1)
Quantity: 16.67% (7/6 - 1)
Rate: -10.2% (3.14/3.5 - 1)

Revenue Change = Quantity Change + Rate Change + Cross Product Term
0.0476 = 0.1667 + -0.102 + .1667 * -0.102

And for the total:

Revenue: 13.79% (33/29 - 1)
Quantity: 25% (10/8 - 1)
Rate: -8.97% (3.3/3.625 - 1)

Revenue Change = Quantity Change + Rate Change + Cross Product Term
0.0476 = 0.1667 + -0.102 + .1667 * -0.102

Now I can calculate Customer 1's contribution to the total change in revenue:

Customer 1 Change in Revenue: 37.5%
Customer 1 2010 Revenue: 8
2010 Total Revenue: 29
Customer 1 Contribution to total change in revenue: .1034 = (8/29) * 0.375

If I did a similar calculation for Customer 2, the sum of the two contributions would equal the total change in revenue: .1034 + .0345 = .1379

I can calculate Customer 1's contribution to the total change in quantity:

Customer 1 Change in Quantity: 50%
Customer 1 2010 Quantity: 2
2010 Total Quantity: 8
Customer 1 Contribution to total change in quantity: .125 = (2/8) * 0.5

If I did a similar calculation for Customer 2, the sum of the two contributions would equal the total change in revenue: .125 + .125 = .25

Now though, the problem is I can't calculate Customer 1's contribution to the total change in price/rate. If I weight by either quantity or revenue, the sum of Customer 1 and Customer 2's change in rate does not equal the total change in rate -8.97%

share|cite|improve this question
    
You are right. You have no reason to expect the the change in rates will add up this way, essentially because of the quantity in the denominator. – Ross Millikan Feb 15 '12 at 14:29
    
Is there any way though to calculate the contributions for rates? I sort of understand why it doesn't work, but I don't know if there's a way to fix it. – Ed. Feb 15 '12 at 14:51
    
Are you only interested in the case with two customers or are you also going to need to do this with three or more customers? – mhum Feb 23 '12 at 2:18
    
Potentially 4. But starting with 2 because it's easier to explain. At this point, I'd just welcome a laymen's explanation about why a simple weighted average does not tie out to the total change in rate/price – Ed. Feb 23 '12 at 14:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.