Network design problem using Excel Solver (USE Simplex LP in the solver)

Network design problem using Excel Solver (USE Simplex LP in the solver)

Please help me out! Please show the formulas to be used and the solution after, will upvote! Thanks

S11.41. Phillups Corporation is a global distributor of electrical parts and components. Its customers are electronics companies in the United States, including computer manufacturers are audio/visual product manufactures. The company contracts to purchase components and parts from manufacturers in Europe and has them delivered to warehouses in three ports, Antwerp, Barcelona, and Cherbourg. The various components and parts are loaded into containers based on demand from their U.S.customers. Each port has a limited fixed number of containers available each month. The containers are then shipped overseas by container ships to the ports of Boston, Savannah, Mobile and Houston. From these seaports the containers are typically coupled with trucks and hauled to inland ports in Ohio, Texas, and North Carolina. There are fixed number of freight haulers available at each port each month. These inland ports are sometimes called "freight villages" or intermodal junctions where the containers are collected and transferred from one transport mode to anotherfrom truck to rail or vice-versa. From the inland ports the containers are transported to the Phillups distribution centers in Phoenix, Columbus, Kansas City, Louisville, and Memphis. Following are the handling and shipping costs ($/container) between each of the em barkation and destination points along this overseas supply chain and the available containers at each port. U.S. PORTS 2,345 125 EUROPEANS PORTS 4. BOSTON 5. SAVANNAH 6. MOBILE 7. HOUSTON AVAILABLE CONTAINERS 1. Antwerp 1,725 1,800 2,700 2. Barcelona 1,825 1,750 1,945 2.320 210 3. Cherbourg 2,060 2,175 2,050 2,475 160 INLAND PORTS U.S. PORTS 4. Boston 5. Savannah 8. OHIO 9. TEXAS 10. NORTH CAROLINA INTERMODAL CAPACITY (CONTAINERS) 825 545 320 85 750 675 450 110 325 605 690 100 270 510 1,050 130 6. Mobile 7. Houston Intermodal Capacity (containers) 170 240 140 DISTRIBUTION CENTERS INLAND PORT 11. PHOENIX 12. COLUMBUS 13. KANSAS CITY 14. LOUISVILLE 15. MEMPHIS 8. Ohio 450 830 565 420 960 9. Texas 880 520 450 380 660 10. North 1,350 390 1,200 450 310 Carolina Demand 85 60 105 50 120 Determine the optimal shipments from each point of embarkation to each destination along this supply chain that will result in the minimum total shipping cost. G . J L M N 0 P Shipping cost ($/container) Available Containers Containers Shipped 125 210 160 European Port 1. Antwerp 2. Cherbourg 3. Barcelona 4. Boston 1,725 1,825 2.060 U.S. Port 5. Savannah 6. Mobile 1,800 2,345 1,750 1,945 2,175 2,050 7. Houston 2,700 2,320 2,475 Shipping cost(S/container) Containers Shipped U.S. Port 4. Boston 5. Savannah 6. Mobile 7. Houston 8. Ohio 825 750 325 270 Inland Port 9. Texas 10. North Carolina 545 320 675 450 605 690 510 1,050 Shipping cost ($/container) A B D E F 1 Homework Problem #S11-41 2 3 U.S. Port 4 European Port 4. Boston 5. Savannah 6. Mobile 7. Houston 5 1. Antwerp 6 2. Cherbourg 7 3. Barcelona 8 Containers Shipped 9 10 11 12 Inland Port Intermodal 13 U.S. Port 8. Ohio 9. Texas 10. North Carolina Capacity 14 4. Boston 85 15 5. Savannah 110 16 6. Mobile 100 17 7. Houston 130 18 Intermodal Capacity 170 240 140 19 Containers shipped 20 21 22 Distribution Centers 23 Inland Port 11. Phoenix 12. Columbus 13. Kansas City 14. Louisville 24 8. Ohio 25 9. Texas 26 10. North Carolina 27 Demand 85 60 105 50 28 Containers Shipped 29 30 31 Transshipment Total cost = 32 Flows 33 34 35 36 37 38 0 39 0 0 40 41 42 43 44 Containers 15. Memphis Shipped Inland Port 8. Ohio 9. Texas 10. North Carolina 11. Phoenix 450 880 1,350 Distribution Centers 12. Columbus 13. Kansas City 14. Louisville 15. Memphis 830 565 420 960 520 450 380 660 390 1,200 450 310 120