Question $1($Network Design$)$

Let's create a network design location problem that involves deciding on the optimal locations for distribution centers $($DCs$)$ to minimize the total logistics costs while serving a set of customers across different regions. This problem will incorporate elements of facility location and network design to determine where to establish DCs and how products should be transported from DCs to customers.

Problem Statement:

A company is looking to optimize its distribution network. It needs to decide on the optimal locations for up to two distribution centers $($DCs$)$ out of four possible locations to serve six customers efficiently. The objective is to minimize the total cost, which includes the fixed costs of establishing and operating the DCs and the transportation costs from factories to DCs and DCs to customers.

Given Data:

Potential DC Locations: D$1,$ D$2,$ D$3,$ D$4$

Customers: C$1,$ C$2,$ C$3,$ C$4,$ C$5,$ C$6$

Fixed Costs and capacity for Operating DCs:

D$1$: $$20,000,600$

D$2$: $$18,000,700$

D$3$: $$22,000,600$

D$4$: $$19,000,800$

Transportation Costs $($per unit$)$:

DCs to Customers:

C$1$ C$2$ C$3$ C$4$ C$5$ C$6$

D$1$ $$2$ $$2$ $$3$ $$3$ $$4$ $$4$

D$2$ $$3$ $$3$ $$2$ $$2$ $$3$ $$3$

D$3$ $$4$ $$4$ $$2$ $$2$ $$2$ $$2$

D$4$ $$2$ $$2$ $$4$ $$4$ $$3$ $$3$

Demand at Each Customer:

C$1$: $80$ units

C$2$: $120$ units

C$3$: $160$ units

C$4$: $100$ units

C$5$: $140$ units

C$6$: $200$ units

$1-$ Carefully write the mathematical formulations including the constraints.

$2-$ Using Excel Solver, Minimize the total cost of the distribution center, and transporting goods from DCs to customers.