Show me the steps to solve Case Problem $($Unit $2)$

Magic Air, Inc. is a manufacturer of air conditioners that has seen its demand

grow significantly. The company anticipates nationwide demand for the year

$2024$ 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.

Table $1-$ Production and Transport Costs for Magic Air, Inc.

New York Atlanta Chicago San Diego

Annual fixed cost of $200,000$ plant $$6$ million $$5\mathrm{.}5$ million $$5\mathrm{.}6$ million $$6\mathrm{.}1$ million

Annual fixed cost of $400,000$ plant $$10$ million $$9\mathrm{.}2$ million $$9\mathrm{.}3$ million $$10\mathrm{.}2$ million

East

South

Midwest

West

$$211$

$$232$

$$240$

$$300$

$$232$

$$212$

$$230$

$$280$

$$238$

$$230$

$$215$

$$270$

$$299$

$$280$

$$270$

$$225$

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 $2024.$

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.Case Problem $($Unit $2)$

Magic Air, Inc. is a manufacturer of air conditioners that has seen its demand

grow significantly. The company anticipates nationwide demand for the year

$2024$ 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.

Table $1-$ Production and Transport Costs for Magic Air, Inc.

New York Atlanta Chicago San Diego

Annual fixed cost of $200,000$ plant $$6$ million $$5\mathrm{.}5$ million $$5\mathrm{.}6$ million $$6\mathrm{.}1$ million

Annual fixed cost of $400,000$ plant $$10$ million $$9\mathrm{.}2$ million $$9\mathrm{.}3$ million $$10\mathrm{.}2$ million

East

South

Midwest

West

$$211$

$$232$

$$240$

$$300$

$$232$

$$212$

$$230$

$$280$

$$238$

$$230$

$$215$

$$270$

$$299$

$$280$

$$270$

$$225$

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 $2024.$

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.