# Contribution (weighted average) of change in rate over time

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%

• 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