Transcript Chapter 3 - Linear Programming: Computer Solution and

Chapter 4 - Linear Programming:
Computer Solution
Excel Solver
1 - Chap 04
Computer Solution
• Early linear programming used lengthy manual mathematical
solution (Linear Algebra and Matrix Algebra) procedure called
the Simplex Method.
• Steps of the Simplex Method have been programmed in
software packages designed for linear programming
problems.
• Many such packages available currently.
• Used extensively in business and government.
• Text focuses on Excel Spreadsheets.
2 - Chap 04
Excel Spreadsheets
Excel Spreadsheet for Beaver Creek Pottery Company Example
3 - Chap 04
Excel Spreadsheets
Excel Spreadsheet for Beaver Creek Pottery Company Example
4 - Chap 04
Excel Model
When formulating a linear programming problem on an Excel spread
sheet:
•Parameters are called Data Cells.
• Decision variables are called Variable Cells.
•Right hand sides are part of the Constraints.
•The objective function is called the Target Cell.
5 - Chap 04
Excel Spreadsheets “Solver” Parameter Screen
Optimization Type
Location of the Objective
function value
Location
of the
Decision
Variables
Location of
Constraints
Check the
box of nonnegativity
Choose Simplex LP
Press “Add” to add constraints
6 - Chap 04
Excel Spreadsheets Adding Model Constraints
7 - Chap 04
Check the box if
integer constraint
is not required
8 - Chap 04
Excel Spreadsheets Solution Screen
9 - Chap 04
Excel Spreadsheets Answer Report
10 - Chap 04
Example Problem: Airplane Parts
Problem Statement
• Two airplane parts: no.1 and no.2.
• Three manufacturing stages: stamping, drilling, finishing.
• Decision variables: x1 (number of part no.1 to produce)
x2 (number of part no.2 to produce)
• Model: maximize Z = $650x1 + 910x2
subject to
4x1 + 7.5x2  105
(stamping,hr)
6.2x1 + 4.9x2  90
(drilling, hr)
9.1x1 + 4.1x2  110
(finishing, hr)
x1,x2  0
11 - Chap 04
Example Problem: Airplane Parts
Excel Spreadsheet Solution
12 - Chap 04
Example Problem: Airplane Parts
Excel Spreadsheet Solution
13 - Chap 04
Excel Solver Exercise
Use Solver to solve the following LPP model with 6 decision variables
and 7 functional constraints.
Minimize Z = 18x1 + 22x2 + 10x3 + 12x4 + 10x5 + 9x6
subject to
Constraint 1 2x2 + 2x3 + 2x4 + 5x5 + 3x6
Constraint 2 270x5 + 8x6
Constraint 3 6x1 + 4x2 + 2x3 + 3x4+ x5
Constraint 4 20x1 + 48x2 + 12x3 + 8x4+ 30x5
Constraint 5 3x1 + 4x2 + 5x3 + 6x4 + 7x5 + 2x6
Constraint 6 5x1 + 2x2 + 3x3 + 4x4
Constraint 7 90x1 + 110x2 + 100x3 + 90x4 + 75x5 + 35x6
Non-negative All xj  0
Integer
All xj are integers
 200
 300
 50
 4000
 200
 120
 4200
14 - Chap 04
Excel Solver Exercise: Excel Spreadsheet
15 - Chap 04