Whirlpool Inc. anticipates refrigerator demands for the following regions:

South: 180,000

Midwest: 120,000

East: 110,000

West: 100,000

Logistics Directors are designing the manufacturing network and they have selected four potential plant locations: NY, ATL, CHI, and SD. These potential plant sites could have capacities of either 200,000 or 400,000.

The fixed costs, and the cost of manufacturing and transporting a refrigerator is listed in the table below.

Using excel and solver, develop a linear programming model that answers the following questions:

1. Where should Whirlpool Inc. build its plant facilities?
2. How large in terms of capacity should each facility be?
3. What is the total cost for the optimal solution?

\begin{tabular}{lllll} & New York & Atlanta & Chicago & San Diego \\ \hline \begin{tabular}{l} Annual fixed cost of \\ 200,000 plant \end{tabular} & $6 million & $5.5 million & $5.6 million & $6.1 million \\ \hline \begin{tabular}{l} Annual fixed cost of \\ 400,000 plant \end{tabular} & $10 million & $9.2 million & $9.3 million & $10.2 million \\ \hline East & $211 & $232 & $238 & $299 \\ \hline South & $232 & $212 & $230 & $280 \\ \hline Midwest & $240 & $230 & $215 & $270 \\ \hline West & $300 & $280 & $270 & $225 \\ \hline \end{tabular}