Problem $25$: A manufacturer is considering alternatives for building new plants

in order to be located closer to three of its primary customers with whom it

intends to develop long$-$term relationships. The net cost of manufacturing and

transporting each unit of the product to its customers will vary depending on

where the plant is built and the production capacity of the plant. These costs are

summarized in the following table:

Net Cost per Unit to Supply Customer

Plant X Y Z

$1353045$

$2454050$

$3706550$

$4204525$

$5654545$

The annual demand for products from customers X$,$ Y$,$ and Z is expected to be

$40,000,25,000,$ and $35,000$ units, respectively. The annual production capacity

and construction costs for each plant are:

Plant Production Capacity Construction Cost $($in $1000$s$)$

$140,000$ $$1,325$

$230,000$ $$1,100$

$350,000$ $$1,500$

$420,000$ $$1,200$

$540,000$ $$1,400$

The company wants to determine which plants to build in order to satisfy

customer demand at a minimum total cost.

a$)$ Formulate an ILP model for this problem.

b$)$ Create a spreadsheet model for this problem and solve it$.$

c$)$ What is the optimal solution?