from pulp import LpProblem, LpVariable, lpSum, LpMinimize

# Data

factories $=[\text{'}$A$\text{'},\text{'}$B$\text{'},\text{'}$C$\text{'}]$

warehouses $=[\text{'}$A$\text{'},\text{'}$B$\text{'},\text{'}$C$\text{'},\text{'}$D$\text{'}]$

markets $=[\text{'}$A$\text{'},\text{'}$B$\text{'},\text{'}$C$\text{'},\text{'}$D$\text{'},\text{'}$E$\text{'}]$

production$\_$costs $=\{$

$(\text{'}$A$\text{'},\text{'}$A$\text{'})$: $5,(\text{'}$A$\text{'},\text{'}$B$\text{'})$: $6,(\text{'}$A$\text{'},\text{'}$C$\text{'})$: $4,(\text{'}$A$\text{'},\text{'}$D$\text{'})$: $7,$

$(\text{'}$B$\text{'},\text{'}$A$\text{'})$: $7,(\text{'}$B$\text{'},\text{'}$B$\text{'})$: $4,(\text{'}$B$\text{'},\text{'}$C$\text{'})$: $6,(\text{'}$B$\text{'},\text{'}$D$\text{'})$: $5,$

$(\text{'}$C$\text{'},\text{'}$A$\text{'})$: $4,(\text{'}$C$\text{'},\text{'}$B$\text{'})$: $8,(\text{'}$C$\text{'},\text{'}$C$\text{'})$: $2,(\text{'}$C$\text{'},\text{'}$D$\text{'})$: $9$

$\}$

transportation$\_$costs $=\{$

$(\text{'}$A$\text{'},\text{'}$A$\text{'})$: $3,(\text{'}$A$\text{'},\text{'}$B$\text{'})$: $5,(\text{'}$A$\text{'},\text{'}$C$\text{'})$: $4,(\text{'}$A$\text{'},\text{'}$D$\text{'})$: $6,(\text{'}$A$\text{'},\text{'}$E$\text{'})$: $7,$

$(\text{'}$B$\text{'},\text{'}$A$\text{'})$: $4,(\text{'}$B$\text{'},\text{'}$B$\text{'})$: $3,(\text{'}$B$\text{'},\text{'}$C$\text{'})$: $5,(\text{'}$B$\text{'},\text{'}$D$\text{'})$: $7,(\text{'}$B$\text{'},\text{'}$E$\text{'})$: $6,$

$(\text{'}$C$\text{'},\text{'}$A$\text{'})$: $5,(\text{'}$C$\text{'},\text{'}$B$\text{'})$: $4,(\text{'}$C$\text{'},\text{'}$C$\text{'})$: $3,(\text{'}$C$\text{'},\text{'}$D$\text{'})$: $8,(\text{'}$C$\text{'},\text{'}$E$\text{'})$: $5,$

$(\text{'}$D$\text{'},\text{'}$A$\text{'})$: $6,(\text{'}$D$\text{'},\text{'}$B$\text{'})$: $5,(\text{'}$D$\text{'},\text{'}$C$\text{'})$: $4,(\text{'}$D$\text{'},\text{'}$D$\text{'})$: $4,(\text{'}$D$\text{'},\text{'}$E$\text{'})$: $3$

$\}$

demand $=\{\text{'}$A$\text{'}$: $500,\text{'}$B$\text{'}$: $700,\text{'}$C$\text{'}$: $600,\text{'}$D$\text{'}$: $800,\text{'}$E$\text{'}$: $900\}$

production$\_$capacities $=\{\text{'}$A$\text{'}$: $2200,\text{'}$B$\text{'}$: $2500,\text{'}$C$\text{'}$: $3000\}$

warehouse$\_$capacities $=\{\text{'}$A$\text{'}$: $10000,\text{'}$B$\text{'}$: $13000,\text{'}$C$\text{'}$: $12000,\text{'}$D$\text{'}$: $9000\}$

# Create the LP problem

prob $=$ LpProblem$("$SupplyChainDesign$",$ LpMinimize$)$

# Define decision variables

X $=$ LpVariable.dicts$("$X$",[($i$,$ j$)$ for i in factories for j in warehouses$],$ lowBound$=0,$ cat$=$'Continuous'$)$

Y $=$ LpVariable.dicts$("$Y$",[($j$,$ k$)$ for j in warehouses for k in markets$],$ lowBound$=0,$ cat$=$'Continuous'$)$

# Define the objective function

prob $+=$ lpSum$($X$[($i$,$ j$)]*$ production$\_$costs$[($i$,$ j$)]$ for i in factories for j in warehouses$)\backslash$

$+$ lpSum$($Y$[($j$,$ k$)]*$ transportation$\_$costs$[($j$,$ k$)]$ for j in warehouses for k in markets$)$

# Add demand constraints

for k in markets:

prob $+=$ lpSum$($Y$[($j$,$ k$)]$ for j in warehouses$)==$ demand$[$k$]$

# Add supply constraints

for j in warehouses:

prob $+=$ lpSum$($X$[($i$,$ j$)]$ for i in factories$)<=$ warehouse$\_$capacities$[$j$]$

# Solve the problem

prob.solve$()$

# Print the results

print$("$Total Cost: $$",$ round$($prob$.$objective.value$(),2))$

print$("$

Production Plan:"$)$

for i in factories:

for j in warehouses:

if X$[($i$,$ j$)].$varValue $>0$:

print$($f$"$Factory $\{$i$\}->$ Warehouse $\{$j$\}$: $\{$X$[($i$,$ j$)].$varValue$\}$ units"$)$

print$("$

Transportation Plan:"$)$

for j in warehouses:

for k in markets:

if Y$[($j$,$ k$)].$varValue $>0$:

print$($f$"$Warehouse $\{$j$\}->$ Market $\{$k$\}$: $\{$Y$[($j$,$ k$)].$varValue$\}$ units"$)$

The code poses a linear programming problem and uses PuLP to find the optimal solution that minimizes total cost while satisfying demand requirements and supply constraints.

can you solve this code in excel?