Question
SportGear Internationalis a designer,manufacturer and distributor of sport gear. While most of their sales occur in the United States, SportGear International has managedto establish itself
SportGear Internationalis a designer,manufacturer and distributor of sport gear. While most of their sales occur in the United States, SportGear International has managedto establish itself as a significant player in the selling of premium qualitysoccer jersey shirts. Those jerseysare produced in the USA andshipped in containers to three major seaportsin Europe: Genoa (Italy), Barcelona(Spain)and Marseilles(France). After reaching the destination seaports, the containersare loaded on trucks forfinal delivery at one of their four warehouse locations: Madrid (Spain), Lyon (France), Munich (Germany) and Milano (Italy).
Answers need to be given in Excel. See question and details below:
Please answer in Excel and upload screenshots of Excel with how-to's if possible.
Thank you!
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 scaports 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). Demand at Dach Warehouse Plant: Madrid Lyon Munich Milano Total #containers 240 540 120 300 1.200 SportGear International has negotiated contracts for a set maximum number of containers at each seaport: Max # of Containers Seaport: #containers Genoa 300 Barcelona 420 Marseilles 480 Total 1.200 The containers are transported by common carrier and charges are on a per-container basis. Ground Transportation Costs Seaport to Warehouse Destination Warehouse Origin Seaport Madrid Lyon Munich Genoa $240 S260 $82 Barcelona $122 S80 S200 Marseilles $205 $180 S244 Milano S124 S220 S84 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 agreeinent 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 alrcady available at other scaports. 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 Lyon Munich S230 $70 Milano $150 Note: Use the Simplex LP algorithm when conducting your computations in Solver. Please answer questions 1 and 2 into two separate worksheets. Submit your Excel file. 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 scaports 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). Demand at Dach Warehouse Plant: Madrid Lyon Munich Milano Total #containers 240 540 120 300 1.200 SportGear International has negotiated contracts for a set maximum number of containers at each seaport: Max # of Containers Seaport: #containers Genoa 300 Barcelona 420 Marseilles 480 Total 1.200 The containers are transported by common carrier and charges are on a per-container basis. Ground Transportation Costs Seaport to Warehouse Destination Warehouse Origin Seaport Madrid Lyon Munich Genoa $240 S260 $82 Barcelona $122 S80 S200 Marseilles $205 $180 S244 Milano S124 S220 S84 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 agreeinent 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 alrcady available at other scaports. 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 Lyon Munich S230 $70 Milano $150 Note: Use the Simplex LP algorithm when conducting your computations in Solver. Please answer questions 1 and 2 into two separate worksheets. Submit your Excel fileStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started