ExcelBanter - View Single Post - scenario analysis-multiple variables

"joshjap" ha scritto nel messaggio

Excel 2000

I want to change input variables (prices), while maintaining the same
output (total price). i.e. If I decrease price 1 by 5%, how much do
I have to increase prices 2,3, and 4 to achieve the same total price?

You can try the solution below (I tried to imagine the situation you
have...).

In column E put the variation (I have understood you want increase all the
price the same amount to reflect the decrease of just one price); in row 2
you have the product you decrease the price and in the other rows the
products you increase the price of the same amount.
All you need is to use the Goal Seek command with these parameters:
Set cell: E10
To value: 0
By changing cell: E3

A
B
C
D
E
F
G

1

price
qty
tot
variation
new price
tot1

2
prod1
5
100
=B2*C2
-0,05
=B2*(1+E2)
=C2*F2

3
prod2
8
26
=B3*C3
0,010836584308626
=B3*(1+E3)
=C3*F3

4
prod3
7
17
=B4*C4
=E3
=B4*(1+E4)
=C4*F4

5
prod4
6
85
=B5*C5
=E4
=B5*(1+E5)
=C5*F5

6
prod5
45
31
=B6*C6
=E5
=B6*(1+E6)
=C6*F6

7
prod6
3
25
=B7*C7
=E6
=B7*(1+E7)
=C7*F7

8

=SOMMA(D2:D7)

=SOMMA(G2:G7)

9

10

=D8-G8

--
Hoping to be helpful...

Regards

Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------