Indirect scenario/sensitivity analysis

[Grant23]

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

 

[cornflakegirl]

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
	A	B	C	D	E
1	1
2			On value	Off value	In use
3	1	Sales increase	10%	0%	10%
4	2	Price increase	5%	0%	0%
5	3	Retention increase	17%	0%	0%
6
7		Result	110
8
9				Result
10				110
11		Base case	0	100
12		Sales increase	1	110
13		Price increase	2	116
14		Retention increase	3	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]

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).