Percentage point effect of individual components on overall ratio

[iliace]

Hi all,

This is more of a math question than an Excel question, but hopefully some folks have a solution and enough people are interested.

Here is the situation, as simplified/generalized as I can make it.

I have clients that pay a contractual periodic fee. It is a dollar amount, but can also be expressed as a percentage of the client's (own) business volume.

I take an average "effective" rate for a group of clients.

I forecast client fees I collect periodically.

The following three types of events can occur in-between any two forecasts:

- A client leaves (e.g. contract ends, breach of contract, etc)
- A new client comes onboard
- A client renews his or her contract


New clients generally have incentives (lower fees) at the beginning of their contract period, and over the life of the contract they are increased up to the full amount. Therefore, clients leaving were generally paying higher fees than new clients coming onboard will be paying. A similar situation happens with renewals: to give an existing client incentive to renew, they are typically offered a discount. However, this is not always the case; sometimes renewals and new clients have higher rates than existing clients or those exiting. The net result is, the effective (weighted average) rate of the total client portfolio changes from one point in time to another in the future.


What I'm after is the amount each category of events contributes to the percentage change in the overall effective rate in the later of the two forecasts. Here is an example of what we might have. I know that the effective rate went down from 3.9068% to 3.7555% (15.138 bps). What I'm after is the column E - the number of basis points by which each category of change contributed to the decrease in the overall effective rate. In other words, how many of the 15.138 bps was each component, in bps.

Removing each component and comparing the ending rate works to an extent, but because the relative weight of each remaining component changes, this is not precise (overestimates). Adding each component individually and comparing it to the beginning rate has the opposite problem (underestimates). Taking the midpoint between the two gets pretty close. I am quite content with this approximation for calculation purposes, but it is difficult to explain. And as we know, when you can't explain something convincingly, people have a much harder time relying on it, and I am foreseeing a time when someone might press for details of "exactly how did you get that number".

Right now, I have a formula (sum of E5:E8) that gets very close to the actual result (D12) but not precisely: 15.136 instead of 15.138.


(1) I am wondering whether this approach is logically valid. It makes sense in my head, but I am no mathematician and I may not be grasping a nuance.
(2) I am wondering if there is a way to make this calculation precisely, or even "properly", in such a way as to refer to some kind of theorem or rule. Something that sounds along the lines of "The Riemann-Gauss Approximation" would be great.

	A	B	C	D	E
Forecast 1
Clients Leaving
New Clients
Renewal (net)
Other Change
Forecast 2

<tbody>
[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: center"]Business Volume[/TD]
[TD="align: center"]Client Fee[/TD]
[TD="align: center"]Effective Rate[/TD]
[TD="align: center"]Contribution[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]$500,000.00[/TD]
[TD="align: right"]$19,534.23[/TD]
[TD="align: right"]3.9068%[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]($5,000.00)[/TD]
[TD="align: right"]($210.48)[/TD]
[TD="align: right"]4.2095%[/TD]
[TD="align: right"]-0.377%%[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]$9,000.00[/TD]
[TD="align: right"]$222.78[/TD]
[TD="align: right"]2.4753%[/TD]
[TD="align: right"]-2.433%%[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]($500.00)[/TD]
[TD="align: right"]($638.75)[/TD]
[TD="align: right"]127.7501%[/TD]
[TD="align: right"]-12.361%%[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]($1,000.00)[/TD]
[TD="align: right"]($36.57)[/TD]
[TD="align: right"]3.6568%[/TD]
[TD="align: right"]0.035%%[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]$502,500.00[/TD]
[TD="align: right"]$18,871.22[/TD]
[TD="align: right"]3.7555%[/TD]
[TD="align: right"]-15.136%%[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]Total Change[/TD]
[TD="align: right"]15.138%%[/TD]

</tbody>

Formeln der Tabelle

Zelle Formel D4 =C4/B4 E5 =AVERAGE(($C$4+C5)/(B5+$B$4)-($C$4/$B$4), ($C$10/$B$10)-(($C$10-C5)/($B$10-B5))) B10 =SUM(B4:B9) C10 =SUM(C4:C9) D10 =C10/B10 E10 =SUM(E5:E9) D12 =D4-D10 <tbody> </tbody>

<tbody>
</tbody>

 

[Fazza]

What about factoring the total change by the change in client fee of an individual event as a fraction of the total change in client fees?

So Clients Leaving, =c5/sum($c$5:$c$8)*$d$12
etc

 

[iliace]

Not what I'm going for. That will give me the percentage, but I'm looking for percentage points, or in this case basis points because the change is so small. Column E above is percent of percent, or 1/100th of %.


So, for instance, using the same example, I want to be able to say:

"Effective client fee rate went from 3.9068% to 3.7555%, a decline of 0.15136%. 0.02433% was due to new clients enrolling at discount rates. 0.12361% was due to discounts given to clients renewing their contracts." and so on


I realize this might seem minuscule, but the real-life situation is dealing with thousands of clients and business volumes measuring in billions, so these can be quite significant.

 

[StephenCrump]

You can easily attribute the movement in business volume and/or the movement in client fee into their respective components, i.e. new clients, renewals or exits.

The problem with trying to attribute the movement in average fee is that both numerator and denominator are changing. The only way you'll get the attribution to always add up exactly is to successively add each component, and measure the incremental change in average fee, e.g. the impact of clients leaving is to reduce the average fee from 3.9068% to ($19,534.23 - $210.48)/($500,000 - $5,000) = 3.9038%, i.e. -0.003% etc.

This does mean that the attribution will vary depending on the order in which you process each component. This is a limitation of trying to attribute the movement in an average value.

In any event, I would think you'd be far more interested in attributing the movement in total fees, rather than average? If I had two clients:

A) $500,000 at 2%
B) $50,000 @ 4%

