Managerial Decision Modeling with Spreadsheets

1
Managerial Decision Modeling with Spreadsheets

• Chapter 3
• Linear Programming Modeling Applications With
  Computer Analyses in Excel

2
Learning Objectives

• Model wide variety of linear programming (LP)
  problems.
• Understand major business application areas for
  LP problems manufacturing, marketing, labor
  scheduling, blending, transportation, finance,
  and multi-period planning.
• Gain experience in setting up and solving LP
  problems using Excels Solver.

3
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.

4
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 Bigs goal - reach largest possible
  high-potential audience through various media.

5
Media Selection Data
Audience Reached Per Ad Cost Per Ad(s) Maximum Ads Per Week
TV spot (1minute) 5,000 800 12
Daily newspaper (full page) 8,500 925 5
Radio spot (30 sec, prime time) 2,400 290 25
Radio spot (1 min, afternoon) 2,800 380 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.

6
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.

7
Media Selection Data LP Formulation

• Objective maximize audience coverage
• 5000 T 8500 N 2400 P 2800 A
• Subject to

8
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.

9
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.

10
MSAs 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.

11
MSAs 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

12
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 lt 0

13
Optimal Solution to MSAs 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

14
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.

15
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.70 6,000 7,000 0.125 Silk
Polyester 3.55 10,000 14,000 0.08 Polyester
Poly-Cotton Blend 1 4.31 13,000 16,000 0.10 50-50
Poly-Cotton Blend 2 4.81 6,000 8,500 0.10 30-70
16
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.

17
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.

18
Objective Function and Constraints
Objective maximize profit 4.075
S 3.07 P 3.56 B1 4.00 B2 Subject to
(Yards of silk) (Yards of polyester) (Yards of
cotton)

(Contract minimum for all silk) (Market
maximum) (Contract minimum for all
polyester) (Market maximum)
19
Objective Function and Constraints
Objective maximize profit
4.075 S 3.07 P 3.56 B1 4.00 B2
Subject to Constraints - Continued

(Contract minimum Blend 1) (Market
maximum) (Contract minimum Blend 2) (Market
maximum)
20
3.4 Employee Scheduling Application
Time period of Tellers Required
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

• Labor Planning Problem
• Hong Kong Bank now employs 12 full-time
  tellers. Part-time employee (four hours per
  day) are available.
• Tellers requirements

21
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
  days total requirement.
• Costs
• Part-timers earn 4 per hour (or 16 per day) on
  average.
• Full-timers earn 50 per day in salary and
  benefits, on average.

22
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.)

23
Hong Kong Bank LP Formulation

• Objective minimize total daily labor cost
• 50 F 16 ( P1 P2 P3 P4 )
• Subject to

(9 A.M. - 10 A.M. needs) (10 A.M. - 11 A.M.
needs) (11 A.M. - noon needs) (noon - 1 P.M.
needs) (1 P.M. - 2 P.M. needs) (2 P.M. - 3 P.M.
needs) (3 P.M. - 4 P.M. needs) (4 P.M. - 5 P.M.
needs) (full-time tellers available)
24
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.
• Simplifying yields,

25
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.

26
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.

27
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
• maximize interest earned on investments made over
  next six months and
• satisfy diversification requirements as set by
  board of directors.

28
Portfolio Specification

• International City Trust
• Investment Possibilities

Investment Interest Earned () Maximum Investment (millions)
Trade credit 7 1
Corporate Bonds 11 2.5
Gold stocks 19 1.5
Construction Loan 15 1.8

• 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.

29
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

30
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

31
Rewriting Last Two Constraints

• G C gt 0.55(T B G C )
• Rewritten as
• -0.55T - 0.55B 0.45G 0.45C ? 0 Gold stock
• And
• T gt 0.15 (T B G C )
• Rewritten as
• 0.85T - 0.15B - 0.15G - 0.15C ? 0
  Trade credit

32
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 trucks weight
  capacity.

33
Transportation Applications

• Goodman ShippingItems Awaiting Shipment

Item Total Value () Weight (Pounds)
1 22,500 7,500
2 24,000 7,500
3 8,000 3,000
4 9,500 3,500
5 11,500 4,000
6 9,750 3,500
34
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 lt 10,000
• P1 lt 1
• P2 lt 1
• P3 lt 1
• P4 lt 1
• P5 lt 1
• P6 lt 1
• P1, P2, P3, P4, P5, P6 gt 0
• Where Pi is proportion of each item i loaded
  on truck.

35
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).

36
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 adults
  minimum daily requirement for protein,
  riboflavin, phosphorus, and magnesium.

37
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.

38
Whole Foods Natural Cereal Requirements
Grain Cost per pound (cents) Protein (unit/lb) Riboflavin (unit/lb) Phosphorus (unit/lb) Magnesium (unit/lb)
A 33 22 16 8 5
B 47 28 14 7 0
C 38 21 25 9 6
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.
39
Whole Foods LP Formulation

• Objective minimize total cost of mixing 2-ounce
  serving
• 0.33 A 0.47 B 0.38 C
• Subject to

(Protein units) (Riboflavin units) (Phosphorous
units) (Magnesium units) (Total mix 2 ounces or
0.125 pound)
40
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.

41
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.

42
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.

43
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.

44
Blending Problem Example

• Low Knock Oil Company
• Product Type Ingredients and Costs

Crude Oil Type Ingredient A () Ingredient B () Cost/Barrel ()
X100 35 55 30.00
X220 60 25 34.80
45
Low Knock Oil Company LP Formulation

• Two More Requirements -
• FIRST requirement
• At least 45 of each barrel of Regular must be
  ingredient A.
• (R1R2) is amount of crude blended to produce
  refined Regular gasoline demanded.
• 0.45 (R1R2) is minimum amount of A required.
• 0.35 R10.60 R2 is amount of A in regular gas.
• 0.35 R10.60 R2 gt 0.45 (R1R2)
• Recalculate as
• -0.10 R10.15 R2 gt 0

46
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 (E1E2) is maximum amount of ingredient B
  allowed.
• 0.55 E1 0.25 E2 amount of ingredient B in
  Economy gas
• 0.55E10.25E2 lt 0.50(E1E2)
• Recalculating yields
• 0.05E1 0.25E2 lt 0

47
LP Problem Formulation
Low Knock Oil Company
Objective minimize cost 30 R1 30 E1
34.80 R2 34.80 E2 Subject to
(Demand for Regular) (Demand for
Economy) (Ingredient A in Regular) (Ingredient B
in Economy)
48
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
  Excels Solver add-in.