Lecture 2 Modeling and Solving LP Problems in a Spreadsheet (1).pptx

brief

1. Modeling and Solving LP Problems in a Spreadsheet Prescriptive Analytics Jindal School of Management

2. Any questions about Assignment 1 How do you feel about trying to solve optimal solution graphically?

3. Introduction Solving LP problems graphically is only possible when there are two decision variables Few real-world LP have only two decision variables. Fortunately, we can use spreadsheets (through the Analytic Solver) to solve LP problems

4. Excel’s Analytic Solver We will be using Analytic Solver to solve linear programming problems Access the Solver from Excel’s Data tool bar menu. Installation instruction is uploaded on eLearning.

5. Solver for MAC In the instruction, you should also be able to install the Analytic Solver for MAC

6. Steps in Implementing an LP model in a spreadsheet Organize the data for the model in the spreadsheet Reserve separate cells in the spreadsheet for each decision variable in the model. Create a formula in a cell in the spreadsheet that corresponds to the objective function. For each constraint, create a formula in a separate cell in the spreadsheet that correspond to the left-hand side (LHS) of the constraint.

7. Blue Ridge Hot Tubs Example MAX: S.T.:  

8. Using Analytic Solver Two steps to solve any type of a linear programming problem: Step 1: Set the problem up in a spreadsheet Step 2: Invoke Solver to enter all pertinent parameters

9. Spreadsheet Setup for Hot Tubs Example Fig3-1.xlsm

10. Discussion: This model requires non-negativity. It is intuitive that we cannot produce a negative number of hot tubs, is the non-negativity redundant? Since it is more profitable to create X1, if we did not have this constraint, the computer would like to deconstruct every X2 hot tub and use the materials to create more X1. The computer may not account for non-negativity, the main goal is to maximize profit.

11. How Solver views the model Objective cell – the cell in the spreadsheet that represents the objective function Variable cell – the cells in the spreadsheet representing the decision variables. Constraint cells – the cells in the spreadsheet representing the LHS formulas on the constraints

12. Goals for Spreadsheet Design Communication – A spreadsheet’s primary business purpose is communicating information to managers. Reliability – The output a spreadsheet generates should be correct and consistent. Auditability – A manager should be able to retrace the steps followed to generate the different outputs from the model in order to understand and verify results. Modifiability – A well-designed spreadsheet should be easy to change or enhance in order to meet dynamic user requirements.

13. Spreadsheet Design Guidelines part 1 Organize the data, then build the model around the data Do not embedded numeric constants in formulas Why? Numeric constraints should be placed in individual cells and labeled properly Things which are logically related should be physically related. Use formula that can be copied. Column/rows totals should be close to the columns/rows being totaled.

14. Spreadsheet Design Guidelines part 2 Use color, shading, borders and protection to distinguish changeable parameters from other model elements. Use text boxes and cell notes to document various elements of the model.

15. Solver (continue) Do not forget to always specify the linearity and non-negativity assumptions in Solver This can be accomplished by checking the check boxes Assume Non-Negativity and Assume Linear Model in the Solver Options dialog box.

16. Make vs. Buy Decisions: The Electro-Poly Corporation Electro-Poly is a leading maker of slip-rings. A $750,000 order has just been received. The company has 10,000 hours of wiring capacity and 5,000 hours of harnessing capacity. Model 1 Model 2 Model 3 Number ordered 3,000 2,000 900 Hours of wiring/unit 2 1.5 3 Hours of harnessing/unit 1 2 2 Cost to make $50 $83 $130 Cost to buy $61 $97 $145

17. Defining the decision variables = Number of model 1 slip rings to make in-house = Number of model 2 slip rings to make in-house = Number of model 3 slip rings to make in-house = Number of model 1 slip rings to buy from competitor = Number of model 2 slip rings to buy from competitor = Number of model 3 slip rings to buy from competitor  

18. Defining the objective function Minimize the total cost of filling the order. MIN:  

19. Defining the constraints Demand constraints Resource constraints Nonnegativity conditions  

20. Implementing the Electro-Ploy model Fig3-19.xlsm

21. An investment problem: Retirement Planning Services, Inc. A client wishes to invest $750,000 in the following bonds. Company Return Years to maturity Rating Acme Chemical 8.65% 11 1-Excellent DynaStar 9.50% 10 3-Good Eagle Vision 10.00% 6 4-Fair Micro Modeling 8.75% 10 1-Excellent OptiPro 9.25% 7 3-Good Sabre Systems 9.00% 13 2-Very Good

