Ardavan Asef-Vaziri Systems and Operations Management

1. Introduction to Linear Programming Ardavan Asef-Vaziri Systems and Operations Management

2. Goals, Aims, and Requirements Goals and aims To introduce Linear Programming To find a knowledge on graphical solution for LP problems To solve linear programming problems using excel.

3. The Lego Production Problem You have a set of logos 8 small bricks 6 large bricks These are your “raw materials”. You have to produce tables and chairs out of these logos. These are your “products”.

4. The Lego Production Problem Weekly supply of raw materials: 8 Small Bricks 6 Large Bricks Products: Table Chair Profit = $20/Table Profit = $15/Chair

5. Problem Formulation X1 is the number of Chairs X2 is the number of Tables Large brick constraint X1+2X2 <= 6 Small brick constraint 2X1+2X2 <= 8 Objective function is to Maximize 15X1+20 X2 X1>=0 X2>= 0

6. Graphical Solution to the Prototype Problem Tables 5 4 3 2 X1 + 2 X2 = 6 Large Bricks 1 0 Chairs 1 2 3 4 5 6

7. Graphical Solution to the Prototype Problem Tables 5 4 2 X1 + 2 X2 = 8 Small Bricks 3 2 1 0 Chairs 1 2 3 4 5 6

8. Graphical Solution to the Prototype Problem Tables 5 4 2 X1 + 2 X2 = 8 Small Bricks 3 2 X1 + 2 X2 = 6 Large Bricks 1 0 Chairs 1 2 3 4 5 6

9. Graphical Solution to the Prototype Problem Tables 5 4 3 X1 + 2 X2 = 6 Large Bricks 2 2 X1 + 2 X2 = 8 Small Bricks 1 0 Chairs 1 2 3 4 5 6

10. The Objective Function Z = 15 X1 + 20 X2 Lets draw it for 15 X1 + 20 X2 = 30 In this case if # of chair = 0, then # of table = 30/20 = 1.5 if # of table = 0, then # of chair = 30/15 = 2

11. Graphical Solution to the Prototype Problem Tables 5 4 3 X1 + 2 X2 = 6 Large Bricks 2 2 X1 + 2 X2 = 8 Small Bricks 1 0 Chairs 1 2 3 4 5 6

12. A second example • We can make Product1 and or Product2. • There are 3 resources; Resource1, Resource2, Resource3. • Product1 needs one unit of Resource1, nothing of Resource2, and three units of resource3. • Product2 needs nothing from Resource1, two units of Resource2, and two units of resource3. • Available amount of resources 1, 2, 3 are 4, 12, 18, respectively. • Net profit of product 1 and Product2 are 3 and 5, respectively. • Formulate the Problem • Solve it graphically • Solve it using excel.

13. Problem 2 Product 1 needs 1 hour of Plant 1, and 3 hours of Plant 3. Product 2 needs 2 hours of plant 2 and 2 hours of plant 3 There are 4 hours available in plant 1, 12 hours in plant 2, and 18 hours in plant 3 Objective Function Z = 3 x1 +5 x2 Constraints Resource 1 x1 4 Resource 2 2x2  12 Resource 3 3 x1 + 2 x2  18 Nonnegativity x1  0, x2  0

14. Problem 2 : Original version x 2 10 9 8 7 6 5 4 3 2 1 x 1 1 2 3 4 5 6 7 8 9 10 Max Z = 3x1 + 5x2 Subject to x1 4 2x2  12 3 x1 + 2 x2  18 x1  0, x2  0

15. Problem 2 x 2 10 9 8 7 6 5 4 3 2 1 x 1 1 2 3 4 5 6 7 8 9 10 Max Z = 3x1 + 5x2 Subject to x1 4 2x2  12 3 x1 + 2 x2  18 x1  0, x2  0

16. Implementing LP Models in Excel • Start by Organizing the data for the model on the spreadsheet. Type in the coefficients of the constraints and the objective function • For each constraint, create a formula in a separate cell that corresponds to the left-hand side (LHS) of the constraint. • Assign a set of cells to represent the decision variable in the model. • Create a formula in a cell that corresponds to the objective function.

17. Constraint LHS, Variables, Objective Function • Constraint cells - the cells in the spreadsheet representing the LHS formulas on the constraints • Changing cells - the cells in the spreadsheet representing the decision variables • Target cell - the cell in the spreadsheet that represents the objective function

18. Solving LP Models with Excel • Enter the input data and construct relationships among data elements in a readable, easy to understand way. • Make sure there is a cell in your spreadsheet for each of the following: • every quantity that you might want to constraint (include both sides of the constraint) • every decision variable • the quantity you wish to maximize or minimize • Usually we don’t have any particular initial values for the decision variables. • The problem starts with assuming a value of 0 in each decision variable cell.

19. Wyndor Example Product 1 needs 1 hour of Plant 1, and 3 hours of Plant 3. Product 2 needs 2 hours of plant 2 and 2 hours of plant 3 There are 4 hours available in plant 1, 12 hours in plant 2, and 18 hours in plant 3 Z = 3 x1 +5 x2 x1 4 2x2  12 3 x1 + 2 x2  18 x1  0, x2  0 Go to EXCEL, solve this problem in EXCEL first

20. Wyndor Example; Enter data

