ch03

DECISION MODELING

DECISION MODELING

Chapter 3

Linear Optimization

WITH

WITH

MICROSOFT EXCEL

MICROSOFT EXCEL

Linear Optimization

A constrained optimization model takes the form of a constrained optimization model

performance measure to be optimized over a range of feasible values of the decision variables.

The feasible values of the decision variables are determined by a set of inequality constraints. constraints

Values of the decision variables must be chosen such that the inequality constraints are all satisfied while either maximizing or minimizing the desired

performance variable.

These models can contain tens, hundreds, or thousands of decision variables and constraints.

Linear Optimization

Very efficient search techniques exist to optimize

constrained linear models

constrained linear models.

These models are historically called linear programs linear programs

(LP). In this chapter we will:

1. Develop techniques for formulating LP models

2. Give some recommended rules for expressing LP models in a spreadsheet that facilitates

application of Excel’s Solver

Every linear programming model has two important features:

Formulating LP Models

Objective Function

Objective Function

Constraints

Constraints

A single performance measure to be maximized

or minimized

(e.g., maximize profit, minimize cost)

Constraints are limitations or requirements on the set

of allowable decisions. Constraints may be further

classified into physical, economic, or policy

limitations or requirements.

Formulating LP Models

The first step in model formulation is the development of the constraints.

For example:

Investment decisions are restricted by the amount of capital and government regulations.

Management decisions are restricted by plant capacity and availability of resources.

Staffing and flight plans of an airline are restricted by the maintenance needs of the planes and the number of employees on hand.

The use of a certain type of crude oil in producing gasoline is restricted by the characteristics of the gasoline (e.g., octane rating, etc.)

Formulating LP Models

A constrained optimization model represents the constrained optimization model

problem of allocating scarce resources in such a way as to optimize an objective of interest.

To illustrate this, we will return to the Oak Products model from Chapter 2. However, we will start with a simplified model.

Based on economic forecasts for the next week, it has been determined that it will be possible to sell as many Captains or Mates chairs as the firm can produce.

So, the question becomes: How many Captains and

Mates chairs should be produced given our constraints in order to maximize next week’s profit contribution?

Oak Products, Inc.

Oak Products, Inc.

Formulating LP Models

Consider the following major factors:

1. The unit contribution margin (price minus unit variable cost) is $56 for each Captain sold and $40 for each Mate.

2. Long dowels, short dowels, legs and one of two types of seats are needed to assemble the chairs.

3. We have a limited inventory of 1280 long dowels and 1600 short dowels for next week’s

production.

Each Captain uses 8 long and 4 short dowels. Each Mate uses 4 long and 12 short dowels.

Formulating LP Models

4. The inventory of legs is 760 units and each chair produced of either type uses 4 legs.

5. The inventory of heavy and light seats is 140 and 120, respectively. Each Captain produced uses a heavy seat and each Mate uses a light seat.

6. In order for management to honor an agreement with the union, the total number of chairs

Let’s summarize the information given so far in a table:

Given these considerations, now decide how many Captains and Mates to produce next week.

This is called an optimal product mix problem or an optimal product mix optimal production plan

optimal production plan.

The first step in solving this is to identify the constraints constraints

For notation purposes, let C = number of Captains

chairs and M = number of Mates chairs to be produced.

Start with the number of long dowels:

Both types of chairs require long dowels. So, we can mathematically describe this as:

