OM 540: Supply Chain Management Assignment 4: Distribution Systems and Network Design Hot & Cold and CaldoFreddo are two European manufacturers of home appliances that have merged. Hot & Cold has plants in France, Germany, and Finland, whereas CaldoFreddo has plants in the United Kingdom and Italy. The European market is divided into four regions: North, East, West, and South. 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 Hot & Cold CaldoFreddo Plant Variable Production and Shipping Costs North East South West Capacity Annual Fixed Cost France German y Finland 100 95 110 105 105 110 100 105 70 100 1000 2000 90 100 115 110 80 1500 Demand 60 20 30 90 UK Italy Demand 105 110 20 120 105 30 110 90 30 90 115 20 60 90 1200 1300 Use Excel Solver to answer the following questions: 1. Before the merger, what was the optimal demand allocation plan for Hot & Cold? 2. Before the merger, what was the optimal demand allocation plan for CaldoFreddo? 3. After the merger, what is the optimal demand allocation plan if none of the plants is to be shut down? 4. After the merger, what is the optimal network configuration and demand allocation plan if plants can be shut down (assume that a shutdown saves 100 percent of the annual fixed cost of the plant)? Please submit your Excel spreadsheet (each question should be on a separate tab) to the \"Week 4 Individual Assignment\" drop box