I'm sure there's a simple solution but I've been staring at this problem for far too long.
I'm using an Excel model to calculate the impact of a certain variable on a percentage and then graph the impact in a waterfall chart.
So for example, Let's say I have a base case situation where I expect a certain return, say 50/100 = 50%
Now I change a variable in the model and it adds 10 to both the numerator and the denominator, so 60/110 or 54.5%
In that example I can obviously tell that the variable improved my return by 4.5%. Simple enough.
When I add a second variable I can still calc the TOTAL impact of both variables, but I cant for the life of me figure out how to calc the individual impact of each variable on the TOTAL return.
To elaborate, using the example above, base case is 50/100 or 50%.
Variable A adds 10 to both numerator and denominator
Variable B adds 20 to the denominator
So my new return is 60/130 or 46.2%. Overall these two variables together decreased my return by 3.8%. However, I need to show (ultimately in a waterfall chart) the impact of each variable on the total return.
Any ideas on how to properly calculate this?
I'm using an Excel model to calculate the impact of a certain variable on a percentage and then graph the impact in a waterfall chart.
So for example, Let's say I have a base case situation where I expect a certain return, say 50/100 = 50%
Now I change a variable in the model and it adds 10 to both the numerator and the denominator, so 60/110 or 54.5%
In that example I can obviously tell that the variable improved my return by 4.5%. Simple enough.
When I add a second variable I can still calc the TOTAL impact of both variables, but I cant for the life of me figure out how to calc the individual impact of each variable on the TOTAL return.
To elaborate, using the example above, base case is 50/100 or 50%.
Variable A adds 10 to both numerator and denominator
Variable B adds 20 to the denominator
So my new return is 60/130 or 46.2%. Overall these two variables together decreased my return by 3.8%. However, I need to show (ultimately in a waterfall chart) the impact of each variable on the total return.
Any ideas on how to properly calculate this?