22. Investment Restrictions No more than 25% can be invested in any single company. At least 50% should be invested in long-term bonds (maturing in 10+ years). No more than 35% can be invested in DynaStar , Eagle Vision, and OptiPro .

23. Defining the decision variables = amount of money to invest in Acme Chemical = amount of money to invest in DynaStar = amount of money to invest in Eagle Vision = amount of money to invest in MicroModeling = amount of money to invest in OptiPro = amount of money to invest in Sabre Systems  

24. Defining the objective function Maximize the total annual investment return:  

25. Defining the constraints Total amount invested No more than 25% in any one investment 50% long term investment restriction 35% Restriction on DynaStar , Eagle Vision, and OptiPro Nonnegativity conditions  

26. Implementing the model Fig3-22.xlsm

27. Practice question One of the goals for spreadsheet design is that the output a spreadsheet generates should be correct and consistent. This is: Auditability Reliability Accuracy Consistency

28. A Transportation Problem: Tropicsun

29. Heuristic Approach Find the shortest route

30. Defining the decision variables = # of bushels shipped from node to node Specifically, the nine decision variables are: = # of bushels shipped from Mt. Dora (node ) to Ocala (node ) = # of bushels shipped from Mt. Dora (node ) to Orlando (node ) = # of bushels shipped from Mt. Dora (node ) to Leesburg (node ) = # of bushels shipped from Eutis (node ) to Ocala (node ) = # of bushels shipped from Eutis (node ) to Orlando (node ) = # of bushels shipped from Eutis (node ) to Leesburg (node ) = # of bushels shipped from Clermont (node ) to Ocala (node ) = # of bushels shipped from Clermont (node ) to Orlando (node ) = # of bushels shipped from Clermont (node ) to Leesburg (node )  

31. Defining the objective function Minimize the total number of bushel-miles MIN:  

32. Defining the constraints Capacity constraints Supply constraints Nonnegativity conditions  

33. Implementing the Tropicsun model Fig3-26.xlsm

34. Transportation problem (continue) General guidelines in formulating the transportation problem: When total supply = total demand , all supply constraints will have equality signs and all demand constraints will have equality signs. When total supply < total demand , all demand constraints will have “ ” signs and all supply constraints will have “ ” signs. When total supply > total demand , all supply constraints will have “ ” signs and all demand constraints will have “ ” signs.  

35. Blending Problem: Product Mix

36. A Blending Problem: The Agri-Pro Company Agri-Pro has received an order for 8,000 pounds of chicken feed to be mixed from the following feeds. Percent of Nutrient in Nutrient Feed 1 Feed 2 Feed 3 Feed 4 Corn 30% 5% 20% 10% Grain 10% 30% 15% 10% Minerals 20% 20% 20% 30% Cost per pound $0.25 $0.30 $0.32 $0.15 The order must contain at least 20% corn, 15% grain, and 15% minerals.

37. Defining the Decision Variables: Agri-Pro X 1 = pounds of feed 1 to use in the mix X 2 = pounds of feed 2 to use in the mix X 3 = pounds of feed 3 to use in the mix X 4 = pounds of feed 4 to use in the mix

38. Defining the Objective Function: Agri-Pro Minimize the total cost of filling the order. MIN: 0.25X 1 + 0.30X 2 + 0.32X 3 + 0.15X 4

39. Defining the Constraints: Agri-Pro Produce 8,000 pounds of feed X 1 + X 2 + X 3 + X 4 = 8,000 Mix consists of at least 20% corn (0.3X 1 + 0.05X 2 + 0.2X 3 + 0.1X 4 )/8000 >= 0.2 Mix consists of at least 15% grain (0.1X 1 + 0.3X 2 + 0.15X 3 + 0.1X 4 )/8000 >= 0.15 Mix consists of at least 15% minerals (0.2X 1 + 0.2X 2 + 0.2X 3 + 0.3X 4 )/8000 >= 0.15 Nonnegativity conditions X 1 , X 2 , X 3 , X 4 >= 0

40. A Comment about Scaling Notice the coefficient for X 2 in the ‘corn’ constraint is 0.05/8000 = 0.00000625 As Solver runs, intermediate calculations are made that make coefficients larger or smaller. Storage problems may force the computer to use approximations of the actual numbers. Such ‘scaling’ problems sometimes prevents Solver from being able to solve the problem accurately. Most problems can be formulated in a way to minimize scaling errors...

