1
$\begingroup$

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%

  • 0
    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/price2012-02-23

0 Answers 0