Weighted average outside the range of the two contributing figures.

LSquall

New member
Joined
Jan 17, 2021
Messages
3
Hi, I wondering if anyone can help me figure out something that’s had me frustrated and scratching my head for days.

There are two monetary amounts that each have two factors applied to them to increase or decrease them. The factors are applied one after the other, not concurrently (Factor A, then Factor B).
My problem is working out the weighted average of those factors. It appears to be fine for the first factor, but the weighted average for the second factor is outside the range of the individual results – I don’t see how that is possible.

The following involves some rounding to 2dp so may not be exact;
Figure 1: Started at 9,350.02 and ended up at 10,335.00
This occurred through;
Factor A is -0.85%, so 9,350.02 x 0.9915 = 9,270.53
Factor B is +11.48%, so 9,270.53 x 1.1148 = 10334.79 (10,335)

Figure 2: Started at 12,500 and ended up at 12,839.97
This occurred through;
Factor A is -10.41%, so 12,500.00 x 89.59 = 11,198.75
Factor B is +14.66%, so 11,198.75 x 1.1466 = 12,840.49 (12,839.97)

Combining Figure 1 & Figure 2: Started at 21,850.02 and ended up at 23,174.97.
Excel tells me the following Factors are the weighted averages of the above-noted factors, and it appears to be correct;
Weighted average Factor A is -4.28%, so 21,850.02 x 0.9572 = 20,914.84
Weighted average Factor B is +10.80%, so 20,914.84 x 1.1080 = 23,173.64 (23,174.97).

Here’s what I don’t understand;
Using the weighted average figures of -4.28% and +10.8% on the combined monetary values seems to align perfectly with the outcomes of the two individual figures, i.e. both exercises arrive at 23,174.97 total.
Factor A looks to be alright, being -4.28% in the middle of -0.85% and -10.41%, weighted towards the greater value monetary figure.
But how can the weighted average of Factor B be +10.8% which is outside the range of the two individual Factors, being +11.48% and +14.66%. I’d have expected the weighted average be 12-13%.

Given the individual calculations align with the combined calculations I’m thinking it’s a maths issue I’m not grasping (as to how the weighted average can be outside the range of the two figures) and not an excel formula issue. Any ideas?
Is it something to do with using the weighted average Factor A before Factor B is applied?
I want Factor B to be as high as possible so it’s not ideal that it’s coming out lower than what was actually achieved on the individual monetary values. It feels wrong to say it’s only 10.8% when each figure experienced something greater than that…?
 
Last edited:
I'm not sure how the weighted values of -4.28% and -10.41% were calculated. Can you share that?

If you look at the totals, you have :
9350.02+12500 ->9270.54+11198.75 ->10334.80 + 12840.49
ie
21850.02 ->20469.29 ->23175.29
working out the % changes between these figures gives -6.3% and 13.2%, both of which lie within the appropriate ranges.
 
Hi Harry_the_cat, thanks for your speedy reply and notes.
I follow how you've arrived at -6.3% and 13.2% which looks promising. What I'm not sure of is if this is fixing Factor B at the expense of Factor A.
I'll try and outline the contributing figures as best as possible without spending an age trying to explain the background.

Figure 1: Started at 9,350.02 - that was the priced applied.
The model last year said the price should have been 10,213.48 (so a concession was allowed last year). This year the same model produces a price of 10,126.65. So that is a reduction of 0.85% (Factor A).
If starting with 9,350.02 and passing on the -0.85% (maintaining the same concession), it would produce 9,270.54.
In efforts to reduce / claw back that concession and make a profit this year, Factor B is applied. An 11.48% increase is applied. 9,270.54 x 1.1148 = 10,334.80.

Figure 2: Started at 12,500.00 - that was the priced applied.
The model last year said the price should have been 5,705.62 (achieving more than the modelled price). This year the same model produces a price of 5,111.45. So that is a reduction of 10.41% (Factor A).
If starting with 12,500.00 and passing on the -10.41% (maintaining the same loading on the model), it would produce 11,198.75
An increase is due this year, Factor B is applied. An 14.66% increase is applied. 11,198.75 x 1.1466 = 12,840.49.


Originally working out the weighted averages, I was doing the following in excel.
FACTOR A
Combined original modelled prices $15,919.10
Combined new modelled prices $15,238.10
Change = -4.28% (in between the -0.85% and -10.41%).

FACTOR B
Combined original applied prices $21,850.02
Combined new applied prices $23,174.97 (Inclusive of Factor A and Factor B)
Factor B should be how much the prices change (increase) after passing on Factor A.

My weighted average Formula was "(Total New - Total Original after Factor A) / (Total Original after Factor A)"
(23,174.97 - (21,850.02 - 4.28%)) / (21,850.02 - 4.28%)
= (23,174.97 - 20,914.84) / (20,914.84) = 10.80% (FACTOR B)

When I look at it differently now, and compared the current prices before and after application of Factor A, I get your -6.3%.
That influences how much Factor B actually accounted for, and it becomes +13.2% like you said.
So now I'm confused as to why my original method gave me -4.28% for Factor A and the different approach gives -6.3%. Is my first approach wrong? I'm measuring the change in combined modelled pricing from $15,919.10 to $15,238.10 (-4.28%) so unsure how I'm going wrong.

Thank you so much for your original reply... I can't tell you how encouraging the steer in the right direction has been over this last hour!!
 
Actually I think I may know where I'm going wrong...
I can't just use that average -4.28% change in the underlying modelled values and apply evenly to the actual values of Figure 1 and Figure 2 because their variance from the modelled values is not uniform.
I see why there is the need to apply the specific/individual change in modelled value (Factor A) to the actual value charged.
It's not fair to say the change in modelled value (Factor A) was -4.28% when that would only be true if the prices charged matched the modelled prices. When Figure 2 was so much higher than the model, it should have a greater weighting in terms of how much the modelled values have changed (Factor 1) because it's a greater proportion of the current pricing/income.
I think that's it anyway!!
 
Top