Question

Sycamore Plastics (SP) is a manufacturer of polyethylene plastic pellets used as a raw material by manufacturers of plastic goods around the U.S. SP currently operates four manufacturing centers in Philadelphia, PA; Atlanta, GA; St. Louis, MO; and Salt Lake City, UT. The plants have different capacities and production costs as indicated in the table below.

PLANT	MAXIMUM CAPACITY (× 100,000 LBS.)	PROD. COST (PER 1,000 LBS.)
Philadelphia	8.1	$322.00
Atlanta	9.2	$272.00
St. Louis	12.1	$302.00
Salt Lake City	10.1	$247.00

     SP currently has six contract customers located in New York City; Birmingham, AL; Terre Haute, IN; Dallas, TX; Spokane, WA; and San Diego, CA. Transportation costs between the plants and various customers, as well as contracted demand from each customer, are shown in the table below.

	TRANSPORT COSTS PER 1,000 LBS.
FROM/TO	NYC	BIRMINGHAM	TERRE HAUTE	DALLAS	SPOKANE	SAN DIEGO
Philadelphia	$46	$53	$57	$63	$72	$85
Atlanta	56	43	59	60	74	82
St. Louis	58	61	51	55	59	70
Salt Lake City	73	72	68	58	52	60
Total Demand (× 1,000 lbs.)	526	415	926	601	334	401
a. Create a solver model and find the optimal solution to help SP develop a distribution plan that will minimize costs to supply the customers’ demand. (Enter your answers in terms of cost per 1,000 lbs and shipment quantities per 1,000 lbs. Round your answers to the nearest whole number.) CombinedCosts Per 1,000 lbs. From/To NYC Birmingham Terre Haute Dallas Spokane San Diego Philadelphia $ $ $ $ $ $ Atlanta St. Louis Salt Lake City Solution (× 1,000 lbs.) From/To NYC Birmingham Terre Haute Dallas Spokane San Diego Philadelphia Atlanta St. Louis Salt Lake City Received Total Costs From/To NYC Birmingham Terre Haute Dallas Spokane San Diego Philadelphia $ $ $ $ $ $ Atlanta St. Louis Salt Lake City Total cost $

Answer 1

Let   be the quantities (in x1000lbs) shipped from 4 plants to 6 contract customers, where

These are the decision variables

The combined cost of transportation and production (per x1000lbs) from a plant to customer is given below

CombinedCosts Per 1,000 lbs.
From/To	NYC	Birmingham	Terre Haute	Dallas	Spokane	San Diego
Philadelphia	$368.00	$375.00	$379.00	$385.00	$394.00	$407.00
Atlanta	$328.00	$315.00	$331.00	$332.00	$346.00	$354.00
St. Louis	$360.00	$363.00	$353.00	$357.00	$361.00	$372.00
Salt Lake City	$320.00	$319.00	$315.00	$305.00	$299.00	$307.00

For example the combined cost for Philadelphia to NYC of transporting a quantity of (x1000 lbs) is $322+$46 = $368

The total costs to supply the customer demand is

This is the objective function that we need to minimize

This cost is subjected to the following constraints

Plant Capacity

• capacity of Philadelphia (in x1000lb)
• capacity of Atlanta (in x1000lb)
• capacity of St. Louis (in x1000lb)
• capacity of Salt Lake City (in x1000lb)

Demand from customers

• Demand of NYC (in x 1000lbs)
• Demand of Birmingham (in x 1000lbs)
• Demand of Terre Haute (in x 1000lbs)
• Demand of Dallas (in x 1000lbs)
• Demand of spokane (in x 1000lbs)
• Demand of San Diego (in x 1000lbs)

the transportation model that we want to solve is

Minimize

s.t

• capacity of Philadelphia (in x1000lb)
• capacity of Atlanta (in x1000lb)
• capacity of St. Louis (in x1000lb)
• capacity of Salt Lake City (in x1000lb)

Demand from customers

• Demand of NYC (in x 1000lbs)
• Demand of Birmingham (in x 1000lbs)
• Demand of Terre Haute (in x 1000lbs)
• Demand of Dallas (in x 1000lbs)
• Demand of spokane (in x 1000lbs)
• Demand of San Diego (in x 1000lbs)
• 

We setup the following spreadsheet

Using the formula given below

PLANT

MAXIMUM CAPACITY

PROD. COST

TRANSPORT COSTS PER 1,000 LBS.

(× 100,000 LBS.)

(PER 1,000 LBS.)

FROM/TO

NYC

BIRMINGHAM

TERRE HAUTE

DALLAS

SPOKANE

SAN DIEGO

Philadelphia

8.1

322

Philadelphia

46

53

57

63

72

85

Atlanta

9.2

272

Atlanta

56

43

59

60

74

82

St. Louis

12.1

302

St. Louis

58

61

51

55

59

70

Salt Lake City

10.1

247

Salt Lake City

73

72

68

58

52

60

Total Demand

526

415

926

601

334

401

(× 1,000 lbs.)

=SUM(F7:K8)

CombinedCosts Per 1,000 lbs.

From/To

NYC

Birmingham

Terre Haute

Dallas

Spokane

San Diego

Minimize

Philadelphia

=F3+$C3

=G3+$C3

=H3+$C3

=I3+$C3

=J3+$C3

=K3+$C3

Total Cost

=SUM(B32:G32)

Atlanta

=F4+$C4

=G4+$C4

=H4+$C4

=I4+$C4

=J4+$C4

=K4+$C4

St. Louis

