MANAGEMENT SCIENCE QUESTION

PLEASE DO IT IN EXCEL. INCLUDE ALL INPUTS AND FORMULAS

Each year, Data Corporal produces up to 10,000 computers in Boston and up to 6000 computers in Charlotte. There are customers in Los Angeles, New York, and Seattle who must receive 5300, 6700, and 3300 computers, respectively. Producing a computer costs $150 in Boston and $175 in Charlotte. Computers are transported by plane and can be sent through Chicago. The costs of sending a computer between pairs of cities are shown below.

a. Determine how to minimize the total (production plus shipping) cost of meeting Data Corporals annual demand. Why doesnt it make sense to ship any computers through Chicago?

b. Modify the model so that no more than 3000 computers can be shipped between any two cities, and find the optimal solution to this modified model. Why are computers now shipped through Chicago

Powerhouse data Unit shipping costs at current locations To \begin{tabular}{|l|l|r|r|r|r|r|} \hline & & NE & NW & MW & SE & SW \\ \hline From & LA & $27.86 & $4.00 & $20.54 & $21.52 & $13.87 \\ \hline & Chicago & $8.02 & $20.54 & $2.00 & $6.74 & $10.67 \\ \hline & NY & $2.00 & $27.86 & $8.02 & $8.41 & $15.20 \\ \hline \end{tabular} All of these values are in thousands of dollars. Unit shipping costs at potential new locations To \begin{tabular}{|r|l|r|r|r|r|r|} \hline & & NE & NW & MW & SE & SW \\ \hline From & Atlanta & $8.41 & $21.52 & $6.74 & $3.00 & $7.89 \\ \hline & Houston & $15.20 & $13.87 & $10.67 & $7.89 & $3.00 \\ \hline \end{tabular} Powerhouse data Unit shipping costs at current locations To \begin{tabular}{|l|l|r|r|r|r|r|} \hline & & NE & NW & MW & SE & SW \\ \hline From & LA & $27.86 & $4.00 & $20.54 & $21.52 & $13.87 \\ \hline & Chicago & $8.02 & $20.54 & $2.00 & $6.74 & $10.67 \\ \hline & NY & $2.00 & $27.86 & $8.02 & $8.41 & $15.20 \\ \hline \end{tabular} All of these values are in thousands of dollars. Unit shipping costs at potential new locations To \begin{tabular}{|r|l|r|r|r|r|r|} \hline & & NE & NW & MW & SE & SW \\ \hline From & Atlanta & $8.41 & $21.52 & $6.74 & $3.00 & $7.89 \\ \hline & Houston & $15.20 & $13.87 & $10.67 & $7.89 & $3.00 \\ \hline \end{tabular}