$1.(10$ pts$)$ Solve the problem "Problem $2-$ Network Design problem" from HW#$6$ using Excel. The solution to HW#$6$ is posted under Module $5.$ Please refer to the correct formulation.

$2.$ A manufacturer of cat towers wishes to locate a super assembly facility to meet their cat tower assembly needs. Currently, carpet, cylinders, yarn and fasteners are purchased in bulk from suppliers in North Haverbrook and Springfield in the quantities shown in table $1.$ The super assembly facility would ship to distribution centers located in Seattle, Arizona, and Philadelphia. The locations of all of these cities on an x$-$y grid, transportation costs on a ton per mile basis, and total tonnage are all contained in table $1.$ Please help to identify the geographic location of the super assembly center that minimizes the total shipping costs.

a$.(5$ pts$)$ Formulate the model and substitute the data in the model.

b$.(5$ pts$)$ Solve the problem in excel $(5$ pts$).$

Table $1$

Sources$/$Markets

Transportation Cost $$/$ton mile $($Fn$)$

Quantity in Tons $($Dn$)$

Xn

Yn

Supply

North Haverbrook

$1\mathrm{.}1$

$600$

$700$

$1200$

Springfield

$1\mathrm{.}4$

$400$

$225$

$825$

Market

Seattle

$2\mathrm{.}25$

$300$

$50$

$1200$

Arizona

$2\mathrm{.}25$

$200$

$450$

$300$

Philadelphia

$2\mathrm{.}25$

$500$

$950$

$1100$