=F5+$C5

=G5+$C5

=H5+$C5

=I5+$C5

=J5+$C5

=K5+$C5

s.t. constraints

Salt Lake City

=F6+$C6

=G6+$C6

=H6+$C6

=I6+$C6

=J6+$C6

=K6+$C6

=SUM(B20:G20)

<=

=B3*100

--capacity of Philadelphia (in x1000lb)

=SUM(B21:G21)

<=

=B4*100

--capacity of Atlanta (in x1000lb)

Solution (× 1,000 lbs.)

=SUM(B22:G22)

<=

=B5*100

--capacity of St. Louis (in x1000lb)

From/To

NYC

Birmingham

Terre Haute

Dallas

Spokane

San Diego

=SUM(B23:G23)

<=

=B6*100

--capacity of Salt Lake City (in x1000lb)

Philadelphia

0

0

0

0

0

0

=B24

=

=F7

--Demand of NYC (in x 1000lbs)

Atlanta

0

0

0

0

0

0

=C24

=

=G7

--Demand of Birmingham (in x 1000lbs)

St. Louis

0

0

0

0

0

0

=D24

=

=H7

--Demand of Terre Haute (in x 1000lbs)

Salt Lake City

0

0

0

0

0

0

=E24

=

=I7

--Demand of Dallas (in x 1000lbs)

Received

=SUM(B20:B23)

=SUM(C20:C23)

=SUM(D20:D23)

=SUM(E20:E23)

=SUM(F20:F23)

=SUM(G20:G23)

=F24

=

=J7

--Demand of spokane (in x 1000lbs)

=G24

=

=K7

--Demand of San Diego (in x 1000lbs)

Total Costs

From/To

NYC

Birmingham

Terre Haute

Dallas

Spokane

San Diego

Philadelphia

=B12*B20

=C12*C20

=D12*D20

=E12*E20

=F12*F20

=G12*G20

Atlanta

=B13*B21

=C13*C21

=D13*D21

=E13*E21

=F13*F21

=G13*G21

St. Louis

=B14*B22

=C14*C22

=D14*D22

=E14*E22

=F14*F22

=G14*G22

Salt Lake City

=B15*B23

=C15*C23

=D15*D23

=E15*E23

=F15*F23

=G15*G23

Total cost

=SUM(B28:B31)

=SUM(C28:C31)

=SUM(D28:D31)

=SUM(E28:E31)

=SUM(F28:F31)

=SUM(G28:G31)

Next we setup the solver using data-->solver

Get the following solution

PLANT	MAXIMUM CAPACITY	PROD. COST		TRANSPORT COSTS PER 1,000 LBS.
	(× 100,000 LBS.)	(PER 1,000 LBS.)		FROM/TO	NYC	BIRMINGHAM	TERRE HAUTE	DALLAS	SPOKANE	SAN DIEGO
Philadelphia	8.1	$322.00		Philadelphia	$46	$53	$57	$63	$72	$85
Atlanta	9.2	$272.00		Atlanta	56	43	59	60	74	82
St. Louis	12.1	$302.00		St. Louis	58	61	51	55	59	70
Salt Lake City	10.1	$247.00		Salt Lake City	73	72	68	58	52	60
				Total Demand	526	415	926	601	334	401
				(× 1,000 lbs.)							3203
CombinedCosts Per 1,000 lbs.
From/To	NYC	Birmingham	Terre Haute	Dallas	Spokane	San Diego		Minimize
Philadelphia	$368.00	$375.00	$379.00	$385.00	$394.00	$407.00		Total Cost	$1,054,831
Atlanta	$328.00	$315.00	$331.00	$332.00	$346.00	$354.00
St. Louis	$360.00	$363.00	$353.00	$357.00	$361.00	$372.00		s.t. constraints
Salt Lake City	$320.00	$319.00	$315.00	$305.00	$299.00	$307.00
									63	<=	810	--capacity of Philadelphia (in x1000lb)
									920	<=	920	--capacity of Atlanta (in x1000lb)
Solution (× 1,000 lbs.)									1210	<=	1210	--capacity of St. Louis (in x1000lb)
From/To	NYC	Birmingham	Terre Haute	Dallas	Spokane	San Diego			1010	<=	1010	--capacity of Salt Lake City (in x1000lb)
Philadelphia	63	0	0	0	0	0			526	=	526	--Demand of NYC (in x 1000lbs)
Atlanta	463	415	0	42	0	0			415	=	415	--Demand of Birmingham (in x 1000lbs)
St. Louis	0	0	926	284	0	0			926	=	926	--Demand of Terre Haute (in x 1000lbs)
Salt Lake City	0	0	0	275	334	401			601	=	601	--Demand of Dallas (in x 1000lbs)
Received	526	415	926	601	334	401			334	=	334	--Demand of spokane (in x 1000lbs)
									401	=	401	--Demand of San Diego (in x 1000lbs)
Total Costs
From/To	NYC	Birmingham	Terre Haute	Dallas	Spokane	San Diego
Philadelphia	$23,184.00	$0.00	$0.00	$0.00	$0.00	$0.00
Atlanta	$151,864.00	$130,725.00	$0.00	$13,944.00	$0.00	$0.00
St. Louis	$0.00	$0.00	$326,878.00	$101,388.00	$0.00	$0.00
Salt Lake City	$0.00	$0.00	$0.00	$83,875.00	$99,866.00	$123,107.00
Total cost	$175,048.00	$130,725.00	$326,878.00	$199,207.00	$99,866.00	$123,107.00