Solve with excel: Problem $2-$ Network Design problem

DryIce Inc., is a manufacturer of air conditioners that has seen its demand grow significantly. The company anticipates nationwide demand for the next year 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 DryIce 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\mathrm{.}1$ and Table $1\mathrm{.}2,$ along with the cost of producing and shipping an air conditioner to each of the four markets. Formulate the problem as a linear programming model to decide where DryIce should build its factories and their capacities. You do Not need to solve it$.$

Production and transport costs for DryIce Inc.

Table $1\mathrm{.}1$ Demand Region

Supply City

East

South

Midwest

West

New York

$$211$

$$232$

$$240$

$$300$

Atlanta

$$232$

$$212$

$$230$

$$280$

Chicago

$$238$

$$230$

$$215$

$$270$

San Diego

$$299$

$$280$

$$270$

$$225$

Demand

$110,000$

$180,000$

$120,000$

$100,000$

Table $1\mathrm{.}2$ Plant Option

Supply City

Capacity

Fixed Cost

Capacity

Fixed Cost

New York

$200,000$

$$6,000,000$

$400,000$

$$10,000,000$

Atlanta

$200,000$

$$5,500,000$

$400,000$

$$9,200,000$

Chicago

$200,000$

$$5,600,000$

$400,000$

$$9,300,000$

San Diego

$200,000$

$$6,100,000$

$400,000$

$$10,200,000$