Question

Maurice’s Pump Manufacturing Company currently maintains plants in Atlanta and Tulsa that supply major distribution centers in Los Angeles and New York. Because of an expanding demand, Maurice has decided to open a third plant and has narrowed the choice to one of two cities—New Orleans or Houston. The pertinent production and distribution costs, as well as the plant capacities and distribution center demands, are shown in the following table.

Plants			Distribution Centers
Location	Capacity	Production Cost (per unit)	LA	New York
Atlanta (existing)	600	$6	$8	$5
Tulsa (existing)	900	$5	$4	$7
New Orleans (Proposed)	500	$4 (anticipated)	$5	$6
Houston (Proposed)	500	$3 (anticipated)	$4	$6
Forecast Demand			800	1,200

Help the company decide which of the two proposed plant should be opened. Create a spreadsheet model of this situation and solve using Solver. Clearly show the values of the decision variables and the objective.

Answer 1

Let be the quantity shipped from location i to the distribution center j, where

• i=1,2,3,4 for locations Atlanta, Tulsa, New Orleans, Houston respectively
• j=1,2 for distribution centers LA, New York respectively

We also create the following indicator variables

• if the new plant is opened at New Orleans, else
• if the new plant is opened at Houston, else

The above are the decision variables

Total production cost is

the total shipping cost is

The total cost would be

We want to minimize this cost, hence this is the objective function

Finally the constraints

Plant capacity

Plant in Atlanta has a capacity of 600 units, the total shipment from this location cannot exceed this

Plant in Tulsa has a capacity of 900 units, the total shipment from this location cannot exceed this

the proposed Plant in New Orleans has a capacity of 500 units if built (y1=1), the total shipment from this location cannot exceed this

the proposed Plant in Houston has a capacity of 500 units if built (y2=1), the total shipment from this location cannot exceed this

Since only one of the plants can be constructed

Demand

Forecast Demand at LA is 800, the total shipment should be equal to this

Forecast Demand at New York is 1200, the total shipment should be equal to this

The LP model is

Maximize

s.t.

Prepare the following sheet

get this

setup the solver using data--->solver

get this

ans: The values of the decision variables are

Decision variables	Distribution Centers		Indicator (y)
Location	LA	New York
Atlanta (existing)	0	600
Tulsa (existing)	800	100
New Orleans (Proposed)	0	0	0
Houston (Proposed)	0	500	1

That is, open the third plant at Houston

The cost would be (the objective) $19,500