please explain how to do it and how to make excel sheet for it

Matt O'Grady, vice president of supply chain at Cool Wipes, thought that his current production and distribution network was not appropriate, given the significant increase in transportation costs over the past few years. Compared to when the company had set up its production facility in Chicago, transportation costs had increased by a factor of more than four and were expected to continue growing in the next few years. A quick decision on building one or more new plants could save the company significant amounts in transportation expense in the future. Cool Wipes Cool Wipes was founded in the late 1980s and produced baby wipes and diaper ointment. Annual demand for the two products was as shown in Table 1. At that time, the company had one factory in Chicago that produced both products for the entire country. The wipes line in the Chicago facility had an annual capacity of 5 million units, an annualized fixed cost of $5 million a year, and a variable cost of $10 per unit. The ointment line in the Chicago facility had an annual capacity of 1 million units, an annualized fixed cost of $1.5 million a year, and a variable cost of $20 per unit. The transportation costs per unit (for both wipes and ointment) are shown in Table 2. Table 1: Regional Annual Demand at Cool Wipes (in thousands) Zone Wipes Ointment Demand Demand Northwest 500 50 Southwest 700 90 Upper Midwest 900 120 Lower Midwest 800 Northeast 1,000 Southeast 600 65 120 70 Table 2: Transportation Costs per Unit Northwest Southwest Northeast Southeast Chicago Princeton Atlanta Los Angeles $6.32 $6.60 $6.72 $4.36 $6.32 $6.60 $6.48 $3.68 Upper Midwest $3.68 $5.76 $5.92 $6.32 Lower Midwest $4.04 $5.92 $4.08 $6.32 $5.76 $3.68 $4.04 $6.72 $5.96 $4.08 $3.64 $6.60 New Network Options Matt had identified Princeton, New Jersey; Atlanta; and Los Angeles as potential sites for new plants. Each new plant could have a wipes line, an ointment line, or both. A new wipes line had an annual capacity of 2 million units, an annual fixed cost of $2.2 million, and a variable production cost of $10 per unit. A new ointment line had an annual capacity of 1 million units, an annual fixed cost of $1.5 million, and a variable cost of $20 per unit. The current transportation costs per unit are shown in Table 2. Matt had to decide whether to build a new plant and if so, which production lines to put into the new plant. Study Questions 1. What is the annual cost of serving the entire nation from Chicago? 2. Do you recommend adding any plant(s)? If so, where should the plant(s) be built and what lines should be included? Assume that the Chicago plant will be maintained at its current capacity but could be run at lower utilization. Would your decision be different if transportation costs are half of their current value? What if they were double their current value? 3. If Matt could design a new network from scratch (assume he did not have the Chicago plant but could build it at the cost and capacity specified in the case), what production network would you recommend? Assume that any new plants built besides Chicago would be at the cost and capacity specified under the new network options. Would your decision be different if transportation costs were half of their current value? What if they were double their current value