$($Distribution Management$)$

Consider a retailer selling a single product planning its distribution operations for the

following three$-$tier supply chain consisting of a set of three suppliers, four warehouses

and four retail locations. The maximum weekly supply from each supplier $($in number

of units per week$),$ the capacity of each warehouse $($in total number of units$),$ and the

weekly demand at each of the retail locations $($in number of units per week$)$ is provided

in the following tables.

The retailer plans its distribution as follows: places orders with suppliers and transports

ordered units from the supplier to its warehouses and then transports the products from

the warehouses to its retail locations. The transportation shipping costs $($per unit$)$ for

each of these two stages is provided below:

Unit transportation costs from each supplier to each warehouse $($first stage$)$

Unit transportation costs from each warehouse to each retail location $($second stage$)$

a$.$ First, formulate and solve an optimization problem $($using Excel Solver$)$ to minimize

the transportation cost for the first stage of the distribution, i$.$e$.$ moving the products

from the suppliers to the warehouses. Note that all units supplied by suppliers must

be transported to warehouses and warehouse capacity cannot be exceeded. By

solving the optimization problem, obtain the minimum transportation cost for the

first stage of the distribution.

b$.$ Using the answers obtained from part $($a$),$ formulate and solve an optimization

problem to minimize the transportation cost for the second stage of the distribution,

i$.$e$.$ moving the units stored in the warehouses to the retail locations. Note that the

number of units in each warehouse will be obtained from the solution to part $($a$).$

Also note that the demand at each retail location must be fully satisfied. By solving

the optimization problem, obtain the transportation cost for the second stage of the

distribution.

c$.$ Finally, formulate a single optimization problem for the two$-$stage distribution

problem, i$.$e$.$ moving the products from the suppliers to the warehouses and then to

the retail locations such that the total transportation cost is minimized. Solve this

two$-$stage distribution problem using Excel Solver.

Supplier $$ Warehouse $->1234$

$130202030$

$240406010$

$320305020$

Warehouse $$ Retail Location

$->1234$

$120101040$

$230403050$

$320503050$

$440405060$

d$.$ Compare the minimum transportation cost obtained from part $($c$).$ with the total

transportation costs obtained in parts $($a$)$ and $($b$).$ Briefly discuss the insights from

the comparison