8(# Captains produced) + 4(# Mates produced) = 1280 8C + 4M = 1280

8C + 4M = 1280

However, since only 1280 long dowels are available and we can use at most, only that amount, we must make at most

this equation an inequality in order to satisfy this restriction. In this case:

8C + 4M < 1280

We use the < (less than or equal to) inequality because, we can use all 1280 long dowels, we can use less than that amount, but we will never be able to use more than 1280.

This is called an inequality constraint. inequality constraint

Left hand side (LHS) is called a constraint function. constraint function

Once again, here is the equation for the long dowels:

Now let’s consider the rest of the constraints:

Short Dowels

Short Dowels: We need 4 for each Captain and 12 for each mate and we cannot exceed 1600 short dowels.

4C + 12M < 1600

Legs

Legs: Each chair requires 4 legs and we have 760 in inventory.

Heavy Seat

Heavy Seat: These seats are only used for Captains

chairs and only 1 per chair is needed. 140 are available.

1C + 0M < 140

or

1C < 140

Light Seat

Light Seat: These seats are only used for Mates chairs and only 1 per chair is needed. 120 are available.

We need to consider one other constraint not given in the table.

Remember that there is a union agreement that we have to honor: The total number of Captains and Mates

chairs cannot fall below 100.

This means that we can make exactly 100 chairs, more than 100 chairs, but never less than 100 chairs.

Therefore, the inequality that we will use will be > (greater than or equal to).

C + M > 100

Note that there are no coefficients in front of C or M because we can make any combination of Captains or Mates chairs – as long as the total number does not fall below 100.

Nonnegativity Conditions

Nonnegativity Conditions

Since it does not make sense to produce a negative

number of Captain or Mate chairs, we must include two additional conditions called Nonnegativity Conditions:Nonnegativity Conditions

C > 0 and M > 0

Nonnegativity means that the resulting values can be 0 or positive, but not negative.

Summary of Constraints

Long Dowels

8C + 4M < 1280

Short Dowels

4C + 12M < 1600

Legs

4C + 4M < 760

Heavy Seat

C < 140

Light Seat

M < 120

Union Requirement

C + M > 100

Nonnegativity

C > 0, M > 0

Evaluating Various Decisions

The choice of values for the pair of variables (C,M) is called a decision.decision

C and M are called decision variables because they are decision variables

quantities that you can control.

In this problem, a decision is a production mix.

The mix, or decision, is a combination of Captains and Mates that will not violate one of the constraints.

Of the infinitely many nonnegative pairs of numbers (C,M), including fractional values, some pairs, or

decisions, will violate at least one of the constraints, and some will satisfy all the constraints.

Only nonnegative decisions that satisfy all the

constraints are allowable. These decisions are called

feasible decisions

feasible decisions.

Evaluating Various Decisions

The

Objective Function

Objective Function

Every linear programming model has a specific

objective as well as constraints. In this case, we would like to maximize next week’s profit.

Profit contribution comes from the sale of two chairs: Captains and Mates. In fact, we will receive $56 for

every Captain and $40 for every Mate sold. We can mathematically state this as:

Evaluating Various Decisions

An Optimal Solution

An optimal solution is one that gives the largest total optimal solution

profit contribution.

An optimal decision is a decision that will maximize the optimal decision

total weekly profit contribution relative to the set of all possible feasible decisions.

Since total profit contribution is a function of the

variables C,M, we refer to the mathematical expression as the payoff or objective function.objective function

The goal is to find feasible values of C and M that optimize (i.e., maximize) the objective function:

Symbolic LP Model

Max 56C + 40M (objective function) Subject to (s.t.)

8C + 4M < 1280 (Long Dowels Restriction)

4C + 12M < 1600 (Short Dowels Restriction)

C + M > 100 (Minimum Production)

4C + 4M < 760 (Legs Restriction)

C < 140 (Heavy Seats Restriction)

M < 120 (Light Seats Restriction)

Symbolic LP Model

Linear Functions

Max 56C + 40M (objective function) Subject to (s.t.) 8C + 4M < 1280 4C + 12M < 1600 C + M > 100 4C + 4M < 760 C < 140 M < 120 C > 0 and M > 0

Notice that the

constraint functions

constraint functions

and the objective functionobjective function

are linear functions of the two linear functions

decision variables.

The graph of a linear function of the two variables is a

straight line. Variable X V ar ia b le Y

8C + 4M 4C + 12M

C + M

Linear Functions

A linear function is one in which

each variable appears only once

there are no products or quotients of variables, no exponents (other than 1), no logarithmic,

exponential or trigonometric terms, and no IF() statements in Excel. Examples of Linear Equations Examples of Nonlinear Equations 14C + 12CM 9C2 + 8M 6 C + M

IF(), MAX(), MIN(), LN(), ABS() Excel functions

Linear Programming

Remember,

1. A linear program always has an objective

function (to be either maximized or minimized) and constraints).

2. All functions in the problem (objective and constraints) are linear functions.

1. Add an “integrality condition” to the LP model, which forces one or more decision variables to take on only integer values. This results in an Integer Optimization Model. Integer Optimization Model

In LP models where fractional solutions are not directly meaningful, there are four possible recourses:

Integrality Considerations

2. Solve the model as an ordinary LP and then round any decision variable for which a fractional

