PLE produes its most popular model of lawn tractor in its Kansas City and Santiago plants and ships these units to major distribution centers in Atlanta, Caracas, Melbourne, Mexico City, London, Shanghai, and Toronto. Unit shipping costs can be found in the PLE database. Both Kansas City and the Santiago plants have a maximum annual capacity of 60,000 units. Long-term forecasts of annual demands at the distribution centers within 5 years that PLE wants plan for are Atlanta- 60,000 Caracas- 10,000 Melbourne- 6,000 Mexico City- 4,000 London- 40,000 Toronto- 5,000 Shanghai- 50,000 To support its growing sales, PLE is considering adding additional plants. The capacities of the proposed plants and the fixed costs of construction can be found in the PLE database. If a new plant is constructed, only one of the two potential capacities can be considered. Location being considered are Birmingham, Alabama; Singapore Frankturt, Germany; Mumbai, India; and Auckland, New Zealand. Other options are to increase the capacities of the existing plants in Kansas City and Santiago. Fixed costs of constructing new facilities or expanding the existing plants can be found in the PLE database. Develop and solve an optimization model to identify the best location for the new plants and transportation allocations to meet demand. Some members of the executive committee are concerned that the estimate for the China market (demand at Shanghai) is too uncertain and may range from 20,000 to 60,000 units. In addition, it was suggested that the capacity of the Kansas City plant be reduced to save distribution costs. Using Solver in Excel to find a solution and please include screen shots of all formulas used and screenshots of data inputed from solver

A B C D Unit Shippina Co A B C D Fixed Costs of Capacity Increase or New Construction \begin{tabular}{|l|l|r} \hline Current Plants & Additional Capacity & \multicolumn{1}{l}{ Cost } \\ \hline \hline Kansas City & 10000 & $605,000.00 \\ \hline Kansas City & 20000 & $985,000.00 \\ \hline Santiago & 5000 & $381,000.00 \\ \hline Santiago & 10000 & $680,000.00 \\ \hline \end{tabular}