Transcript Introduction to Management Science

Chapter 4 (Linear Programming: Formulation and Applications)
• Advertising-Mix Problem (Section 4.1)
– Super Grain Corp | 4.2–4.5
• Resource Allocation Problems (Section 4.2)
–Think-Big Capital Budgeting | 4.6–4.10
• Cost-Benefit-Trade-Off Problems (Section 4.3)
–Union Airways | 4.11–4.15
• Distribution-Network Problems (Section 4.4)
–Big M Co. | 4.16–4.20
• Student Exercises
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.1
Optimization© The McGraw-Hill Companies,
Inc., 2003
Super Grain Corp. Advertising-Mix Problem
•
Goal: Design the promotional campaign for Crunchy Start.
•
The three most effective advertising media for this product are
– Television commercials on Saturday morning programs for children.
– Advertisements in food and family-oriented magazines.
– Advertisements in Sunday supplements of major newspapers.
•
The limited resources in the problem are
– Advertising budget ($4 million).
– Planning budget ($1 million).
– TV commercial spots available (5).
•
The objective will be measured in terms of the expected number of exposures.
Question: At what level should they advertise Crunchy Start in each of the
three media?
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.2
Optimization© The McGraw-Hill Companies,
Inc., 2003
Cost and Exposure Data
Costs
Cost Category
Ad Budget
Planning budget
Expected number of
exposures
Each
TV Commercial
Each
Magazine Ad
Each
Sunday Ad
$300,000
$150,000
$100,000
90,000
30,000
40,000
1,300,000
600,000
500,000
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.3
Optimization© The McGraw-Hill Companies,
Inc., 2003
Spreadsheet Formulation
B
3
4
5
6
7
8
9
10
11
12
13
14
15
Exposures per Ad
(thousands)
Ad Budget
Planning Budget
Number of Ads
Max TV Spots
C
TV Spots
1,300
300
90
D
Magazine Ads
600
Cost per Ad ($thousands)
150
100
30
40
TV Spots
0
<=
5
Magazine Ads
20
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
E
SS Ads
500
4.4
SS Ads
10
F
Budget
Spent
4,000
1,000
G
H
<=
<=
Budget
Av ailable
4,000
1,000
Total Exposures
(thousands)
17,000
Optimization© The McGraw-Hill Companies,
Inc., 2003
Algebraic Formulation
Let TV = Number of commercials for separate spots on television
M = Number of advertisements in magazines.
SS = Number of advertisements in Sunday supplements.
Maximize Exposure = 1,300TV + 600M + 500SS
subject to
Ad Spending:
300TV + 150M + 100SS ≤ 4,000 ($thousand)
Planning Cost:
90TV + 30M + 30SS ≤ 1,000 ($thousand)
Number of TV Spots:
TV ≤ 5
and
TV ≥ 0, M ≥ 0, SS ≥ 0.
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.5
Optimization© The McGraw-Hill Companies,
Inc., 2003
Think-Big Capital Budgeting Problem
•
Think-Big Development Co. is a major investor in commercial real-estate
development projects.
•
They are considering three large construction projects
– Construct a high-rise office building.
– Construct a hotel.
– Construct a shopping center.
•
Each project requires each partner to make four investments: a down payment
now, and additional capital after one, two, and three years.
Question: At what fraction should Think-Big invest in each of the three
projects?
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.6
Optimization© The McGraw-Hill Companies,
Inc., 2003
Financial Data for the Projects
Investment Capital Requirements
Year
Office Building
Hotel
Shopping Center
0
$40 million
$80 million
$90 million
1
60 million
80 million
50 million
2
90 million
80 million
20 million
3
10 million
70 million
60 million
Net present value
$45 million
$70 million
$50 million
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.7
Optimization© The McGraw-Hill Companies,
Inc., 2003
Spreadsheet Formulation
B
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Net Present Value
($millions)
Now
End of Y ear 1
End of Y ear 2
End of Y ear 3
Participation Share
C
Of f ice
Building
45
D
Hotel
70
E
Shopping
Center
50
Cumulativ e Capital Required ($millions)
40
80
90
100
160
140
190
240
160
200
310
220
Of f ice
Building
0.00%
Hotel
16.50%
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.8
Shopping
Center
13.11%
F
Cumulativ e
Capital
Spent
25
44.757
60.583
80
G
H
<=
<=
<=
<=
Cumulativ e
Capital
Av ailable
25
45
65
80
Total NPV
($millions)
18.11
Optimization© The McGraw-Hill Companies,
Inc., 2003
Algebraic Formulation
Let OB = Participation share in the office building,
H = Participation share in the hotel,
SC = Participation share in the shopping center.
Maximize NPV = 45OB + 70H + 50SC
subject to
Total invested now:
40OB + 80H + 90SC ≤ 25 ($million)
Total invested within 1 year:
100OB + 160H + 140SC ≤ 45 ($million)
Total invested within 2 years: 190OB + 240H + 160SC ≤ 65 ($million)
Total invested within 3 years: 200OB + 310H + 220SC ≤ 80 ($million)
and
OB ≥ 0, H ≥ 0, SC ≥ 0.
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.9
Optimization© The McGraw-Hill Companies,
Inc., 2003
Summary of Formulation Procedure for ResourceAllocation Problems
1. Identify the activities for the problem at hand.
2. Identify an appropriate overall measure of performance (commonly profit).
3. For each activity, estimate the contribution per unit of the activity to the
overall measure of performance.
4. Identify the resources that must be allocated.
5. For each resource, identify the amount available and then the amount used per
unit of each activity.
6. Enter the data in steps 3 and 5 into data cells.
7. Designate changing cells for displaying the decisions.
8. In the row for each resource, use SUMPRODUCT to calculate the total
amount used. Enter ≤ and the amount available in two adjacent cells.
9. Designate a target cell. Use SUMPRODUCT to calculate this measure of
performance.
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.10
Optimization© The McGraw-Hill Companies,
Inc., 2003
Union Airways Personnel Scheduling
•
Union Airways is adding more flights to and from its hub airport and so needs
to hire additional customer service agents.
•
The five authorized eight-hour shifts are
–
–
–
–
–
Shift 1:
Shift 2:
Shift 3:
Shift 4:
Shift 5:
6:00 AM to 2:00 PM
8:00 AM to 4:00 PM
Noon to 8:00 PM
4:00 PM to midnight
10:00 PM to 6:00 AM
Question: How many agents should be assigned to each shift?
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.11
Optimization© The McGraw-Hill Companies,
Inc., 2003
Schedule Data
Time Periods Covered by Shift
Time Period
1
6 AM to 8 AM
√
8 AM to 10 AM
√
√
79
10 AM to noon
√
√
65
Noon to 2 PM
√
√
√
87
√
√
64
2 PM to 4 PM
2
3
4
5
Minimum
Number of
Agents Needed
48
4 PM to 6 PM
√
√
73
6 PM to 8 PM
√
√
82
8 PM to 10 PM
√
43
10 PM to midnight
√
Midnight to 6 AM
Daily cost per agent
$170
$160
$175
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.12
$180
√
52
√
15
$195
Optimization© The McGraw-Hill Companies,
Inc., 2003
Spreadsheet Formulation
B
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
C
6am-2pm
Shif t
Cost per Shif t
$170
Time Period
6am-8am
8am-10am
10am- 12pm
12pm-2pm
2pm-4pm
4pm-6pm
6pm-8pm
8pm-10pm
10pm-12am
12am-6am
Number Working
1
1
1
1
0
0
0
0
0
0
6am-2pm
Shif t
48
D
8am-4pm
Shif t
$160
E
Noon-8pm
Shif t
$175
F
4pm-midnight
Shif t
$180
Shif t Works Time Period? (1=y es,
0
0
1
0
1
0
1
1
1
1
0
1
0
1
0
0
0
0
0
0
8am-4pm
Shif t
31
Noon-8pm
Shif t
39
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.13
0=no)
0
0
0
0
0
1
1
1
1
0
4pm-midnight
Shif t
43
G
10pm-6am
Shif t
$195
0
0
0
0
0
0
0
0
1
1
10pm-6am
Shif t
15
H
Total
Working
48
79
79
118
70
82
82
43
58
15
I
J
>=
>=
>=
>=
>=
>=
>=
>=
>=
>=
Minimum
Needed
48
79
65
87
64
73
82
43
52
15
Total Cost
$30,610
Optimization© The McGraw-Hill Companies,
Inc., 2003
Algebraic Formulation
Let Si = Number working shift i (for i = 1 to 5),
Minimize Cost = $170S1 + $160S2 + $175S3 + $180S4 + $195S5
subject to
Total agents 6AM–8AM:
S1 ≥ 48
Total agents 8AM–10AM:
S1 + S2 ≥ 79
Total agents 10AM–12PM:
S1 + S2 ≥ 65
Total agents 12PM–2PM:
S1 + S2 + S3 ≥ 87
Total agents 2PM–4PM:
S2 + S3 ≥ 64
Total agents 4PM–6PM:
S3 + S4 ≥ 73
Total agents 6PM–8PM:
S3 + S4 ≥ 82
Total agents 8PM–10PM:
S4 ≥ 43
Total agents 10PM–12AM:
S4 + S5 ≥ 52
Total agents 12AM–6AM:
S5 ≥ 15
and
Si ≥ 0 (for i = 1 to 5)
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.14
Optimization© The McGraw-Hill Companies,
Inc., 2003
Summary of Formulation Procedure for
Cost-Benefit-Tradeoff Problems
1. Identify the activities for the problem at hand.
2. Identify an appropriate overall measure of performance (commonly cost).
3. For each activity, estimate the contribution per unit of the activity to the
overall measure of performance.
4. Identify the benefits that must be achieved.
5. For each benefit, identify the minimum acceptable level and then the
contribution of each activity to that benefit.
6. Enter the data in steps 3 and 5 into data cells.
7. Designate changing cells for displaying the decisions.
8. In the row for each benefit, use SUMPRODUCT to calculate the level
achieved. Enter ≤ and the minimum acceptable level in two adjacent cells.
9. Designate a target cell. Use SUMPRODUCT to calculate this measure of
performance.
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.15
Optimization© The McGraw-Hill Companies,
Inc., 2003
The Big M Distribution-Network Problem
•
The Big M Company produces a variety of heavy duty machinery at two
factories. One of its products is a large turret lathe.
•
Orders have been received from three customers for the turret lathe.
Question: How many lathes should be shipped from each factory to each
customer?
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.16
Optimization© The McGraw-Hill Companies,
Inc., 2003
Some Data
Shipping Cost for Each Lathe
To
Customer 1
Customer 2
Customer 3
From
Output
Factory 1
$700
$900
$800
12 lathes
Factory 2
800
900
700
15 lathes
Order Size
10 lathes
8 lathes
9 lathes
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.17
Optimization© The McGraw-Hill Companies,
Inc., 2003
The Distribution Network
C1
10 lathes
needed
C2
8 lathes
needed
C3
9 lathes
needed
$700/lathe
12 lathe
produced
F1
$900/lathe
$800/lathe
$900/lathe
$800/lathe
15 lathes
produced
F2
$700/lathe
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.18
Optimization© The McGraw-Hill Companies,
Inc., 2003
Spreadsheet Formulation
3
4
5
6
7
8
9
10
11
12
13
14
15
B
C
Shipping Cost
(per Lathe) Customer 1
Factory 1
$700
Factory 2
$800
Units Shipped Customer 1
Factory 1
10
Factory 2
0
Total To Customer
10
=
Order Size
10
D
E
Customer 2
$900
$900
Customer 3
$800
$700
Customer 2
2
6
8
=
8
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.19
Customer 3
0
9
9
=
9
F
Total
Shipped
Out
12
15
G
H
=
=
Output
12
15
Total Cost
$20,500
Optimization© The McGraw-Hill Companies,
Inc., 2003
Algebraic Formulation
Let Sij = Number of lathes to ship from i to j (i = F1, F2; j = C1, C2, C3).
Minimize Cost = $700SF1-C1 + $900SF1-C2 + $800SF1-C3
+ $800SF2-C1 + $900SF2-C2 + $700SF2-C3
subject to
Factory 1:
SF1-C1 + SF1-C2 + SF1-C3 = 12
Factory 2:
SF2-C1 + SF2-C2 + SF2-C3 = 15
Customer 1: SF1-C1 + SF2-C1 = 10
Customer 2: SF1-C2 + SF2-C2 = 8
Customer 3: SF1-C3 + SF2-C3 = 9
and
Sij ≥ 0 (i = F1, F2; j = C1, C2, C3).
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.20
Optimization© The McGraw-Hill Companies,
Inc., 2003
Types of Functional Constraints
Type
Resource constraint
Benefit constraint
Fixed-requirement
constraint
Form*
Typical Interpretation
Main Usage
LHS ≤ RHS
For some resource,
Amount used ≤
Amount available
Resource-allocation
problems and mixed
problems
LHS ≥ RHS
For some benefit,
Level achieved ≥
Minimum Acceptable
Cost-benefit-trade-off
problems and mixed
problems
LHS = RHS
For some quantity,
Amount provided =
Required amount
Distribution-network
problems and mixed
problems
* LHS = Left-hand side (a SUMPRODUCT function).
RHS = Right-hand side (a constant).
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.21
Optimization© The McGraw-Hill Companies,
Inc., 2003
Formulating an LP Spreadsheet Model
•
Enter all of the data into the spreadsheet. Color code (blue).
•
What decisions need to be made? Set aside a cell in the spreadsheet for each
decision variable (changing cell). Color code (yellow with border).
•
Write an equation for the objective in a cell. Color code (orange with heavy
border).
•
Put all three components (LHS, ≤/=/≥, RHS) of each constraint into three cells
on the spreadsheet.
•
Some Examples:
–
–
–
–
–
Production Planning
Diet / Blending
Workforce Scheduling
Transportation / Distribution
Assignment
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.22
Optimization© The McGraw-Hill Companies,
Inc., 2003
Product Mix Exercise
Blue Ridge Hot Tubs manufactures and sells two models of hot tubs: the AquaSpa and the Hydro-Lux. Howie Jones, the owner and manager of the company
needs to decide how many of each type of hot tub to produce during his next
production cycle. Howie buys prefabricated fiberglass hot tub shells from a
local supplier and adds the pump and tubing to the shells to create his hot tubs.
(The supplier has the capacity to deliver as many hot tub shells as Howie
needs.) Howie installs the same type of pump into both hot tubs. He will have
only 200 pumps available during his next production cycle. From a
manufacturing standpoint, the main difference between the two models of hot
tubs is the amount of tubing and labor required. Each Aqua-Spa requires 9
hours of labor and 12 feet of tubing. Each Hydro-Lux requires 6 hours of
labor and 16 feet of tubing. Howie expects to have 1,566 production labor
hours and 2,880 feet of tubing available during the next production cycle.
Howie earns a profit of $350 on each Aqua-Spa he sells and $300 on each
Hydro-Lux he sells. He is confident that he can sell all the hot tubs he
produces. The question is, how many Aqua-Spas and Hydro-Luxes should
Howie produce if he wants to maximize his profits during the next production
cycle?
Source: Ragsdale, Spreadsheet Modeling and Decision Analysis.
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.23
Optimization© The McGraw-Hill Companies,
Inc., 2003
Elements Common to Every Problem
Decision variables:
number of aqua-spas (A) to produce and
number of hydro-luxes (H) to produce.
Objective function: Max: Profit = 350 A + 300 H
Constraints:
Pump
1A + 1H <= 200
Labor
9A + 6H <= 1566
Tubing
12A + 16H <= 2880
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.24
Optimization© The McGraw-Hill Companies,
Inc., 2003
AquaSpa
300
285
Graphical Solution Using Graphic LP Optimizer
270
255
240
225
210
195
180
165
150
135
120
105
90
75
60
45
30
15
0
0 15 30 45 60 75 90 105120135150165180195210225240255270285300
HydroLux
Payoff: 300.0 HydroLux + 350.0 AquaSpa =
Optimal Decisions(HydroLux,AquaSpa): ( 0.0, 0.0)
Pump: 1.0HydroLux + 1.0AquaSpa <= 200.0
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.25
Optimization© The McGraw-Hill Companies,
Inc., 2003
AquaSpa
300
285
Graphical Solution Using Graphic LP Optimizer
270
255
240
225
210
195
180
165
150
135
120
105
90
75
60
45
30
15
0
0 15 30 45 60 75 90 105120135150165180195210225240255270285300
HydroLux
Payoff: 300.0 HydroLux + 350.0 AquaSpa = 0.0
Optimal Decisions(HydroLux,AquaSpa): ( 0.0, 0.0)
Pump: 1.0HydroLux + 1.0AquaSpa <= 200.0
: 6.0HydroLux + 9.0AquaSpa <= 1566.0
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.26
Optimization© The McGraw-Hill Companies,
Inc., 2003
Graphical Solution Using Graphic LP Optimizer
AquaSpa
Constraints
168
160
152
144
136
128
120
112
104
96
88
80
72
64
56
48
40
32
24
16
8
0
0
9
18
27
36
45
54
63
72
81
90
99
108
117
126
135
144
153
162
171
180
HydroLux
Payoff: 300.0 HydroLux + 350.0 A
Optimal Decisions(HydroLux,AquaSpa): ( 0.0, 0.0)
Pump: 1.0HydroLux + 1.0AquaSpa <= 200.0
: 6.0HydroLux + 9.0AquaSpa <= 1566.0
: 16.0HydroLux + 12.0AquaSpa <= 2880.0
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.27
Optimization© The McGraw-Hill Companies,
Inc., 2003
Graphical Solution Using Graphic LP Optimizer
AquaSpa
168
160
152
144
136
128
120
112
104
96
88
80
72
64
56
48
40
32
24
16
8
0
Feasible Solution Space
0
9
18
27
36
45
54
63
72
81
90
99
108
117
126
135
144
153
162
171
180
HydroLux
Payoff: 300.0 HydroLux + 350.0 Aqua
Optimal Decisions(HydroLux,AquaSpa): ( 0.0, 0.0)
Pump: 1.0HydroLux + 1.0AquaSpa <= 200.0
: 6.0HydroLux + 9.0AquaSpa <= 1566.0
: 16.0HydroLux + 12.0AquaSpa <= 2880.0
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.28
Optimization© The McGraw-Hill Companies,
Inc., 2003
Graphical Solution Using Graphic LP Optimizer
Payoff: 300.0 HydroLux + 350.0 AquaSpa = 66100.0
AquaSpa
168
160
152
144
136
128
120
112
104
96
88
80
72
64
56
48
40
32
24
16
8
0
Optimal Solution
0
9
18
27
36
45
54
63
72
81
90
99
108
117
126
135
144
153
162
171
180
HydroLux
Optimal Decisions(HydroLux,AquaSpa): (78.0, 122.0)
Pump: 1.0HydroLux + 1.0AquaSpa <= 200.0
: 6.0HydroLux + 9.0AquaSpa <= 1566.0
: 16.0HydroLux + 12.0AquaSpa <= 2880.0
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.29
Optimization© The McGraw-Hill Companies,
Inc., 2003
Organizing Spreadsheet & Entering Formulas
Decision Variable Cells
D6.
=SUMPRODUCT($B$5:$C$5,B6:C6)
D9.
=SUMPRODUCT($B$5:$C$5,B9:C9)
Constraint Coefficients
D10.
=SUMPRODUCT($B$5:$C$5,B10:C10)
Constraint RHS Formulas
Decision Variable Coefficients
Constraint RHS Limits
D11.
=SUMPRODUCT($B$5:$C$5,B11:C11)
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.30
Optimization© The McGraw-Hill Companies,
Inc., 2003
Tools | Solver
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.31
Optimization© The McGraw-Hill Companies,
Inc., 2003
Sensitivity Analysis
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.32
Optimization© The McGraw-Hill Companies,
Inc., 2003
A Classic Problem
See In class handout.
First, identify the decision variables, objective
function and constraints.
Second, think about spreadsheet layout.
Third, implement and solve model.
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.33
Optimization© The McGraw-Hill Companies,
Inc., 2003
In Class Exercise
End of chapter problem 4.6
McGraw-Hill/Irwin
Modified for Quan 6610 by Dr. Jim Grayson
4.34
Optimization© The McGraw-Hill Companies,
Inc., 2003