Managerial Decision Modeling with Spreadsheets - PowerPoint PPT Presentation

1 / 48
About This Presentation
Title:

Managerial Decision Modeling with Spreadsheets

Description:

Title: Managerial Decision Modeling with Spreadsheets Author: Isaac Gottlieb, Ph.D Description: isaacg_at_andromeda.rutgers.edu Last modified by: Tung Liu – PowerPoint PPT presentation

Number of Views:172
Avg rating:3.0/5.0
Slides: 49
Provided by: IsaacGot5
Category:

less

Transcript and Presenter's Notes

Title: 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.
Write a Comment
User Comments (0)
About PowerShow.com