We now consider a modified version of example $2$ covered in the synchronous class meeting on network design.

We assume:

There are two plants, three warehouses, and four market areas.

The capacity of plant $2$ is $100,000$ while the capacity of plant $1$ is unlimited.

The demands of the four market areas are $60,000,80,000,150,000,$ and $60,000$ respectively.

The capacity of each warehouse is $200,000$ if opened.

The fixed warehouse costs for the three potential warehouses are $500,000,600,000,$ and $450,000,$ respectively.

You are allowed to open two warehouses only.

The distribution cost $($$$)$ per unit is shown in the following table.

$\backslash$table$[[$Warehouse$,$P$1,$P$2,$C$1,$C$2,$C$3,$C$4],[$W$1,4,2,1,3,4,3],[$W$2,0,5,3,2,1,4],[$W$3,5,0,2,7,3,5]]$

Requirements:

Develop a spreadsheet model and apply Excel Solver to find out the optimal distribution strategies.

Submit your Excel file with all Solver parameters.