570 Quantitative Business Analysis, homework 5 wk 6
kentucky79Linear Programming Applications
8
To accompany Quantitative Analysis for Management, Twelfth Edition,
by Render, Stair, Hanna and Hale
Power Point slides created by Jeff Heyl
Copyright ©2015 Pearson Education, Inc.
After completing this chapter, students will be able to:
LEARNING OBJECTIVES
Copyright ©2015 Pearson Education, Inc.
8 – 2
Model a wide variety of medium to large LP problems.
Understand major application areas, including marketing, production, labor scheduling, fuel blending, transportation, and finance.
Gain experience in solving LP problems with Excel Solver software.
Copyright ©2015 Pearson Education, Inc.
8 – 3
8.1 Introduction
8.2 Marketing Applications
8.3 Manufacturing Applications
8.4 Employee Scheduling Applications
8.5 Financial Applications
8.6 Ingredient Blending Applications
8.7 Other Linear Programming Applications
CHAPTER OUTLINE
Introduction
The graphical method of LP is useful for understanding how to formulate and solve small LP problems
Many types of problems can be solved using LP
Principles developed here are applicable to larger problems
Copyright ©2015 Pearson Education, Inc.
8 – 4
Marketing Applications
Linear programming models have been used in the advertising field as a decision aid in selecting an effective media mix
Media selection LP problems can be approached from two perspectives
Maximize audience exposure
Minimize advertising costs
Copyright ©2015 Pearson Education, Inc.
8 – 5
Win Big Gambling Club
Club promotes gambling junkets to the Bahamas
$8,000 per week to spend on advertising
Goal is to reach the largest possible high-potential audience
Media types and audience figures shown below
Place at least five radio spots per week
No more than $1,800 can be spent on radio advertising each week
Copyright ©2015 Pearson Education, Inc.
8 – 6
Win Big Gambling Club
Advertising options
Copyright ©2015 Pearson Education, Inc.
8 – 7
MEDIUM | AUDIENCE REACHED PER AD | COST PER AD ($) | MAXIMUM ADS PER WEEK |
TV spot (1 minute) | 5,000 | 800 | 12 |
Daily newspaper (full-page ad) | 8,500 | 925 | 5 |
Radio spot (30 seconds, prime time) | 2,400 | 290 | 25 |
Radio spot (1 minute, afternoon) | 2,800 | 380 | 20 |
Win Big Gambling Club
Problem formulation
Copyright ©2015 Pearson Education, Inc.
8 – 8
X1 = number of 1-minute TV spots taken each week
X2 = number of daily newspaper ads taken each week
X3 = number of 30-second prime-time radio spots taken each week
X4 = number of 1-minute afternoon radio spots taken each week
Objective:
Maximize audience coverage = 5,000X1 + 8,500X2 + 2,400X3 + 2,800X4
Subject to X1 ≤ 12 (max TV spots/wk)
X2 ≤ 5 (max newspaper ads/wk)
X3 ≤ 25 (max 30-sec radio spots/wk)
X4 ≤ 20 (max 1-min radio spots/wk)
800X1 + 925X2 + 290X3 + 380X4 ≤ $8,000 (weekly advertising budget)
X3 + X4 ≥ 5 (min radio spots contracted)
290X3 + 380X4 ≤ $1,800 (max dollars spent on radio)
X1, X2, X3, X4 ≥ 0
X1 = number of 1-minute TV spots taken each week
X2 = number of daily newspaper ads taken each week
X3 = number of 30-second prime-time radio spots taken each week
X4 = number of 1-minute afternoon radio spots taken each week
Objective:
Maximize audience coverage = 5,000X1 + 8,500X2 + 2,400X3 + 2,800X4
Subject to X1 ≤ 12 (max TV spots/wk)
X2 ≤ 5 (max newspaper ads/wk)
X3 ≤ 25 (max 30-sec radio spots/wk)
X4 ≤ 20 (max 1-min radio spots/wk)
800X1 + 925X2 + 290X3 + 380X4 ≤ $8,000 (weekly advertising budget)
X3 + X4 ≥ 5 (min radio spots contracted)
290X3 + 380X4 ≤ $1,800 (max dollars spent on radio)
X1, X2, X3, X4 ≥ 0
Problem formulation
Win Big Gambling Club
Copyright ©2015 Pearson Education, Inc.
8 – 9
Solution
X1 = 1.97 TV spots
X2 = 5 newspaper ads
X3 = 6.2 30-second radio spots
X4 = 0 1-minute radio spots
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 10
PROGRAM 8.1 – Win Big Solution
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 11
PROGRAM 8.1 – Win Big Solution
Solver Parameter Inputs and Selections | Key Formulas | |
Set Objective: F6 By Changing cells: B5:E5 To: Max Subject to the Constraints: F9:F14 <= H9:H14 F15 >= H15 Solving Method: Simplex LP R Make Variables Non-Negative | Copy F6 to F9:F15 |
Management Sciences Association
MSA is a marketing research firm
Several requirements for a statistical validity
Survey at least 2,300 U.S. households
Survey at least 1,000 households whose heads are ≤ 30 years old
Survey at least 600 households whose heads are between 31 and 50
Ensure that at least 15% of those surveyed live in a state that borders Mexico
Ensure that no more than 20% of those surveyed who are 51 years of age or over live in a state that borders Mexico
Copyright ©2015 Pearson Education, Inc.
8 – 12
Management Sciences Association
MSA decides to conduct all surveys in person
Estimates of the costs of reaching people in each age and region category
Goal is to meet the sampling requirements at the least possible cost
Copyright ©2015 Pearson Education, Inc.
8 – 13
COST PER PERSON SURVEYED ($) | |||
REGION | AGE ≤ 30 | AGE 31-50 | AGE ≥ 51 |
State bordering Mexico | $7.50 | $6.80 | $5.50 |
State not bordering Mexico | $6.90 | $7.25 | $6.10 |
Management Sciences Association
Decision variables
Copyright ©2015 Pearson Education, Inc.
8 – 14
X1 = number of 30 or younger and in a border state
X2 = number of 31-50 and in a border state
X3 = number 51 or older and in a border state
X4 = number 30 or younger and not in a border state
X5 = number of 31-50 and not in a border state
X6 = number 51 or older and not in a border state
Management Sciences Association
Copyright ©2015 Pearson Education, Inc.
8 – 15
Objective function
subject to
X1 + X2 + X3 + X4 + X5 + X6 ≥ 2,300 (total households)
X1 + X4 ≥ 1,000 (households 30 or younger)
X2 + X5 ≥ 600 (households 31-50)
X1 + X2 + X3 ≥ 0.15(X1 + X2+ X3 + X4 + X5 + X6)
(border states)
X3 ≤ 0.20(X3 + X6) (limit on age group
51+ who can live
in border state)
X1, X2, X3, X4, X5, X6 ≥ 0
Minimize total interview costs
= $7.50X1 + $6.80X2 + $5.50X3
+ $6.90X4 + $7.25X5 + $6.10X6
Management Sciences Association
Optimal solution will cost $15,166
Copyright ©2015 Pearson Education, Inc.
8 – 16
REGION | AGE ≤ 30 | AGE 31-50 | AGE ≥ 51 |
State bordering Mexico | 0 | 600 | 140 |
State not bordering Mexico | 1,000 | 0 | 560 |
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 17
PROGRAM 8.2 – MSA Solution
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 18
PROGRAM 8.2 – MSA Solution
Solver Parameter Inputs and Selections | Key Formulas | |
Set Objective: H5 By Changing cells: B4:G4 To: Min Subject to the Constraints: H8:H11 <= J8:J11 H12 >= J12 Solving Method: Simplex LP R Make Variables Non-Negative | Copy H5 to H8:H12 |
Manufacturing Applications
Production Mix
LP can be used to plan the optimal mix of products to manufacture
Company must meet a myriad of constraints
Financial concerns
Sales demand
Material contracts
Union labor demands
Primary goal is to generate the largest profit possible
Copyright ©2015 Pearson Education, Inc.
8 – 19
Fifth Avenue Industries
Produces four varieties of ties
Expensive all-silk
All-polyester
Two are polyester-cotton or silk-cotton blends
Cost and availability of the three materials used in the production process
Copyright ©2015 Pearson Education, Inc.
8 – 20
MATERIAL | COST PER YARD ($) | MATERIAL AVAILABLE PER MONTH (YARDS) |
Silk | 24 | 1,200 |
Polyester | 6 | 3,000 |
Cotton | 9 | 1,600 |
Fifth Avenue Industries
The firm has contracts with several major department store chains
Contracts require a minimum number of ties
May be increased if demand increases
Goal is to maximize monthly profit
Decision variables
Copyright ©2015 Pearson Education, Inc.
8 – 21
X1 = number of all-silk ties produced per month
X2 = number all-polyester ties
X3 = number of blend 1 polyester-cotton ties
X4 = number of blend 2 silk-cotton ties
Fifth Avenue Industries
Copyright ©2015 Pearson Education, Inc.
8 – 22
VARIETY OF TIE | SELLING PRICE PER TIE ($) | MONTHLY CONTRACT MINIMUM | MONTHLY DEMAND | MATERIAL REQUIRED PER TIE (YARDS) | MATERIAL REQUIREMENTS |
All silk | 19.24 | 5,000 | 7,000 | 0.125 | 100% silk |
All polyester | 8.70 | 10,000 | 14,000 | 0.08 | 100% polyester |
Poly-cotton blend 1 | 9.52 | 13,000 | 16,000 | 0.10 | 50% polyester – 50% cotton |
Silk-cotton blend 2 | 10.64 | 5,000 | 8,500 | 0.11 | 60% silk – 40% cotton |
TABLE 8.1 – Data for Fifth Avenue
Fifth Avenue Industries
Establish profit per tie
Copyright ©2015 Pearson Education, Inc.
8 – 23
SILK REQ’D | COST | POLY-ESTER REQ’D | COST | COTTON REQ’D | COST | MATERIAL COST | SELLING PRICE | PROFIT | |||
All-silk X1 | |||||||||||
0.125 | $24.00 | $3.00 | $19.24 | $16.24 | |||||||
All-polyester X2 | |||||||||||
0.08 | $6 | $0.48 | $8.70 | $8.22 | |||||||
Poly-cotton blend X3 | |||||||||||
0.05 | $6 | 0.05 | $9 | $0.75 | $9.52 | $8.77 | |||||
Silk-cotton blend X4 | |||||||||||
0.066 | $24.00 | 0.044 | $9 | $1.98 | $10.64 | $8.66 |
Fifth Avenue Industries
Copyright ©2015 Pearson Education, Inc.
8 – 24
Objective function
Maximize profit = $16.24X1 + $8.22X2 + $8.77X3 + $8.66X4
Subject to 0.125X1 + 0.066X4 ≤ 1200 (yds of silk)
0.08X2 + 0.05X3 ≤ 3,000 (yds of polyester)
0.05X3 + 0.44X4 ≤ 1,600 (yds of cotton)
X1 ≥ 5,000 (contract min for silk)
X1 ≤ 7,000 (contract min)
X2 ≥ 10,000 (contract min for all polyester)
X2 ≤ 14,000 (contract max)
X3 ≥ 13,000 (contract min for blend 1)
X3 ≤ 16,000 (contract max)
X4 ≥ 5,000 (contract min for blend 2)
X4 ≤ 8,500 (contract max)
X1, X2, X3, X4 ≥ 0
Fifth Avenue Industries
Optimal solution will result in a profit of $412,028 per month
Copyright ©2015 Pearson Education, Inc.
8 – 25
TIE | QUANTITY PER MONTH |
All-Silk | 5,112 |
All-Polyester | 14,000 |
Poly-Silk | 16,000 |
Silk-Cotton | 8,500 |
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 26
PROGRAM 8.3 – Fifth Avenue Solution
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 27
PROGRAM 8.3 – Fifth Avenue Solution
Solver Parameter Inputs and Selections | Key Formulas | |
Set Objective: F6 By Changing cells: B5:E5 To: Max Subject to the Constraints: F9:F15 <= H9:H15 F16:F19 >= H16:H19 Solving Method: Simplex LP R Make Variables Non-Negative | Copy F6 to F9:F19 |
Manufacturing Applications
Production Scheduling
Low-cost production schedule
Period of weeks or months
Important factors include
Labor capacity
Inventory and storage costs
Space limitations
Product demand
Labor relations
With more than one product, the scheduling process can be quite complex
The problem resembles the product mix model for each time period in the future
Copyright ©2015 Pearson Education, Inc.
8 – 28
Greenberg Motors
Manufactures two different electric motors for sale under contract to Drexel Corp
Orders placed three times a year for four months at a time
Demand varies month to month
Develop a production plan for the next four months
Copyright ©2015 Pearson Education, Inc.
8 – 29
MODEL | JANUARY | FEBRUARY | MARCH | APRIL |
GM3A | 800 | 700 | 1,000 | 1,100 |
GM3B | 1,000 | 1,200 | 1,400 | 1,400 |
TABLE 8.2 – Four-Month Order Schedule
Greenberg Motors
Production planning must consider four factors
Produce the required number of motors each month and ensure the desired ending inventory
Desire to keep inventory carrying costs down
No-lay-off policy, minimize fluctuations in production levels
Warehouse limitations
Copyright ©2015 Pearson Education, Inc.
8 – 30
Greenberg Motors
Basic data
Copyright ©2015 Pearson Education, Inc.
8 – 31
MOTOR | ENDING INV | CARRYING COST | LABOR HRS REQ’D | PRODUCTION COST PER UNIT |
GM3A | 450 | $0.36 | 1.3 | $20 |
GM3B | 300 | $0.26 | 0.9 | $15 |
2,240 ≤ Desired labor hrs per month ≤ 2,560 | ||||
Maximum total inventory space available = 3,300 units | ||||
Labor cost increases 10% March 1 |
Greenberg Motors
Model formulation
Copyright ©2015 Pearson Education, Inc.
8 – 32
Objective
Minimize total cost (production plus inventory carrying cost)
Constraints
4 demand constraints (1 constraint for each of 4 months) for GM3A
4 demand constraints (1 constraint for each of 4 months) for GM3B
2 constraints (1 for GM3A and 1 for GM3B) for the inventory at the end of April
4 constraints for minimum labor hours (1 constraint for each month)
4 constraints for maximum labor hours (1 constraint for each month)
4 constraints for inventory storage capacity each month
Greenberg Motors
Objective function – production costs
Copyright ©2015 Pearson Education, Inc.
8 – 33
Ai = | Number of model GM3A motors produced in month i (i = 1, 2, 3, 4 for January – April) |
Bi = | Number of model GM3B motors produced in month i |
Cost of production = $20A1 + $20A2 + $22A3 + $22A4
+ $15B1 + $15B2 + $16.50B3 + $16.50B4
Greenberg Motors
Objective function – inventory carrying costs
Copyright ©2015 Pearson Education, Inc.
8 – 34
IAi= | Units of GM3A left in inventory at the end of month i (i = 1, 2, 3, 4 for January – April) |
IBi= | Units of GM3B left in inventory at the end of month i (i = 1, 2, 3, 4 for January – April) |
Cost of carrying inventory = $0.36IA1 + $0.36IA2 + $0.36IA3 + 0.36IA4
+ $0.26IB1 + $0.26IB2 + $0.26IB3 + $0.26IB4
Greenberg Motors
Complete objective function
Copyright ©2015 Pearson Education, Inc.
8 – 35
Minimize costs = $20A1 + $20A2 + $22A3 + $22A4
+ $15B1 + $15B2 + $16.50B3 + $16.50B4 + $0.36IA1 + $0.36IA2 + $0.36IA3 + 0.36IA4 + $0.26IB1 + $0.26IB2 + $0.26IB3 + $0.26IB4
Greenberg Motors
End of month inventory is calculated using
Copyright ©2015 Pearson Education, Inc.
8 – 36
Inventory at the
end of
this month
Current month’s production
Sales to Drexel this month
Inventory at the
end of
last month
= + –
Rearranged to create a standard format for a constraint equation
Inventory at the
end of
last month
Current month’s production
Sales to Drexel this month
Inventory
at the
end of
this month
+ – =
Greenberg Motors
The demand constraints
Copyright ©2015 Pearson Education, Inc.
8 – 37
A1 – IA1 = 800 (demand for GM3A in Jan)
IA1 + A2 – IA2 = 700 (demand for GM3A in Feb)
IA2 + A3 – IA3 = 1,000 (demand for GM3A in Mar)
IA3 + A4 – IA4 = 1,100 (demand for GM3A in Apr)
B1 – IB1 = 1,000 (demand for GM3B in Jan)
IB1 + B2 – IB2 = 1,200 (demand for GM3B in Feb)
IB2 + B3 – IB3 = 1,400 (demand for GM3B in Mar)
IB3 + B4 – IB4 = 1,400 (demand for GM3B in Apr)
IA4 = 450 (inventory of GM3A at end of Apr)
IB4 = 300 (inventory of GM3B at end of Apr)
Greenberg Motors
The labor hour constraints
Copyright ©2015 Pearson Education, Inc.
8 – 38
1.3A1 + 0.9B1 ≥ 2,240 (min labor hrs in Jan)
1.3A2 + 0.9B2 ≥ 2,240 (min labor hrs in Feb)
1.3A3 + 0.9B3 ≥ 2,240 (min labor hrs in Mar)
1.3A4 + 0.9B4 ≥ 2,240 (min labor hrs in Apr)
1.3A1 + 0.9B1 ≤ 2,560 (max labor hrs in Jan)
1.3A2 + 0.9B2 ≤ 2,560 (max labor hrs in Feb)
1.3A3 + 0.9B3 ≤ 2,560 (max labor hrs in Mar)
1.3A4 + 0.9B4 ≤ 2,560 (max labor hrs in Apr)
Greenberg Motors
The storage constraints
Copyright ©2015 Pearson Education, Inc.
8 – 39
IA1 + IB1 | ≤ | 3,300 | (storage capacity in Jan) |
IA2 + IB2 | ≤ | 3,300 | (storage capacity in Feb) |
IA3 + IB3 | ≤ | 3,300 | (storage capacity in Mar) |
IA4 + IB4 | ≤ | 3,300 | (storage capacity in Apr) |
All variables | ≥ | 0 | (nonnegativity constraints) |
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 40
PROGRAM 8.4 – Greenberg Motors Solution
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 41
PROGRAM 8.4 – Greenberg Motors Solution
Solver Parameter Inputs and Selections | Key Formulas | |
Set Objective: F5 By Changing cells: B4:Q4 To: Min Subject to the Constraints: R19:R22 >= T19:T22 R23:R26 <= T23:T31 R28:R31 <= T28:T31 R8:R17 = T8:T17 Solving Method: Simplex LP R Make Variables Non-Negative | Copy formula in R5 to R8:R17 Copy formula in R5 to R19:R26 Copy formula in R5 to R28:R31 |
Greenberg Motors
Copyright ©2015 Pearson Education, Inc.
8 – 42
PRODUCTION SCHEDULE | JANUARY | FEBRUARY | MARCH | APRIL |
Units GM3A produced | 1,277 | 223 | 1,758 | 792 |
Units GM3B produced | 1,000 | 2,522 | 78 | 1,700 |
Inventory GM3A carried | 477 | 0 | 758 | 450 |
Inventory GM3B carried | 0 | 1,322 | 0 | 300 |
Labor hours required | 2,560 | 2,560 | 2,355 | 2,560 |
TABLE 8.3 – Solution to Greenberg Motors Problem
Total cost for this four month period is about $169,295
Complete model has 16 variables and 22 constraints
Employee Scheduling Applications
Labor Planning
Address staffing needs over a particular time
Especially useful when there is some flexibility in assigning workers that require overlapping or interchangeable talents
Copyright ©2015 Pearson Education, Inc.
8 – 43
Hong Kong Bank
Hong Kong Bank of Commerce and Industry requires between 10 and 18 tellers depending on the time of day
The bank wants a schedule that will minimize total personnel costs
Lunch time from noon to 2 pm is generally the busiest
Bank employs 12 full-time tellers, many part-time workers
Copyright ©2015 Pearson Education, Inc.
8 – 44
Hong Kong Bank
Part-time workers must put in exactly four hours per day, can start anytime between 9 am and 1 pm, and are inexpensive
Full-time workers work from 9 am to 3 pm and have 1 hour for lunch
Part-time hours are limited to a maximum of 50% of the day’s total requirements
Part-timers earn $8 per hour on average
Full-timers earn $100 per day on average
It will release one or more of its full-time tellers if it is profitable to do so
Copyright ©2015 Pearson Education, Inc.
8 – 45
Hong Kong Bank
Labor requirements
Copyright ©2015 Pearson Education, Inc.
8 – 46
TIME PERIOD | NUMBER OF TELLERS REQUIRED |
9 am – 10 am | 10 |
10 am – 11 am | 12 |
11 am – Noon | 14 |
Noon – 1 pm | 16 |
1 pm – 2 pm | 18 |
2 pm – 3 pm | 17 |
3 pm – 4 pm | 15 |
4 pm – 5 pm | 10 |
TABLE 8.4
Hong Kong Bank
Variables
Copyright ©2015 Pearson Education, Inc.
8 – 47
F = full-time tellers
P1 = part-timers starting at 9 am (leaving at 1 pm)
P2 = part-timers starting at 10 am (leaving at 2 pm)
P3 = part-timers starting at 11 am (leaving at 3 pm)
P4 = part-timers starting at noon (leaving at 4 pm)
P5 = part-timers starting at 1 pm (leaving at 5 pm)
Minimize total daily personnel cost
= $100F + $32(P1 + P2 + P3 + P4 + P5)
Objective
Hong Kong Bank
Constraints
Copyright ©2015 Pearson Education, Inc.
8 – 48
F | + P1 | ≥ | 10 | (9 am – 10 am needs) | ||||
F | + P1 | + P2 | ≥ | 12 | (11 am – noon needs) | |||
0.5F | + P1 | + P2 | + P3 | ≥ | 14 | (1 pm – 2 pm needs) | ||
0.5F | + P1 | + P2 | + P3 | + P4 | ≥ | 16 | (noon – 1 pm needs) | |
F | + P2 | + P3 | + P4 | + P5 | ≥ | 18 | (1 pm – 2 pm needs) | |
F | + P3 | + P4 | + P5 | ≥ | 17 | (2 pm – 3 pm needs) | ||
F | + P4 | + P5 | ≥ | 15 | (3 pm – 4 pm needs) | |||
F | + P5 | ≥ | 10 | (4 pm – 5 pm needs) | ||||
F | ≤ | 12 | (12 full-time tellers) | |||||
4P1 | + 4P2 | + 4P3 | + 4P4 | + 4P5 | ≤ | 0.50(112) | (max 50% part-timers) | |
F, P1, P2, P3, P4, P5 | ≥ | 0 | (nonnegativity) |
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 49
PROGRAM 8.5 – Labor Planning Solution
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 50
PROGRAM 8.5 – Labor Planning Solution
Solver Parameter Inputs and Selections | Key Formulas | |
Set Objective: H6 By Changing cells: B5:G5 To: Min Subject to the Constraints: H9:H16 >= J9:J16 H17:H18 <= J17:J18 Solving Method: Simplex LP R Make Variables Non-Negative | Copy H6 to H9:H18 |
Hong Kong Bank
Alternate solutions are possible for this problem
Each has the same total cost – $1,448/day
Copyright ©2015 Pearson Education, Inc.
8 – 51
SOLUTION 1 | SOLUTION 2 | |
Full-Time Tellers | 10 | 10 |
P1 Tellers | 0 | 6 |
P2 Tellers | 7 | 1 |
P3 Tellers | 2 | 2 |
P4 Tellers | 5 | 5 |
P5 Tellers | 0 | 0 |
Financial Applications
Portfolio Selection
Bank, investment funds, and insurance companies often have to select specific investments from a variety of alternatives
Overall objective is generally to maximize the potential return on the investment given a set of legal, policy, or risk restraints
Copyright ©2015 Pearson Education, Inc.
8 – 52
International City Trust
International City Trust (ICT) invests in short-term trade credits, corporate bonds, gold stocks, and construction loans
The board of directors has placed limits on how much can be invested in each area
INVESTMENT | INTEREST EARNED (%) | MAXIMUM INVESTMENT ($ MILLIONs) |
Trade credit | 7 | 1.0 |
Corporate bonds | 11 | 2.5 |
Gold stocks | 19 | 1.5 |
Construction loans | 15 | 1.8 |
Copyright ©2015 Pearson Education, Inc.
8 – 53
International City Trust
ICT has $5 million to invest and wants to accomplish two things
Maximize the return on investment over the next six months
Satisfy the diversification requirements set by the board
The board has also decided that at least 55% of the funds must be invested in gold stocks and construction loans and no less than 15% be invested in trade credit
Copyright ©2015 Pearson Education, Inc.
8 – 54
International City Trust
Investment possibilities
Copyright ©2015 Pearson Education, Inc.
8 – 55
INVESTMENT | INTEREST RETURN | MAXIMUM INVESTMENT ($ MILLIONs) |
Trade credit | 7% | 1.0 |
Corporate bonds | 11% | 2.5 |
Gold stocks | 19% | 1.5 |
Construction loans | 15% | 1.8 |
International City Trust
Variables
X1 = dollars invested in trade credit
X2 = dollars invested in corporate bonds
X3 = dollars invested in gold stocks
X4 = dollars invested in construction loans
Copyright ©2015 Pearson Education, Inc.
8 – 56
International City Trust
Formulation
Copyright ©2015 Pearson Education, Inc.
8 – 57
Maximize dollars of interest earned
= 0.07X1 + 0.11X2 + 0.19X3 + 0.15X4
subject to: X1 ≤ 1,000,000
X2 ≤ 2,500,000
X3 ≤ 1,500,000
X4 ≤ 1,800,000
X3 + X4 ≥ 0.55(X1 + X2 + X3 + X4)
X1 ≥ 0.15(X1 + X2 + X3 + X4)
X1 + X2 + X3 + X4 ≤ 5,000,000
X1, X2, X3, X4 ≥ 0
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 58
PROGRAM 8.6 – ICT Portfolio Solution
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 59
PROGRAM 8.6 – ICT Portfolio Solution
Solver Parameter Inputs and Selections | Key Formulas | |
Set Objective: F5 By Changing cells: B4:E4 To: Min Subject to the Constraints: F8:F11 <= H8:H11 F12:F13 >= H12:H13 F14 <= H14 Solving Method: Simplex LP R Make Variables Non-Negative | Copy F5 to F8:F14 |
International City Trust
Optimal solution
Make the following investments
X1 = $750,000
X2 = $950,000
X3 = $1,500,000
X4 = $1,800,000
Total interest earned = $712,000
Copyright ©2015 Pearson Education, Inc.
8 – 60
Truck Loading Problem
Truck Loading Problem
Deciding which items to load on a truck so as to maximize the value of a load shipped
Goodman Shipping has to ship the following six items
Copyright ©2015 Pearson Education, Inc.
8 – 61
ITEM | 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 |
Goodman Shipping
The objective is to maximize the value of items loaded into the truck
The truck has a capacity of 10,000 pounds
Decision variable
Xi = proportion of each item i loaded on the truck
Copyright ©2015 Pearson Education, Inc.
8 – 62
Goodman Shipping
Formulation
Copyright ©2015 Pearson Education, Inc.
8 – 63
Maximize load value
$22,500X1 + $24,000X2 + $8,000X3
+ $9,500X4 + $11,500X5 + $9,750X6
=
subject to
7,500X1 + 7,500X2 + 3,000X3
+ 3,500X4 + 4,000X5 + 3,500X6 ≤ 10,000 lb capacity
X1 ≤ 1
X2 ≤ 1
X3 ≤ 1
X4 ≤ 1
X5 ≤ 1
X6 ≤ 1
X1, X2, X3, X4, X5, X6 ≥ 0
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 64
PROGRAM 8.7 – Goodman Truck Loading Solution
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 65
PROGRAM 8.7 – Goodman Truck Loading Solution
Solver Parameter Inputs and Selections | Key Formulas | |
Set Objective: H5 By Changing cells: B4:G4 To: Min Subject to the Constraints: H8:H14 <= H8:H11 Solving Method: Simplex LP R Make Variables Non-Negative | Copy H5 to H8:H14 |
Goodman Shipping
Goodman Shipping raises an interesting issue
The solution calls for one third of Item 1 to be loaded on the truck
What if Item 1 cannot be divided into smaller pieces?
Rounding down leaves unused capacity on the truck and results in a value of $24,000
Rounding up is not possible since this would exceed the capacity of the truck
Using integer programming, the solution is to load one unit of Items 3, 4, and 6 for a value of $27,250
Copyright ©2015 Pearson Education, Inc.
8 – 66
Ingredient Blending Applications
Diet Problems
One of the earliest LP applications
Used to determine the most economical diet for hospital patients
This is also known as the feed mix problem
Copyright ©2015 Pearson Education, Inc.
8 – 67
Whole Food Nutrition Center
Uses three bulk grains to blend a natural cereal
Advertises that the cereal meets the U.S. Recommended Daily Allowance (USRDA) for four key nutrients
Select the blend that will meet the requirements at the minimum cost
Copyright ©2015 Pearson Education, Inc.
8 – 68
NUTRIENT | USRDA |
Protein | 3 units |
Riboflavin | 2 units |
Phosphorus | 1 unit |
Magnesium | 0.425 unit |
Whole Food Nutrition Center
Variables
XA = pounds of grain A in one 2-ounce serving of cereal
XB = pounds of grain B in one 2-ounce serving of cereal
XC = pounds of grain C in one 2-ounce serving of cereal
Copyright ©2015 Pearson Education, Inc.
8 – 69
GRAIN | COST PER POUND (CENTS) | PROTEIN (UNITS/LB) | RIBOFLAVIN (UNITS/LB) | PHOSPHOROUS (UNITS/LB) | MAGNESIUM (UNITS/LB) |
A | 33 | 22 | 16 | 8 | 5 |
B | 47 | 28 | 14 | 7 | 0 |
C | 38 | 21 | 25 | 9 | 6 |
TABLE 8.5 – Whole Food’s Natural Cereal requirements:
Whole Food Nutrition Center
Formulation
Copyright ©2015 Pearson Education, Inc.
8 – 70
Minimize total cost of mixing a 2-ounce serving
= $0.33XA + $0.47XB + $0.38XC
subject to
22XA + 28XB + 21XC ≥ 3 (protein units)
16XA + 14XB + 25XC ≥ 2 (riboflavin units)
8XA + 7XB + 9XC ≥ 1 (phosphorous units)
5XA + 0XB + 6XC ≥ 0.425 (magnesium units)
XA + XB + XC = 0.125 (total mix)
XA, XB, XC ≥ 0
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 71
PROGRAM 8.8 – Whole Food Diet Solution
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 72
PROGRAM 8.8 – Whole Food Diet Solution
This solution is in pounds of grain
Expressed as ounces/serving, the optimal mix is:
0.4 oz Grain A
0.8 oz Grain B
0.8 oz Grain C
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 73
PROGRAM 8.8 – Whole Food Diet Solution
Solver Parameter Inputs and Selections | Key Formulas | |
Set Objective: E6 By Changing cells: B5:D5 To: Min Subject to the Constraints: E9:E12 >= G9:G12 E13 = G13 Solving Method: Simplex LP R Make Variables Non-Negative | Copy E6 to E9:E13 |
Ingredient Blending Applications
Ingredient Mix and Blending Problems
Diet and feed mix problems are special cases of a more general class of problems known as ingredient or blending problems
Blending problems arise when decisions must be made regarding the blending of two or more resources to produce one or more product
Resources may contain essential ingredients that must be blended so that a specified percentage is in the final mix
Copyright ©2015 Pearson Education, Inc.
8 – 74
Low Knock Oil Company
Company produces two grades of cut-rate gasoline for industrial distribution
Regular and economy grades created by blending two different types of crude oil
The crude oil differs in cost and in its content of crucial ingredients
Copyright ©2015 Pearson Education, Inc.
8 – 75
CRUDE OIL TYPE | INGREDIENT A (%) | INGREDIENT B (%) | COST/BARREL ($) |
X100 | 35 | 55 | 30.00 |
X220 | 60 | 25 | 34.80 |
Low Knock Oil Company
Variables
Copyright ©2015 Pearson Education, Inc.
8 – 76
X1 = barrels of crude X100 blended to produce the refined regular
X2 = barrels of crude X100 blended to produce the refined economy
X3 = barrels of crude X220 blended to produce the refined regular
X4 = barrels of crude X220 blended to produce the refined economy
Low Knock Oil Company
Formulation
Copyright ©2015 Pearson Education, Inc.
8 – 77
Minimize cost = $30X1 + $30X2 + $34.80X3 + $34.80X4
subject to | ||||||
X1 | + X3 | ≥ | 25,000 | (demand for regular) | ||
X2 | + X4 | ≥ | 32,000 | (demand for economy) | ||
subject to | ||||||
X1 | + X3 | ≥ | 25,000 | (demand for regular) | ||
X2 | + X4 | ≥ | 32,000 | (demand for economy) |
Formulation
Low Knock Oil Company
Copyright ©2015 Pearson Education, Inc.
8 – 78
Minimize cost = $30X1 + $30X2 + $34.80X3 + $34.80X4
45% of each barrel of regular must be ingredient A
(X1 + X3) = total amount of crude blended to produce regular
Thus,
0.45(X1 + X3) = minimum amount of ingredient A required
But
0.35X1 + 0.60X3 = amount of ingredient A in regular
So
0.35X1 + 0.60X3 ≥ 0.45X1 + 0.45X3
or
– 0.10X1 + 0.15X3 ≥ 0 (A in regular constraint format)
– 0.10X1 | + 0.15X3 | ≥ | 0 | (ingredient A in regular) | ||
0.05X2 | – 0.25X4 | ≤ | 0 | (ingredient B in economy) | ||
X1, X2, X3, X4 | ≥ | 0 | (nonnegativity) |
Formulation
Low Knock Oil Company
Copyright ©2015 Pearson Education, Inc.
8 – 79
Minimize cost = $30X1 + $30X2 + $34.80X3 + $34.80X4
subject to | ||||||
X1 | + X3 | ≥ | 25,000 | (demand for regular) | ||
X2 | + X4 | ≥ | 32,000 | (demand for economy) |
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 80
PROGRAM 8.9 – Low Knock Oil Solution
Solution in Excel 2013
Copyright ©2015 Pearson Education, Inc.
8 – 81
PROGRAM 8.9 – Low Knock Oil Solution
Solver Parameter Inputs and Selections | Key Formulas | |
Set Objective: F6 By Changing cells: B5:E5 To: Min Subject to the Constraints: F9:F11 >= H9:H11 F12 <= H12 Solving Method: Simplex LP R Make Variables Non-Negative | Copy F6 to F9:F12 |
Other LP Applications
Revenue Management
Developed by American Airlines
Differential pricing of seats to generate additional revenue
How many seats to make available to each type of passenger
Adopted by hotel industry
Copyright ©2015 Pearson Education, Inc.
8 – 82
Other LP Applications
Data Envelopment Analysis (DEA)
Measure efficiency of similar operating units
Can be used when there is no single objective to be optimized
Identify inputs and outputs
Develop constraints for each unit in the system
Objective is to minimize the resources required to generate specific levels of output
Identify areas where improvement might be possible
Copyright ©2015 Pearson Education, Inc.
8 – 83
Other LP Applications
Transportation, Transshipment, Assignment Problems
Very widely used in business
Special purpose algorithms have been developed
Allow more rapid solution of these types of problems
Presented in Chapter 9
Copyright ©2015 Pearson Education, Inc.
8 – 84
Copyright
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America.
1
1.6 The Role of Computers and Spreadsheet Models in the Quantitative Analysis Approach
1.7 Possible Problems in the Quantitative Analysis Approach
1.8 Implementation—Not Just the Final Step
1.1 Introduction 1.2 What Is Quantitative Analysis? 1.3 Business Analytics 1.4 The Quantitative Analysis Approach 1.5 How to Develop a Quantitative Analysis Model
CHAPTER OUTLINE
5. Use computers and spreadsheet models to perform quantitative analysis.
6. Discuss possible problems in using quantitative analysis.
7. Perform a break-even analysis.
1. Describe the quantitative analysis approach. 2. Understand the application of quantitative analysis
in a real situation. 3. Describe the three categories of business analytics. 4. Describe the use of modeling in quantitative
analysis.
After completing this chapter, students will be able to:
Introduction to Quantitative Analysis
1CHAPTER
LEARNING OBJECTIVES
M01_REND7331_12_SE_C01_pp2.indd 1 01/10/13 9:50 AM
1
1.6 The Role of Computers and Spreadsheet Models in the Quantitative Analysis Approach
1.7 Possible Problems in the Quantitative Analysis Approach
1.8 Implementation—Not Just the Final Step
1.1 Introduction 1.2 What Is Quantitative Analysis? 1.3 Business Analytics 1.4 The Quantitative Analysis Approach 1.5 How to Develop a Quantitative Analysis Model
CHAPTER OUTLINE
5. Use computers and spreadsheet models to perform quantitative analysis.
6. Discuss possible problems in using quantitative analysis.
7. Perform a break-even analysis.
1. Describe the quantitative analysis approach. 2. Understand the application of quantitative analysis
in a real situation. 3. Describe the three categories of business analytics. 4. Describe the use of modeling in quantitative
analysis.
After completing this chapter, students will be able to:
Introduction to Quantitative Analysis
1CHAPTER
LEARNING OBJECTIVES
M01_REND7331_12_SE_C01_pp2.indd 1 01/10/13 9:50 AM