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 MAXIMUM CAPACITY (x 100,000 LBS.) PROD. COST (PER 1,000 LBS.) PLANT Philadelphia S330.00 $280.00 S310.00 $255.00 9.3 12.2 10.2 St. Louis Salt Lake City 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 TERRE HAUTE DALLAS FROM/TO Philadelphia SAN DIEGO NY 559 61 53 70 $68 70 $89 86 74 45 63 74 62 57 60 St Louis Salt Lake City Total Demand (x 1,000 lbs.) 60 75 530 930 419 605 330 a. Create a solver model and fhnd 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 erre Haute From To Philadelphia S St. Louis Salt Lake City

Answer 1

We will solve this transportation problem using Excel solver as detailed out in below screenshots.

Bock1- Excel Share Insert Page Layout Formulas Data Review View Tell me what you want to do. Punoet Sabharwal Cut Copy Furmal Painter 團 | ..「Normal-Bad 9-5, Wrap Text = 11AA Calibn Good Merge & Center. $. % , รเ:1 Conditional runnal as Neutral Insert Delele Fumal Surl & Find & . 2.Δ.--- .Ξ.Ξ Check Cell B 1 u . Formattin Table Ton dting B57 30 b. Find the optimal solution n arder to salv쓴 through salver, we will first create blank table with required number of units and setup formulas to add demand and capacity, as given belaw Optimal Units in 1000 lbs FROM/ TO Philadelphia Atlanta St. Lauis Salt Lake Cit 5um of units NYC BIRMINGHAM TERRE HAUTE SAN DIEGO Ca o SUM C3:H35) 930 1,220 37 Sum of units Total Demand (*1000 Ihs Total Cost SUMIC35.C SUMID35:D38) 530 930 43 Total cost to beminimize 45 SUMPHOUUCIC3S:H38,C23:H26) 47 Sheet1 ョ1 - 1:33 PM 58/2019 OType here to search

Bock1- Excel Home Insert Page Layout Formulas lell me what you want to do. Punoet Sabharwal Shirw Queries Dala Analysis From Tabble Reapply rom From From Frum Olher Existing New Access Web Text Sources Connections Query Sont Filter Text to Flash Remove Dala Consolidale Relationships What-If Furecast Group Ungroup Sublotal Columns Fil Duplicates Validation Recent Sources Allle Edit Links Analysis Sheet Connestias Sert&&iter Dals Toals What-if analysis tool that finds the optimal value of a target cell by changing values in cells used to crallculate the target cell Sclver Parameters 30 b. Find the optimal solution SOLVER XLAM 9A4 In arder to salve thrnugh soler, we will first create blank table with required number of units and setup formulas to add demand Optimal Units in 1000 lbs FROM/ TO Philadelphia Atlanta St. Lauis Salt Lake Cit Tell me mere NYC BIRMINGHAM TERRE HAUTE By Changing Vanabl: Cells: 36 37 ubject to the Constrairts Sum of units Total Demand (*1000 Ihs Total Cost SUMIC35.C SUMID35:D38) 530 930 43 Total cost to beminimize Beset Al 45 SUMPHOUUCIC3S:H38,C23:H26) Mase Uncorstrained Varisbles Non-Negsive 47 GRG Noninear Cetione 19 Suving Method Select the GRG Nonlirear engine far Sover Prohlems at are smacth neninear. Select the IP Smplee engine for Iner Sclver Prcb ems, and select the Evclrio ary engine for Scher problems that are nan smot Sheet1 1:34 PM 58/2019 Type here to search

Bock1- Excel Share Rewiew View Tell me what you want to do.. Home Insert Page Layout Formulas Punoet Sabharwal Show Queries From Tabble Recent SourcesAldi Links Dala Analysis Reapply. rom From From Frum Olher Existing New Access Web Text Sources Connections Query Sont Filter Text to Flash Remove Dala Consolidale Relationships What-If Furecast Group Ungroup Sublotal Columns Fil Duplicates Validation Analysis Sheet Get Esternal Dala Connestias Sert & iter Dals Toals C35 Solver ParameterS 30 b. Find the optimal solution n arder to salv쓴 through salver, we will first create blank table with required number of units and setup formulas to add demand and capacity, as given belaw Optimal Units in 1000 lbs FROM/ TO Philadelphia Atlanta St. Lauis Salt Lake Cit 5um of units BIRMINGHAM TERRE HAUTE SAN DIEGO Ca o SUM C3:H35) 36 37 930 1,220 Sum of units Total Demand (*1000 Ihs Total Cost SUMIC35.C SUMID35:D38) 530 930 43 Total cost to beminimize 45 SUMPHOUUCIC3S:H38,C23:H26) 47 19 Sheet1 ョ圓ロ 1:34 PM 58/2019 OType here to search

Bock1- Excel Share Rewiew View Tell me what you want to do.. Home Insert Page Layout Formulas Punoet Sabharwal Show Queries From Tabble Recent SourcesAldi Links Dala Analysis Reapply. rom From From Frum Olher Existing New Access Web Text Sources Connections Query Sont Filter Text to Flash Remove Dala Consolidale Relationships What-If Furecast Group Ungroup Sublotal Columns Fil Duplicates Validation Analysis Sheet Get Esternal Dala Connestias Sert & iter Dals Toals 30 b. Find the optimal solution n arder to salv쓴 through salver, we will first create blank table with required number of units and setup formulas to add demand and capacity, as given belaw Optimal Units in 1000 lbs FROM/ TO Philadelphia Atlanta St. Lauis Salt Lake Cit 5um of units NYC BIRMINGHAM TERRE HAUTE SAN DIEGO Ca o SUM C3:H35) 930 1,220 37 Sum of units Total Demand (*1000 Ihs Total Cost SUMIC35.C SUMID35:D38) 530 930 30 43 Total cost to beminimize 45 SUMPHOUUCIC3S:H38,C23:H26) Change Constraint 47 Cl Rsterenc: Sheet1 1:35 PM 58/2019 OType here to search

If you liked my answer, please give like/ thumbs up. Thank you!