Dynamic scenario

[Tigerexcel]

Dynamic scenario.xlsx
	A	B	C	D	E	F
1		2021	2021 Alternative scenario
2	Sales Prod B	$1,000	$1,000		Assumptions
3	Sales Prod A	$2,000	$2,000		Cost of goods	$1,200
4	Total Revenue	$3,000	$3,000		Sales Prod A	$400
5	Cost of goods	$600	$600		Delivery	$600
6	Administration	$500	$500
7	Printing	$300	$300
8	Delivery	$250	$250
9	Total Expenses	$1,650	$1,650
10	Profit	$1,350	$1,350
11	Profit Margin	45%	45%
Sheet1

Cell Formulas
Range	Formula
B4	B4	=B2+B3
C2:C10	C2	=B2
B9	B9	=SUM(B5:B8)
B10	B10	=B4-B9
B11:C11	B11	=B10/B4

Cells with Data Validation
Cell	Allow	Criteria
E3:E5	List	=$I$2:$I$7

Basically I'd like to test various scenarios. The formulae need to update so that if there are any values in F3, F4 or F5 then these values will replace what is in Column C placed against the relevant item labels. In the example above Cost of goods would change from $600 to $1200 etc. I need the ability to test a number of scenarios so the formulae need to be able to go back to their default values which are in Column B. Thus if there's nothing in Column E the values in Column C should be back to the same values in Column B. Ignore the Data Validation above, it is not needed.

 

[Fluff]

How about
=IFERROR(INDEX($F$3:$F$5,MATCH(A2,$E$3:$E$5,0)),B2)

 

[Eric W]

Try:

Book1 (version 1).xlsb
	A	B	C	D	E	F
1		2021	2021 Alternative scenario
2	Sales Prod B	1000	1000		Assumptions
3	Sales Prod A	2000	400		Cost of goods	1200
4	Total Revenue	3000	1400		Sales Prod A	400
5	Cost of goods	600	1200		Delivery	600
6	Administration	500	500
7	Printing	300	300
8	Delivery	250	600
9	Total Expenses	1650	2600
10	Profit	1350	-1200
11	Profit Margin	0.45	-0.85714286
Sheet7

Cell Formulas
Range	Formula
B4:C4	B4	=B2+B3
C5:C8,C2:C3	C2	=IFERROR(1/(1/(VLOOKUP(A2,$E$3:$F$10,2,0))),B2)
B9:C9	B9	=SUM(B5:B8)
B10:C10	B10	=B4-B9
B11:C11	B11	=B10/B4

 

[Tigerexcel]

Thanks guys for the super-quick response. I didn't realise that IFERROR will also return a cell value if it doesn't find a value, I had thought it would only do this if it found an error.
The actual spreadsheet that I have to apply this to has hundreds of rows, when I apply the formula to Column C it will overwrite the existing formulae. Does anyone know of a way to apply this formula to a column of cells such that it will only apply the formula to the constant values but leave the formulae untouched? This might be hoping for too much, I can workaround this by copying the formula sections at a time but would like to know if there's a faster way.

 

[Fluff]

Why don't you want to replace the existing formula?

 

[Tigerexcel]

Why don't you want to replace the existing formula?

Info is downloaded from Accounting software and is hard-coded, so I have to manually enter formulae into Column C. A blanket copy of the index formula throws out C4 and C9 to C11 calculatrions.

 

[Fluff]

Sorry but I don't understand what you're saying.

 

[Tigerexcel]

Hi again Fluff, are you suggesting I copy the new formula all the way down Column C? The purpose of the exercise is to do What-if type analyses, I need to calculate what margins will be if some of the variables change. Existing formulae reference values from previous column thus don't update, hard to explain on-line.

 

[Fluff]

Please remember we have no idea what you are trying to do, other than what you have told us.
You asked for a formula to go in col C & that is what both Eric & I supplied.
If that is not what you want then you need to clearly explain what you do want.