Please solve in EXCEL with pictuers and formaulas

CASE STUDY 5S.1: CONFIGURING THE SUPPLY CHAIN NETWORK: ADD DISTRIBUTION CENTERS OR NOT? Flooring and Beyond, Inc., is a U.S.-based importer and producer of flooring products. Its key product lines are engineered wood flooring, laminate flooring, and hardwood flooring. The company produces its engineered wood flooring and laminate flooring lines in its three manufacturing facilities located in Talladega, AL; Greenville, SC; and Rockford, IL. The hardwood flooring line is imported in a prefinished stage from manufacturers overseas, and then finished in the same facilities. Flooring and Beyond's major customers are home improvement retailers, commercial businesses, and professional installers. Its largest customers are in Seattle, WA; Phoenix, AZ; Harford, CT; Durango, CO; Austin, TX; and St. Paul, MN. Due to steady growth in the supply of housing in these markets, Flooring and Beyond has been receiving stable demand from these customers. The predictions for the year 2021 show that the company will fulfill a total of 1,450 pallets of flooring products each quarter. The demand requests from the major customers are shown in Table 5S.11. Table 5S.11 The company currently utilizes less-than-truckload services to ship its products from the manufacturing facilities to the customer locations. The costs associated with these shipments are summarized in Iable 5S.12. Company analysts have developed an optimization model to allocate demand from the major customers to the supply facilities. The resulting distribution plan minimizes the total distribution costs in the current configuration. However, company executives are considering certain modifications in Flooring and Beyond's distribution network in order to possibly reduce the total operating costs. Particularly, they are considering opening new distribution centers that will serve as transshipment points. The potential locations are Tulsa, OK; Provo, UT; and Charleston, WV. If a distribution center is open, then the manufacturing facilities will ship products to this center rather than shipping them directly to customer locations. The units that arrive at a distribution center will be unloaded, consolidated, sorted, and finally shipped to the customer locations. No production activity will take place at the distribution centers, meaning that any quantity of products that arrives at a distribution center will leave the distribution center with no change in its quantity. The executives think this new configuration will simplify the supply chain network, but they would like to see a detailed cost analysis to understand whether any cost savings can be achieved. They determined the relevant costs for this analysis as fixed costs of the new distribution centers and the transportation costs between the newly introduced origin-destination pairs. The data are summarized in Lables5S.13 and 5S.14. The results from the detailed cost analysis will provide company executives with insights into whether a distribution center should be made operational or not, and how the products will be distributed over the modified supply chain network in a way that will minimize the total distribution cost and the fixed cost of operating the distribution centers. Table 5S.13 Table 5S.14 Questions 1. What is the distribution plan that Flooring and Beyond uses in its current supply chain network? To answer this question, you need to develop the optimization model to allocate demand from the major customers to the supply facilities. Use Example 5 S. 3 as a reference when developing your model. 2. Without doing any further analysis, discuss how a supply chain network with distribution centers used as transshipment points can achieve cost savings. What might be some trade-offs in designing the network to include distribution centers? 3. Develop a new optimization model to determine whether the company can generate any cost savings by introducing the distribution centers. As in Question 1, you need to develop an optimization model to determine the distribution plan. Your model should also optimally determine whether a distribution center should be made operational. When writing the constraints in your optimization model, you will need to ensure that any quantity that has been shipped to a distribution center will depart from the same center with no change in the quantity. (Use Solved Problem 1 as a reference for developing an optimization model with transshipment centers.) What is the new distribution plan generated by solving this model? Is Flooring and Beyond able to obtain any cost savings