Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) v 12 A" A ab Wrap Text v General Insert v "APO. Ex Delete v I v Paste BIUV OvAv Merge & Center $ ~ % " Conditional Format Cell Sort & Find & Analyze Sensitivity Formatting as Table Styles Format v Filter Select Data A12 X V fx H 1 K L M N 0 P Q R S T U V W Z Exercise 2: Transportation Problem INPUTS Unit Cost (in dollars Per Unit) from Plant to Customer Customer Supply (# Plant 2 Plant Units) Your company manufactures product at three different plants (A, B and C) and ships them to A $5 $6 $5 $3 A 1000 four different Customers (1, 2, 3 and 4). The cost to ship one unit (one unit) from each Plant to B $3 $8 $6 $4 each Customer is shown below. CO 800 $4 $9 $5 $6 1200 Unit Cost (in dollars Per Unit) from Plant to Customer Customer Customer Plant 1 2 3 3 4 4 $ 11 Demands 800 900 600 700 Units A 5 $ 3 3 $ 8 5 6 5 C 4 5 5 14 Each plant has a capacity (supply) of so many Units on hand, and each Customer has a demand of so many Units, as shown below. Supply (# Units) Plant A 1000 800 O 1200 Customer 1 2 3 4 Demands 800 900 600 700 Units STEP 1)On the worksheet "Exercise 2", formulate and solve an optimization model to MINIMIZE TOTAL SHIPPING COSTS in Excel. Be sure the Solver dialog box is populated and the correct ( optimal answer) is displayed in your objective cell when you submit your file. STEP 2) In a cell or textbox in the worksheet containing this problem, indicate HOW MANY of each product should be shipped FROM EACH PLANT TO EACH CUSTOMER. For example, how much should be shipped from Plant A to Customer 1? From Plant A to customer 2? etc. (Note there are potentially up to 12 different amounts shipped from three plants to four customers.) 35 36 STEP 3) In another cell or textbox in the worksheet containing this problem, indicate what the minimum total shipping cost will be. 37 38 39 40 D Exercise1 Exercise 2 + Ready + 100%