Take the 2-minute tour ×
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.

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|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.