BUS 190 Homework 6 Solutions 1. Tom’s Inc. makes two salsa products: Western Foods salsa and Mexico

BUS
190

Homework 6 Solutions

1.
Tom’s Inc. makes two salsa products: Western Foods salsa and Mexico City salsa.
Essentially, the two products have different blends of whole tomatoes, tomato
sauce, and tomato paste. A jar of Western Foods salsa uses 5 ounce of whole
tomatoes, 3 ounces of tomato sauce, and 2 ounces of tomato paste. A jar of
Mexico City salsa consists of 7 ounces of whole tomatoes, 1 ounce of tomato
sauce, and 2 ounces of tomato paste.

For the current production period Tom’s Inc. can
purchase up to 4480 ounces of whole tomatoes, 2080 ounces of tomato sauce, and
1600 ounces of tomato paste. Tom’s Inc. makes a profit of $1.00 per jar of
Western Foods salsa and $1.25 per jar of Mexico City salsa.

The
following linear programming model was used to determine the mix of salsa products
that will maximize the total profit contribution.

W = Jars
of Western Foods Salsa

M = Jars of Mexico City
Salsa

Maximize 1W + 1.25M

Subject to

5W + 7M? 4480 (Whole tomatoes)

3W + 1M? 2080 (Tomato sauce)

2W + 2M? 1600 (Tomato paste)

W,M? 0

The problem was
solved in Excel and the sensitivity report is shown on the next page.

Microsoft Excel
11.0 Sensitivity Report

Adjustable Cells
.gif”>

Final

Reduced

Objective

Allowable

Allowable

Cell

Name

Value

Cost

Coefficient

Increase

Decrease

$C$17

Western
Foods Salsa

560

0.000

1.00

0.107

0.25

$D$17 Mexico
City Salsa

240

0.000

1.25

0.25

0.15

Constraints

Final

Shadow

Constraint

Allowable

Allowable

Cell

Name

Value

Price

R.H. Side

Increase

Decrease

$K$10

Whole
Tomatoes

4480

0.125

4480

1120

160

$K$11

Tomato Sauce

1920

0.000

2080

1E+30

160

$K$12

Tomato Paste

1600

0.187

1600

40

320

a) What are the optimal production quantities?

b) What is the
profit of the optimal solution?

c) Which
constraints are binding?

.

d)
Which would be more valuable, an additional ounce of whole tomatoes or an
additional ounce of tomato paste?

e)
How much less profit would Tom’s make if they had 200 fewer ounces of tomato
sauce?

f)
How would Tom’s profit change if they had 100 more ounces of whole tomatoes?

2.
Tri-County Utilities Inc. supplies natural gas to customers in a three county
area. The company purchases natural gas from two companies: Southern Gas and
Northwest Gas. Demand forecasts for the coming winter season are Hamilton
County, 400 units; Butler County, 200 units;
and Clermont

County,
300 units. Contracts to provide the following quantities have been written:
Southern Gas, 500units; and Northwest Gas, 400
units. Distribution costs for the counties vary, depending upon thelocation
of the suppliers. The distribution costs per unit (in thousands of dollars) are
as follows:

To

From

Hamilton

Butler

Clermont

Southern
Gas

10

20

15

Northwest
Gas

12

15

18

a) Draw a network representation of this
problem.

.jpg”>

b)
Formulate a transportation model
that can be used to determine the plan that will minimize total distribution
costs.

c)
Use Excel to solve the problem.
State the optimal distribution plan and the total distribution costs. Please
turn in your spreadsheet as well.

Please see the spreadsheet online
for the model.

3. An air-conditioning manufacturer produces room
air conditioners at plants in Houston, Phoenix, and Memphis. These are sent to
regional distributors in Dallas, Atlanta, and Denver. The shipping costs vary,
and the company would like to find the least-cost way to meet the demands at
each of the distribution centers.

Dallas
needs to receive 800 air-conditioners per month, Atlanta needs 600, and Denver
needs 200. Houston has 750 air-conditioners available each month, Phoenix has
550, and Memphis has 300.

The shipping
cost per unit from Houston to Dallas is $8, to Atlanta is $12, and to Denver is
$10. Due to truck capacity limits, no more than 50 air-conditioners can be
shipped between Houston and Denver each month.

The
cost per unit from Phoenix to Dallas is $10, and to Denver is $9. Air
conditioners from Phoenix are never shipped to Atlanta.

The
cost per unit from Memphis to Dallas is $11, to Atlanta is $8, and to Denver is
$12.

Formulate
a linear optimization model to determine how many units should be shipped from
each plant to each regional distribution center. You do not need to solve the
problem.

4.
Arnoff Enterprises manufactures the CPU for a line of computers. The CPUs are
manufactured in Seattle and Santa Clara and shipped to warehouses in
Pittsburgh, Mobile, Denver, Los Angeles, and Washington, D.C. The following
table shows the number of CPUs available at each plant, the number of CPUs
required by each warehouse, and the shipping costs (in dollars per CPU).

Warehouse

Plant

Pittsburgh

Mobile

Denver

Los

Washington

CPUs

Angeles

available

Seattle

10

20

5

9

10

9000

Santa

1

20

7

10

4

8000

Clara

CPUs

3000

5000

4000

6000

3000

required

(a) Draw a
network diagram of the problem.

.jpg”>

(b)
Are supply and demand equal to each other? No
(c)
Formulate a linear optimization
model to determine the distribution plan that will minimize cost while meeting
as much demand as possible.

Since supply is less than
demand, we will need to include a dummy origin in our formulation with a
capacity of 4000 CPUs.

(d) Enter
the model in Excel. Please turn in your spreadsheet.

(e)
Report the optimal solution and its cost.

(f) Which,
if any, warehouses will have shortages and how big will the shortages be?