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

 

[Herbert Seidenberg]

Excel 2007
With macro, not Solver.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_15_09a.xlsm

 

[Mike Middleton]

maywood -

Do you have any ideas how to solve it correctly? <

If you have to use the standard Solver shipped with pre-2010 Excel, you can
replace the IF function with linear equations and a binary variable. See
below for a message containing a description of the method by Tushar Mehta.
Also, the topic is covered in most linear programming textbooks.

Is it possible to use an If-Then-Else Equation with the Solver? <

No, not directly. See above. The Premium Solver for Education, included with
many textbooks, can handle some non-continuous functions.

For more details, see http://www.solver.com/xlsplatformb.htm

One of my recent messages is included below.

- Mike
http://www.MikeMiddleton.com

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

+++++++++++++++++++++++++++++++++++++++++++++++
I found where you address the IF statement, but I'm having trouble
following. I think this is on the right path for me, so I'd appreciate
any patience and help to clarify. I've posted your previous post below
with questions at the end of each sentence:


"First, the IF statement. Suppose that a firm has a choice of 2
plants where it can produce a product. If it uses a particular plant
to
produce any amount of the product, it incurs a fixed cost of say
$50,000." -----Do I understand this as choose the plant that creates
more product for the fixed $50,000 price?


"This has the nature of an IF statement of the type [IF x>0 then K
else 0], where K is a constant." -----I don't get what x or K
represent. Does x = amount of product and K = $50,000?


"One can replace the IF with linear equations by introducing a binary
variable, b, and a large constant, say, M. Now, the IF statement
becomes
K*b
x <= M*b
b = 0/1 (b is binary)
x >= 0
How does it work? If x is anything other than 0, the x <= M*b will
be
satisfied only if b is 1. If b is 1, the K*b will evaluate to K!
Also,
since M is a very large number, once b is 1, x <= M*b will always be
true no matter how large x becomes". ----Does x represent essentially
the binary threshold (i.e. less than x then with this plant, more than
x go with the other plant). If so, can it be a non-zero number? ----
Also I don't get how the binary is applied in Excel.


Thanks!
K


+++++++++++++++++++++++++++++++++++++++++++++++

Kerry -

Are there other Solver limitations I need to know about that could be
causing the issue? <

Bernard Liengme suggested checking www.solver.com, where you will see that
Premium Solver can automatically transform nonsmooth functions like IF, MIN,
MAX, ABS, AND, OR, and NOT. As you have found, standard Solver generally
cannot.

For the standard Solver add-in shipped with pre-2010 Excel, Tushar Mehta
suggested a workaround for dealing with the nonsmooth IF function using a
binary variable.


For a brief description of these issues, see
http://www.solver.com/xlsplatformb.htm

What is a binary variable in excel, how do I incorporate it and wouldn't
it also cause the function gaps or sudden jumps that Solver has issues
with? <

A binary variable is restricted to the values zero or one. On the Solver
Parameters dialog box, you click the Add button (for the Constraints),
specify the cell reference where your model's binary variable is located,
and use the unlabeled "relationship" drop-down list to select "bin," which
automatically enters "binary" in the Constraint edit box.

Using a binary variable does not have the same issues as a nonsmooth
function, because Solver uses a different algorithm for model formulations
that contain a binary or integer variable.


- Mike
http://www.MikeMiddleton.com

 

[Herbert Seidenberg]

Excel 2007
Changed goal from minimum production cost
to maximum profit.
Added selling price and cost of excess capacity.
Sensitivity analysis with Monte Carlo method and
multiple regression.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_15_09a.xlsm