Please set up problem to look like my Excel below. I need to figure out how to set up in the template provided and show formulas. Your task is to develop a spreadsheet model to solve this problem and present its solution using the appropriate Excel Solver. Use the Excel template provided.

Magic Air, Inc. is a manufacturer of air conditioners that has seen its demand grow significantly. The company anticipates nationwide demand for the year 2023 to be 180,000 units in the South, 120,000 units in the Midwest, 110,000 units in the East, and 100,000 units in the West. Managers at Magic Air are designing the manufacturing network and have selected four potential sites: New York, Atlanta, Chicago, and San Diego. Plants could have a capacity of either 200,000 or 400,000 units. The annual fixed costs at the four locations are shown in Table 1, along with the cost of producing and shipping an air conditioner to each of the four markets.

Develop an approach to the problem that allows Magic Air to determine where to build its factories and how large they should be. Your model should take into consideration the following preliminary thoughts that have been shared and validated with the management. Magic Air Inc. faces the tradeoff between fixed cost (that is lower per item in a larger plant) versus the cost of shipping and manufacturing. The typical scenarios that need to be considered are either having regional manufacturing if the shipping costs are significant or have a centralized facility if the fixed costs show significant economies to scale. Accordingly, the model should keep the units shipped from each plant to every region as variable and choose the fixed cost based on the emerging production quantities in each plant location.

The total system cost should then be minimized with the following constraints:

a. All shipment numbers need to be positive integers.

b. The maximum production capacity is 400,000

c. All shipments to a region should add up to the requirement for 2023.

Thank you in advance!!!

10 Decision Variables 18 Constraints Objective Function 28 Cost =