SportGear International is a designer, manufacturer and distributor of sport gear. While most of their sales occur in the United States, SportGear International has managed to establish itself as a significant player in the selling of premium quality soccer jersey shirts. Those jerseys are produced in the USA and shipped in containers to three major seaports in Europe: Genoa (Italy), Barcelona (Spain) and Marseilles (France). After reaching the destination seaports, the containers are loaded on trucks for final delivery at one of their four warehouse locations: Madrid (Spain), Lyon (France), Munich (Germany) and Milano (Italy). Plant: Madrid Demand at Each Warehouse Lyon Munich Milano Total Barcelona Marseilles SportGear International has negotiated contracts for a set maximum number of containers at each seaport: Max # of Containers Seaport: Genoa Barcelona Marseilles Total Origin Seaport Madrid Genoa $240 # containers 240 540 120 300 1,200 $122 $205 The containers are transported by common carrier and charges are on a per-container basis. Ground Transportation Costs Seaport to Warehouse # containers 300 420 480 1,200 Destination Warehouse Munich $82 $200 $244 Lyon $260 S80 $180 Milano $124 $220 $84 1) SportGear International must decide how many containers to send from each seaport to each warehouse. Using Solver, identify the solution that would minimize overall ground transportation costs for SportGear International. What would be the total ground transportation costs? Realizing that an extra entry point in Europe would provide more flexibility in their network, SportGear International negotiated an agreement with a fourth seaport. Piraeus (Greece). The shipping costs from Piraeus are provided below. They can ship up to 300 containers through Piraeus, on top of the capacity already available at other seaports. Would this solution save money for SportGear International? If so, how much? If not, how much more expensive is it? Origin Seaport Piraeus Madrid $260 Destination Warehouse Munich $70 Lyon $230 Milano $150 Note: Use the Simplex IP algorithm when conducting your computations in Solver. Please answer questions 1 and 2 into two separate worksheets. Submit your Excel file.