Please use Excel Solver and Screenshot all steps!

Question $2$

The Distribution Unlimited Co$.$ will be producing the same new product at two different

factories, and then the product must be shipped to two warehouses. Factory $1$ can send an

unlimited amount by rail to Warehouse $1$ only, whereas Factory $2$ can send an unlimited

amount by rail to Warehouse $2$ only. Independent truckers can be used to ship up to $50$

units from each factory to a distribution center, from which up to $50$ units can be shipped

to each warehouse. The shipping cost per unit for each alternative is shown in Table $2\mathrm{.}1$

along with the amounts to be produced at the factories and the amounts needed at the

warehouses. The objective is to determine how to ship the needed units at a minimum total

cost$.$ Figure $2\mathrm{.}1$ displays the distribution network for this problem. Each arrow shows one

of the feasible shipping lanes; the corresponding unit shipping cost is given along the

middle of the arrow. Also shown is the capacity limit $($if any$)$ for how much can be shipped

through each shipping lane. Formulate and solve this as a linear programming model.

Solve using any IP software $($Lindo$,$ Excel Solver etc.$).$ Attach the computer output

containing the optimal solution.

TABLE $2\mathrm{.}1$ Data for the Distribution Unlimited Co$.$

Rest of the story

This is a "classroom" version of a larger problem of planning the Supply, Distribution,

and Marketing $($SDM$)$ at Citgo Corporation that was successfully solved by linear

programming during $1984-85.$ At the time the SDM System was studied, Citgo owned or

leased $36$ product storage terminals that were supplied through $5$ Distribution Center

terminals via a network of pipelines, tankers and barges. Citgo had $350$ storage terminals

from where products were sold to customers. A Linear Programming model was

developed to coordinate the supply, distribution and marketing of each of its major

products throughout the United States.

The SDM System has greatly improved the efficiency of Citgo's operations, enabling a

huge reduction in product inventory with no drop in service levels. Soon after its

introduction, the value of petroleum products being held in inventory was reduced by

$$116\mathrm{.}5$ million. This huge reduction in capital tied up in carrying inventory resulted in

saving about $$14$ million annually in interest expenses for borrowed capital adding $$14$

million to Citgo's annual profits. Now many years after the introduction of the SDM

system, Citgo continues to use and benefit from this system. For more details on Citgo's

SDM LP system, read: