Using a formulae within scenarios
 

Hi,

I've just used scenarios for the first time but am a little frustrated in
that it only remembers specific values rather than the actual cell reference
that I type into the "Enter values for each of the changing cells" boxes.

Is there a way of doing this as I wanted to include a whole range of values
in the scenario that would be replaced each time.

For example
consider a basic cashflow forecast where
line 1 is balance brought forward
lines 2-5 are various incomes
lines 6-8 are various payments
line 9 is balance carry forward (sum of all of the above)
each column represents a new week and starts with the balance in line 9 of
the previous column in line 1.

Now suppose that line two was a range of values that I wanted to replace
with 3 scenarios
scenario 1 being income maintained at current level
scenario 2 being income forecast in a std statistical distribution based on
histotica data and
scenario 3 being an average of the previous 2
This data was already listed out in lines 12-14

The problem is that if I update the figures in lines 12-14 this has no
effect on the scenarios as although I type in cell references it only
remembers the numbers that were in the cell at that time and I have to type
the new values all in again.

Any suggestions?

The problem is that if I update the figures in lines 12-14 this has no
effect on the scenarios as although I type in cell references it only
remembers the numbers that were in the cell at that time and I have to
type the new values all in again.

I don't use the Scenario Manager I must confess but it doesn't seem to
support what you want to do. Frankly, I think it's pretty easy to use basic
Excel formulas to get where I think you want to go.

Say you have three "Scenario values" in the range F12:F14. And say you
designate cell A1 to control the "scenario number". Then in cell F2 you can
enter this formula: =INDEX(F12:F14,A1) to return in it the value from below
according to the scenario entered in A1.

--
Jim Rech
Excel MVP