Transcript Managerial Decision Modeling with Spreadsheets

Managerial Decision Modeling
with Spreadsheets
Chapter 3
Linear Programming Modeling Applications:
With Computer Analyses in Excel
Learning Objectives
1. Model wide variety of linear
programming (LP) problems.
2. Understand major business application
areas for LP problems: manufacturing,
marketing, labor scheduling, blending,
transportation, finance, and multi-period
planning.
3. Gain experience in setting up and solving
LP problems using Excel’s Solver.
2
3.1 Introduction
• Delta Airlines is example of use of LP model for
solving real-world scheduling problems resulting in
significant cost reductions for company.
• Similar examples exist in other managerial decision
making areas, such as:
–
–
–
–
production mix, labor scheduling, job assignment,
production scheduling, marketing research,
media selection, shipping and transportation,
ingredient mix, and financial portfolio selection.
• Purpose is to show how one can use LP to
modeling for decision-making in these areas.
3
3.2 Marketing Application
Media Selection • Win Big Gambling Club promotes gambling
junkets from a large Midwestern city to
casinos in the Bahamas.
• Club has budgeted up to $8,000 per week for
local advertising.
– Money is to be allocated among four
promotional media:
• TV spots,
• Newspaper ads, and
• Two types of radio advertisements.
• Win Big’s goal - reach largest possible highpotential audience through various media.
4
Media Selection Data
TV spot (1minute)
Daily newspaper (full page)
Radio spot (30 sec, prime time)
Radio spot (1 min, afternoon)
Audience
Reached
Per Ad
Cost
Per Ad(s)
Maximum
Ads Per
Week
5,000
8,500
2,400
2,800
800
925
290
380
12
5
25
20
• Contract arrangements require at least five radio
spots be placed each week.
• Management insists no more than $1,800 be spent
on radio advertising each week.
5
Media Selection Data LP Formulation
Objective: maximize audience coverage =
5000T + 8500N + 2400P +2800A
T = number of 1-minute TV spots taken each
week.
N = number of full-page daily newspaper ads
taken each week.
P = number of 30-second prime-time radio
spots taken each week.
A = number of 1-minute afternoon radio spots
taken each week.
6
Media Selection Data LP Formulation
Objective: maximize audience
coverage =
5000 T + 8500 N + 2400 P +2800 A
Subject to
T  12
N 5
P  25
A  20
800 T  925 N  290 P  380 A  $8, 000
P A5
290 P  380 A  $1, 800
7
Marketing Research Problem
Management Sciences Associates (MSA) handles
consumer surveys. MSA has to determine, for a
client, that it must fulfill several requirements in
order to draw statistically valid conclusions on
sensitive issue of new U.S. immigration laws:
1. Survey at least 2,300 U.S. households.
2. Survey at least 1,000 households whose heads
are 30 years of age or younger.
8
Marketing Research Problem
3. Survey at least 600 households whose heads are
between 31 and 50 years of age.
4. Ensure that at least 15% of those surveyed live
in a state that borders on Mexico.
5. Ensure that no more than 20% of those surveyed
who are 51 years of age or over live in a state
that borders on Mexico.
9
MSA’s Goal: Meet Sampling Requirements
With Minimum Cost
Objective: minimize total interview costs =
$7.50 B1 + $6.80 B2 + $5.50 B3 +
$6.90 N1 + $7.25 N2 + $6.10 N3
B1 = number 30 years or younger and live in border state.
B2 = number 31-50 years and live in border state.
B3 = number 51 years or older and live in border state.
N1 = number 30 years or younger and do not live in border
state.
N2 = number 31-50 years and do not live in border state.
N3 = number 51 years or older and do not live in border state.10
MSA’s Goal LP Formulation
Objective: minimize total interview costs =
$7.50 B1 + $6.80 B2 + $5.50 B3 +
$6.90 N1 + $7.25 N2 + $6.10 N3
Subject to
B1  B 2  B 3  N 1  N 2  N 3  2, 300
B1  N 1  1, 000
B 2  N 2  600
B1  B 2  B 3  0.15  B1  B 2  B 3  N 1  N 2  N 3 
B 3  0.2  B 3  N 3 
B1 , B 2 , B 3 , N 1 , N 2 , N 3  0
11
Rewriting Last Two Constraints
B1 + B2 + B3  0.15(B1 + B2 + B3 + N1 + N2 + N3)
Rewritten as:
B1 + B2 + B3 - 0.15(B1 + B2 + B3 + N1 + N2 + N3)  0
Simplifies to:
0.85B1 + 0.85B2 + 0.85B3 - 0.15N1 - 0.15N2 - 0.15N3  0
And
B3  0.2(B3 + N3)
Rewritten as:
0.8B3 - 0.2N3 < 0
12
Optimal Solution to MSA’s Marketing Research
Problem
Optimal solution shows that it costs $15,166 and
requires one to survey households as follows:
State borders Mexico and 31-50 years
=
600
State borders Mexico and  51 years
=
140
State not borders Mexico and  30 years = 1,000
State not borders Mexico and  51 years = 560
13
3.3 Manufacturing Applications
Production Mix Problem
Fifth Avenue Industries
• Nationally known menswear manufacturer.
• Produces four varieties of neckties.
– All-silk tie.
– All-polyester tie.
– Two different polyester and cotton blends.
• Has fixed contracts with major department stores.
– Table 3.1 summarizes contract demand for products.
14
3.3 Manufacturing Applications
Fifth Avenue Industries
Table 3.1 Data for Fifth Avenue Industries
Tie
Price
Monthly
Contract
Monthly
Demand
Material
Required
Material
Silk
6,000
10,000
7,000
14,000
0.125
0.08
Silk
Polyester
6.70
3.55
Polyester
Poly-Cotton
4.31
13,000
16,000
0.10
50%-50%
4.81
6,000
8,500
0.10
30%-70%
Blend 1
Poly-Cotton
Blend 2
15
Profit Per Unit
Fifth Avenue Industries
For each all-silk tie • Cost per tie = 0.125 yards of silk x $21 per yard =
$2.625.
• Revenue per tie = $6.70 selling price per silk tie.
• Profit per tie = Revenue per tie - Cost per tie =
$6.70 - $2.625 = $4.075.
Profit for other three products • Profit per all-polyester tie = $3.07.
• Profit per Blend - 1 poly-cotton tie = $3.56.
• Profit per Blend - 2 poly-cotton tie = $4.00.
16
Objective Function
Fifth Avenue Industries
Objective: maximize profit menswear ties.
$4.075 S + $3.07 P + $3.56 B1 + $4.00 B2
Where:
S = number of all-silk ties produced per month.
P = number of polyester ties.
B1 = number of Blend - 1 poly-cotton ties.
B2 = number of Blend - 2 poly-cotton ties.
17
Objective Function and Constraints
Objective: maximize profit =
$4.075 S + $3.07 P + $3.56 B1 + $4.00 B2
Subject to
0.125 S 1  800
(Yards of silk)
0.08 P2  0.05 B1  0.03 B 2  3, 000
(Yards of polyester)
0.05 B1  0.07 B 2  1, 600
(Yards of cotton)
S  6, 000
(Contract minimum for all silk)
S  7, 000
(Market maximum)
P  10, 000
(Contract minimum for all polyester)
P  14, 000
(Market maximum)
18
0 .0 5 B1  0 .0 7 B 2  1, 6 0 0
6, 0 0 0
S Objective
Function and Constraints
Objective:
maximize
profit
=
S  7, 000
$4.075 S + $3.07 P + $3.56 B1 + $4.00 B2
P  1 0, 0 0 0
Subject to
P  1 4, 0 0 0
Constraints - Continued
B1  1 3, 0 0 0
(Contract minimum Blend 1)
B1  1 6, 0 0 0
(Market maximum)
B 2  6, 0 0 0
(Contract minimum Blend 2)
B 2  8, 5 0 0
(Market maximum)
S , P , B1 , B 2  0
19
3.4 Employee Scheduling Application
Labor Planning
Problem
Time period
Hong Kong Bank now
employs 12 full-time
tellers.
Part-time employee
(four hours per day)
are available.
9 a.m. – 10a.m.
10
10 a.m.–11a.m.
12
11 a.m. – noon
14
Noon – 1 p.m.
16
1 p.m. – 2 p.m.
18
2 p.m. – 3 p.m.
17
3 p.m. – 4 p.m.
15
4 p.m. – 5 p.m.
10
Tellers requirements:
# of Tellers
Required
20
Employee Scheduling Application
Hong Kong Bank
Labor Constraints:
• Full-timers work from 9 A.M. to 5 P.M.
– Allowed 1 hour for lunch.
– Half of full-timers eat at 11 A.M. and other half at noon.
– Full-timers thus provide 35 hours per week of productive
labor time.
• Part-time hours limited to a maximum of 50% of
day’s total requirement.
Costs:
• Part-timers earn $4 per hour (or $16 per day) on
average.
21
• Full-timers earn $50 per day in salary and benefits,
Employee Scheduling Application
Hong Kong Bank
Decision Variables:
F = full-time tellers
P1 = part-timers starting at 9 A.M. (leaving at 1 P.M.)
P2 = part-timers starting at 10 A.M. (leaving at 2 P.M.)
P3 = part-timers starting at 11 A.M. (leaving at 3 P.M.)
P4 = part-timers starting at noon (leaving at 4 P.M.)
P5 = part-timers starting at 1 P.M. (leaving at 5 P.M.)
22
Hong Kong Bank LP Formulation
Objective: minimize total daily labor cost
$50 F + $16 ( P1 + P2 + P3 + P4 )
Subject to
F  P1  1 0
(9 A.M. - 10 A.M. needs)
F  P1  P2  1 2
(10 A.M. - 11 A.M. needs)
0 .5 F  P1  P2  P3  1 4
(11 A.M. - noon needs)
0 .5 F  P1  P2  P3  P4  1 6
(noon - 1 P.M. needs)
F  P2  P3  P4  P5  1 8
(1 P.M. - 2 P.M. needs)
F  P3  P4  P5  1 7
(2 P.M. - 3 P.M. needs)
F  P4  P5  1 5
(3 P.M. - 4 P.M. needs)
F  P5  1 0
(4 P.M. - 5 P.M. needs)
F  12
23
(full-time tellers available)
Hong Kong Bank LP Formulation
Constraints (Continued):
• Part-time worker hours cannot exceed 50% total hours
required each day, which is sum of tellers needed each hour.
4( P1  P2  P3  P4  P5 )  0.50(10  12  14  16  18  17  15  10)
Simplifying yields,
4 P1  4 P2  4 P3  4 P4  4 P5  0 .0 5 0 (1 1 2 )
F , P1 , P2 , P3 , P4 , P5  0
24
Hong Kong Bank Solution
• Excel entries for model reveal optimal
solution.
– Employ 10 full-time tellers.
– 7 part-time tellers at 10 A.M.
– 2 part-time tellers at 11 A.M.
– 5 part-time tellers at noon.
– Total cost of $724 per day.
• There are several alternate optimal solutions.
25
Hong Kong Bank Solution
• There are several alternate optimal solutions.
– In practice sequence in which constraints are
listed in model may affect specific solution found.
– One alternate solution.
• Employ 10 full-time tellers.
• 6 part-time tellers at 9 A.M.
• 1 part-time teller at 10 A.M.
• 2 part-time teller at 11 A.M.
• 5 part-time tellers at noon.
• Total cost of this policy is also $724.
26
3.5 Financial Applications
Portfolio Selection
International City Trust (ICT) invests in short-term
trade credits, corporate bonds, gold stocks,
and construction loans.
ICT has $5 million available for immediate
investment and wishes to do two things:
(1) maximize interest earned on investments
made over next six months and
(2) satisfy diversification requirements as set by
board of directors.
27
Portfolio Specification
International City Trust
Investment Possibilities:
Interest Maximum Investment
($millions)
Earned (%)
Trade credit
7
1
Corporate Bonds
11
2.5
Gold stocks
19
1.5
Construction Loan
15
1.8
Investment
• Board specifies at least 55% of funds invested must be in
gold stocks and construction loans.
• No less than 15% be invested in trade credit.
28
Investment Formulation
International City Trust
Decision Variables:
T = dollars invested in trade credit
B = dollars invested in corporate bonds
G = dollars invested in gold stocks
C = dollars invested in construction loans
29
Investment Formulation
International City Trust
Objective: maximize investment interest dollars earned.
0.07 T + 0.11 B + 0.19 G + 0.15 C
Subject to
T  1, 0 0 0, 0 0 0
B  2, 5 0 0, 0 0 0
G  1, 5 0 0, 0 0 0
C  1, 8 0 0, 0 0 0
G  C  0 .5 5  T  B  G  C
T  0 .1 5  T  B  G  C


