Contribution (weighted average) of change in rate over time

C

ChipT

New member
Joined
Feb 15, 2012
Messages
4
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%
 
Yes, you're absolutely right. By rate, I mean price (I've been using those two words interchangeably, mostly because the context of the question uses the word rate).

I agree with all of your vocabulary. Just need to clarify a few points (that are probably typos).

Your relative change in price paid by customer i. I assume the denominator is Pi,j?
And TQ,J is total quantity for period j?

Sorry, I don't know how to input the mathematical symbols.

But with that vocabulary, my question is how much does the relative change in price paid by customer i contribute to the relative change in average price? Or rather, how do I calculate the percent contribution of the relative change in price paid by customer i to the relative change in average price?
 
Thank you for the detailed reply!

First, this is a work question. I've got a strong mathematical background, but found myself quite frustrated with this because it seems at first glance like it should be simple. Like you, I went the route of breaking it down algebraically (in a very similar way you have above). I kept assuming I was missing something or that the perhaps, after expanding all the terms, I would hit on something simpler. I did find the inherent problem that as n increases, the number of cross-product terms blows up. So this confirms for me that the math is not trivial.

The business question revolves around trying to determine the sources of change among programs. So instead of n customers, it's n pricing programs, each of which has its own quantity, revenue, and price over two periods. Looking at the data in aggregate, we can measure the change in price over time. Looking at each individual program, we can also measure the change in price over time. But the audience then inevitably asks why when Program 1 increases 2.0% and Program 2 decreases 5.0%, the total change in price is let's say -1% instead of -3%. If this were purely a mathematical discussion, then it's easy to show algebraically why this is not necessarily the case. But how to translate that into something easier to digest. . .

The way I try to explain it is that the total change in price is a function of changes in revenue and quantity. You can get the contribution to change in revenue or change in quantity because they don't necessarily interact with each other to reach a total. But price is a function of revenue and quantity. Therefore both changes in revenue and​ changes in quantity must be taken into account when measuring contribution. This makes a contribution determination much more difficult.

Another way to think about it is to instead say revenue = distance, quantity = time, and price = velocity. Summing total distance makes sense. Summing total time makes sense. Summing total velocity does not give any relevant information about the scenario because it really should be the relationship between distance and time that matters. Thus when measuring change in velocity and contribution to change in velocity, it will be much more than a simple weighted average.

But I did need to check that my algebra was sufficiently intact before assuming that it was indeed much more complex than it seems it should be.

Can you offer any better layman explanation than I have? Or to address one of your points, what might be a better way to ask this question?
 
Dear Chip, dear Jeff,

I am searching for a solution to my problem, which also "seems" very easy, but somehow has got me struggling.

First of all to the above problem. The way I would start looking at the programmes, which you are trying to evaluate - I would look at it from the perspective - what is the volume and margin effect. The volume effect I would calculate through taking the absolute change in sales times the "old" margin and the margin effect would be the difference from the new margin minus the old one times the "new" sales. Therefore you can see how much of the absolute change in gross profit is contributed by the volume change and by the margin. Dunno...it was just an idea - maybe it helps. Put in a picture with a "bridge" one can see how these two variables change the picture from one year to the next.

To my problem...to make it more clear, here is the following example:

Company A has two product lines


Year 1 Year 2
Sales
Product 1 60 65
Product 2 40 45
Total Sales 100 110

Gross Profit
Product 1 25 30
GP P1% 41.7% 46.2%

Product 2 15 19
GP P2% 37.5% 42.2%

Total GP 40 49
GP % 40.0% 44.5%

% of Total GP P1 62.5% 61.2%
% of Total GP P2 37.5 % 38.8%

So in words - there is a margin change from Year 1 to Year 2, but also a volume change, whereas the basis also changes (total gross profit). What I would like to calculate is - how much (in percent) does Product 1 and Product 2 contribute (or then also demolish) my total gross margin - taking into consideration the margin change per product, but also taking into consideration the portion of product gross profit from the total gross profit. I want to bridge relatively the difference from 40% to 44.5% (or the 4.5% increase in GP) by showing how much does each product make out in this change (considering the above mentioned).

Would be glad to recieve some help on this one...I am pretty lost...the more I try to make some sense of it, the more I get lost in trillions of percentages.

My maths background is pretty simple - last time I did math was in Calculus class back in 2004 in University :) And my problem is a work one :)

Would be glad to read you thoughts on this one! Would appreciate any help!


Thanks a lot in advance!


Best,
 
You must log in or register to reply here.
Top