OM 540: Supply Chain Management Spring 2021 Assignment #3: Location and Flow Optimization Due in D2L drop box by 6 pm on Tuesday, 3/2 Hot & Cold and Caldo Freddo are two European manufacturers of home appliances that have merged. Hot & Cold has plants in Sweden, Germany, and Finland, whereas CaldoFreddo has plants in the Spain and Italy. The European market is divided into four regions: North, East, South, and West. Plant capacities (millions of units per year), annual fixed costs (millions of euros per year), regional demand (millions of units), and variable production and shipping costs (euros per unit) are shown below. + Company Plant East Variable Production and Shipping Costs North Capacity Annual South West Fixed Cost Sweden 100 110 105 100 50 1200 Hot & Cold Germany 95 105 110 105 90 1500 Finland 90 100 115 110 70 1300 Demand 50 20 20 70 Spain 105 120 110 90 50 1400 CaldoFreddo Italy 110 105 90 115 70 1600 Demand 20 10 30 20 Use Excel Solver to answer the following questions: 1. Before the merger, show the optimal demand allocation plan for Hot & Cold. 2. Before the merger, show the optimal demand allocation plan for Caldo Freddo. 3. After the merger, show the optimal demand allocation plan if none of the plants is to be shut down. 4. After the merger, show the demand allocation plan if plants can be shut down (assume that a shutdown saves 100 percent of the annual fixed cost of the plant). Be sure to indicate which plants, if any, should be shut down. Submit your Excel spreadsheet (each question should be on a separate tab) to the drop box on D2L. You can write the answers on the spreadsheet itself.