Calculate Benefits-to-Costs ratio - want to obtain specific integers (1-4). Current method is trial-and-error for costs, possible to automate?

[SleightOfHand]

My thread title is missing some connectives and such because of char limit and it's hard to fit everything in.


Currently, we have a model used to calculate a benefits-to-cost ratio, BCR, which involves other parameters. This is a really simplistic, watered-down version to describe what's occurring. Sorry if it's confusing.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	1	Raw Costs			-£930,000
2	2	Indice 1			1.022
3	3	Costs			-£950,460
4
5	4	BIAS			54%
6	5	Biased Costs			-£1,463,708
7
8	6	Tax			1.19
9	7	Biased Costs * Tax			-£1,741,813.00
10
11	8	Discount Rate			0.662
12	9	Discounted Costs			-£1,153,080.20
13
14	10	Other discount			0.91
15	11	New Costs			£1,049,302.99		(positive for purpose of calculation)
16
17	12	Format			1,000,000
18	13	Overall Costs - in millions			1.05
19	14	Revenues			-0.75		Obtained separately							BCR Off?	0
20	15	Benefits			0.6		Obtained separately							Current Costs	-£930,000
21	16	BCR			2.00									Suggested Costs	????
22
23
24
25
26
27
28
29
Sheet1

Cell Formulas
Range	Formula
E3	E3	=E1*E2
E6	E6	=E3*(1+E5)
E9	E9	=E8*E6
E12	E12	=E9*E11
E14	E14	=0.1+0.81
E15	E15	=-(E14)*E12
E18	E18	=E15/E17
O19	O19	=IF(ISNUMBER(E21),IF(NOT(OR(ROUND(E21,2)={1,2,3,4})),1,0))
O20	O20	=E1
E21	E21	=E20/(E19+E18)

Sometimes we don't get near to our integers, so we have to keep retrying costs to see if we can try and get closer to that. At present, the RAW costs are what we try and manipulate because that's an estimation of a potential scheme. 930,000 works for this current scheme, but initially we had 1,000,000 etc and had to try and reduce it until we got to 2.00 or very close to it.


I would like to know if it's possible to make a separate calculation that gives you a suggestion of what raw costs to use to obtain the specified value (shown in the sheet), if our BCR is not at the specified integer we want.

Many thanks for any help

 

[SleightOfHand]

Sorry, I forgot to add that there should be option there of 1,2,3,4 within or near to the N19:O21 range to highlight the selected BCR that we are looking for.

 

[Eric W]

All those formulas are pretty basic. It would take some algebraic manipulation, but I'd think you could come up with a formula to get what you need. But a faster way might be to use Goal Seek. Go to the Data tab > What-If Analysis > Goal Seek > and set cell E21 to 2 (or 1, 3, or 4), by changing cell E1.

 

[SleightOfHand]

Wow I had no idea that even existed. Thank you very much. Yeah the formulas are pretty basic in that sheet.

Honestly just because many people here hate using things from the toolbar, and seem to forget easily, a formula just to look at may be easier. Would that be too hard to do?

 

[Eric W]

Not particularly hard, but tedious. I'd have to write all of your formulas as equations, using E1, E2, etc. as the variables, then solve for E1. I don't have time to do that right now, but if I come up with some time, I might give it a shot.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	1	Raw Costs			-930000
2	2	Indice 1			1.022
3	3	Costs			-950460
4
5	4	BIAS			0.54
6	5	Biased Costs			-1463708
7
8	6	Tax			1.19
9	7	Biased Costs * Tax			-1741813
10
11	8	Discount Rate			0.662
12	9	Discounted Costs			-1153080
13
14	10	Other discount			0.91
15	11	New Costs			1049303		(positive for purpose of calculation)
16
17	12	Format			1000000
18	13	Overall Costs - in millions			1.049303
19	14	Revenues			-0.75		Obtained separately
20	15	Benefits			0.6		Obtained separately							BCR	2
21	16	BCR			2.004658									Suggested Costs	-930617.766
Sheet3

Cell Formulas
Range	Formula
E3	E3	=E1*E2
E6	E6	=E3*(1+E5)
E9	E9	=E8*E6
E12	E12	=E9*E11
E14	E14	=0.1+0.81
E15	E15	=-(E14)*E12
E18	E18	=E15/E17
E21	E21	=E20/(E19+E18)
O21	O21	=(E19*E17-E20*E17/O20)/E14/E8/E11/E2/(E5+1)

Put your desired BCR in O20, and the suggested costs will come up in O21. Note that the formula in O21 uses all of the constant cells in column E, and none of the formulas. If those formulas change, the O21 formula will have to change also. But you can change any of the constants (like if the discount changes in E11) and the formula will also adapt.