Question

Bindley Corporation has a one-year contract to supply motors for all washing machines produced by Rinso Ltd. Rinso manufactures the washers at four locations around the country: New York, Fort Worth, San Diego, and Minneapolis. Plans call for the following numbers of washing machines to be produced at each location:

New York	70,000
Fort Worth	67,000
San Diego	98,000
Minneapolis	85,000

Bindley has three plants that can produce the motors. The plants and production capacities are

Boulder	110,000
Macon	114,000
Gary	121,000

Due to varying production and transportation costs, the profit Bindley earns on each 1,000 units depends on where they were produced and where they were shipped. The following table gives the accounting department estimates of the dollar profit per unit. (Shipment will be made in lots of 1,000.)

	SHIPPED TO
PRODUCED AT	NEW YORK	FORT WORTH	SAN DIEGO	MINNEAPOLIS
Boulder	15	11	12	13
Macon	22	21	18	14
Gary	25	10	24	19

Question: Find the optimal solution using Microsoft Excel.

	Candidate Solution Total Shipped Boulder    Macon Gary Total shipped

	Profit Boulder    Macon Gary Total profit

Answer 1

Let number of lots (each lot is of size 1000) of motors shipped from plant i to location j, where i=1,2,3 correspond to {Boulder, Macon,Gary} and j=1,2,3,4 correspond to {NEW YORK, FORT WORTH, SAN DIEGO,MINNEAPOLIS} respectively.

These are the decision variables.

The total profit (in $1000s) is

The constraints are

Demand at 4 locations

• Demand at New York is 70,000 units (70 lots). The lots shipped to New York should be equal to 70
  • 
• Demand at Fort Worth is 67,000 units (67 lots). The lots shipped to Fort Worth should be equal to 67
• Demand at San Diego is 98,000 units (98 lots). The lots shipped to San Diego should be equal to 98
  • 
• Demand at Minneapolis is 85,000 units (85 lots). The lots shipped to Minneapolis should be equal to 85
  
  • 

Capacity constraint

• Capacity of plant at Boulder is 110,000. The total lots shipped from this plant cannot exceed 110
  • 
• Capacity of plant at Macon is 114,000. The total lots shipped from this plant cannot exceed 114
  • 
• Capacity of plant at Gary is 121,000. The total lots shipped from this plant cannot exceed 121
  • 

Shipments are made in lots of 1000s. Since we cannot ship a fraction of lot, Xs are integers.

The LP model is

Maximize

s.t.

• 
• 
• 
• 
• 
• 
• 
• 

Prepare the following sheet

get this

setup the solver using data--->solver

get this

ans:

	SHIPPED TO
PRODUCED AT	NEW YORK	FORT WORTH	SAN DIEGO	MINNEAPOLIS	Total Shipped (1000s)
Boulder	0	0	0	85	85
Macon	47	67	0	0	114
Gary	23	0	98	0	121
Total shipped					320

Profit	NEW YORK	FORT WORTH	SAN DIEGO	MINNEAPOLIS	Total Profit ($1000s)
Boulder	$           -	$               -	$           -	$          1,105	$       1,105
Macon	$      1,034	$          1,407	$           -	$               -	$       2,441
Gary	$         575	$               -	$      2,352	$               -	$       2,927
Total profit					$       6,473