May be more algebra related than Excel related

[nodroj81]

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?

 

[shg]

For a value A added to both numerator and denominator, the change is =A*(D-N)/(D*(D+A))

For a value B added to the denominator, the change is =-N*B/(D*(D+B))

 

[nodroj81]

thanks for the quick response shg!

I used the formulas you provided and was able to get them to work with one variable. But when I added a second, third, etc. variable I couldnt get the formula to work.

Let me elaborate in case more info is needed. The model I'm working on has 5 key variables that impact a rate of return. I am trying to show the impact of each variable on the modified rate of return. So for example:

Base case, 50/100 or 50%
Variable A, add 10 to numerator and denominator
Variable B, add 20 to denominator
Variable C, no change
Variable D, subtract 20 from numerator and denominator
Variable E, add 15 to numerator and denominator.
End result, 55/125 or 44%

Net change = -6%

Will the above formulas calculate individual impact of each variable toward total net impact?

So using my preceding example, another way to read the solution I'm trying to get to is,

impact of Var A + impact of Var B + impact of Var C+ impact of Var D + impact of Var E = -6%

 

[shg]

That's a net change of +5 to the numerator and +25 to the denominator:

      --A--- --B--
  1   N         50
  2   D        100
  3   DeltaN     5
  4   DeltaD    25
  5   Change -6.0%

The formula in B5 is

=(D * DeltaN - N * DeltaD) / (D * (D + DeltaD))

 

[nodroj81]

I agree with net change of -6% so we're on the same page there. But I'm trying to calculate something different - see below for what I'm trying to figure out

	Base	Var A	Var B	Var C	Var D	Var E	New
Numerator	50	10			-20	15	55
Denominator	100	10	20		-20	15	125
Return	50.00%						44.00%	-6.00%	< < < NET CHANGE
My question: What is the impact of individual variables on the NET CHANGE of -6.0%(yellow highlighted area)? Formula I'm using to calculate net change is 44.0% - 50.0% = -6.0% A solution would look something like this (I'm just making up numbers here): = Var A + Var B + Var C + Var D + Var E = -6.0% = -1.5% + -4.0% + 0% + 2.0% + - 2.5% = -6.0% Does that make sense? Thanks again for the help.

<tbody>
</tbody>

 

[shg]

      -----A----- --B-- --C-- --D-- --E-- --F-- --G--
  1               Base  Var A Var B Var C Var D Var E
  2   Numerator      50    10               -20    15
  3   Denominator   100    10    20         -20    15
  4   Return      50.0% 54.5% 46.2% 46.2% 36.4% 44.0%

B3 and copy across:

=SUM($B2:B2)/SUM($B3:B3)

Wouldn't it have been easier to post that at the outset?

 

[nodroj81]

That worked thanks. Was hoping not to assign a variable a sequential spot because the order impacts the outcome. But the solution works so thanks for the help.

 

[shg]

Was hoping not to assign a variable a sequential spot ...

Assign a variable to a sequential spot?

... because the order impacts the outcome

It doesn't affect the final outcome.

 

[nodroj81]

Sorry my response was not clear. Using my previous example, if I took Variable D (-20 / -20) and I switched its place with Variable A (10 / 10), then my OVERALL return would be the same, but my individual returns would change.

For example, with variable D in the fourth spot, the change = (36.4% - 46.2%) = -9.8%
But if I move Variable D to the first position in sequential order, then its individual impact = (50-20)/(100-20) = 30/80 = 37.5%, so change would be 37.5% - 50.0% = -12.5%

So yes sequence matters and that's what I meant by it affecting the final outcome. I'm not trying to solve for the total change, only for the individual variables.

 

[shg]

Well, you have a potpourri of formulas to choose from. Good luck.