3. Consider the one-week Oak Product model results to be an average week’s production for an

ongoing multiple week situation. The whole units are produced and the fraction is carried over to the next week as a “work-in-process.”

Integrality Considerations

4. Consider the one-week Oak Product model results to be for planning purposes only and notplanning purposes only

operational decisions to be implemented per se. Take into account other information when making the final decision.

The Art of LP Model Formulation

Create a verbal model to translate a managerial verbal model

situation into a symbolic model: symbolic model

1. Express the objective and its performance measure objective function in words.

2. Express each constraint in words.

3. Verbally identify the decision variables. Ask

yourself “What decisions must be made in order

to optimize the objective function?”

4. Express each decision variable in the constraint using symbols.

5. Express each decision variable in the objective function using symbols.

Finally, check your work for consistency of units of measurement.

Sunk vs. Variable Cost

There are often two types of costs: sunk costs (fixed sunk costs

costs) and variable costs. variable costs

Only the variable costs are relevant in optimization models.

Unit contribution margin

Unit contribution margin = per unit revenue – per unit variable cost

Sunk costs affect only the accounting report of income or net profit in financial statements. They play no part in future decisions.

Oak Product Spreadsheet Model

Here is a spreadsheet version of the simplified Oak Product model containing the previously specified objective function and constraints.

Note that producing 110 Captains and 90 Mates violates the Legs constraint.

Labels are used to clarify the meaning of other entries. The numbers represent the coefficients, parameters, and decision values.

Formulas represent the objective function and constraints. Slack is the difference between the constraint function and the right-hand side computed so that it is non-negative.

More on Slack:

The purpose of these

calculations is to give an indication of how close any constraint is to bindingbinding

(evaluated as an equality). Zero Slack indicates a

binding constraint.

Ending Inventory is another name for Slack.

For a < constraint, slack is the right-hand side minus the left hand side.

For a > constraint, slack is the left-hand side minus the right hand side.

To calculate Slack:

Optional but useful, Slack tells us if any constraints are violated, resulting in an infeasible production plan.

“What if?” Projections

You can use the spreadsheet model to perform “What if?” analyses.

Type production values for the Captain and Mate chairs and observe the resulting weekly Profit.

Optimizing the Spreadsheet

Note that you can enter an infinite number of decision values for C and M in a “What if?” fashion. However, with Solver, you can transform any spreadsheet LP model into an optimized model with just a few mouse clicks. Here is the optimal model:

The LP Model and Spreadsheet Modeling

To produce an Excel LP model, follow these steps:

1. Write out the model on paper as a symbolic LP. Examine your written formulation and look for errors in the logic.

2. Use the symbolic LP model as a guide in creating the Excel representation. Further debug the Excel model by changing the decision variables and

checking for obvious errors.

3. Try to optimize the model with Solver. If there is a problem, Solver will give an error message.

Overview of Solver

Solver is an add-in package to Excel.

Solver numerically optimizes constrained models using a technique called a mathematical

programming algorithm (Simplex Method).

To use Solver, you must set up your spreadsheet model in the appropriate form.

Solver can optimize both linear and nonlinear models. We will focus only on linear models.

For LP optimization, every formula in your model must be linear.

START EXCEL

BUILD OR RETRIEVE YOUR OPTIMIZATION MODEL

SAVE YOUR WORKBOOK!!

SAVE YOUR WORKBOOK!!

CHOOSE “Solver…” IN THE “Tools” MENU

SPECIFY IN SOLVER DIALOG BOX: 1. CELL TO BE OPTIMIZED

2. CHANGING CELLS 3. CONSTRAINTS

IN OPTIONS DIALOG, CLICK “ASSUME LINEAR MODEL” & CLICK THE “OK” BUTTON

CLICK ON “SOLVE” BUTTON TO BEGIN OPTIMIZATION

REVIEW SOLVER COMPLETION MESSAGE MODIFY MODEL Solver Flowchart Solver Flowchart Solver Flowchart Solver Flowchart

SOLVER FOUND OPTIMUM SOLUTION? NO

YES

CLICK “KEEP SOLVER SOLUTION” & CLICK “OK” BUTTON

WANT TO CHANGE MODEL AND RE-OPTIMIZE?

NO YES

SAVE FINAL MODEL AND EXIT EXCEL

Overview of Solver

Changing Variable Cells Constraints

Constraint Cell Reference Constraint or Bound

