html5-img
1 / 37

QM B Linear Programming

QM B Linear Programming. Overview. What is linear programming (LP)? Formulating LPs The Stratton company Graphical insight Using Excel Solver to solve LPs Mile-High Microbrewery. What is linear programming?. It is NOT computer programming. Programming here means planning.

tyler
Télécharger la présentation

QM B Linear Programming

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. QM B Linear Programming

  2. Overview • What is linear programming (LP)? • Formulating LPs • The Stratton company • Graphical insight • Using Excel Solver to solve LPs • Mile-High Microbrewery

  3. What is linear programming? • It is NOT computer programming. Programming here means planning. • Mathematical technique • Optimization technique • A decision needs to made • Our goal is to determine the ‘best’ or ‘optimum’ decision • There are scarce resources available and/or specified requirements for achieving our goal.

  4. Proctor and GambleNorth American Product Sourcing • 50 products • 60 plants • 10 distribution centers • 1000 customer zones • Save $200 million dollars annually • Which products should be produced in which plants? • Which plants should supply which distribution centers? • Which distribution centers should supply which customer zones.

  5. What does an LP look like? • There is a goal: objective function • Maximized or minimized • Written as a linear equation • There are scarce resources, restrictions and/or requirements: constraints • Limits your ability to achieve the goal • Written as a linear equation

  6. Formulating an LP: Stratton Co. • Produces two basic types of plastic pipes • Three resources have been identified as critical to pipe output • Pipe extrusion hours • Packaging hours • Special additive mix

  7. Stratton Company Data All data given is for a package of pipe – 100 feet

  8. Stratton Company (cont) Formulate an LP model to determine how much of each type of pipe should be produced to maximize profit.

  9. Three questions to formulate an LP: • What is the decision to be made? • Stratton Company • How much of pipe 1 to produce • How much of pipe 2 to produce • Defines the variables (if you are specific enough). • P1 – number of packages of Pipe 1 to produce • P2 – number of packages of Pipe 2 to produce

  10. Question 2 for Formulating an LP: • What is the goal? • Stratton Company • Maximize profit • Defines the objective function MAX 34 P1 + 40 P2

  11. What are the limited resources or requirements? Extrusion hours Packaging hours Additive mix 4P1 + 6P2  48 2P1 + 2P2  18 2P1 + 1P2  16 Question 3 for Formulating an LP:

  12. Objective Function LP for Stratton Company MAX 34 P1 + 40 P2 Subject to: 4 P1 + 6 P2  48 Extrusion hours 2 P1 + 2 P2  18 Packaging hours 2 P1 + 1 P2  16 Additive supply P1  0 and P2  0 Non-negativity Constraints

  13. Solving LPs • ‘What if’ analysis (go to Excel) • Graphical analysis • For insight • Simplex method • Solver – an Excel add-in • Computer packages designed for linear optimization

  14. Graphical analysis – non-negativity

  15. Graphical analysis – Extrusion constraint 4 P1 + 6 P2  48

  16. Graphical analysis – Packaging Constraint 2 P1 + 2 P2  18

  17. Graphical analysis – Additive supply constraint Feasible Region 2 P1 + 1 P2  16

  18. Feasible Region • Set of all solutions that satisfy all of the constraints • Infinite number of solutions Which is the optimal solution? • One of the solutions at the corner points

  19. Corner point solutions Feasible Region MAX 34 P1 + 40 P2 (go to Excel)

  20. Stratton Company – Summary • Optimal solution • P1 = 3 • P2 = 6 • Max = $342 • The optimal product mix is 3 packages of Pipe 1 and 6 packages of Pipe 2. This provides a maximum profit of $342.

  21. Setting up Excel Solver to solve LPs • Solver is an add-in to Excel • Not automatically ready • To get solver ready: In Excel Tools -> Add ins Scroll down to Solver Add in Check the box Click on OK • Only need to do this one time

  22. Mile-High Microbrewery Mile-High Microbrewery makes a light beer and a dark beer. Mile-High has a limited supply of barley, limited bottling capacity, and a limited market for light beer. Profits are $0.20 per bottle of light beer and $0.50 per bottle of dark beer. Formulate an LP to maximize profits and determine how many bottles of each product should be produced per month.

  23. Mile-High Microbrewery Data

  24. Think-pair-share: Three questions: • What are the decisions to be made? • What is the goal? • What are the limited resources or requirements?

  25. What are the decisions to be made? • L – Number of bottles of light beer to produce • D – Number of bottles of dark beer to produce

  26. What is the goal? • Maximize profit MAX 0.20 L + 0.50 D

  27. What are the limited resources or requirements? • Barley supply 0.10 L + 0.60 D  2000 • Bottling capacity 1 L + 1 D  6000 • Market capacity 1 L  4000

  28. An aside for SUMPRODUCT function • 2 groups of cells • Both in a row or both in a columns • Wish to multiply the corresponding entries then sum the products =sumproduct(a2:c2, a3:c3) = 2*5 + 3*6 + 4*7

  29. Think-pair-share: SUMPRODUCT function =SUMPRODUCT(B6:C6,B10:C10) = 2*2 + 1*4 = 8

  30. To solve an LP using Excel Solver • Setup the spreadsheet • TYPE data in one place (go to Excel) • CREATE Cells for decisions variables • ENTER formulas to calculate LHS of constraints • ENTER formulas to calculate Objective Function • Open solver box Tools -> Solver

  31. Excel Solver Dialog Box Click on cell that calculates objective function Select Max or Min Click & drag to select decision variables Click add to add the constraints

  32. Excel solver – constraints dialog box Select cell(s) with LHS Select cell(s) with RHS Select symbol (, , =) Remember – Non-negativity constraints

  33. Go to the Options Dialog box Click options to assume linear model

  34. Last dialog box - options Click OK Check the Assume linear models box Check the Assume Non-negative box

  35. Now SOLVE Click solve to find optimal solution

  36. Solver found a solution Click on Answer and Sensitivity Click OK

  37. Answer and sensitivity reports

More Related