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	7.7	$320.00
Atlanta	9.0	$270.00
St. Louis	12.0	$300.00
Salt Lake City	10.3	$245.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	$45	$52	$56	$62	$76	$81
Atlanta	55	42	58	59	78	78
St. Louis	57	60	50	54	63	66
Salt Lake City	72	71	67	57	56	56
Total Demand (× 1,000 lbs.)	521	411	921	596	321	396

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

Complete problem was modelled in Excel and solved using SOLVER. Snapshots showing solutions, formulae and SOLVER equations are shown in snapshots below.

Solution:

MAXIMUM PROD. COST CAPACITY TRANSPORT COSTS PER 1,000 LBS 2 FROM/TO NYCI BIRMINGHAM TERRE HAUTE DALLAS SPOKANE | SAN DIEGO x 1,000 LBS ER 1,000 LBS 3 Philadelphia$45 $55 $57 6 Salt Lake City $72 7 Total Demand 521 $52 $42 $60 $71 411 $56 $58 $50 $67 921 $62 $59 $54 $57 596 $76 $78 $63 $56 321 $81 $78 $66 $56 396 3,166 Philadelphia Atlanta St. Louis Salt Lake Ci 770 900 1,200 1,030 3,900 $320 $270 $300 $245 4 Atlanta 5 St. Louis 8 (x 1,000 lbs Total FROM/TO NYCI BIRMINGHAM TERRE HAUTE DALLA SI SPOKANE | SAN DIEGO 10 11 Philadelphia 12 Atlanta 13 St. Louis 14 Salt Lake Ci 15 Total Demand 521 16 I Lu 36 485 36 900 01 1.200 396 1,030 396 3,166 411 279 313 596 921 321 321 411 921 18 19 20 otalTransportation Cost$164,902 Total Production Cost$866,870 Total Cost$1,031,772

Formulae used:

MAXIMUM CAPACITY PROD. COST TRANSPORT COSTS PER 1,000 LBS 76 63 321 x 1,000 LBS ER 1,000 LBS PLANT Philadelphia-7.7 100 Atlanta St. Louis Salt Lake City-10.3*100 SPOKANE SAN DIEGO DALLAS NYC BIRMINGHAM | TERRE HAUTE | 2 FROM/TO 3 Philadelphia 45 4 Atlanta 5 St. Louis 6 Salt Lake City 72 7 Total Demand 521 8 (1,000 Ibs.) 62 59 81 78 -9 100 12 100 270 300 245 42 60 71 50 67 921 396 SUM(B7:G7) 596 Total Supply -SUM(B11 G11 SUMB12 G12 SUMB13 G13 SUM(B14:G14 SPOKANE SAN DIEGO BIRMINGHAM | TERRE HAUTE DALLAS NYC 10 11 Philadelphia 36 12 Atlanta 13 St. Louis 14 Salt Lake City 0 15 Total Demand-SUM(B11:B14) |=SUM(Cl 1:C14) |-sUMD 11:D14) |=SUME 11:E14) |=SUM(F11:F14 16 17 18 19 20 21 485 279 313 396 -SUMIG11:G14 321 Transportation Cost SUMPRODUCTB11:G14.B3 G6 tal Production Cost = =SUMPRODUCT(K3K6,H11-H14 Total CostSUM(G18:G19 23

SOLVER Equations used:

Solver Parameters SGS201 Set Objective To: O Max By Changing aiable Cells: Value Of: SBS11:SGS14 Subject to the Constraints: SBS15:SGS15 SBS7:SGS SHS11:SHS14 SJS3:SJS6 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a SolvingSimplex LP Method: Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Solution Matrix:

FROM/TO	NYC	BIRMINGHAM	TERRE HAUTE	DALLAS	SPOKANE	SAN DIEGO
Philadelphia	36	0	0	0	0	0
Atlanta	485	411	0	4	0	0
St. Louis	0	0	921	279	0	0
Salt Lake City	0	0	0	313	321	396
Received	521	411	921	596	321	396

Transportation Cost Matrix:

Transportation Costs
FROM/TO	NYC	BIRMINGHAM	TERRE HAUTE	DALLAS	SPOKANE	SAN DIEGO
Philadelphia	$1,620	$0	$0	$0	$0	$0
Atlanta	$26,675	$17,262	$0	$236	$0	$0
St. Louis	$0	$0	$46,050	$15,066	$0	$0
Salt Lake City	$0	$0	$0	$17,841	$17,976	$22,176

Total Cost Matrix:

Total Costs
FROM/TO	NYC	BIRMINGHAM	TERRE HAUTE	DALLAS	SPOKANE	SAN DIEGO
Philadelphia	$13,140	$0	$0	$0	$0	$0
Atlanta	$157,625	$128,232	$0	$1,316	$0	$0
St. Louis	$0	$0	$322,350	$98,766	$0	$0
Salt Lake City	$0	$0	$0	$94,526	$96,621	$119,196
Total Cost	$170,765	$128,232	$322,350	$194,608	$96,621	$119,196