Assignment $3$

The Hardgrave Machine Company produces computer components at its factories in Cincinnati, Kansas City, and Pittsburgh. These factories have not been able to keep up with demand for orders at Hardgrave's four warehouses in Detroit, Houston, New York, and Los Angeles. As a result, the firm has decided to build one more factory to expand its productive capacity. The two sites being considered are Seattle and Birmingham. Both cities are attractive in terms of labor supply, municipal services, and ease of factory financing. The following table presents the production costs and monthly supplies at each of the three existing factories, monthly demands at each of the four warehouses, and estimated production costs at the two proposed factories.

Hardgrave estimates that the monthly fixed cost of operating the proposed facility in Seattle would be $\backslash (\backslash$$ $400,000\backslash ).$ The Birmingham plant would be somewhat cheaper, due to the lower cost of living at that location. Hardgrave therefore estimates that the monthly fixed cost of operating the proposed facility in Birmingham would be $\backslash (\backslash$$ $325,000\backslash ).$ Note that the fixed costs at existing plants need not be considered here because they will be incurred regardless of which new plant Hardgrave decides to open$-$that is$,$ they are sunk costs.

Per unit transportation costs from each factory to each warehouse are summarized in the following table. Where should Hardgrave locate the new factory?

Note that the unit cost of shipping from each plant to each warehouse is found by adding production costs and shipping costs from the two tables provided.

a$)(70$pts$)$ Formulate the problem.

b$)\backslash ((30\backslash$mathrm$\{$pts$\})\backslash )$ Solve in Excel.