faster, more effective scenario analysis?

[BoulderBen]

Hello,

I have a set of 40 inputs that are used to calculate 1 output. I have 4
scenarios (each with 40 inputs) and wish to compare the outputs of 4
scenarios side-by-side. The Scenario Analysis tool does this, but I want to
be able to quickly change the value of 1 input in 1 scenario and watch a
table (with the 4 scenario outputs) on the worksheet automatically update.

The very slow way to do this is create the scenarios and create a summary
table. Then change one value with the scenario edit option, and calculate a
new summary table, then copy and paste the output to the original worksheet.

Your help is very much appreciated - this will help me explain to colleagues
what I am doing when I sit in front of Excel all day.

Thanks,
Ben

 

[Niek Otten]

Hi Ben,

Ideally suited for the Data>Table command.

Takes a bit of time to set up once, very ease to use and fast thereafter.

Look in HELP for the Data>Table command. Use a 1-dimension table, in which you just vary the record (line) number of the set of
inputs. Last (or first) item on the line is the result.
Insert one sheet one which you do all the table handling; just copy from other sheets (=Sheet2!Input1 etc).
Post again in this thread if you have problems, then I'll try to find the step-by-step recipe I wrote once but can't find right
now.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hello,
|
| I have a set of 40 inputs that are used to calculate 1 output. I have 4
| scenarios (each with 40 inputs) and wish to compare the outputs of 4
| scenarios side-by-side. The Scenario Analysis tool does this, but I want to
| be able to quickly change the value of 1 input in 1 scenario and watch a
| table (with the 4 scenario outputs) on the worksheet automatically update.
|
| The very slow way to do this is create the scenarios and create a summary
| table. Then change one value with the scenario edit option, and calculate a
| new summary table, then copy and paste the output to the original worksheet.
|
| Your help is very much appreciated - this will help me explain to colleagues
| what I am doing when I sit in front of Excel all day.
|
| Thanks,
| Ben

 

[BoulderBen]

Thanks, Niek. I set up a lookup to hinge the 40 variables on one single
variable, then used a data-table as you describe. This is much faster than
scenario analysis - thanks.

Ben