and I lost client A, I wouldn't be at all happy that my average fee rate had increased to 4%!

 

[PaddyD]

Haven't time to read through this in detail, but isn't this just a rate / volume variance analysis with a bit of segmentation thrown in for good measure?

Given changes in volume and rate over two periods:

volume contribution: (new volume - old volume) * original rate
rate contribution: (new rate - old rate) * new volume

 

[StephenCrump]

Haven't time to read through this in detail, but isn't this just a rate / volume variance analysis with a bit of segmentation thrown in for good measure?

Given changes in volume and rate over two periods:

volume contribution: (new volume - old volume) * original rate
rate contribution: (new rate - old rate) * new volume

Yes, it's the same thing. I don't know whether with rate/volume analysis there's a convention that says you increment in this order? If not, then it's also true that:

rate contribution: (new rate - old rate) * original volume
volume contribution: (new volume - old volume) * new rate

The two ways of slicing will give different attributions, but add to the same, i.e. NV.NR - OR.OV

But effectively, the question requires quantifying the impact of three successive rate/volume changes, i.e. NV.NR - OR.OV for each, rather than the rate and volume components of each.

 

[iliace]

You can easily attribute the movement in business volume and/or the movement in client fee into their respective components, i.e. new clients, renewals or exits.

The problem with trying to attribute the movement in average fee is that both numerator and denominator are changing. The only way you'll get the attribution to always add up exactly is to successively add each component, and measure the incremental change in average fee, e.g. the impact of clients leaving is to reduce the average fee from 3.9068% to ($19,534.23 - $210.48)/($500,000 - $5,000) = 3.9038%, i.e. -0.003% etc.

This does mean that the attribution will vary depending on the order in which you process each component. This is a limitation of trying to attribute the movement in an average value.

In any event, I would think you'd be far more interested in attributing the movement in total fees, rather than average? If I had two clients:

A) $500,000 at 2%
B) $50,000 @ 4%

and I lost client A, I wouldn't be at all happy that my average fee rate had increased to 4%!

Yes, essentially that's what I'm already doing. I thought there might be some way to get to a precise answer.

You are correct in total fees, if those were the amounts and the portfolio. But consider a portfolio of 1000 clients, with BV ranging from 50,000 to 2,500,000. There, a single $500,000 doesn't make nearly as much difference, especially a new one coming in at 2.5% vs an old one of $100,000 but at 4.5%.

This question is interesting for a variety of reasons, not simply the rate. There are questions of potentially misplaced incentives, for example, from signing new clients or renewing old ones at steep discounts, just to meet quotas.


Thanks all for contributing, good discussion.