Solver with If-Then-Else?

[maywood]

Hi,
I am using the Excel Solver to optimize my production-quantity. Now I have a
problem with 2 products X & Y which are produced on the same machine:
The sum of production of X & Y is capacity constrained. There are also
restrictions concerning a minimum and maximum inventory for both products.
Demand should be satisfied each month. Demand can be satisfied through usage
of inventory, production and external purchase of a product.
The Solver should minimize costs (inventory=cheapest, purchase=most
expensive) times quantities of inventory, production and purchase.

The problem is the following:
The production of product Y is a campaign. That means the production should
only start when there are insufficient inventories of Y. If it starts the
minimum production quantity is 500. If the inventory is sufficient, the
production should be 0.

As you can see in the example file that doesnt work at the moment. The
Solver cant find a solution and the production of Y is min. 500 each month.
Do you have any ideas how to solve it correctly? Is it possible to use an
If-Then-Else Equation with the Solver?
http://www.herber.de/bbs/user/66575.xls

 

[Mark Wiley]

You can generally emulate an IF statement using binary variables (adjustable cells restricted to integer values of 0 or 1) and some constraints. For example, say you wanted cell A1 to be 0 or between 500 and 5000. You create an adjustable cell, Z1, and restrict it to be binary (0 or 1). Then add the constraints:
A1 <= Z1*5000
A1 >= Z1*500

If A1 is greater than zero then the first constraint forces Z1 to be 1. If Z1 is one, then the second constraint forces A1 to be greater than 500.

This example uses an upper bound of 5000. However, in your example you do not appear to have a production capacity. In this case you need to put in some artificial upper bound ? something high enough you know it will not hit.


I work for a company that has developed and maintains What?sBest, an optimization add-in for Excel that essentially competes with Excel?s internal solver. It would allow you to use standard Excel IF statements to model this situation. If you wanted to check it out you could download a trial version from www.lindo.com






maywood wrote:

Solver with If-Then-Else?
14-Dec-09

Hi
I am using the Excel Solver to optimize my production-quantity. Now I have
problem with 2 products X & Y which are produced on the same machine
The sum of production of X & Y is capacity constrained. There are als
restrictions concerning a minimum and maximum inventory for both products
Demand should be satisfied each month. Demand can be satisfied through usag
of inventory, production and external purchase of a product
The Solver should minimize costs (inventory=cheapest, purchase=mos
expensive) times quantities of inventory, production and purchase

The problem is the following
The production of product Y is a campaign. That means the production shoul
only start when there are insufficient inventories of Y. If it starts th
minimum production quantity is 500. If the inventory is sufficient, th
production should be 0

As you can see in the example file that doesnt work at the moment. Th
Solver cant find a solution and the production of Y is min. 500 each month
Do you have any ideas how to solve it correctly? Is it possible to use a
If-Then-Else Equation with the Solver
http://www.herber.de/bbs/user/66575.xls

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Dr. Dotnetsky's Cool .NET Tips and Tricks No. 25
http://www.eggheadcafe.com/tutorial...945e-ee0399a19ed6/dr-dotnetskys-cool-net.aspx