Can you please do Q2 in excel , and also instead of the given data in the case, use the data from below.

CASE STUDY Managing a Merger at Lightning Networks After receiving regulatory approval from the European her initial attention on the distribution networks the two Union, Lightning Networks, a major wireless carrier, companies used to fulfill demand for installation and and SatTV, the largest satellite TV provider in Europe, repair products. The merger offered an opportunity to 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 laroe The Current Distribution Network combine the two distribution networks. installation or repair by Lightning 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 some cost reduction opportunities. She decided to focus Any new 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 or SatTV was charged with identifying 136 Chapter 5* Network Design in the Supply Chain TABLE 5-16 Annual Demand in Europe for Lightning Networks (wireless) and SatTV (satellite) Satellite Wireless Satellite Wireless Demand Demand Zone Zone Demand Demand Middle South Northwest 200,000 120,000 120,000 120,000 110,000 Southwest Northeast 100,000 100,000 150,000 Southeast Middle North 220,000 100,000 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 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 W: observe that the Madrid warehouse can serve demand of warehouses flexible required an investment equivalent TABLE 5-17 Warehouse Specialization, Capacities, and Fixed Costs Specialization Fixed Cost (euro/year) Location Capacity Madrid Wireless 370,000 600,000 Wireless Rotterdam 650,000 420,000 Krakow Wireless 520,000 310,000 Toulouse Satellite 280,000 475,000 Munich Satellite 290,000 488,000 Satellite Budapest 250,000 425,000 TABLE 5-18 Variable Distribution Cost per Unit in Euro Middle North Middle Southwest Northwest South Northeast Southeast Madrid 4.00 2.50 1.50 3.00 2.75 4.50 Rotterdam 1.75 3.00 1.50 3.00 2.50 3.50 Krakow 3.25 4.00 2.50 3.00 2.00 2.50 2,75 2.50 Toulouse 2.00 2.00 3.75 4.00 Munich 2.50 3.00 2.25 3.00 2.25 2.75 Budapest 3.50 3.75 2.50 2.50 2.50 2.00 Chapter 5 Network Design in the Supply Chain 137 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 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- 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 ity doubled? cost of 100,000 euro because only 80 percent of the fixed cost is saved. A G 1 Warehouse Capacity and Fixed Cost Annual Fixed Cost 2 Location Capacity 3 Madrid 4 Rotterdam 5 Krakow 6 Toulouse 378634 610106 429814 660956 317218 528746 286510 482981 7 Munich 296746 496202 8 Budapest 255802 432131 10 Regional Demand by Product Middle South Middle Northwest Southwest North Northeast Southeast 11 12 Wireless 13 Satellite 191,064 86,069 95,614 95,614 210,154 114,704 143,339 95,614 114,704 95,614 114,704 105,159 14 15 Distribution Costs Middle Middle South Northwest Southwest North Northeast Southeast 16 17 Madrid 18 Rotterdam 19 Krakow 20 Toulouse 21 Munich 22 Budapest 2.78 2.53 1.52 3.03 4.04 4.55 1.77 3.03 1.52 3.03 2.53 3.54 3.28 4.04 2.53 3.03 2.02 2.53 2.02 2.78 2.02 2.53 3.79 4.04 3.03 2.27 3.03 2.27 2.53 2.78 3.54 3.79 2.53 2.53 2.53 2.02 CASE STUDY Managing a Merger at Lightning Networks After receiving regulatory approval from the European her initial attention on the distribution networks the two Union, Lightning Networks, a major wireless carrier, companies used to fulfill demand for installation and and SatTV, the largest satellite TV provider in Europe, repair products. The merger offered an opportunity to 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 laroe The Current Distribution Network combine the two distribution networks. installation or repair by Lightning 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 some cost reduction opportunities. She decided to focus Any new 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 or SatTV was charged with identifying 136 Chapter 5* Network Design in the Supply Chain TABLE 5-16 Annual Demand in Europe for Lightning Networks (wireless) and SatTV (satellite) Satellite Wireless Satellite Wireless Demand Demand Zone Zone Demand Demand Middle South Northwest 200,000 120,000 120,000 120,000 110,000 Southwest Northeast 100,000 100,000 150,000 Southeast Middle North 220,000 100,000 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 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 W: observe that the Madrid warehouse can serve demand of warehouses flexible required an investment equivalent TABLE 5-17 Warehouse Specialization, Capacities, and Fixed Costs Specialization Fixed Cost (euro/year) Location Capacity Madrid Wireless 370,000 600,000 Wireless Rotterdam 650,000 420,000 Krakow Wireless 520,000 310,000 Toulouse Satellite 280,000 475,000 Munich Satellite 290,000 488,000 Satellite Budapest 250,000 425,000 TABLE 5-18 Variable Distribution Cost per Unit in Euro Middle North Middle Southwest Northwest South Northeast Southeast Madrid 4.00 2.50 1.50 3.00 2.75 4.50 Rotterdam 1.75 3.00 1.50 3.00 2.50 3.50 Krakow 3.25 4.00 2.50 3.00 2.00 2.50 2,75 2.50 Toulouse 2.00 2.00 3.75 4.00 Munich 2.50 3.00 2.25 3.00 2.25 2.75 Budapest 3.50 3.75 2.50 2.50 2.50 2.00 Chapter 5 Network Design in the Supply Chain 137 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 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- 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 ity doubled? cost of 100,000 euro because only 80 percent of the fixed cost is saved. A G 1 Warehouse Capacity and Fixed Cost Annual Fixed Cost 2 Location Capacity 3 Madrid 4 Rotterdam 5 Krakow 6 Toulouse 378634 610106 429814 660956 317218 528746 286510 482981 7 Munich 296746 496202 8 Budapest 255802 432131 10 Regional Demand by Product Middle South Middle Northwest Southwest North Northeast Southeast 11 12 Wireless 13 Satellite 191,064 86,069 95,614 95,614 210,154 114,704 143,339 95,614 114,704 95,614 114,704 105,159 14 15 Distribution Costs Middle Middle South Northwest Southwest North Northeast Southeast 16 17 Madrid 18 Rotterdam 19 Krakow 20 Toulouse 21 Munich 22 Budapest 2.78 2.53 1.52 3.03 4.04 4.55 1.77 3.03 1.52 3.03 2.53 3.54 3.28 4.04 2.53 3.03 2.02 2.53 2.02 2.78 2.02 2.53 3.79 4.04 3.03 2.27 3.03 2.27 2.53 2.78 3.54 3.79 2.53 2.53 2.53 2.02