In: Operations Management
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.5 | $326.00 |
Atlanta | 9.5 | $278.00 |
St. Louis | 12.5 | $306.00 |
Salt Lake City | 10.3 | $251.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 | $77 | $80 |
Atlanta | 55 | 42 | 58 | 59 | 79 | 77 |
St. Louis | 57 | 60 | 50 | 54 | 64 | 65 |
Salt Lake City | 72 | 71 | 67 | 57 | 57 | 55 |
Total Demand (× 1,000 lbs.) |
524 | 420 | 932 | 595 | 350 | 450 |
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 | $ | |||||
(a) Calculate combined costs by adding the production cost for each plant and transport cost from each plant to customers.
Combined Costs Per 1000 lbs. | ||||||
FROM/TO | NYC | BIRMINGHAM | TERRE HAUTE | DALLAS | SPOKANE | SAN DIEGO |
Philadelphia | 371 | 378 | 382 | 388 | 403 | 406 |
Atlanta | 333 | 320 | 336 | 337 | 357 | 355 |
St. Louis | 363 | 366 | 356 | 360 | 370 | 371 |
Salt Lake City | 323 | 322 | 318 | 308 | 308 | 306 |
Formulas:
B15 =SUM(B11:B14) copy to B15:G15
H11 =SUM(B11:G11) copy to H11:H15
B17 =SUMPRODUCT(B11:G14,B3:G6)
Total costs | ||||||
FROM/TO | NYC | BIRMINGHAM | TERRE HAUTE | DALLAS | SPOKANE | SAN DIEGO |
Philadelphia | 15,211 | 0 | 0 | 0 | 0 | 0 |
Atlanta | 160,839 | 134,400 | 0 | 15,839 | 0 | 0 |
St. Louis | 0 | 0 | 331,792 | 114,480 | 0 | 0 |
Salt Lake City | 0 | 0 | 0 | 70,840 | 107,800 | 137,700 |
Total Costs | 1,088,901 |