Transportation$/$Distribution Problem Assignment

A distribution system has $3$ DCs and $5$ stores. Table $1$ provides the transportation$/$distribution costs per route for a family of high end computers. The available number of units as well as the requirements and penalties at each store are in Tables $2$ and $3$ respectively.

A$.$ Setup in Excel$/$Solver$.$

B$.$ Perform a sensitivity analysis and consider the following scenarios.

Supply scenarios

Supply case $1.$ Current situation.

Supply case $2.$ Eliminating inveOry availability in DC$1(0$ units$),$ while DC$2$ will have $134$ and DC$3$ will have $96$ units available respectively. This would represent a cost reduction of $$300$ in overhead.

Supply case $3.$ Eliminating inventory availability in DC$3(0$ units$),$ while DC$1$ will have $90$ and DC$2$ will have $140$ units available respectively. This would represent a cost reduction of $$220$ in overhead.

Demand scenarios

Demand case $1.$ Current situation.

Demand case $2.$ Demand in stores $2$ and $3$ decreases by $15$ units$/$ store and increases in stores $4$ and $5$ by $15$ units$/$store$.$

There are a total of $6$ scenario combinations $($supply case $1$& demand case $1($the baseline$),$ s$.$ case $1$& d$.$ case $2,$ s$.$ case $2$ & d$.$ case $1,$ s$.$ case $2$ & d$.$ case $2,$ s$.$ case $3$ & d$.$ case $1,$ s$.$ case $3$ & d$.$ case $2).$

Demand case $2.$ Demand in stores $2$ and $3$ decreases by $15$ units$/$ store and increases in stores $4$ and $5$ by $15$ units$/$store$.$

There are a total of $6$ scenario combinations $($supply case $1$ & demand case $1($the baseline$),$ s$.$ case $1$ & d$.$ case $2,$ s$.$ case $2$ & d$.$ case $1,$ s$.$ case $2$ & d$.$ case $2,$ s$.$ case $3$ & d$.$ case $1,$ s$.$ case $3$ & d$.$ case $2).$ Describe the changes in total cost and the decisions made $($flows and units missing at each store$)$ for each case. Use tables to summarize the results and graphs to explain the tendencies.

Table $1.$ Transportation$/$Distribution costs

$\backslash$table$[[,$s$1,$s$2,$s$3,$s$4,$s$5],[$dc$1,$$$18\mathrm{.}4,$$$15\mathrm{.}1,$$$20\mathrm{.}2,$$$17\mathrm{.}8,$$$20\mathrm{.}0$