Indirect scenario/sensitivity analysis

Grant23

New Member
Joined
Jul 21, 2008
Messages
1
Hi

I have a model in excel which takes a large amount of data and runs it through the model to produce two output numbers. I then have to remove groups of the data while keeping the rest in and pull out the results, then put that group back in and remove the next group and so on.

I am trying to find a way to make this be done automatically so other people can use the model easily. I have seen that somehting similar to this can be done by creating scenarios and then creating a summarry pivot table. However, this pivot table will not update when a new set of data is used and therefore other users would not be able to change the data and still use the table.

Is there a way to make this table update or achieve the same results using a different method?

Thanks
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
Grant23 - welcome to the board.

I'm not precisely clear on what you're trying to do, but it sounds similar to what I do in my models.

Say I have a base model, and then 3 variables that can be on or off. I need to know the results for base, base + 1, base + 1 + 2, base + 1 + 2 + 3. So I build myself a sheet like this:

Excel Workbook
ABCDE
11
2On valueOff valueIn use
31Sales increase10%0%10%
42Price increase5%0%0%
53Retention increase17%0%0%
6
7Result110
8
9Result
10110
11Base case0100
12Sales increase1110
13Price increase2116
14Retention increase3135
Sheet1


Obviously this is a Mickey Mouse version. But if it looks like something that might suit your needs, shout, and I'll explain further.
 

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
If you haven't used Data Tables before, you can't create them like an array formula, despite what Excel Jeanie says! They have to be created through the menu (Data - Table).
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,730
Members
417,108
Latest member
Thein Than

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top