5.

A Steelworks company is working two mines to obtain its iron ore. This iron ore is shipped to either of two storage facilities. When needed, it then is shipped on to the companys steel plant. The diagram below depicts this distribution network, where M1 and M2 are the two mines, S1 and S2 are the two storage facilities, and P is the steel plant. The diagram also shows the monthly amounts produced at the mines and needed at the plant, as well as the shipping cost and the maximum amount that can be shipped per month through each shipping lane. Management now wants to determine the most economical plan for shipping and distributing the iron ore from the mines through the distribution network to the steel plant.

(a) Provide a complete spreadsheet model for the above case and solve it using Solver module on Excel to determine how to distribute the iron ore at the lowest possible total annual shipping costs.

(b) Comment on the results obtained in part (a) using your own words.