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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

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
7Result
110
8
9Result
10
110
11Base case0
100
12Sales increase1
110
13Price increase2
116
14Retention increase3
135
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,099,462
Messages
5,468,786
Members
406,608
Latest member
G3TEN

This Week's Hot Topics

Top