Assume Linear Model or Standard Simplex LP

LP Modeling Terminology

LP Modeling Terminology Solver TerminologySolver Terminology

NOTE: if you get a negative decision and it is not meaningful to your model, be sure to specify the nonnegativity

Optimizing with Solver

To optimize the Oak Products model with Solver, first open the file in Excel and then choose Tools – Solver…

The Solver Parameters dialog box will appear.

By default, Max is selected (for maximization) and the cursor is in the first edit field: Set Target Cell.

Look for the Premium button. If it is not there, then you have not installed Premium Solver for Education (available from the CD). Please install this version.

Clicking on Premium allows you to specify the type of optimization that it will perform.

We will use the default Standard Simplex LP optimization.

With your cursor in the Set Target Cell: edit field, specify the cell to be optimized (i.e., your model’s performance measure).

The easiest way to do this is to move the dialog (drag the title bar) so that cell D4 is exposed and then click on that cell.

You can also click on the button in the edit field of the dialog to collapse the dialog, click on the cell, and then click on the button to expand the dialog.

The Equal to: field allows you to specify the type of

optimization. You can either maximize or minimize the performance variable or cause the Target Cell to be

equal to a value of your choosing (select Value of:).

Specify the Oak Product model’s decision variables (cells B4:C4) in the By Changing Cells: edit field.

To specify the constraints, click on the Add button to open the Add Constraint dialog.

For the LHS of the constraint, specify

the cell ranges for the Total LHS of Total LHS

either one constraint or a group of similar constraints (i.e., constraints with the

same inequality) in the Cell Reference: edit field of the Add

For the RHS of the constraint, specify the cell ranges of either one resource limitation or a group of similar

limitations in the Constraint: edit field.

Note that when specifying many constraints at the same time, the

number of cells referenced in the LHS must equal the number in the RHS.

Note that the inequality is > for

this constraint.

Finally, add the Chair Production

Here is the resulting Solver dialog after adding all of the constraints:

Now, in the Solver Parameters dialog, click the Options button to specify a linear model.

In the resulting Solver Options dialog, click on the following options:

Assume Linear Model

Assume Non-Negative

Use Automatic Scaling

(Specifies an LP model, same as Standard Simplex LP) (Apply nonnegativity constraints)

It is important to check to see if Solver found a solution and if all constraints and optimality conditions were satisfied.

This information will be displayed in the first sentence in this dialog. ALWAYS READ THIS SENTENCE!_{ALWAYS READ THIS SENTENCE!}

Click OK to return to the Solver Parameters window and then click Solve to start the optimization.

Remember that this is an iterative technique and may take a few seconds or a few minutes depending on the size of the model.

Upon the successful completion of the Solver program, you have the following options:

Keep the Solver Solution

Restore the Original Values (throw the solution away) Receive up to three reports on the solution (each

formatted as a new worksheet added to the Excel workbook.

NOTE: The Premium edition may also list an Infeasibility Report and a Non-Linear Report if there is a problem.

Now, select the Answer report, accept the default Keep

Solver Solution, and click OK.

The resulting report will be placed in the Excel workbook as a worksheet with the tab name: Answer Report 1

Note: this report has been edited to remove unneeded blank lines.

Note that these cells have now changed to reflect the inventory consequences of the optimal production

decisions. Remember, a Slack (End. Inv.) = 0 indicates the constraint is “binding at optimality.”

Recommendations for Solver

Three LP modeling habits you should develop for better use of Solver:

1. Make sure the numbers in your LP model are scaled such that the difference between the

smallest and largest numbers in the spreadsheet is no more than 6 or 7 digits of precision (e.g., .05 and 10.00 is an acceptable range while .05 and

2. All RHS’s in the Constraints section of the Solver

Parameters dialog should contain cell references

(to the RHS cells in the spreadsheet model itself), and

Recommendations for Solver

The RHS cells on the spreadsheet model itself should contain constants and not formulas (or

more precisely, no formulas that involve decision variables directly or indirectly).

3. Use Excel’s Range Naming commands to give

range names to the performance measure cell, its decision cells, its constraint function (Total LHS) cells, and its RHS cells.

Recommendations for Solver

These names will automatically be substituted for the corresponding cell ranges in the Solver

Solver is used on the more complex version of the Oak Products model from Chapter 2. The optimal solution is given below.

A Transportation Example

A company has 2 plants and 3 warehouse outlet stores. Consider the following information:

Plant 1 can supply at most 100 units

Plant 2 can supply at most 200 units (of same product)

Maximum sales quantities and price/unit sold are: Warehouse Outlet 1: 150 units, $12/unit

Warehouse Outlet 2: 200 units, $14/unit Warehouse Outlet 3: 350 units, $15/unit

This is a class of LP problems called Network Problems Network Problems

(notice later that all the coefficients in the constraints are equal to one).

A Transportation Example

The cost of manufacturing one unit at plant i and shipping it to warehouse outlet j is as follows:

TO WAREHOUSE ($)

FROM PLANT 1 2 3

1 8 10 9

2 7 9 11

Given this information, determine how many units

should be shipped from each plant to each warehouse so as to maximize profit.

A Transportation Example

The decision variables are:

X_ij = number of units manufactured and sent from plant i to warehouse outlet j

For each X_ij (decision variable), the corresponding unit profit is equal to :

Sales price/unit sold at warehouse j – cost of making and shipping a unit from plant i to warehouse outlet j.

For example:

sales revenue = $12/unit, shipping cost = $8/unit, so

Symbolic LP Model

Max 4X₁₁ + 5X₂₁ + 4X₁₂ + 5X₂₂ + 3X₁₃ + 4X₂₃

Subject to (s.t.)

X₁₁ + X₁₂ + X₁₃ < 100 (supply capacity, plant 1)

X₂₁ + X₂₂ + X₂₃ < 200 (supply capacity, plant 2)

X₁₁ + X₂₁ < 150 (sales limit, warehouse outlet 1)

X₁₂ + X₂₂ < 200 (sales limit, warehouse outlet 2)

X₁₃ + X₂₃ < 350 (sales limit, warehouse outlet 3)

Here is the spreadsheet model associated with the previous information.

Now, perform the Solver analysis to find the optimal solution and maximum profit.

A Blending Example

Eastern Steel: A Blending Example.

In this example, we are concerned not with maximizing profit, but with minimizing cost. Consider the following minimizing cost

information:

Iron ore from 4 different mines is blended to make a metal alloy. In order to produce a blend with suitable tensile qualities, the following minimum requirements must be met on three basic elements (A, B, and C):

MIN. REQUIREMENT PER TON OF BLEND (pounds of each element)

BASIC ELEMENT

A 5

B 100

A Blending Example

The amount of these elements in the ores from the three different mines is given below:

MINE

(pounds per ton of each element) BASIC ELEMENT

A 10 3 8 2

B 90 150 75 175

C 45 25 20 37

A Blending Example

We must now take into consideration the cost of ore from each mine:

DOLLAR COST PER TON OF ORE MINE

1 800

2 400

3 600

A Blending Example

Eastern Steel’s objective is to come up with a least-cost

feasible blend of the ores.

First, specify the decision variables:

T₁ = fraction of a ton to be chosen from mine 1

T₂ = fraction of a ton to be chosen from mine 2

T₃ = fraction of a ton to be chosen from mine 3

A Blending Example

Now, specify the amounts of the basic elements in 1 ton of blend:

A = 10T₁ + 3T₂ + 8T₃ + 2T₄

Pounds of Element in 1 Ton of Blend B = 90T₁ + 150T₂ + 75T₃ + 175T₄

C = 45T₁ + 25T₂ + 20T₃ + 37T₄

Finally, create the constraints based on this information: _{10T1 + 3T2 + 8T3 + 2T4 > 5}

90T₁ + 150T₂ + 75T₃ + 175T₄ > 100

T₁ , T₂ , T₃ , T₄ > 0

T₁ + T₂ + T₃ + T₄ = 1

A Blending Example

There are two more constraints to consider:

Nonnegativity Constraint

Nonnegativity Constraint:

Equality Constraint

Equality Constraint (also called a material balance condition) restricts the values of the decision variables in such a way that the left-hand side exactly equals the right-hand side.

Note that the constraints in a linear programming model can be equalities as well as inequalities.

A Blending Example

The objective function is to minimize cost of any blend and is given by:

Cost of 1 ton of blend = 800T₁ + 400T₂ + 600T₃ + 500T₄ Now let’s put it all together and write the complete

Symbolic LP Model

The spreadsheet model and resulting Solver analysis:

Solver Options

Note that this is a

minimization