$7.$ A manufacturer of cat towers wishes to locate a super assembly facility to meet their cat tower assembly needs for the next millennium. Currently, carpet, cylinders, yarn and fasteners are purchased in bulk from suppliers in North Haverbrook, Ogden, and Springfield in the quantities shown in the table. The super assembly facility would ship to distribution centers located in Seattle, Lubbock, Nashville, and Philadelphia. The locations of these cities on an x$-$y grid, transportation costs on a ton per mile basis, and total tonnage are all contained in the table. Use the spreadsheet provided to determine the location that will minimize the shipping costs to the super assembly center. $(10$ points$)$

Sources$/$Markets Transportation Cost $$/$ton mile $($Fn$)$ Quantity in Tons $($Dn$)$ Xn Yn

Sources $($Supply$)$

North Haverbrook $1\mathrm{.}4500225825$

Ogden $1\mathrm{.}2550250600$

Springfield $1\mathrm{.}04507001200$

Market

Seattle $2\mathrm{.}20450501200$

Lubbock $2\mathrm{.}00300450300$

Nashville $2\mathrm{.}20350800250$

Philadelphia $2\mathrm{.}004009501100$

You will need to modify the file provided to the information above.

In Solver, use the GRG Nonlinear $$ Solving Method.

A$.$ Where should the new plant be located and what is the total system shipping cost?

B$.$ The Logistic Manager is concerned, because he believes the transportation cost per ton mile from Ogden and to Seattle and Lubbock will increase by as much as $50\%$ of the next year. If the Logistic Manager concerns are realized where should the new plant be located and what is the total system shipping cost?

C$.$ Given the Logistic Manager has generally been accurate in the past with their concerns, but often overstates the percentage of the change. Where would you recommend to locate the new plant and what is the total system shipping cost of this location? Why do you recommend this location?