Optimizing Child Push Chair Shipments: A Transportation Essay

1.  The Childfair Company has three plants producing child push chairs that are to be shipped to four distribution centers. Plants 1,2 and 3 produce 12, 17, and 11 shipments per month, respectively. Each distribution center needs to receive 10 shipments per month. The distance from each plant to the respective distribution centers is given below:  (Read pp. 425-431 & pp.480-484) (30 points) 

Distance to Distribution Center (Mile)  1 2 3 4 Plant     1 800 1,300 750 700 2 1100 1,400 900 1,100 3 600 1,300 800 900 

The freight cost for each shipment is $85 plus 55 cents mile. How much should be shipped from each plant to each of the distribution centers to minimize the total shipping cost? Formulate this problem as a transportation problem on a spreadsheet and then use the Excel Solver to obtain an optimal solution. Also, interpret the results using at least 150 words by addressing the following issues. Also, please make sure you provide answer report you obtained from Excel Solver.  

a. What is the optimal mix of shipments from each plant to each of the distribution center?  b. What is the minimum cost that could be achieved

1.The Childfair Company has three plants producing child push chairs that are to be shipped to four distribution centres. Plants 1, 2 and 3 produce 12, 17, and 11 shipments per month, respectively. Each distribution centre needs to receive 10 shipments per month. The distance from each plant to the respective distribution centres is given below: 

The freight cost for each shipment is $85 plus 55 cents mile. How much should be shipped from each plant to each of the distribution centres to minimize the total shipping cost?
The above situation can be formulated as a transportation problem. Using the Solver tool in Microsoft Excel, we can obtain the optimal mix of shipments from each plant to each of the distribution centre.

Basically this is a linear programming exercise. The whole exercise of linear programming involves three elements:
Firstly the data is tabulated in the form given in Sheet 1 of the attached excel file. The decision variables are the optimal mix of shipments from each plant to each of the distribution centre. The per shipment costs from each plant to the different distribution centres are given.