How to do this problem in excel?

1 Exercise 2: Transportation Problem 2 3 INPUTS 4 5 Your company manufactures product at three different plants (1, 2 and 3) and ship them to four different Customers (A, B, C and D). The cost to ship one unit (one pallet) from each Plant to each Customer is shown below. Unit Cost (in dollars Per Pallet) from Plant to Customer Customer Plant ABCD 1 $ 7 $ 9 $ 5 $ 3 2 $ 8 $ 14 $ 7 $ 5 3 $ 4 $ 11 $ 4 $ 6 $ Unit Cost (in dollars Per Pallet) from Plant to Customer Customer Plant A B C D Plant 1 $ 7 $ 9 $ 5 3 1 2 $ 8 $ 14 $ 7 $ 5 2 3 $ 4 $ 11 $ 4 s 6 3 Customer A B D Demands 90 50 60 70 Pallets 6 7 Supply (# Pallets) 90 80 100 8 9 10 11 12 13 Each plant has a capacity (supply) of so many Pallets on hand, and each Customer has a demand of so many Pallets, as shown below. Supply (# Pallets) Plant 190 2 80 3 100 14 15 16 17 18 19 20 Customer ABCD Demands 90 50 60 70 Pallets STEP 1) Formulate and solve an optimization model TO MINIMIZE TOTAL SHIPPING COSTS in Excel. Be sure the Solver dialog box is populated and the correct (optima answer) is displayed when you submit your file. 21 22 23 24 25 26 27 STEP 2) In a cell or textbox in the worksheet containing this problem, indicate HOV MANY of each product should be shipped FROM EACH PLANT TO EACH CUSTOMER For example, how much should be shipped from Plant 1 to Customer A? From Plan 1 to customer B? etc. (Note there are potentially up to 12 different amounts shipped from three plants to four customers.) 28 29 30 31 32 STEP 3) In another cell or textbox in the worksheet containing this problem, indicate what the minimum total shipping cost will be. 33