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%