Solver and how it influences variable

[immerse]

HI,

I'm a big fan of solver and use it often in my work and just enjoy tinkering with it..

anyway, question is when you have a matrix of information such as for manufacturing input to produce a product..eg.

3 columns..

col a( minimum)
col b (benchmark or starting values or adjustables)
col c (maximum)

let's say you have 5 inputs or 5 rows in accordance to the columns above..

2000 -> 5000 ->10 000
2500 -> 6000 ->11 000
3000 -> 3400 -> 4000
1000 -> 1200 -> 1300
1000 -> 1050 -> 1060

I'm trying to explain how solver arrives at its conclusions and have read up on it.. with frontline systems,

but in this scenario.. let's say you want to say achieve a target or goal of using 6000 inputs. (the table above are all inputs)... for whatever reason let's say 6000 is our budget of inputs...

the best mix or combination? is the result or predicted values..

but question is: how does solver go about this?

For example, does it have an order of preference by starting with the first available row as it has a variance from 2000 to 10 000, so in fact if this is the case, there's no need to use any inputs from the rest, but in reality Solver doesn't do this this way...

Is it because I have not bound the target cell in the worksheet to any formulae (which one must do as some units have to be given preference over others and one does this with a formulae..eg

1 = name of unit 1 and is row 1

so For every 1 unit of product 1, we need 2 units of product 2...

so its like target cell = 1x + 2x

2nd question, if you decrease the slack ,ie. between min and max, does the solution that is found go and use the ones with the least slack first to use up all qantities there first and than continue? or does this deepend on the formulae of construaction that binds the variables?

 

[tactps]

From Excel Help, I found this:

Algorithm and methods used by Solver
Microsoft Excel Solver uses the Generalized Reduced Gradient (GRG2) nonlinear optimization code developed by Leon Lasdon, University of Texas at Austin, and Allan Waren, Cleveland State University.

Linear and integer problems use the simplex method with bounds on the variables, and the branch-and-bound method, implemented by John Watson and Dan Fylstra, Frontline Systems, Inc. For more information on the internal solution process used by Solver, contact:

Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288
(702) 831-0300
Web site: http://www.frontsys.com
Electronic mail: info@frontsys.com

Portions of the Microsoft Excel Solver program code are copyright 1990, 1991, 1992, and 1995 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc.


It may be worth visiting the website.

Hope that this helps

 

[mdavide]

hi immerse,

i read what you wrote about solver:

I'm a big fan of solver and use it often in my work and just enjoy tinkering with it..

you can help me with this problem that i post a few days ago?

this is the link

http://www.mrexcel.com/board2/viewtopic.php?t=116765&highlight=


thanks

DM