Can you please do Q1 in excel, and instead of using the data from above use the one from below tables.

her initial attention on the distribution networks the two combine the two distribution networks. CASE STUDY Managing a Merger at Lightning Networks After receiving regulatory approval from the European Union, Lightning Networks, a major wireless carrier, and SatTV, the largest satellite TV provider in Europe, completed their 50 billion euro merger in 2016. After initial skepticism when the deal was first announced, analysts had warmed to the idea of synergies in the merger. Lightning expected to benefit from the large customer base of SatTV and the company announced that it expected significant annual cost savings within three years of the merger. Simone Durand, senior VP of supply chain at Lightning, was charged with identifying some cost reduction opportunities. She decided to focus The Current Distribution Network Any new installation or repair by Lightning or SatTV required a set of products for the technician to complete the job. Rather than carrying these products with techni- cians, both companies had decided to centralize product inventories in a few locations. Annual product demand 136 Chapter 5 Network Design in the Supply Chain TABLE 5-16 Annual Demand in Europe for Lightning Networks (wireless) and SatTV (satellite) Wireless Satellite Wireless Satellite Zone Demand Demand Zone Demand Demand Northwest 200,000 120,000 Middle South 120,000 120,000 Southwest 100,000 100,000 Northeast 150,000 110,000 Middle North 220,000 100,000 Southeast 90,000 100,000 for the two companies across six regions in Europe was much annual demand it can handle. From Table 5-17. as shown in Table 5-16. observe that the Madrid warehouse can serve demand of Lightning had served its product needs from three up to 370,000 units. The variable cost of shipping one unit warehouses located in Madrid, Spain; Rotterdam, Nether- (either wireless or satellite) from each warehouse location lands; and Krakow, Poland. SatTV had served its product to each market is shown in Table 5-18. needs from three warehouses located in Toulouse, France; Munich, Germany; and Budapest, Hungary. Each facility The Network Options was specialized to handle either wireless or satellite prod- ucts because of the historical focus of the company it Simone had a short term and a long term decision to belonged to. The specialization, capacity, and annual make. In the short term, she had to decide whether to fixed cost for each facility were as shown in Table 5-17. make all the warehouses flexible or not. Making all The capacity of each warehouse is given in terms of how warehouses flexible required an investment equivalent TABLE 5-17 Warehouse Specialization, Capacities, and Fixed Costs Location Specialization Capacity Fixed Cost (euro/year) Madrid Wireless 370,000 600,000 Rotterdam Wireless 420,000 650,000 Krakow Wireless 310,000 520,000 Toulouse Satellite 280,000 475,000 Munich Satellite 290,000 488,000 Budapest Satellite 250,000 425,000 TABLE 5-18 Variable Distribution Cost per Unit in Euro Northwest 2.50 1.75 3.25 2.00 2.25 3.50 Southwest 1.50 3.00 4.00 Rotterdam Krakow Toulouse Munich Budapest Middle North 3.00 1.50 2.50 2.75 2.25 2.50 Middle South 2.75 3.00 3.00 2.50 2.50 2.50 Northeast 4.00 2.50 2.00 3.75 2.75 2.50 Southeast 4.50 3.50 2.50 4.00 3.00 2.00 2.00 3.00 3.75 to an additional annual cost of 200.000 euro. Flexible warehouses, however, could be used to serve demand for both wireless and satellite products. In the longer term, Simone had to decide whether to restructure the distribution network. She could choose to close some warehouses, leave others open as they were, or double the capacity of some warehouses. Dou- bling the capacity of a warehouse would increase its annual fixed cost by 80 percent. Thus, if the capacity of the Madrid warehouse was doubled, its annual fixed cost would be 900.000 euro. Closing a warehouse would also incur some cost, thus reducing the annual fixed cost that could be saved. Simone's team estimated that closing a warehouse would save 80 percent of the annual fixed cost. Thus, closing the Madrid warehouse would still result in an annual cost of 100,000 euro because only 80 percent of the fixed cost is saved. Study Questions 1. What is the annual cost if Lightning uses the current net- work with warehouses specialized as in Table 5-17) opti- mally to meet European demand? 2. Should Simone make all warehouses flexible given the additional cost of 200.000 euro per year? 3. What supply chain network configuration do you recom- mend for the long term if demand is as in Table 5-16? Should any warehouses be closed? Should any warehouses see their capacity doubled? 4. What supply chain network configuration do you recom- mend for the long term if the Northeast and Southeast demand is expected to increase by 30 percent while all other demands remain as in Table 5-16? Should any ware- houses be closed? Should any warehouses see their capac- ity doubled? A B C D E 61 1 Warehouse Capacity and Fixed Cost Annual 2 Location Capacity Fixed Cost 3 Madrid 378634 610106 4 Rotterdam 429814 660956 5 Krakow 317218 528746 6 Toulouse 286510 482981 7 Munich 296746 496202 8 Budapest 255802 432131 9 10 Regional Demand by Product Middle Middle Northwest Southwest North South Northeast Southeast 12 Wireless 191,064 95,614 210,154 114,704 143,339 86,069 13 Satellite L 114,704 95,614 95,614 114,704 105,159 95,614 11 14 15 Distribution Costs Middle 16 Northwest Southwest North 17 Madrid 2.53 1.52 3.03 18 Rotterdam 1.77 3.03 1.52 19 Krakow 3.28 4.04 2.53 20 Toulouse 2.02 2.02 2.78 21 Munich 2.27 2.27 22 Budapest 2.53 Middle South 2.78 3.03 3.03 2.53 2.53 2.53 Northeast 4.04 2.53 2.02 3.79 2.78 2.53 Southeast 4.55 3.54 2.53 4.04 3.03 3.03 3.79 3.54 2.02 her initial attention on the distribution networks the two combine the two distribution networks. CASE STUDY Managing a Merger at Lightning Networks After receiving regulatory approval from the European Union, Lightning Networks, a major wireless carrier, and SatTV, the largest satellite TV provider in Europe, completed their 50 billion euro merger in 2016. After initial skepticism when the deal was first announced, analysts had warmed to the idea of synergies in the merger. Lightning expected to benefit from the large customer base of SatTV and the company announced that it expected significant annual cost savings within three years of the merger. Simone Durand, senior VP of supply chain at Lightning, was charged with identifying some cost reduction opportunities. She decided to focus The Current Distribution Network Any new installation or repair by Lightning or SatTV required a set of products for the technician to complete the job. Rather than carrying these products with techni- cians, both companies had decided to centralize product inventories in a few locations. Annual product demand 136 Chapter 5 Network Design in the Supply Chain TABLE 5-16 Annual Demand in Europe for Lightning Networks (wireless) and SatTV (satellite) Wireless Satellite Wireless Satellite Zone Demand Demand Zone Demand Demand Northwest 200,000 120,000 Middle South 120,000 120,000 Southwest 100,000 100,000 Northeast 150,000 110,000 Middle North 220,000 100,000 Southeast 90,000 100,000 for the two companies across six regions in Europe was much annual demand it can handle. From Table 5-17. as shown in Table 5-16. observe that the Madrid warehouse can serve demand of Lightning had served its product needs from three up to 370,000 units. The variable cost of shipping one unit warehouses located in Madrid, Spain; Rotterdam, Nether- (either wireless or satellite) from each warehouse location lands; and Krakow, Poland. SatTV had served its product to each market is shown in Table 5-18. needs from three warehouses located in Toulouse, France; Munich, Germany; and Budapest, Hungary. Each facility The Network Options was specialized to handle either wireless or satellite prod- ucts because of the historical focus of the company it Simone had a short term and a long term decision to belonged to. The specialization, capacity, and annual make. In the short term, she had to decide whether to fixed cost for each facility were as shown in Table 5-17. make all the warehouses flexible or not. Making all The capacity of each warehouse is given in terms of how warehouses flexible required an investment equivalent TABLE 5-17 Warehouse Specialization, Capacities, and Fixed Costs Location Specialization Capacity Fixed Cost (euro/year) Madrid Wireless 370,000 600,000 Rotterdam Wireless 420,000 650,000 Krakow Wireless 310,000 520,000 Toulouse Satellite 280,000 475,000 Munich Satellite 290,000 488,000 Budapest Satellite 250,000 425,000 TABLE 5-18 Variable Distribution Cost per Unit in Euro Northwest 2.50 1.75 3.25 2.00 2.25 3.50 Southwest 1.50 3.00 4.00 Rotterdam Krakow Toulouse Munich Budapest Middle North 3.00 1.50 2.50 2.75 2.25 2.50 Middle South 2.75 3.00 3.00 2.50 2.50 2.50 Northeast 4.00 2.50 2.00 3.75 2.75 2.50 Southeast 4.50 3.50 2.50 4.00 3.00 2.00 2.00 3.00 3.75 to an additional annual cost of 200.000 euro. Flexible warehouses, however, could be used to serve demand for both wireless and satellite products. In the longer term, Simone had to decide whether to restructure the distribution network. She could choose to close some warehouses, leave others open as they were, or double the capacity of some warehouses. Dou- bling the capacity of a warehouse would increase its annual fixed cost by 80 percent. Thus, if the capacity of the Madrid warehouse was doubled, its annual fixed cost would be 900.000 euro. Closing a warehouse would also incur some cost, thus reducing the annual fixed cost that could be saved. Simone's team estimated that closing a warehouse would save 80 percent of the annual fixed cost. Thus, closing the Madrid warehouse would still result in an annual cost of 100,000 euro because only 80 percent of the fixed cost is saved. Study Questions 1. What is the annual cost if Lightning uses the current net- work with warehouses specialized as in Table 5-17) opti- mally to meet European demand? 2. Should Simone make all warehouses flexible given the additional cost of 200.000 euro per year? 3. What supply chain network configuration do you recom- mend for the long term if demand is as in Table 5-16? Should any warehouses be closed? Should any warehouses see their capacity doubled? 4. What supply chain network configuration do you recom- mend for the long term if the Northeast and Southeast demand is expected to increase by 30 percent while all other demands remain as in Table 5-16? Should any ware- houses be closed? Should any warehouses see their capac- ity doubled? A B C D E 61 1 Warehouse Capacity and Fixed Cost Annual 2 Location Capacity Fixed Cost 3 Madrid 378634 610106 4 Rotterdam 429814 660956 5 Krakow 317218 528746 6 Toulouse 286510 482981 7 Munich 296746 496202 8 Budapest 255802 432131 9 10 Regional Demand by Product Middle Middle Northwest Southwest North South Northeast Southeast 12 Wireless 191,064 95,614 210,154 114,704 143,339 86,069 13 Satellite L 114,704 95,614 95,614 114,704 105,159 95,614 11 14 15 Distribution Costs Middle 16 Northwest Southwest North 17 Madrid 2.53 1.52 3.03 18 Rotterdam 1.77 3.03 1.52 19 Krakow 3.28 4.04 2.53 20 Toulouse 2.02 2.02 2.78 21 Munich 2.27 2.27 22 Budapest 2.53 Middle South 2.78 3.03 3.03 2.53 2.53 2.53 Northeast 4.04 2.53 2.02 3.79 2.78 2.53 Southeast 4.55 3.54 2.53 4.04 3.03 3.03 3.79 3.54 2.02