# May be more algebra related than Excel related

#### nodroj81

##### Board Regular
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?

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### shg

##### MrExcel MVP
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

##### Board Regular
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

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

Code:
``````      --A--- --B--
1   N         50
2   D        100
3   DeltaN     5
5   Change -6.0%``````

The formula in B5 is

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

#### nodroj81

##### Board Regular
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

##### MrExcel MVP
Code:
``````      -----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?

Last edited:

#### nodroj81

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
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

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

Replies
5
Views
232
Replies
13
Views
368
Replies
1
Views
2K
Replies
3
Views
1K
Replies
1
Views
498

### Forum statistics

1,195,696
Messages
6,011,178
Members
441,592
Latest member
Vasant bangalore ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back