Problem $2$: Binary Integer Programming and Transportation $(32$ points$)$

Noble Amazon sells books online. Management is trying to determine the best sites for the company$$s warehouses. The five potential sites under consideration are listed in the first column of the table below. Most of the sales come from customers in the United States. The average weekly demand from each region of the country, the fixed cost per week of each warehouse if it is operated, and the maximum capacity of each warehouse $($if it is operated$)$ are shown in the following table.

Average Shipping Cost $($$$/$book$)$

Warehouse Site

Northwest

Southwest

Midwest

Southeast

Northeast

Fixed Cost $($per Week$)$

Warehouse Capacity $($Books$/$Week$)$

Spokane, WA

$$2\mathrm{.}40$

$$3\mathrm{.}50$

$$4\mathrm{.}80$

$$6\mathrm{.}80$

$$5\mathrm{.}75$

$$40,000$

$20,000$

Reno, NV

$$3\mathrm{.}25$

$$2\mathrm{.}30$

$$3\mathrm{.}40$

$$5\mathrm{.}25$

$$6\mathrm{.}00$

$$30,000$

$20,000$

Omaha, NE

$$4\mathrm{.}05$

$$3\mathrm{.}25$

$$2\mathrm{.}85$

$$4\mathrm{.}30$

$$4\mathrm{.}75$

$$25,000$

$15,000$

Harrisburg, PA

$$5\mathrm{.}25$

$$6\mathrm{.}05$

$$4\mathrm{.}30$

$$3\mathrm{.}25$

$$2\mathrm{.}75$

$$40,000$

$25,000$

Jacksonville, FL

$$6\mathrm{.}95$

$$5\mathrm{.}85$

$$4\mathrm{.}80$

$$2\mathrm{.}10$

$$3\mathrm{.}50$

$$30,000$

$15,000$

Customer demand $($per week$)$

$8,000$

$12,000$

$9,000$

$14,000$

$17,000$

Noble Amazon wants to determine which warehouse site should operate and how books should be distributed from each warehouse to each region of the country to minimize the total cost.

a$.$

Is it a balanced transportation problem? Justify. $(2$ points$)$

b$.$

Define the decision variables of this problem. $(5$ points$)$

c$.$

Formulate algebraically the supply$/$warehouse constraints for this model. $(5$ points$)$

d$.$

Formulate this problem on a spreadsheet and use Excel$$s Solver to determine which warehouse site should operate and how books should be distributed from each warehouse to each region in the country to minimize the total cost. $($Provide the corresponding $$Excel Spreadsheet$$ and the $$Answer Report$).$ Include $$managerial statements$$ that communicate the results of the analyses $($i$.$e$.$ describe verbally the results$).(20$ points