T  B  G  C  5, 0 0 0, 0 0 0
T , B,G,C  0
30
Rewriting Last Two Constraints
G + C > 0.55(T + B + G +C )
Rewritten as:
-0.55T - 0.55B + 0.45G + 0.45C  0
Gold stock
And
T > 0.15 (T + B + G +C )
Rewritten as:
0.85T - 0.15B - 0.15G - 0.15C  0
Trade credit
31
3.6 Transportation Applications
Truck Loading Problem
Truck loading problem involves deciding which items
to load on a truck so as to maximize value of a load
shipped.
Consider Goodman Shipping.
• One truck with a capacity of 10,000 pounds is next
to be loaded.
• Several other items are awaiting shipment.
• Each items awaiting shipment has associated dollar
value and weight.
• Objective - maximize total value of items loaded on
truck without exceeding truck’s weight capacity.
32
Transportation Applications
Goodman Shipping
Items Awaiting Shipment:
Item
1
2
3
4
5
6
Total Value ($)
22,500
24,000
8,000
9,500
11,500
9,750
Weight (Pounds)
7,500
7,500
3,000
3,500
4,000
3,500
33
Goodman Shipping LP Formulation
Objective: maximize load value =
$22,500 P1+ $24,000 P2 + $8,000 P3 + $9,500 P4 +
$11,500 P5 + $9,7500 P6
Subject to
7,500 P1 + 7,500 P2+ 3,000 P3+ 3,500 P4 + 4,000 P5 + 3,000 P6
< 10,000
P1 < 1
P2 < 1
P3 < 1
P4 < 1
P5 < 1
P6 < 1
P1, P2, P3, P4, P5, P6 > 0
Where: Pi is proportion of each item i loaded on truck.
34
Goodman Shipping Problem
Using Pounds – Not Proportions
• Formulate alternate model for problem.
• Decision variables in model are weights in pounds
shipped, rather than proportion.
• Layout for model is identical to model shown
previously.
• Solution to model shows maximum load value is
$31,500.
• Load value achieved by shipping 2,500 pounds
(0.33 of 7,500 pounds available item 1) and 7,500
pounds (all 7,500 pounds available item 2).
35
3.7 Ingredient Blending Applications
Diet Problems
Diet problem involves specifying a food or food
ingredient combination that satisfies stated
nutritional requirements at minimum cost.
• Whole Food Nutrition Center uses three bulk
grains to blend natural cereal that sells by the
pound.
• Each 2-ounce serving of cereal, when taken
with 1.2 cup of whole milk, meets an average
adult’s minimum daily requirement for protein,
riboflavin, phosphorus, and magnesium.
36
3.7 Ingredient Blending Applications
Whole Food Nutrition Center
Diet Problems
• Minimum adult daily requirement:
– Protein 3 units.
– Riboflavin 2 units.
– Phosphorus 1 unit.
– Magnesium 0.425 unit.
• Select blend of grains to meet USRDA at
minimum cost.
37
Whole Food’s Natural Cereal
Requirements
Grain Cost per Protein Riboflavin Phosphorus Magnesium
pound
(unit/lb)
(unit/lb)
(unit/lb)
(unit/lb)
22
28
21
16
14
25
8
7
9
5
0
6
(cents)
A
B
C
33
47
38
Decision Variables:
A = pounds of grain in one 2-ounce cereal serving.
B = pounds of grain in one 2-ounce cereal serving.
C = pounds of grain in one 2-ounce cereal serving.
38
Whole Food’s LP Formulation
Objective: minimize total cost of mixing 2ounce serving =
$0.33 A + $0.47 B + $0.38 C
Subject to
22 A  28 B  21C  3
(Protein units)
16 A  14 B  25 C  2
(Riboflavin units)
8 A  7 B  9C  1
(Phosphorous units)
5 A  0 B  6 C  0.425
(Magnesium units)
A  B  C = 0.125
(Total mix 2 ounces or
0.125 pound)
A, B , C  0
39
Ingredient Blending Applications
Ingredient Mix and Blending Problems
Blending problems arise when decision must be
made regarding blending of two or more products
to produce one or more products. Resources
contain one or more essential ingredients that
must be blended so each final product contains
specific percentages of each ingredient.
Example • Deals with application frequently seen in petroleum
industry.
• Blending crude oils to produce refinable gasoline.
40
Blending Problem Example
Low Knock Oil Company
• Low Knock Oil Company produces two grades
of cut-rate gasoline for industrial distribution.
– Regular.
– Economy.
• Produced by refining a blend of two types of
crude oil.
– Type X100.
– Type X220.
41
Blending Problem Example
• Weekly demand for Regular at least 25,000
barrels.
• Weekly demand for Economy grade at least
32,000 barrels.
• At least 45% of each barrel of regular must be
ingredient A.
• At most 50% of each barrel of economy
should contain ingredient B.
42
Blending Problem Example
Decision Variables:
R1 = barrels crude oil X100 blended to produce refined
Regular.
E1 = barrels crude oil X100 blended to produce refined
Economy.
R2 = barrels crude oil X220 blended to produce refined
Regular.
E2 = barrels crude oil X100 blended to produce refined
Economy.
43
Blending Problem Example
Low Knock Oil Company
Product Type Ingredients and Costs
Crude Oil
Type
Ingredient
A (%)
Ingredient Cost/Barrel
B (%)
($)
X100
35
55
30.00
X220
60
25
34.80
44
Low Knock Oil Company LP Formulation
Two More Requirements FIRST requirement:
• At least 45% of each barrel of Regular must be
ingredient A.
– (R1+R2) is amount of crude blended to produce
refined Regular gasoline demanded.
– 0.45 (R1+R2) is minimum amount of A required.
– 0.35 R1+0.60 R2 is amount of A in regular gas.
0.35 R1+0.60 R2 > 0.45 (R1+R2)
– Recalculate as:
-0.10 R1+0.15 R2 > 0
45
Low Knock Oil Company LP Formulation
Two More Requirements SECOND requirement:
• At most 50% of each barrel of economy must be
ingredient B.
– (E1 + E2) is amount of crude blended to produce
refined Economy gasoline demanded.
– 0.50 (E1+E2) is maximum amount of ingredient B
allowed.
– 0.55 E1+ 0.25 E2 = amount of ingredient B in
Economy gas
0.55E1+0.25E2 < 0.50(E1+E2)
– Recalculating yields:
0.05E1 – 0.25E2 < 0
46
LP Problem Formulation
Low Knock Oil Company
Objective: minimize cost =
$30 R1 + $30 E1 + $34.80 R2 + $34.80 E2
Subject to
R1  R 2  25, 000
(Demand for Regular)
E 1  E 2  32, 000
(Demand for Economy)
 0.10 R1  0.15 R 2  0
(Ingredient A in Regular)
0.05 E 1  0.25 E 2  0
(Ingredient B in Economy)
R1, E 1, R 2 , E 2  0
47
Summary
• Continued discussion of LP models.
• More experience in formulating and solving problems
from variety of disciplines and applications:
– Marketing, manufacturing, employee scheduling,
– Finance, transportation, ingredient blending.
• Illustrated setup and solution of models using Excel’s
Solver add-in.
48