Question

In: Statistics and Probability

Sycamore Plastics (SP) is a manufacturer of polyethylene plastic pellets used as a raw material by...

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 $

Solutions

Expert Solution

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

Related Solutions

Sycamore Plastics (SP) is a manufacturer of polyethylene plastic pellets used as a raw material by...
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.0 $321.00 Atlanta 9.2 $271.00 St. Louis 12.1 $301.00 Salt Lake City...
Sycamore Plastics (SP) is a manufacturer of polyethylene plastic pellets used as a raw material by...
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...
Sycamore Plastics (SP) is a manufacturer of polyethylene plastic pellets used as a raw material by...
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...
63. Ethylene, C2H4, used to make polyethylene plastics, can be made from ethane, C2H6, one of...
63. Ethylene, C2H4, used to make polyethylene plastics, can be made from ethane, C2H6, one of the components of natural gas. The heat of reaction for the decomposition of ethane gas into ethylene gas and hydrogen gas is 136.94 kJ per mole of C2H4 formed, so it is endothermic. The reaction is run at high temperature, in part because at 800-900 °C, the equilibrium constant for the reaction is much higher, indicating that a higher percentage of products forms at...
Berry Plastics is a manufacturer. Berry has inventory consisting of raw materials, work in process and...
Berry Plastics is a manufacturer. Berry has inventory consisting of raw materials, work in process and finished goods totaling $24 million. Is Berry required to report the amounts for each class of inventory or can it just report the total of $24 million? Prepare a memo to me explaining your answer. You must provide Codification references to support your answer or you will receive a You answer should be in the FIRAC format discussed in the video or in class....
Berry Plastics is a manufacturer. Berry has inventory consisting of raw materials, work in process and...
Berry Plastics is a manufacturer. Berry has inventory consisting of raw materials, work in process and finished goods totaling $24 million. Is Berry required to report the amounts for each class of inventory or can it just report the total of $24 million? You must provide Codification references to support your answer! You answer should be in the FIRAC format Facts Issue(s)(typically a question) Review of authoritative literature (ASC) Analysis (link the facts to the authority) Conclude (answer the issue...
Piscataway Plastics Company manufactures a highly specialized plastic that is used extensively in the automobile industry....
Piscataway Plastics Company manufactures a highly specialized plastic that is used extensively in the automobile industry. The following data have been compiled for the month of June. Conversion activity occurs uniformly throughout the production process. Work in process, June 1—40,000 units: Direct material: 100% complete, cost of $ 219,500 Conversion: 40% complete, cost of 61,800 Balance in work in process, June 1 $ 281,300 Units started during June 220,000 Units completed during June and transferred out to finished-goods inventory 190,000...
Piscataway Plastics Company manufactures a highly specialized plastic that is used extensively in the automobile industry....
Piscataway Plastics Company manufactures a highly specialized plastic that is used extensively in the automobile industry. The following data have been compiled for the month of June. Conversion activity occurs uniformly throughout the production process. Work in process, June 1—60,000 units: Direct material: 100% complete, cost of $ 292,500 Conversion: 40% complete, cost of 159,200 Balance in work in process, June 1 $ 451,700 Units started during June 240,000 Units completed during June and transferred out to finished-goods inventory 190,000...
Piscataway Plastics Company manufactures a highly specialized plastic that is used extensively in the automobile industry....
Piscataway Plastics Company manufactures a highly specialized plastic that is used extensively in the automobile industry. The following data have been compiled for the month of June. Conversion activity occurs uniformly throughout the production process. Work in process, June 1—60,000 units: Direct material: 100% complete, cost of $ 292,500 Conversion: 40% complete, cost of 159,200 Balance in work in process, June 1 $ 451,700 Units started during June 240,000 Units completed during June and transferred out to finished-goods inventory 190,000...
Piscataway Plastics Company manufactures a highly specialized plastic that is used extensively in the automobile industry....
Piscataway Plastics Company manufactures a highly specialized plastic that is used extensively in the automobile industry. The following data have been compiled for the month of June. Conversion activity occurs uniformly throughout the production process. Work in process, June 1—40,000 units: Direct material: 100% complete, cost of $ 155,500 Conversion: 40% complete, cost of 25,200 Balance in work in process, June 1 $ 180,700 Units started during June 200,000 Units completed during June and transferred out to finished-goods inventory 190,000...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT