1 / 71

Modeling and Solving LP Problems in a Spreadsheet

3-2. Introduction. Solving LP problems graphically is only possible when there are two decision variablesFew real-world LP have only two decision variablesFortunately, we can now use spreadsheets to solve LP problems. 3-3. Spreadsheet Solvers. The company that makes the Solver in Excel, Lotus 1-2-

ninon
Download Presentation

Modeling and Solving LP Problems in a Spreadsheet

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. 3-1 Modeling and Solving LP Problems in a Spreadsheet

    2. 3-2 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 now use spreadsheets to solve LP problems

    3. 3-3 Spreadsheet Solvers The company that makes the Solver in Excel, Lotus 1-2-3, and Quattro Pro is Frontline Systems, Inc. Check out their web site: http://www.solver.com Make sure to install Premium Solver (steps on page 53 in the textbook). Other packages for solving MP problems: AMPL XPRESS CPLEX MPSX For large-scale applications (more than several hundred variables and constraints), using Solver through Excel is not a viable option Inefficient (too slow) Custom algorithms may be necessary (in particular, for non-linear models or models with integer decision variables)

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

    5. 3-5 Let’s Implement a Model for the Blue Ridge Hot Tubs Example...

    6. 3-6 Implementing the Model Let’s start with a blank spreadsheet and implement the model … (Completed spreadsheet is in Fig3-1.xls)

    7. 3-7 How Solver Views the Model Target cell - the cell in the spreadsheet that represents the objective function Changing cells - the cells in the spreadsheet representing the decision variables Constraint cells - the cells in the spreadsheet representing the LHS formulae for the constraints

    8. 3-8 Let’s go back to Excel and go through the steps for using Solver (follow Section 3.6 in book) ...

    9. 3-9 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.

    10. 3-10 Spreadsheet Design Guidelines - I Implement a clear logical layout to the overall model. Organize the data, then build the model around the data. Do not embed numeric constants in formulas. Things which are logically related should be physically related. Group changing cells together Group constraints, of different types, together

    11. 3-11 Spreadsheet Design Guidelines - II Use formulas that can be copied. Column/rows totals should be close to the columns/rows being totaled. The English-reading eye scans left to right, top to bottom. Use color, shading, borders and protection to distinguish changeable parameters from other model elements. Book follows following font color standard that you may want to follow: Decision variables (changing cells) are green surrounded by a dotted box Constraint left-hand-sides are red surrounded by a box Objective function (set cell) is blue surrounded by a double line box Use text boxes and cell notes to document various elements of the model.

    12. 3-12 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.

    13. 3-13 Defining the Decision Variables

    14. 3-14 Defining the Objective Function

    15. 3-15 Defining the Constraints Demand Constraints M1 + B1 = 3,000 } model 1 M2 + B2 = 2,000 } model 2 M3 + B3 = 900 } model 3 Resource Constraints 2M1 + 1.5M2 + 3M3 <= 10,000 } wiring 1M1 + 2.0M2 + 1M3 <= 5,000 } harnessing Nonnegativity Conditions M1, M2, M3, B1, B2, B3 >= 0

    16. 3-16 Implementing the Model Spreadsheet Fig3-17.xls

    17. 3-17 An Investment Problem: Retirement Planning Services, Inc. A client wishes to invest $750,000 in the following bonds.

    18. 3-18 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.

    19. 3-19 Defining the Decision Variables

    20. 3-20 Defining the Objective Function

    21. 3-21 Defining the Constraints Total amount is invested X1 + X2 + X3 + X4 + X5 + X6 = 750,000 No more than 25% in any one investment Xi <= 187,500, for all i 50% long term investment restriction. X1 + X2 + X4 + X6 >= 375,000 35% Restriction on DynaStar, Eagle Vision, and OptiPro. X2 + X3 + X5 <= 262,500 Nonnegativity conditions Xi >= 0 for all i

    22. 3-22 Implementing the Model See file Fig3-20.xls

    23. 3-23 A Transportation Problem: Tropicsun

    24. 3-24 Defining the Decision Variables

    25. 3-25 Defining the Objective Function

    26. 3-26 Defining the Constraints Capacity constraints X14 + X24 + X34 <= 200,000 } Ocala X15 + X25 + X35 <= 600,000 } Orlando X16 + X26 + X36 <= 225,000 } Leesburg Supply constraints X14 + X15 + X16 = 275,000 } Mt. Dora X24 + X25 + X26 = 400,000 } Eustis X34 + X35 + X36 = 300,000 } Clermont Nonnegativity conditions Xij >= 0 for all i and j

    27. 3-27 Implementing the Model See file Fig3-24.xls

    28. 3-28 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.

    29. 3-29 Defining the Decision Variables

    30. 3-30 Defining the Objective Function

    31. 3-31 Defining the Constraints Produce 8,000 pounds of feed X1 + X2 + X3 + X4 = 8,000 Mix consists of at least 20% corn (0.3X1 + 0.05X2 + 0.2X3 + 0.1X4)/8000 >= 0.2 Mix consists of at least 15% grain (0.1X1 + 0.3X2 + 0.15X3 + 0.1X4)/8000 >= 0.15 Mix consists of at least 15% minerals (0.2X1 + 0.2X2 + 0.2X3 + 0.3X4)/8000 >= 0.15 Nonnegativity conditions X1, X2, X3, X4 >= 0

    32. 3-32 A Comment About Scaling Notice the coefficient for X2 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...

    33. 3-33 Re-Defining the Decision Variables

    34. 3-34 Re-Defining the Objective Function

    35. 3-35 Re-Defining the Constraints Produce 8,000 pounds of feed X1 + X2 + X3 + X4 = 8 Mix consists of at least 20% corn (0.3X1 + 0.5X2 + 0.2X3 + 0.1X4)/8 >= 0.2 Mix consists of at least 15% grain (0.1X1 + 0.3X2 + 0.15X3 + 0.1X4)/8 >= 0.15 Mix consists of at least 15% minerals (0.2X1 + 0.2X2 + 0.2X3 + 0.3X4)/8 >= 0.15 Nonnegativity conditions X1, X2, X3, X4 >= 0

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

    37. 3-37 The Assume Linear Model Option The Solver Options dialog box has an option labeled “Assume Linear Model”. This option makes Solver perform some tests to verify that your model is in fact linear. These test are not 100% accurate & may fail as a result of a poorly scaled model. If Solver tells you a model isn’t linear when you know it is, try solving it again. If that doesn’t work, try re-scaling your model.

    38. 3-38 Implementing the Model See file Fig3-28.xls

    39. 3-39 A Production Planning Problem: The Upton Corporation Upton is planning the production of their heavy-duty air compressors for the next 6 months.

    40. 3-40 Defining the Decision Variables

    41. 3-41 Defining the Objective Function

    42. 3-42 Defining the Constraints - I Production levels 2,000 <= P1 <= 4,000 } month 1 1,750 <= P2 <= 3,500 } month 2 2,000 <= P3 <= 4,000 } month 3 2,250 <= P4 <= 4,500 } month 4 2,000 <= P5 <= 4,000 } month 5 1,750 <= P6 <= 3,500 } month 6

    43. 3-43 Defining the Constraints - II Beginning Balances B1 = 2750 B2 = B1 + P1 - 1,000 B3 = B2 + P2 - 4,500 B4 = B3 + P3 - 6,000 B5 = B4 + P4 - 5,500 B6 = B5 + P5 - 3,500 B7 = B6 + P6 - 4,000

    44. 3-44 Defining the Constraints – III Ending Inventory (EI = BI + P - D) 1,500 < B1 + P1 - 1,000 < 6,000 } month 1 1,500 < B2 + P2 - 4,500 < 6,000 } month 2 1,500 < B3 + P3 - 6,000 < 6,000 } month 3 1,500 < B4 + P4 - 5,500 < 6,000 } month 4 1,500 < B5 + P5 - 3,500 < 6,000 } month 5 1,500 < B6 + P6 - 4,000 < 6,000 } month 6

    45. 3-45 Implementing the Model Let’s implement the model from scratch. Also, see file Fig3-31.xls for an alternative implementation.

    46. 3-46 A Multi-Period Cash Flow Problem: The Taco-Viva Sinking Fund - I Taco-Viva needs a sinking fund to pay $800,000 in building costs for a new restaurant in the next 6 months. Payments of $250,000 are due at the end of months 2 and 4, and a final payment of $300,000 is due at the end of month 6. The following investments may be used.

    47. 3-47 Summary of Possible Cash Flows

    48. 3-48 Defining the Decision Variables

    49. 3-49 Defining the Objective Function

    50. 3-50 Defining the Constraints Cash Flow Constraints 1.018A1 – 1A2 = 0 } month 2 1.035B1 + 1.018A2 – 1A3 – 1B3 = 250 } month 3 1.058C1 + 1.018A3 – 1A4 – 1C4 = 0 } month 4 1.035B3 + 1.018A4 – 1A5 – 1B5 = 250 } month 5 1.018A5 –1A6 = 0 } month 6 1.11D1 + 1.058C4 + 1.035B5 + 1.018A6 = 300 } month 7 Nonnegativity Conditions Ai, Bi, Ci, Di >= 0, for all i

    51. 3-51 Implementing the Model See file Fig3-35.xls

    52. 3-52 Risk Management: The Taco-Viva Sinking Fund - II Assume the CFO has assigned the following risk ratings to each investment on a scale from 1 to 10 (10 = max risk)

    53. 3-53 Defining the Constraints Risk Constraints

    54. 3-54 An Alternate Version of the Risk Constraints Equivalent Risk Constraints -4A1 – 2B1 + 3C1 + 1D1 < 0 } month 1 -2B1 + 3C1 + 1D1 – 4A2 < 0 } month 2 3C1 + 1D1 – 4A3 – 2B3 < 0 } month 3 1D1 – 2B3 – 4A4 + 3C4 < 0 } month 4 1D1 + 3C4 – 4A5 – 2B5 < 0 } month 5 1D1 + 3C4 – 2B5 – 4A6 < 0 } month 6

    55. 3-55 Implementing the Model See file Fig3-38.xls

    56. 3-56 Data Envelopment Analysis (DEA): Steak & Burger Steak & Burger needs to evaluate the performance (efficiency) of 12 units. Outputs for each unit i (Oij) include measures of: Profit, Customer Satisfaction, and Cleanliness Inputs for each unit i (Iij) include: Labor Hours, and Operating Costs The “Efficiency” of unit i is defined as follows:

    57. 3-57 Data Envelopment Analysis (DEA): Steak & Burger Accordingly, for Unit 1 in the example, the measure of efficiency is:

    58. 3-58 Defining the Decision Variables

    59. 3-59 Defining the Objective Function

    60. 3-60 Defining the Constraints Efficiency cannot exceed 100% for any unit For Unit 1: For Unit 2: And so on for the other units …

    61. 3-61 Defining the Constraints Sum of weighted inputs for unit i must equal 1: For Unit 1: Nonnegativity Condition wj, vj >= 0, for all j

    62. 3-62 Complete DEA Formulation for Unit 1

    63. 3-63 Important Point for DEA Models

    64. 3-64 Comments If the LP for Unit i yields an efficiency ratio of 100% (i.e., the efficiency constraint for Unit i is binding), then, given the weights selected in the LP solution, no other unit was able to outperform Unit i On the other hand if the LP for Unit i yields an efficiency ratio < 100% (i.e., the efficiency constraint for Unit i is nonbinding), then one or more other units were able to outperform Unit i given the weights selected by the LP for Unit i More on this after implementing and solving the model …

    65. 3-65 Implementing the Model See file Fig3-41.xls

    66. 3-66 Solution for Unit 1

    67. 3-67 Analyzing Solution for Unit 1 Note that the LP solution for Unit 1 indicates that Unit 1 achieves a score of 0.9667, which means that it is slightly inefficient Four other units in the solution for Unit 1 achieve a 100% efficiency score, namely Units 2, 4, 7 and 12 Consider Units 2 and 12: Each generates more output than Unit 1 However, each uses more input than Unit 1 as well Unit 4 generates output less than or equal to that of Unit 1 with input less than those of Unit 1 Unit 7 generates two output measures less than those of Unit 1 and one output measure more than that of Unit 1, while using less input than that of Unit 1 A question that arises here: “Can we construct a fictitious efficient unit that is 100% efficient (given the determined weights), and that uses input measures that are less than or equal to that of Unit 1 and generate output measures greater than or equal to that of Unit 1?” The answer to the question is “yes”: A procedure for doing this will be presented shortly “Why” the procedure works is beyond the scope of this class (it requires introducing “duality theory” for Linear Programming)

    68. 3-68 Procedure for Creating an Efficient Composite Unit Run LP and select Sensitivity report to be generated (to be discussed in much detail in Chapter 4)

    69. 3-69 Procedure for Creating an Efficient Composite Unit Use shadow prices for efficiency constraints as the weights to create the composite fictitious unit

    70. 3-70 Procedure for Creating an Efficient Composite Unit Formula for B16 is =SUMPRODUCT(B3:B14,$G$3:$G$14) and is copied to C16:F16. Note that the composite unit indeed generates more or same output as Unit 1 with same or less input Also, you can verify that the unit is 100% efficient given the obtained weights.

    71. 3-71 End of Chapter 3

More Related