21. Noncomputational Entries

22. Sumproduct SUMPRODUCT function is used to multiply element by element of two tables and addup all values. In EXCELterminology, SUMPRODUCT sums the products of individual cells in two ranges. For example, SUMPRODUCT(C6:D6, C4:D4) sums the products C6*C4 plus D6*D4. The two specified ranges must be of the same size ( the same number of rows and columns). For linear programming you should try to always use the SUMPRODUCT function (or SUM) for the objective function and constraints. This is to remember that the equations are all linear.

23. Solving LP Models with Excel

24. Solving LP Models with Excel

25. Solving LP Models with Excel

26. Solving LP Models with Excel

27. Solving LP Models with Excel

28. Designing the Target Cell ( Objective Function)

29. Defining the Target Cell ( The Objective Function) You have already defined the target cell. It contains an equation that defines the objective and depends on the decision variables. You can ONLY have one objective function, therefore the target cell must be a single cell. In the Solver dialogue boxselect the “SetTarget Cell” window, then click on the cell that you have already defined it as the objective function. This is the cell you wish to optimize. Then lick on the radio button of either “Max” or “Min” depending on whether the objective is to maximize or minimize the target cell.

30. Designing the Target Cell ( Objective Function)

31. Identifying the Changing Cells ( Decision Variables) You next tell Excel which cells are decision variables, i.e., which cells Excel is allowed to change when trying to optimize. Move the cursor to the “By Changing Cells” window, and drag the cursor across all cells you wish to treat as decision variables

32. Identifying the Changing Cells ( Decision Variables)

33. Dragging with non-adjacent cells • If the decision variables do not all lie in a connected rectangle in the spreadsheet, then • Drag the cursor across one group of decision variables. • Ctrl after that group in the “By Changing Cells” window. • Drag the cursor across the next group of decision variables. • etc....

34. Adding Constraints Click on the “Add” button to the right of the constraints window. A new dialogue box will appear. The cursor will be in the “Cell Reference” window within this dialogue box. Click on the cell that contains the quantity you want to constrain. The default inequality that first appears for a constraint is “<= ”. To change this, click on the arrow beside the “<= ” sign. After setting the inequality, move the cursor to the “Constraint” window. Click on the cell you want to use as the constraining value for that constraint.

35. Adding Constraints

36. Adding Constraints

37. Adding Constraints

38. Adding Constraints • You may define a set of similar constraints (e.g., all <= constraints, or all >= constraints) in one step if they are in adjacent rows. • Simply select the range of cells for the set of constraints in both the “Cell Reference” and “Constraint” window. • After you are satisfied with the constraint(s), • click the “Add” button if you want to add another constraint, or • click the “OK” button if you want to go back to the original dialogue box. • Notice that you may also force a decision variable to be an integer or binary (i.e., either 0 or 1) using this window.

39. Some Important Options The Solver dialogue box now contains the optimization model, including the target cell (objective function), changing cells (decision variables), and constraints.

40. Some Important Options There is one important step. Click on the “Options” button in the Solver dialogue box, and click in both the “Assume Linear Model” and the “Assume Non-Negative” box. The “Assume Linear Model” option tells Excel that it is a linear program. This speeds the solution process, makes it more accurate, and enables the more informative sensitivity report. The “Assume Non-Negative” box adds non-negativity constraints to all of the decision variables.

41. Some Important Options

42. The Solution After setting up the model, and selecting the appropriate options, it is time to click “Solve”.

44. The Solution When it is done, you will receive one of four messages: Solver found a solution. All constraints and optimality conditions are satisfied. This means that Solver has found the optimal solution. Cell values did not converge. This means that the objective function can be improved to infinity. You may have forgotten a constraint (perhaps the non-negativity constraints) or made a mistake in a formula. Solver could not find a feasible solution. This means that Solver could not find a feasible solution to the constraints you entered. You may have made a mistake in typing the constraints or in entering a formula in your spreadsheet. Conditions for Assume Linear Model not satisfied. You may have included a formula in your model that is nonlinear. There is also a slim chance that Solver has made an error. (This bug shows up occasionally.)

45. The Solution If Solver finds an optimal solution, you have some options. First, you must choose whether you want Solver to keep the optimal values in the spreadsheet (you usually want this one) or go back to the original numbers you typed in. Click the appropriate box to make you selection. you also get to choose what kind of reports you want. Once you have made your selections, click on “OK”. You will often want to also have the “Sensitivity Report”. To view the sensitivity report, click on the “Sensitivity Report” tab in the lower-left-hand corner of the window.

46. Sensitivity Analysis Using Excel Ardavan Asef-Vaziri Systems and Operations Management

47. Terminology • Binding (or Active) Constraints • Non-Binding (or Inactive) Constraints • Redundant Constraints • Slack/Surplus • Tightening a Constraint • Loosening a Constraint

48. The Objective Function of the Prototype Problem 0

49. Solve the Problem using Solver

50. Questions Answered by Excel • What is the optimal solution? • What is the profit ( value of the O.F.) for the optimal solution? • If the net profit per table changes, will the solution change? • If the net profit per chair changes, will the solution change? • If more (or less) large bricks are available, how will this affect • our profit? • If more (or less) small bricks are available, how will this affect • our profit?