ch06

DECISION MODELING WITH

DECISION MODELING WITH

MICROSOFT EXCEL

MICROSOFT EXCEL

Integer

Integer

Optimization

Optimization

Integer Linear Programming

Integer Linear Programming (ILP) models

are LP models in which some or all of the variables are required to assume integer values.

ILP has become an important specialized area of optimization modeling.optimization modeling

In previous chapters, noninteger solutions were allowed and often adapted to the

integer requirement by rounding (called a

rounded solution). However, a number of

important models require integer solutions.

Introduction

Integer Programming

Integer Programming is a general term for

optimization models with integrality integrality conditions

conditions.

The different types of ILP models are described next.

Types of Integer

Types of Integer

Optimization Models

Optimization Models

Integrality conditions stipulate that some or all of the decision variables must have

Classifications of

Classifications of

Integer Optimization Models

Integer Optimization Models

All-integer linear program

All-integer linear program

This is a model in which all of the all

decision variables are required to be integers.

Mixed integer linear program (MILP)

Mixed integer linear program (MILP)

This is a model in which only some of only some the decision variables are required to be integers and others can assume any nonnegative number (i.e., any any

continuous value

In this model, integer variables are restricted to the values 0 or 0 1. The 1 models may be used to represent dichotomous decisions (yes/no

decisions).

Binary (or 0-1) integer linear program

Binary (or 0-1) integer linear program

This LP model results when you start

with an ILP formulation but then ignore the integer restrictions.

LP relaxation of the ILP model

Graphical Interpretation

Graphical Interpretation

Of Integer Models

Of Integer Models

In this two variable ILP product mix model, let E and F represent the quantities of two products to make and sell for a profit.

Optimizing a Two Variable ILP Model

Optimizing a Two Variable ILP Model

Now, assume the < constraints represent resource restrictions and > represents

s.t. E – 3F < 0 (1) 42.8E + 100F < 800 (2) 20E + 6F < 142 (3) 30E + 10F > 135 (4) E, F > 0 and integer Max 18E + 6F

We will solve this model using the graphical approach following these three steps:

1. Find the feasible set for the LP relaxation of the ILP model.

2. Identify the integer points inside the set determined in step 1.

3. Find, among those points determined in step 2, one that optimizes the

objective function.

We will use the GLP program (covered in

Chapter 4) and Excel’s Solver to obtain the solution to the LP relaxation of the ILP.

Here are the GLP results: The shaded region is the feasible set for the LP relaxation. The blue dots are the integer points contained within the LP feasible region. These 13 integer points are

the set of feasible solutions to the ILP. (3,6) (4,6) (3,5) (4,5) (5,5) (4,4) (5,4) (4,2) (5,2) (6,2) (4,3) (5,3) (6,3)

To optimize the model, determine which of the ILP feasible points yields the largest value of the objective function.

To do this, drag the

objective function line in an uphill direction until it

is not possible to move it farther and still intersect an integer feasible point.

The optimal

solution is E=6 and F=3 for a max. profit of $126.

Here are the Solver results and parameters:

In order to specify the integer constraint, use the int option in the Add Constraint dialog.

Change %

Tolerance

setting to “0”

Nearest rounded solution (5,6) Optimal solution to LP relaxation (5.39,5.69) Only feasible rounded integer solution (5,5) Optimal solution to ILP (6,3)

1. In a Max model the OV (optimal Max

value) of the LP relaxation always

provides an upper bound on the OV ofupper bound the original ILP. Adding the integer

constraints either hurts or leaves

unchanged the OV for the LP. In a Max model, hurting the OV means making it smaller.

Comments

Comments

Graphical Interpretation

Graphical Interpretation

Of Integer Models

Of Integer Models

2. In a Min model, the OV of the LP Min relaxation always provides a lowerlower

bound

bound on the OV of the original ILP.

Again, adding the integer constraints either hurts or leaves unchanged the OV for the LP. In a Min model, hurting the OV means making it larger.

Graphical Interpretation

Graphical Interpretation

Of Integer Models

Of Integer Models

The optimal solution to the LP relaxation is E*=5.39 and F*=5.69.

Rounded Solutions

Rounded Solutions

Since each of these variables can be

rounded either up or down, there are four (22) rounded solutions:

(5,5) (5,6) (6,5) (6,6)

With n decision variables, there would 2n

Of all the rounded points, (5,5) is the only feasible point. All other points are

infeasible.

Two important facts about rounded solutions:

1. A rounded solution need not be optimal.

2. A rounded solution need not be near the optimal ILP solution.

Optimal solution to ILP _Optimal solution to the LP relaxation. Feasible set for

LP relaxation

Integer points

Recall that Solver’s LP simplex method

makes use of the fact that the solution to an LP always lies on the boundary of the

feasible region.

Enumeration

Enumeration

Graphical Interpretation

Graphical Interpretation

Of Integer Models

Of Integer Models

Thus, Solver never has to consider any interior points of the feasible region.

For an ILP optimization, Solver might have to visit many integer points strictly within the interior of the feasible region, and thus, the LP simplex method cannot be used.

Once you identify all the integer feasible points in ILP, you could solve the model by

complete enumeration

complete enumeration (i.e., evaluate the

objective function at each integer point and then select the best one).

Unfortunately, complete enumeration of all the feasible integer points is not a

reasonable procedure for most ILPs.

For example, if you had 20 decision

variables, each of which could take on an

integer value between 1 and 50, then there would be 5020 (9.5x1023) points to

Applications of

Applications of

Binary Variables

Binary Variables

Binary (0-1) variables make it possible to incorporate yes/no decisions (called

dichotomous decisions) into an optimization model.

For example:

1. In a plant location model, let x_j = 1 if we choose to have a plant at location j and x_j = 0 if we do not.

2. In a routing model, let x_ijk = 1 if

truck k goes from city i to city j and x_ijk = 0 if it does not.

The use of 0-1 variables allows many variations of logical conditions in an

optimization model without resorting to any of Excel’s “=IF()” statements (which would not allow Solver to optimize the model).

Applications of

Applications of

Binary Variables

Binary Variables

The capital budgeting decision is a matter

of choosing among n alternatives in order to maximize the return, subject to constraints on the amount of capital invested over time.

Capital Budgeting:

Capital Budgeting:

An Expansion Decision

An Expansion Decision

Applications of

Applications of

Binary Variables

Binary Variables

For example, suppose that AutoPower’s

Expand Belgian Plant 400 100 50 200 100 0 Expand Sm. Machine Capacity in US 700 300 200 100 100 100 Establish New Plant in Chile 800 100 200 270 200 100 Expand Lg. Machine Capacity in US 1000 200 100 400 200 200 Capital Available 500 450 700 400 300 ALTERNATIVE PRESENT VALUE OF NET RETURN ($000s)

CAPTIAL REQUIRED IN YEAR BY ALTERNATIVE ($000s)

1 2 3 4 5

The Board must select one or more of these alternatives:

An ILP Model for Capital Budgeting at

An ILP Model for Capital Budgeting at

AutoPower:

AutoPower: This model can be modeled as

an ILP (called a binary or binary 0-1 ILP model) in 0-1 ILP which all the variables are binary variables. Let x_i = 1 if project i is accepted and x_i = 0 if project i is not accepted. The model

Max 400x₁ + 700x₂ + 800x₃ + 1000x₄ s.t. 100x₁ + 300x₂ + 100x₃ + 200x₄ < 500 50x₁ + 200x₂ + 200x₃ + 100x₄ < 450 200x₁ + 100x₂ + 270x₃ + 400x₄ < 700 100x₁ + 100x₂ + 200x₃ + 200x₄ < 400 100x₂ + 100x₃ + 200x₄ < 300 x_i = 0 or 1; i = 1, 2, 3, 4 Present value from accepted projects Capital required in year 1 Capital available in year 1

The LP Relaxation:

The LP Relaxation: First, approach this

model by solving the LP relaxation.

=SUMPRODUCT(Decisions, C5:F5) =H7-G7

Note: named ranges were used to improve readability of models.

Note that your solution may differ because the model has alternative optimal solutions.

In the LP relaxation model, x_i was

constrained to be less than or equal to 1. This resulted in fractional values (0<x_i<1) for the decision variables.

Since we are looking for 1’s (yes) and 0’s (no), these fractional values are not

meaningful. In addition, rounding these values would not work very well.

Rounding to: x₁ = 1, x₂ = 1, x₃ = 0, x₄ = 1 would result in an infeasible solution.

The optimal ILP solution can be obtained

using Solver’s integer programming option.integer programming Use the bin

option to force all four

of the decision variables to

The Premium Edition Solver for Education produces a different Solver Results

completion message for ILPs to remind you that the ILP solution may not be optimal.

The default Tolerance field on the Solver

Options dialog (relevant only for ILP

models) is 5%.

This means that the

Solver ILP optimization procedure is continued

only until the ILP solution OV is within 5% of the

ILP’s optimum OV.

A higher Tolerance speeds

up Solver at the risk of a reported solution further from the true ILP optimum.

Setting Tolerance to 0% forces Solver to find the ILP optimum but with much longer

An important use of binary variables is to impose constraints that arise from logical conditions.

Logical Conditions

Logical Conditions

Applications of

Applications of

Binary Variables

Binary Variables

Alternatives:_{Let xi = 0 or 1, for i = 1, 2, …, n}

The constraint

x₁ + x₂ + … + x_n < k

implies that, at most, k alternatives of n possibilities can be selected (i.e., not

For example, adding the constraint: x₁ + x₃ < 1

to the previous AutoPower example, implies that the solution can contain at most one of the overseas alternatives.

Dependent Decisions:

Dependent Decisions: You can use 0-1

variables to force a dependent relationship on two or more decisions.

For example, suppose that AutoPower’s management does not want to select

alternative k unless it first selects

alternative m. The following constraint enforces this condition:

If alternative m is not selected, then x_m = 0 which forces x_k to be 0 (i.e., not selected).

x_k < x_m or equivalently x_k - x_m < 0

If alternative m is selected, then x_m = 1 and x_k < 1. Solver is then free to select 0 or 1

Lot Size Constraints:

Lot Size Constraints: A portfolio manager is considering purchasing security j. Let x_j be the number of shares purchased. Consider the following constraints:

1. If he purchases security j, he must

purchase at least 200 shares (called a minimum lot size or minimum lot size batch sizebatch size

constraint)

2. He may not purchase more than 1000 shares of security j.

200 < x_j < 1000

These constraints insist that x_j always be at least 200. We want the conditions to be

To achieve the constraint conditions, use a binary variable, say y_j, for security j.

If y_j = 1, then purchase security j

The constraints are:

x_j < 1000y_j x_j > 200y_j

If y = 1, then the above constraints imply that 200 < x_j < 1000 (purchase j).

If y = 0, then the above constraints imply that x_j = 0 (do not purchase j).

If y_j = 0, do not purchase security j These 2 constraints together guarantee

the “minimum lot size” constraint.

K

K of of mm Constraints: Constraints: In general notation, let the “superset” of m constraints on a

model’s (non-binary) decision variables, x_i, be

g_i(x₁ , …, x_n) < b_i, for i = 1, …, m

Now, introduce m additional 0-1 decision variables y_i to the model, and let M be

chosen as a very large number, so large that, for each i,

g_i(x_i , …, x_n) < M

for every x satisfying any set of k inequalities taken from the above m.

The following m + 1 constraints express the desired condition:

m

i=1



This constraint forces exactly k of the new y_i decision variables to have the value 1.

g_i(x₁ , …, x_n) < b_i y_i + M(1- y_i), i=1, …, m

Exactly k of the inequality constraints are equivalent to: gi(x1 , …, xn) < bi

The remaining m-k inequality constraints are equivalent to: gi(x1 , …, xn) < M

The very large number choice for M causes each such constraint to be redundant and not affect the model’s optimal solution.

K

K of of mm Constraints Example: Constraints Example: Assume a

company must find production quantities of three products (x₁, x₂, x₃) as part of a large LP model.

Within the LP formulation, the company

must choose one or the other (but not both) of two different production technologies for the 3 products.

Here are the two constraints:

30x₁ + 20x₂ + 10x₃ < 100 (Technology 1) 10x₁ + 30x₂ + 5x₃ < 110 (Technology 2)

These constraints cannot be added directly to the LP model. Instead, add two new

binary decision variables (y₁ and y₂) to the LP model, making it an ILP model.

y₁ = 1 “Solver, choose Technology 1”

y₁ = 0 “Solver, do not choose Technology 1”

y₂ = 1 “Solver, choose Technology 2”

The original constraints are then modified:

30x₁ + 20x₂ + 10x₃ < 100y₁ + 999999(1-y₁) 10x₁ + 30x₂ + 5x₃ < 110y₂ + 999999(1-y₂)

y₁ + y₂ = 1

Forces Solver to choose exactly one

technology option

999999 was chosen to guarantee that

one or the other constraint will be redundant for the

A Fixed Charge Model

A Fixed Charge Model

electronics parts wholesaler, leases regional warehouses for its use.

The cost per month to lease warehouse i is F_i. Warehouse i can load a maximum of T_i trucks per month.

There are four sales districts, and the typical monthly demand in district j is d_j truckloads.

The average cost of sending a truck from warehouse i to district j is c_ij.

STECO wants to know which warehouses to lease and how many trucks to send from

each warehouse to each district.

STECO pays no lease cost for a given warehouse unless at least one truck is

dispatched from it, and then it pays the full monthly lease amount.

Lot size models incorporating this cost

behavior are common and are called fixed fixed charge models

Here is the network flow diagram: A A BB CC 1 1 22 33 44 Leasing cost/mth for warehouses F_A F_B F_C Capacity (truckloads) T_A T_B T_C Warehouses Districts Demands per month d₁ d₂ d₃ d₄

Here are the data for this model: A 170 40 70 160 200 7750 B 150 195 100 10 250 4000 C 100 240 140 60 300 5500 Monthly Demand (truck loads) 100 90 110 60 WAREHOUSE Monthly Capacity (No. of Trucks) Cost Per Truck

Sales District ($) 1 2 3 4

Monthly Leasing Costs ($)

A Fixed Charge Model

A Fixed Charge Model

Modeling Considerations:

Modeling Considerations:

Define y_i as a binary decision variable and let:

y_i = 1 if warehouse i is leased

y_i = 0 if warehouse i is not leased

Although it may seem logical to treat the

number of trucks sent from a warehouse to a district as an integer variable, in reality, there are three arguments as to why we should not:

1. This is a planning model, not a detailed operating model.

2. Treating the number of trucks as

integer variables may make the model more difficult to optimize.

3. It costs more to lease one of the

warehouses than to send a truck from a warehouse to a sales district.

A Fixed Charge Model

A Fixed Charge Model

The MILP Model

The MILP Model

To model STECO’s model as an MILP, let

y_i = 1 if warehouse i is leased, i=A, B, C y_i = 0 if warehouse i is not leased

x_ij = number of trucks sent from warehouse i to district j

Min 7750y

Min 7750y_{A A} + 4000y+ 4000y_BB + 5500y + 5500y_CC + 170x + 170x_A1A1 + … + 60x + … + 60x_C4C4

Total leasing cost Total truck cost

x_A1 + x_B1 + x_C1 > 100 demand at district 1

Demand Constraints:

x_A2 + x_B2 + x_C2 > 90demand at district 2

x_A3 + x_B3 + x_C3 > 110 demand at district 3

x_A4 + x_B4 + x_C4 > 60demand at district 4

These four constraints guarantee that

demand will be satisfied at the respective sales district.

x_A1 + x_A2 + x_A3 + x_A4 < 200y_A or

Capacity Constraints (for each warehouse):

These constraints serve 2 purposes:

x_A1 + x_A2 + x_A3 + x_A4 - 200y_A < 0

1. It guarantees that capacity at warehouse i is not exceeded.

2. It forces STECO to lease warehouse i if anything is sent out of it.

x_B1 + x_B2 + x_B3 + x_B4 < 250y_B x_C1 + x_C2 + x_C3 + x_C4 < 300y_C

Here is the Solved spreadsheet model:

=J4*C9

In conclusion, the optimal solution to this model with integer supplies and demands will always include an integer allocation of trucks.

A Fixed Charge Model

A Fixed Charge Model

The argument involves two steps:

1. The optimal solution must lease some set of warehouses, and

2. Every possible set of leased warehouses yields an integer allocation of trucks.

Integer Optimization Methods

Integer Optimization Methods

model (called Branch-and-Bound) is as Branch-and-Bound follows for a maximization model:

1. Solve the original ILP formulation as a relaxed LP.

The OV for the relaxation is the ILP upper bound.

If the optimal solution is all-integer, it is optimal for the ILP, and so, quit.

2. If the LP relaxation has some integer variable at a fractional value, form two sub models from this parent branch so as to create two new unsolved LP models (the successors) with the

property that the optimal solution to one of the successor ILPs will be the optimal solution to the parent ILP.

Let x_i* be any fractional variable of the

optimal solution to the parent’s relaxation.

Let [x_i*] be the truncation of x

i* to its

Then, [x_i*] + 1 is the next integer

larger than x_i*.

One successor sub-model will be the parent’s LP model augmented by the constraint, x_i < [x_i*].

The other successor sub-model is

formed by augmenting the parent’s LP model with x_i > [x_i*] + 1.

3. Commence with any unsolved model in step 2. And optimize it as a relaxed LP sub-model.

If the optimal solution is all-integer, evaluate its objective function.

Compare the OV of the best ILP

model’s solution found so far with this relaxed sub-model’s OV.

If the relaxed sub-model’s OV is worse than the best ILP solution found so far, don’t continue. Instead, continue with another sub-model.

If the relaxed sub-model’s OV is not

worse than the best ILP solution found so far, then proceed to eliminate any other fractional variables it may have using the constraint augmentation

procedure by going back to step 2. If all remaining relaxed sub-models have integer solutions, go to step 4; otherwise, go back to step 2.

4. The optimal solution to the original ILP is the all-integer solution of some sub-model that produced the best

In the branch-and-bound method, the

original ILP is decomposed into a growing sequence of LP sub-models.

Solver uses numerous optimizations of increasingly augmented LP sub-model formulations to solve a given ILP.

Therefore, it is more time-consuming to optimize ILPs than LPs.

The operation of the branch-and-bound

procedure in Solver displays the following message in Excel’s lower left corner:

Integer Optimization Methods

Integer Optimization Methods

Sensitivity Analysis for ILPs

Sensitivity Analysis for ILPs

The Solver solution to an ILP does not contain any sensitivity information (as

evidenced by the lack of a Sensitivity option Sensitivity in the Solver Results dialog).Solver Results

An ILP solution does not include information that is equivalent to the shadow price,

reduced cost and objective coefficient sensitivity information in an LP.

Solutions to ILPs can be extremely sensitive to changes in parameter values. To

illustrate, consider the following capital budgeting model:

Although this model is easily solved by

inspection, consider the following optimal solution:

Max 10x₁ + 100x₂ + 1000x₃ 29x₁ + 30x₂ + 31x₃ < b₁ x₁, x₂, x₃ are binary (0 or 1)

x₁ x₂ x₃ 29 1 0 0 10 30 0 1 0 100 31 0 0 1 1000 b₁ Optimal Solution OV

Here are the sensitivity data for the model:

A change in the right-hand side of the

constraint increases the OV by a factor of 1000%.

Integer Optimization Methods

Integer Optimization Methods

Heuristic Methods:

Heuristic Methods:

Heuristic methods are designed to

efficiently produce “good,” although not necessarily optimal, solutions.

These methods will be discussed in a the next chapter.