Ragsdale, Chapter 3
Modeling and Solving LP Problems 3.4 | A Spreadsheet Model for the Blue Ridge Hot Tubs Problem
Figure 3.1
EXCEL Note: Note: =SUMPRODUCT(B5:C5,B6: =SUMPRODUCT(B5:C5,B6:C6) C6) is equivalent to B5*B6 B5*B6 + C5*C6 (or, (or, a sum of products)
1
Management Science
QUAN3600
Ragsdale, Chapter 3 EXCEL: Data | Solver If not activated then: Office Button | EXCEL Options
Add-Ins | Select from list | Go
2
Management Science
QUAN3600
Ragsdale, Chapter 3
Target cell: ________ (which cell is our objective?) Equal to: ________ (do we want max or min?) Changing cells: ________ (which cells are our decision variable cells?) Each constraint has three parts:
Fill in these blanks: Constraint Pumps Labor Tubing
Cell Reference
Relationship
Constraint
The first one looks like in EXCEL:
3
Management Science
QUAN3600
Ragsdale, Chapter 3 So far it looks like this:
Go to Options and Select Assume Linear Assume Non-Negativity • •
Back to Solver Parameters Dialog Box and select SOLVE This is what you hope to see:
Click OK
4
Management Science
QUAN3600
Ragsdale, Chapter 3 Now you see the solution:
What does this tell you? Decision? Objective? Constraints?
Solver Sensitivity Report
5
Management Science
QUAN3600
Ragsdale, Chapter 3 Your Turn: Problem 3-12 In words, what are the: Decision variables? Objective function? Constraints? Algebraically, what are the: Decision variables? Objective function? Constraints?
Identify “zones” (dec var, obj function, constraints) on the spreadsheet for setting up this problem: (hint: look at the Hot Tubs spreadsheet)
6
Management Science
QUAN3600
Ragsdale, Chapter 3
7
Management Science
QUAN3600
Ragsdale, Chapter 3
3.8 | Make vs. Buy Decisions
In words, what are the: Decision variables? Objective function? Constraints?
Algebraically, what are the: Decision variables?
Objective function?
Constraints?
8
Management Science
QUAN3600
Ragsdale, Chapter 3 Fig 3.17
9
Management Science
QUAN3600
Ragsdale, Chapter 3 Problem 3-181 In words, what are the: Decision variables? Objective function? Constraints?
Algebraically, what are the: Decision variables?
Objective function?
Constraints?
1
Solution at end of notes
10
Management Science
QUAN3600
Ragsdale, Chapter 3
11
Management Science
QUAN3600
Ragsdale, Chapter 3
3.9 | An Investment Problem
In words, what are the: Decision variables? Objective function? Constraints?
Algebraically, what are the: Decision variables?
Objective function?
Constraints?
12
Management Science
QUAN3600
Ragsdale, Chapter 3
Fig 3.20
13
Management Science
QUAN3600
Ragsdale, Chapter 3
3.12 | A Production and Inventory Planning Problem
Fig 3.31
14
Management Science
QUAN3600
Ragsdale, Chapter 3 3.10 | A Transportation Problem
Grove Mt. Dora Eustis Clermont
Distance (in miles) Between Groves and Plants Ocala Orlando Leesburg 21 50 40 35 30 22 55 20 25
Fig 3.23
15
Management Science
QUAN3600
Ragsdale, Chapter 3
Fig 3.24
16
Management Science
QUAN3600
Ragsdale, Chapter 3
Your Turn: Problem 3-292
2
Solution at end of notes
17
Management Science
QUAN3600
Ragsdale, Chapter 3 Comprehensive Examples Case 3-3 Identify the problem: Why would LP help us? What are we trying to decide? What is the goal? Any limiting factors?
18
Management Science
QUAN3600
Ragsdale, Chapter 3
Case 3-4 Identify the problem: Why would LP help us? What are we trying to decide? What is the goal? Any limiting factors?
19
Management Science
QUAN3600
Ragsdale, Chapter 3 Your Turn Partial Solutions Problem 3-18
Problem 3-29
20
Management Science
QUAN3600
