should be solve by using excel solver. please also provide formula as well.

Soft Wipe, located in Chicago, was founded in late 1980s. It produces baby wipes and diaper ointment. The company currently has one factory in Chicago that produced a few products for the entire country. The wipe production line in Chicago facility had a capacity of 5 million units, annualized fixed cost of $5 million and a variable cost (i.e. material and labor costs) of $10 per unit. Next year's demand for wipes is shown in table 1 John, the vice president of supply chain, thought that the current production and distribution network was not appropriate, given the significant increase in demand and in transportation cost over the past few years. John is considering building one or more new plants. He has identified three potential locations, Princeton, NJ, Atlanta, and Los Angeles. Each new plant will have a production capacity of 2 million units, an annual fixed cost of $2.2 million, and a variable cost of $10 per unit. The current transportation cost per unit o wipe is given in Table 2 able 1 Demand for wipes Demand Region Upper lower Midwest Northeast Southeast orthwest Southwest Midwest Annual Demand for Wipes 500,000 700,000 900,000 800,000 1.000,000 600,000 Table 2 Transportation Cost Upper Lower Supply Region Northwest Southwest Midwes Midwest tst Southeast Chicago Princeton, NJ Atlanta Los Angeles 6.32 6.60 6.72 4.36 6.32 6.60 6.48 3.68 3.68 5.76 5.92 6.32 4.04 5.92 4.08 6.32 5.76 3.68 4.04 6.72 5.96 4.08 3.64 6.60 Questions: (1) What is the annual cost (including fixed cost and transportation cost) of serving the entire nation from the plant in Chicago? (2) What is the total cost if variable cost (i.e. material and labor costs) is added to the annual cost from question (1)? (3) What will be the total cost if more plants are built? (4) What will be the difference in costs if more plants are built (comparing part (2) and (3)