IN EXCEL FORMULAS ONLY

A F H K B E G Problem 2. Wheat is harvested at farms in Nebraska and Colorado before being shipped to the three grain elevators in Kansas City, Omaha, and Des Moines. The amount of wheat harvested at each farm is 320 tons. The wheat is then shipped from grain elevators to the mills in Chicago, St. Louis, and Cincinnati. Each mill demands the following number of tons of wheat: Chicago - 200. St. Louis - 100. and Cincinnati - 300. The unit shipping cost (i.e., cost per ton) between points are given in the table below. The maximum capacities of the three grain elevators are: Kansas City - 160, Omaha - 185. Des Moines - 285. Create a model to determine the best shipment plan, which meets all requirements and minimizes the total shipment cost. 1 2 F G H J Chicago St. Louis Cincinnati Des Moines $15 $19 Omaha $14 $16 $7 $9 $7 $11 $13 $7 $13 $12 $15 From B C D E 3 Unit shipping costs (per ton) 4 5 Nebraska Colorado Kansas City 6 Nebraska $19 7 Colorado $18 8 Kansas City 9 Omaha 10 Des Moines 11 Chicago 12 St. Louis 13 Cincinnati 14 15 Network formulation (flows in tons) Origin Destination Cost Flow 17 18 19 20 21 22 23 Flow balance constraints Net flow out/in 16 Index 1 2 3 4 5 6 7 8 24 25 26 Capacity constraint for Kansas City, Omaha, and Des Moines 3 4 5 27 28 29 30 31 32 33 34 Total Cost 35 36