41. Re-Defining the Decision Variables X 1 = thousands of pounds of feed 1 to use in the mix X 2 = thousands of pounds of feed 2 to use in the mix X 3 = thousands of pounds of feed 3 to use in the mix X 4 = thousands of pounds of feed 4 to use in the mix

42. Re-Defining the Objective Function Minimize the total cost of filling the order. MIN: 250X 1 + 300X 2 + 320X 3 + 150X 4

43. Re-Defining the Constraints Produce 8,000 pounds of feed X 1 + X 2 + X 3 + X 4 = 8 Mix consists of at least 20% corn (0.3X 1 + 0.05X 2 + 0.2X 3 + 0.1X 4 )/8 >= 0.2 Mix consists of at least 15% grain (0.1X 1 + 0.3X 2 + 0.15X 3 + 0.1X 4 )/8 >= 0.15 Mix consists of at least 15% minerals (0.2X 1 + 0.2X 2 + 0.2X 3 + 0.3X 4 )/8 >= 0.15 Nonnegativity conditions X 1 , X 2 , X 3 , X 4 >= 0

44. Scaling: Before and After Before Largest constraint coefficient was 8,000 Smallest constraint coefficient was 0.05/8 = 0.00000625. After Largest constraint coefficient is 8 Smallest constraint coefficient is 0.05/8 = 0.00625. The problem is now more evenly scaled!

45. Implementing the Agri-Pro Model Fig3-30.xlsm

46. Production Planning Problem

47. A Production Planning Problem: The Upton Corporation Upton is planning the production of their heavy-duty air compressors for the next 6 months. Month 1 2 3 4 5 6 Unit Production Cost $240 $250 $265 $285 $280 $260 Units Demanded 1,000 4,500 6,000 5,500 3,500 4,000 Maximum Production 4,000 3,500 4,000 4,500 4,000 3,500 Minimum Production 2,000 1,750 2,000 2,250 2,000 1,750 Beginning inventory = 2,750 units Safety stock = 1,500 units Unit carrying cost = 1.5% of unit production cost Maximum warehouse capacity = 6,000 units

48. Defining the Decision Variables: The Upton Corporation P i = number of units to produce in month i , i =1 to 6 B i = beginning inventory month i , i =1 to 6

49. Defining the Objective Function: The Upton Corporation Minimize the total cost production & inventory costs. MIN: 240P 1 +250P 2 +265P 3 +285P 4 +280P 5 +260P 6 + 3.6(B 1 +B 2 )/2 + 3.75(B 2 +B 3 )/2 + 3.98(B 3 +B 4 )/2 + 4.28(B 4 +B 5 )/2 + 4.20(B 5 + B 6 )/2 + 3.9(B 6 +B 7 )/2 Note: The beginning inventory in any month is the same as the ending inventory in the previous month.

50. Defining the Upton Constraints (1 of 3) Production levels 2,000 <= P 1 <= 4,000 } month 1 1,750 <= P 2 <= 3,500 } month 2 2,000 <= P 3 <= 4,000 } month 3 2,250 <= P 4 <= 4,500 } month 4 2,000 <= P 5 <= 4,000 } month 5 1,750 <= P 6 <= 3,500 } month 6

51. Defining the Upton Constraints (2 of 3) Ending Inventory (EI = BI + P − D) 1,500 < B 1 + P 1 − 1,000 < 6,000 } month 1 1,500 < B 2 + P 2 − 4,500 < 6,000 } month 2 1,500 < B 3 + P 3 − 6,000 < 6,000 } month 3 1,500 < B 4 + P 4 − 5,500 < 6,000 } month 4 1,500 < B 5 + P 5 − 3,500 < 6,000 } month 5 1,500 < B 6 + P 6 − 4,000 < 6,000 } month 6

52. Defining the Upton Constraints (3 of 3) Beginning Balances B 1 = 2750 B 2 = B 1 + P 1 − 1,000 B 3 = B 2 + P 2 − 4,500 B 4 = B 3 + P 3 − 6,000 B 5 = B 4 + P 4 − 5,500 B 6 = B 5 + P 5 − 3,500 B 7 = B 6 + P 6 − 4,000 Notice that the B i can be computed directly from the P i . Therefore, only the P i need to be identified as changing cells.

53. Implementing the Upton Model Fig3-33.xlsm