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.4 | $334.00 |
Atlanta | 9.1 | $284.00 |
St. Louis | 12.0 | $314.00 |
Salt Lake City | 10.4 | $259.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 | $40 | $47 | $51 | $57 | $60 | $97 |
Atlanta | 50 | 37 | 53 | 54 | 62 | 94 |
St. Louis | 52 | 55 | 45 | 49 | 47 | 82 |
Salt Lake City | 67 | 66 | 62 | 52 | 40 | 72 |
Total
Demand (× 1,000 lbs.) |
538 | 427 | 938 | 602 | 305 | 413 |
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 | $ |
Spreadsheet model and solution using Solver is as follows:
Combined cost is sum of transport cost and prod. cost
Formulas:
B15 =SUM(B11:B14) copy to B15:G15
H11 =SUM(B11:G11) copy to H11:H14
B19 =B3*B11 copy to B19:G22
G23 =SUM(B19:G22)
Total cost = $ 1,085,651