570 Quantitative Business Analysis, homework 5 wk 6

subject to

(demand for regular)

(demand for economy)

kentucky79

rshh_qam12_ch08.pptx

Home >Mathematics homework help >Statistics homework help >570 Quantitative Business Analysis, homework 5 wk 6

Linear Programming Applications

To accompany Quantitative Analysis for Management, Twelfth Edition,

by Render, Stair, Hanna and Hale

Power Point slides created by Jeff Heyl

After completing this chapter, students will be able to:

LEARNING OBJECTIVES

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.

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

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

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

8 – 6

Win Big Gambling Club

Advertising options

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

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

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

8 – 10

PROGRAM 8.1 – Win Big Solution

Solution in Excel 2013

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

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

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

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

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

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

8 – 17

PROGRAM 8.2 – MSA Solution

Solution in Excel 2013

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

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

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

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

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

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

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

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

8 – 26

PROGRAM 8.3 – Fifth Avenue Solution

Solution in Excel 2013

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

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

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

8 – 30

Greenberg Motors

Basic data

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

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

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

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

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

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

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

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

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

8 – 40

PROGRAM 8.4 – Greenberg Motors Solution

Solution in Excel 2013

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

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

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

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

8 – 45

Hong Kong Bank

Labor requirements

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

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

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

8 – 49

PROGRAM 8.5 – Labor Planning Solution

Solution in Excel 2013

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

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

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

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

8 – 54

International City Trust

Investment possibilities

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

8 – 56

International City Trust

Formulation

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

8 – 58

PROGRAM 8.6 – ICT Portfolio Solution

Solution in Excel 2013

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

8 – 60

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

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

8 – 62

Goodman Shipping

Formulation

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

8 – 64

PROGRAM 8.7 – Goodman Truck Loading Solution

Solution in Excel 2013

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

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

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

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

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

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

8 – 71

PROGRAM 8.8 – Whole Food Diet Solution

Solution in Excel 2013

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

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

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

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

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

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)

Formulation

Low Knock Oil Company

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

0.35X1 + 0.60X3 ≥ 0.45X1 + 0.45X3

– 0.10X1 + 0.15X3 ≥ 0 (A in regular constraint format)

Formulation

Low Knock Oil Company

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

8 – 80

PROGRAM 8.9 – Low Knock Oil Solution

Solution in Excel 2013

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

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

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

8 – 84

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