SOLVER: gives different solution depending on start value

[mikewray]

Hello, I have set up the solver to maximise the return on investment by changing certain variables and constraining others (see table below). The problem is the solver does not come up with the optimum solution, although it always gives an answer that is within the constraints. The solution it gives depends on the starting values. For example, if I used the data in the table below and changed cell B2 to 21, it comes up with a solution of a Return on Investment of 26.6% (B2=24, B3=9, B8=10.6% and B4 and B5 stay as is). However, how can this be the optimal solution because as shown below the return on investment with variables below is 27.3%. I have tried to change my degree of convergence (added a few more zeros in) and also ticked automatic scaling) - it gives a different answer but the same problem (gives a different solution depending on your start values of your variables. Any advice...I would like it to give the same solution no matter what the start variables are and then I can play with the constraints to get the optimum solution. Many thanks in advance...

Book2
	A	B	C	D
1		Scenario	Bychanging	Constraints(commaseperated)
2	Pmtperiod(mths)	56	x	<=70,int,>=12
3	Settlement(mths)	6	x	<=50,int,>=6
4	%settlement	95.0%	x	<=0.95
5	Principal	1,000,000		>=0.2*B6,<=0.5*B6
6	Propertyvalue	2,000,000	x
7	InterestRate	2.0%
8	Repaymentrate	19.0%	x	>=0.1,((B7+1)^12)-1
9	RVatsettlement	1,600,000
10	ROI	27.3%	MAXIMISE
11	Startdate	30-Jun-04
12	LTVend	80.0%		<=0.8
13	LTVbegin	50.0%
Sheet1

 

[SydneyGeek]

Re: SOLVER: gives different solution depending on start valu

Here's the hand-waving answer...
Solver works by using methods to get to a local minimum. Think of a slice through a landscape, with a range of peaks and valleys. IN terms of a Solver solution, the optimum result is the lowest point. Trouble is, Solver (and other minimisation routines) expects you to start in the correct valley -- it won't go over the hill, looking for a valley with a lower point. So... you need to start in the correct valley, which means giving a starting point near the correct result. IF you start too far away from the optimum result, you'll never get there.

Note: Just before viewing the result, you can click to see a Solver report. That provides a useful snapshot of where you started and finished, and where you hit the limit on one or more constraints.

Denis

 

[mikewray]

Re: SOLVER: gives different solution depending on start valu

Thanks Dennis...glad it was me not going mad. Should I do anything with precision, tolerance, convergence or use automatic scaling (in options). I know when I change these values it generates different solutions. I know about the reports the solver produces...they are useful...

Right, forward to the right valley I go...

 

[tusharm]

Re: SOLVER: gives different solution depending on start valu

Denis's point is very valid for *non* linear problems. The solution may depend on the starting values provided. This is true not only for Solver but for all software that use that class of algorithms for problem solving. This class of problems yield what is known as a local optimum.

On the other hand, if you specify a *linear* problem, you are guaranteed an globally optimal solution. It means that no other combination of values for the variables will give you a better result, though you may get the *same* result from different combinations of values. The easiest way to see if your problem can be classified as a linear problem is to use the Solver's Option dialog box (in the Solver dialog box click the Options button). In there check the 'Assume linear model' option. Then, use the Solve button. If Solver detects non-linearity in your model, it will complain.

An added benefit of a linear model is the dramatic improvement in speed.

Thanks Dennis...glad it was me not going mad. Should I do anything with precision, tolerance, convergence or use automatic scaling (in options). I know when I change these values it generates different solutions. I know about the reports the solver produces...they are useful...

Right, forward to the right valley I go...

 

[mikewray]

Re: SOLVER: gives different solution depending on start valu

Thanks Tushar. It has a problem with it unfortunately ("conditions for linear model are not satisfied")...so it looks like I will have to go for the local optimum i.e. be in the right valley. Any other ideas?

 

[sarahrosenberg]

Re: SOLVER: gives different solution depending on start valu

I realize this is an old message string...but did anyone come up with a better solution on this? I am having the exact same problem described here. Except, it is very difficult for me to enter a starting value in the "right valley." My optimal solution is based on changing 6 different cells, and there are too many combo options for me to make a "best valley" guess. Other ideas?