Question

M4_IND3. Global Foods, Inc. imports food products to the United States from Hamburg, Marseilles, and Liverpool. Ships from these ports deliver the products to Norfolk, New York, and Savannah, where they are stored in company warehouses before being shipped to distribution centers in Dallas, St. Louis, and Chicago. The shipping costs ($/1,000 lbs.) from the European ports to the U.S> cities and available supplies (1,000 lbs.) at the European ports are provided in the following table: Shipping Costs ($/1,000 lbs.): U.S. Warehouses Supply (1,000 lbs.) Norfolk New York Savannah Port Hamburg $420 $510 $450 $390 $590 $360 $610 $470 $480 55 Marseilles 78 Liverpool 37 The transportation costs ($/1,000 lbs.) from each U.S. city of the three distribution centers and the demands (1,000 lbs.) at the distribution centers: Distribution Center Chicago $81 $95 $95 Dallas St. Louis $75 $125 $68 $63 $110 $82 Norfolk From New York Warehouse Savannah Demand (1,000 lbs.) 60 45 50 Formulate this problem in Excel and solve to determine the minimum shipping costs required to meet demands a) What is the total shipping cost meet the demands at the Distribution Centers? b) Which European supplier did not ship out all its supply? c) How many units should be shipped from Liverpool to New York? d) How many units should be shipped from Savannah to St. Louis?

Answer 1

Answer: Date:--.3/06/2019 The answers are: (a): $77,362 (b): Marseilles It ships only 63 (1,000 lbs) against a capacity of 78 (1,000) lbs (c): 37 units are shipped from Liverpool to New York (d): 3 units are shipped from Savannah to St. Louis Calculations and explanations: (using excel's solver function): Let the variables be as defined in the tables below Port Norfolk New York Savannah Hamburg h1 h2 h3 Marseilles m2 m1 m3 Liverpool 11 12 13 Dallas Chicago St. Louis Norfolk a1 а2 а3 New York b1 b2 b3 Savannah cl c2 c3 Thus total cost = 420h1+390h2+610h3+510m1+590m2+470m3+45011+36012+48013+75a1+63a2 +81 a3+125bl+110b2+95b3+68c1+82c2+95c3 Constraints: The 1st three constraints are with regards to supply (1) h1+h2+h3<=55 (2) m1+m2+m3<=78 (3)12+1337

The next three constraints are with regards to demands at the distribution cent-res (DC) (4): a1+bl+cl = 60 (5): a2+b2+c2 45 (6) a3+b3+c3 50 The next 3 constraints states that amount received by a warehouse should be equal to or more than the amount shipped by it to a DC (7): h1+m1+1>=a1+a2+a3 (8): h2+m2+12>=b1+b2+b3 (9): h3+m3+13>=c1+c2+c3 The solution provided be excel's solver is shown below: Norfolk New York Savannah Port Hamburg 42 13 0 Marseilles 0 0 63 Liverpool 0 37 0 Dallas St. Louis Chicago Norfolk 0 42 0 New York 0 50 Savannah 60 3 0 Total cost 77,362 Constraints 55 55 63 78 37 37 60 60 45 45 50 50

Q quant 2018 19 Microsoft Excel Home Insert Page Layout Formulas Data Review View File Connections K Clear Solver Parameters Reapply Properties pa From From Other Sort Filter From From Existing Connections Refresh Text to Advanced Go Edit Links Columns Dup Access Web Text Sources All SD579 Set Objective: Get External Data Connections Sort & Filter f(420*D70)+(390 E70) +(610* F70) +( 510*D71) +(590*E71) +(470*F71)+(450*D To: Value Of: Max Min By Changing Variable Cels: B F C D E 66 $D$70:$F$72,$D$75: SF$77 67 Subject to the Constraints: $C$82 <SE$82 68 Add Norfolk New York Savannah 69 Port SCs84 < SES84 Hamburg 70 12 13 C Change Marseilles 71 0 63 SC$87 Liverpool 37 72 0 0 SE$87 Delete 73 SC$90>-SE$90 Dallas St. Louis Chicago 74 Reset Al Sp$75-65 6770 Norfolk 42 75 0 New York 76 0 50 Load/Save Savannah 60 77 0 Make Unconstrained Variables Non-Negative 78 77,362 GRG Noninear Total cost Select a Solving Method: 79 Optons 80 Solving Method 81 Constraints Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex problems that are 82 55 <= 55 Solver Problems, and select the Evolutionary engine non-smooth 83 63 <= 78 37 <= 37 84 85 60 60 Help Solve Close 86 45 - 45 87 50= 50 88 42>: 42 89 50> 50 90 63> 63