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…?
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: