Table of Contents Chapter 2 Linear Programming Basic

Table of Contents Chapter 2 (Linear Programming: Basic Concepts) Three Classic Applications of LP (Section 2. 1) The Wyndor Glass Company Product Mix Problem (Section 2. 2) Formulating the Wyndor Problem on a Spreadsheet (Section 2. 3) The Algebraic Model for Wyndor (Section 2. 4) The Graphical Method Applied to the Wyndor Problem (Section 2. 5) Using the Excel Solver with the Wyndor Problem (Section 2. 6) A Minimization Example—The Profit & Gambit Co. (Section 2. 7) 2. 2 2. 3 2. 4– 2. 8 2. 9 2. 10– 2. 20 2. 21– 2. 26 2. 27– 2. 32 Introduction to Linear Programming (UW Lecture) 2. 33– 2. 48 These slides are based upon a lecture introducing the basic concepts of linear programming and the Solver to first-year MBA students at the University of Washington (as taught by one of the authors). The lecture is largely based upon a production problem using lego building blocks. The Graphical Method and Properties of LP Solutions (UW Lecture) 2. 49– 2. 57 These slides are based upon a lecture introducing the graphical method and other concepts about linear programming solutions to first-year MBA students at the University of Washington (as taught by one of the authors). Mc. Graw-Hill/Irwin 1 © The Mc. Graw-Hill Companies, Inc. , 2003

Three Classic Applications of LP • Product Mix at Ponderosa Industrial – Considered limited resources, and determined optimal mix of plywood products. – Increased overall profitability of company by 20%. • Personnel Scheduling at United Airlines – Designed work schedules for all employees at a location to meet service requirements most efficiently. – Saved $6 million annually. • Planning Supply, Distribution, and Marketing at Citgo Petroleum Corporation – The SDM system uses LP to coordinate the supply, distribution, and marketing of each of Citgo’s major products throughout the United States. – The resulting reduction in inventory added $14 million annually to Citgo’s profits. Mc. Graw-Hill/Irwin 2 © The Mc. Graw-Hill Companies, Inc. , 2003

Wyndor Glass Co. Product Mix Problem • Wyndor has developed the following new products: – An 8 -foot glass door with aluminum framing. – A 4 -foot by 6 -foot double-hung, wood-framed window. • The company has three plants – Plant 1 produces aluminum frames and hardware. – Plant 2 produces wood frames. – Plant 3 produces glass and assembles the windows and doors. Questions: 1. Should they go ahead with launching these two new products? 2. If so, what should be the product mix? Mc. Graw-Hill/Irwin 3 © The Mc. Graw-Hill Companies, Inc. , 2003

Developing a Spreadsheet Model • Step #1: Data Cells – Enter all of the data for the problem on the spreadsheet. – Make consistent use of rows and columns. – It is a good idea to color code these “data cells” (e. g. , light blue). Mc. Graw-Hill/Irwin 4 © The Mc. Graw-Hill Companies, Inc. , 2003

Developing a Spreadsheet Model • Step #2: Changing Cells – Add a cell in the spreadsheet for every decision that needs to be made. – If you don’t have any particular initial values, just enter 0 in each. – It is a good idea to color code these “changing cells” (e. g. , yellow with border). Mc. Graw-Hill/Irwin 5 © The Mc. Graw-Hill Companies, Inc. , 2003

Developing a Spreadsheet Model • Step #3: Target Cell – Develop an equation that defines the objective of the model. – Typically this equation involves the data cells and the changing cells in order to determine a quantity of interest (e. g. , total profit or total cost). – It is a good idea to color code this cell (e. g. , orange with heavy border). Mc. Graw-Hill/Irwin 6 © The Mc. Graw-Hill Companies, Inc. , 2003

Developing a Spreadsheet Model • Step #4: Constraints – For any resource that is restricted, calculate the amount of that resource used in a cell on the spreadsheet (an output cell). – Define the constraint in three consecutive cells. For example, if Quantity A ≤ Quantity B, put these three items (Quantity A, ≤, Quantity B) in consecutive cells. Mc. Graw-Hill/Irwin 7 © The Mc. Graw-Hill Companies, Inc. , 2003

A Trial Solution The spreadsheet for the Wyndor problem with a trial solution (4 doors and 3 windows) entered into the changing cells. Mc. Graw-Hill/Irwin 8 © The Mc. Graw-Hill Companies, Inc. , 2003

Algebraic Model for Wyndor Glass Co. Let D = the number of doors to produce W = the number of windows to produce Maximize P = $300 D + $500 W subject to D ≤ 4 2 W ≤ 12 3 D + 2 W ≤ 18 and D ≥ 0, W ≥ 0. Mc. Graw-Hill/Irwin 9 © The Mc. Graw-Hill Companies, Inc. , 2003

Graphing the Product Mix Mc. Graw-Hill/Irwin 10 © The Mc. Graw-Hill Companies, Inc. , 2003

Graph Showing Constraints: D ≥ 0 and W ≥ 0 Mc. Graw-Hill/Irwin 11 © The Mc. Graw-Hill Companies, Inc. , 2003

Nonnegative Solutions Permitted by D ≤ 4 Mc. Graw-Hill/Irwin 12 © The Mc. Graw-Hill Companies, Inc. , 2003

Nonnegative Solutions Permitted by 2 W ≤ 12 Mc. Graw-Hill/Irwin 13 © The Mc. Graw-Hill Companies, Inc. , 2003

Boundary Line for Constraint 3 D + 2 W ≤ 18 Mc. Graw-Hill/Irwin 14 © The Mc. Graw-Hill Companies, Inc. , 2003

Changing Right-Hand Side Creates Parallel Constraint Boundary Lines Mc. Graw-Hill/Irwin 15 © The Mc. Graw-Hill Companies, Inc. , 2003

Nonnegative Solutions Permitted by 3 D + 2 W ≤ 18 Mc. Graw-Hill/Irwin 16 © The Mc. Graw-Hill Companies, Inc. , 2003

Graph of Feasible Region Mc. Graw-Hill/Irwin 17 © The Mc. Graw-Hill Companies, Inc. , 2003

Objective Function (P = 1, 500) Mc. Graw-Hill/Irwin 18 © The Mc. Graw-Hill Companies, Inc. , 2003

Finding the Optimal Solution Mc. Graw-Hill/Irwin 19 © The Mc. Graw-Hill Companies, Inc. , 2003

Summary of the Graphical Method • Draw the constraint boundary line for each constraint. Use the origin (or any point not on the line) to determine which side of the line is permitted by the constraint. • Find the feasible region by determining where all constraints are satisfied simultaneously. • Determine the slope of one objective function line. All other objective function lines will have the same slope. • Move a straight edge with this slope through the feasible region in the direction of improving values of the objective function. Stop at the last instant that the straight edge still passes through a point in the feasible region. This line given by the straight edge is the optimal objective function line. • A feasible point on the optimal objective function line is an optimal solution. Mc. Graw-Hill/Irwin 20 © The Mc. Graw-Hill Companies, Inc. , 2003

Identifying the Target Cell and Changing Cells • • Choose the “Solver” from the Tools menu. Select the cell you wish to optimize in the “Set Target Cell” window. Choose “Max” or “Min” depending on whether you want to maximize or minimize the target cell. Enter all the changing cells in the “By Changing Cells” window. Mc. Graw-Hill/Irwin 21 © The Mc. Graw-Hill Companies, Inc. , 2003

Adding Constraints • • To begin entering constraints, click the “Add” button to the right of the constraints window. Fill in the entries in the resulting Add Constraint dialogue box. Mc. Graw-Hill/Irwin 22 © The Mc. Graw-Hill Companies, Inc. , 2003

The Complete Solver Dialogue Box Mc. Graw-Hill/Irwin 23 © The Mc. Graw-Hill Companies, Inc. , 2003

Some Important Options • Click on the “Options” button, and click in both the “Assume Linear Model” and the “Assume Non-Negative” box. – “Assume Linear Model” tells the Solver that this is a linear programming model. – “Assume Non-Negative” adds nonnegativity constraints to all the changing cells. Mc. Graw-Hill/Irwin 24 © The Mc. Graw-Hill Companies, Inc. , 2003

The Solver Results Dialogue Box Mc. Graw-Hill/Irwin 25 © The Mc. Graw-Hill Companies, Inc. , 2003

The Optimal Solution Mc. Graw-Hill/Irwin 26 © The Mc. Graw-Hill Companies, Inc. , 2003

The Profit & Gambit Co. • Management has decided to undertake a major advertising campaign that will focus on the following three key products: – A spray prewash stain remover. – A liquid laundry detergent. – A powder laundry detergent. • The campaign will use both television and print media • The general goal is to increase sales of these products. • Management has set the following goals for the campaign: – Sales of the stain remover should increase by at least 3%. – Sales of the liquid detergent should increase by at least 18%. – Sales of the powder detergent should increase by at least 4%. Question: how much should they advertise in each medium to meet the sales goals at a minimum total cost? Mc. Graw-Hill/Irwin 27 © The Mc. Graw-Hill Companies, Inc. , 2003

Profit & Gambit Co. Spreadsheet Model Mc. Graw-Hill/Irwin 28 © The Mc. Graw-Hill Companies, Inc. , 2003

Algebraic Model for Profit & Gambit Let TV = the number of units of advertising on television PM = the number of units of advertising in the print media Minimize Cost = TV + 2 PM (in millions of dollars) subject to Stain remover increased sales: PM ≥ 3 Liquid detergent increased sales: 3 TV + 2 PM ≥ 18 Powder detergent increased sales: –TV + 4 PM ≥ 4 and TV ≥ 0, PM ≥ 0. Mc. Graw-Hill/Irwin 29 © The Mc. Graw-Hill Companies, Inc. , 2003

Applying the Graphical Method Mc. Graw-Hill/Irwin 30 © The Mc. Graw-Hill Companies, Inc. , 2003

The Optimal Solution Mc. Graw-Hill/Irwin 31 © The Mc. Graw-Hill Companies, Inc. , 2003

Summary of the Graphical Method • Draw the constraint boundary line for each constraint. Use the origin (or any point not on the line) to determine which side of the line is permitted by the constraint. • Find the feasible region by determining where all constraints are satisfied simultaneously. • Determine the slope of one objective function line. All other objective function lines will have the same slope. • Move a straight edge with this slope through the feasible region in the direction of improving values of the objective function. Stop at the last instant that the straight edge still passes through a point in the feasible region. This line given by the straight edge is the optimal objective function line. • A feasible point on the optimal objective function line is an optimal solution. Mc. Graw-Hill/Irwin 32 © The Mc. Graw-Hill Companies, Inc. , 2003

A Production Problem Weekly supply of raw materials: 8 Small Bricks 6 Large Bricks Products: Table Profit = $20 / Table Mc. Graw-Hill/Irwin Chair Profit = $15 / Chair 33 © The Mc. Graw-Hill Companies, Inc. , 2003

Linear Programming • Linear programming uses a mathematical model to find the best allocation of scarce resources to various activities so as to maximize profit or minimize cost. Let T = Number of tables to produce C = Number of chairs to produce Maximize Profit = ($20)T + ($15)C subject to 2 T + C ≤ 6 large bricks 2 T + 2 C ≤ 8 small bricks and T ≥ 0, C ≥ 0. Mc. Graw-Hill/Irwin 34 © The Mc. Graw-Hill Companies, Inc. , 2003

Graphical Representation Mc. Graw-Hill/Irwin 35 © The Mc. Graw-Hill Companies, Inc. , 2003

Components of a Linear Program • Data Cells • Changing Cells (“Decision Variables”) • Target Cell (“Objective Function”) • Constraints Mc. Graw-Hill/Irwin 36 © The Mc. Graw-Hill Companies, Inc. , 2003

Four Assumptions of Linear Programming • Linearity • Divisibility • Certainty • Nonnegativity Mc. Graw-Hill/Irwin 37 © The Mc. Graw-Hill Companies, Inc. , 2003

When is a Spreadsheet Model Linear? • All equations (output cells) must be of the form = ax + by + cz + … where a, b, c are constants (data cells) and x, y, z are changing cells. • Suppose C 1: C 6 are changing cells and D 1: D 6 are data cells. Which of the following can be part of an LP? – – – – – SUMPRODUCT(D 1: D 6, C 1: C 6) SUM(C 1: C 6) C 1 * SUM(C 4: C 6) SUMPRODUCT(C 1: C 3, C 4: C 6) IF(C 1 > 3, 2*C 3 + C 4, 3*C 3 + C 5) IF(D 1 > 3, C 1, C 2) MIN(D 1, D 2) * C 1 ROUND(C 1) Mc. Graw-Hill/Irwin 38 © The Mc. Graw-Hill Companies, Inc. , 2003

Why Use Linear Programming? • Linear programs are easy (efficient) to solve • The best (optimal) solution is guaranteed to be found (if it exists) • Useful sensitivity analysis information is generated • Many problems are essentially linear Mc. Graw-Hill/Irwin 39 © The Mc. Graw-Hill Companies, Inc. , 2003

Developing a Spreadsheet Model • Step #1: Data Cells – Enter all of the data for the problem on the spreadsheet. – Make consistent use of rows and columns. – It is a good idea to color code these “data cells” (e. g. , light blue). Mc. Graw-Hill/Irwin 40 © The Mc. Graw-Hill Companies, Inc. , 2003

Developing a Spreadsheet Model • Step #2: Changing Cells – Add a cell in the spreadsheet for every decision that needs to be made. – If you don’t have any particular initial values, just enter 0 in each. – It is a good idea to color code these “changing cells” (e. g. , yellow with border). Mc. Graw-Hill/Irwin 41 © The Mc. Graw-Hill Companies, Inc. , 2003

Developing a Spreadsheet Model • Step #3: Target Cell – Develop an equation that defines the objective of the model. – Typically this equation involves the data cells and the changing cells in order to determine a quantity of interest (e. g. , total profit or total cost). – It is a good idea to color code this cell (e. g. , orange with heavy border). Mc. Graw-Hill/Irwin 42 © The Mc. Graw-Hill Companies, Inc. , 2003

Developing a Spreadsheet Model • Step #4: Constraints – For any resource that is restricted, calculate the amount of that resource used in a cell on the spreadsheet (an output cell). – Define the constraint in three consecutive cells. For example, if Quantity A ≤ Quantity B, put these three items (Quantity A, ≤, Quantity B) in consecutive cells. – Note the use of relative and absolute addressing to make it easy to copy formulas in column E. Mc. Graw-Hill/Irwin 43 © The Mc. Graw-Hill Companies, Inc. , 2003

Defining the Target Cell • • • Choose the “Solver” from the Tools menu. Select the cell you wish to optimize in the “Set Target Cell” window. Choose “Max” or “Min” depending on whether you want to maximize or minimize the target cell. Mc. Graw-Hill/Irwin 44 © The Mc. Graw-Hill Companies, Inc. , 2003

Identifying the Changing Cells • Enter all the changing cells in the “By Changing Cells” window. – You may either drag the cursor across the cells or type the addresses. – If there are multiple sets of changing cells, separate them by typing a comma. Mc. Graw-Hill/Irwin 45 © The Mc. Graw-Hill Companies, Inc. , 2003

Adding Constraints • • To begin entering constraints, click the “Add” button to the right of the constraints window. Fill in the entries in the resulting Add Constraint dialogue box. Mc. Graw-Hill/Irwin 46 © The Mc. Graw-Hill Companies, Inc. , 2003

Some Important Options • Click on the “Options” button, and click in both the “Assume Linear Model” and the “Assume Non-Negative” box. – “Assume Linear Model” tells the Solver that this is a linear programming model. – “Assume Non-Negative” adds nonnegativity constraints to all the changing cells. Mc. Graw-Hill/Irwin 47 © The Mc. Graw-Hill Companies, Inc. , 2003

The Solution • After clicking “Solve”, you will receive one of four messages: – “Solver found a solution. All constraints and optimality conditions are satisfied. ” – “Set cell values did not converge. ” – “Solver could not find a feasible solution. ” – “Conditions for Assume Linear Model are not satisfied. ” Mc. Graw-Hill/Irwin 48 © The Mc. Graw-Hill Companies, Inc. , 2003

The Graphical Method for Solving LP’s • Formulate the problem as a linear program • Plot the constraints • Identify the feasible region • Draw an imaginary line parallel to the objective function (Z = a) • Find the optimal solution Mc. Graw-Hill/Irwin 49 © The Mc. Graw-Hill Companies, Inc. , 2003

Example #1 Maximize Z = 3 x 1 + 5 x 2 subject to x 1 ≤ 4 2 x 2 ≤ 12 3 x 1 + 2 x 2 ≤ 18 and x 1 ≥ 0, x 2 ≥ 0. Mc. Graw-Hill/Irwin 50 © The Mc. Graw-Hill Companies, Inc. , 2003

Example #2 Minimize Z = 15 x 1 + 20 x 2 subject to x 1 +2 x 2 ≥ 10 2 x 1 – 3 x 2 ≤ 6 x 1 + x 2 ≥ 6 and x 1 ≥ 0, x 2 ≥ 0. Mc. Graw-Hill/Irwin 51 © The Mc. Graw-Hill Companies, Inc. , 2003

Example #3 Maximize Z = x 1 + x 2 subject to x 1 +2 x 2 = 8 x 1 – x 2 ≤ 0 and x 1 ≥ 0, x 2 ≥ 0. Mc. Graw-Hill/Irwin 52 © The Mc. Graw-Hill Companies, Inc. , 2003

Properties of Linear Programming Solutions • An optimal solution must lie on the boundary of the feasible region. • There are exactly four possible outcomes of linear programming: – – A unique optimal solution is found. An infinite number of optimal solutions exist. No feasible solutions exist. The objective function is unbounded (there is no optimal solution). • If an LP model has one optimal solution, it must be at a corner point. • If an LP model has many optimal solutions, at least two of these optimal solutions are at corner points. Mc. Graw-Hill/Irwin 53 © The Mc. Graw-Hill Companies, Inc. , 2003

Example #4 (Multiple Optimal Solutions) Minimize Z = 6 x 1 + 4 x 2 subject to x 1 ≤ 4 2 x 2 ≤ 12 3 x 1 + 2 x 2 ≤ 18 and x 1 ≥ 0, x 2 ≥ 0. Mc. Graw-Hill/Irwin 54 © The Mc. Graw-Hill Companies, Inc. , 2003

Example #5 (No Feasible Solution) Maximize Z = 3 x 1 + 5 x 2 subject to x 1 ≥ 5 x 2 ≥ 4 3 x 1 + 2 x 2 ≤ 18 and x 1 ≥ 0, x 2 ≥ 0. Mc. Graw-Hill/Irwin 55 © The Mc. Graw-Hill Companies, Inc. , 2003

Example #6 (Unbounded Solution) Maximize Z = 5 x 1 + 12 x 2 subject to x 1 ≤ 5 2 x 1 –x 2 ≤ 2 and x 1 ≥ 0, x 2 ≥ 0. Mc. Graw-Hill/Irwin 56 © The Mc. Graw-Hill Companies, Inc. , 2003

The Simplex Method Algorithm 1. Start at a feasible corner point (often the origin). 2. Check if adjacent corner points improve the objective function: a) If so, move to adjacent corner and repeat step 2. b) If not, current corner point is optimal. Stop. Mc. Graw-Hill/Irwin 57 © The Mc. Graw-Hill Companies, Inc. , 2003