qm b linear programming
DESCRIPTION
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. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/1.jpg)
QM B Linear Programming
![Page 2: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/2.jpg)
Overview
• What is linear programming (LP)?
• Formulating LPs– The Stratton company
• Graphical insight
• Using Excel Solver to solve LPs– Mile-High Microbrewery
![Page 3: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/3.jpg)
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.
![Page 4: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/4.jpg)
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.
![Page 5: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/5.jpg)
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
![Page 6: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/6.jpg)
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
![Page 7: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/7.jpg)
Stratton Company Data
ProductResource Availability
Resource Type 1
Type 2
Extrusion 4 hrs. 6 hrs. 48 hrs.
Packaging 2 hrs. 2 hrs. 18 hrs.
Additive Mix
2 lbs. 1 lbs. 16 lbs.
Profit $34 $40All data given is for a package of pipe – 100 feet
![Page 8: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/8.jpg)
Stratton Company (cont)
Formulate an LP model to determine how much of each type of pipe should be produced to maximize profit.
![Page 9: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/9.jpg)
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
![Page 10: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/10.jpg)
Question 2 for Formulating an LP:
• What is the goal?– Stratton Company
• Maximize profit
– Defines the objective function
MAX 34 P1 + 40 P2
![Page 11: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/11.jpg)
Question 3 for Formulating an LP:• What are the
limited resources or requirements?– Extrusion hours
– Packaging hours
– Additive mix
4P1 + 6P2 48
2P1 + 2P2 18
2P1 + 1P2 16
![Page 12: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/12.jpg)
LP for Stratton Company
MAX 34 P1 + 40 P2
Subject to:4 P1 + 6 P2 48 Extrusion hours2 P1 + 2 P2 18 Packaging hours2 P1 + 1 P2 16 Additive supply
P1 0 and P2 0 Non-negativity
Objective Function
Constraints
![Page 13: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/13.jpg)
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
![Page 14: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/14.jpg)
Graphical analysis – non-negativity
P1P
2
P1 NegativeP2 Positive
P1 NegativeP2 Negative
P1 PositiveP2 Negative
P1 - PositiveP2 - Positive
![Page 15: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/15.jpg)
Graphical analysis – Extrusion constraint
Stratton Company
0123456789
0 1 2 3 4 5 6 7 8 9 10 11 12 13
P1
P2 Extrusion
4 P1 + 6 P2 48
![Page 16: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/16.jpg)
Graphical analysis – Packaging Constraint
Stratton Company
0123456789
10
0 1 2 3 4 5 6 7 8 9 10 11 12 13
P1
P2 Extrusion
Packaging
2 P1 + 2 P2 18
![Page 17: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/17.jpg)
Graphical analysis – Additive supply constraint
Stratton Company
0
2
4
6
8
10
12
14
16
18
0 1 2 3 4 5 6 7 8 9 10 11 12 13
P1
P2
Extrusion
Packaging
Additive Supply
2 P1 + 1 P2 16
Feasible Region
![Page 18: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/18.jpg)
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
![Page 19: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/19.jpg)
Corner point solutionsStratton Company
0
2
4
6
8
10
12
14
16
18
0 1 2 3 4 5 6 7 8 9 10 11 12 13
P1
P2
Extrusion
Packaging
Additive Supply
Feasible Region
MAX 34 P1 + 40 P2 (go to Excel)
![Page 20: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/20.jpg)
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.
![Page 21: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/21.jpg)
Setting up Excel Solver to solve LPs
• Solver is an add-in to Excel– Not automatically ready– To get solver ready:
In ExcelTools -> Add insScroll down to Solver Add inCheck the boxClick on OK
• Only need to do this one time
![Page 22: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/22.jpg)
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.
![Page 23: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/23.jpg)
Mile-High Microbrewery Data
Mile-High Microbrewery
Resource
Resource Light Dark Availability
Barley (grams) 0.1 0.6 2000
Bottling (bottles) 1 1 6000
Market (bottles) 1 4000
Profi t $0.20 $0.50
Product
![Page 24: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/24.jpg)
Think-pair-share: Three questions:
• What are the decisions to be made?
• What is the goal?
• What are the limited resources or requirements?
![Page 25: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/25.jpg)
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
![Page 26: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/26.jpg)
What is the goal?
• Maximize profit
MAX 0.20 L + 0.50 D
![Page 27: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/27.jpg)
What are the limited resources or requirements?• Barley supply
0.10 L + 0.60 D 2000
• Bottling capacity1 L + 1 D 6000
• Market capacity1 L 4000
![Page 28: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/28.jpg)
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
![Page 29: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/29.jpg)
Think-pair-share: SUMPRODUCT function
=SUMPRODUCT(B6:C6,B10:C10)
= 2*2 + 1*4 = 8
![Page 30: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/30.jpg)
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 boxTools -> Solver
![Page 31: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/31.jpg)
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
![Page 32: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/32.jpg)
Excel solver – constraints dialog box
Select cell(s) with LHS
Select symbol (, , =)
Select cell(s) with RHS
Remember – Non-negativity constraints
![Page 33: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/33.jpg)
Go to the Options Dialog box
Click options to assume linear model
![Page 34: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/34.jpg)
Last dialog box - options
Check the Assume linear models box
Click
OK
Check the Assume Non-negative box
![Page 35: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/35.jpg)
Now SOLVEClick solve to find optimal solution
![Page 36: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/36.jpg)
Solver found a solution
Click on Answer and Sensitivity
Click
OK
![Page 37: QM B Linear Programming](https://reader033.vdocuments.us/reader033/viewer/2022061600/56815a9c550346895dc81c04/html5/thumbnails/37.jpg)
Answer and sensitivity reports