3. Modern Machine produces computer components at three plants and ships to four warehouses. The plants have not been able to keep up with demand, so the firm wants to build a new plant. Two sites are being considered, Seattle and Birmingham. Data has been collected for each possible location. (7 Marks) Monthly Monthly Warehouse Supply Detroit 20, Cincinnati To One Demand (Units) Production Plant Cost Produce Unit (S) 50 20, 000 000 Dallas 22, Salt Lake 10, 60 000 000 New York 15. Pittsburgh 15, 55 000 000 9,0 45, Los Angeles 00 000 66, 000 Supply needed from new plant = 66,000 - 45,000 = 21,000 units per month Estimated Production Cost Per Unit at Proposed Plants Seattle 50 52 Birmingham To From Detroit 20 Dallas 50 New York 50 Los Angeles 60 Cincinnati Salt Lake 40 30 50 40 Pittsburgh 30 45 26 66 Scattle 55 40 60 25 Birmingham 40 35 40 52 Using Excel solver answer the following question: Which new location will yield the lowest cost for the firm in combination with the existing plants and warehouses? 3. Modern Machine produces computer components at three plants and ships to four warehouses. The plants have not been able to keep up with demand, so the firm wants to build a new plant. Two sites are being considered, Seattle and Birmingham. Data has been collected for each possible location. (7 Marks) Monthly Monthly Warehouse Supply Detroit 20, Cincinnati To One Demand (Units) Production Plant Cost Produce Unit (S) 50 20, 000 000 Dallas 22, Salt Lake 10, 60 000 000 New York 15. Pittsburgh 15, 55 000 000 9,0 45, Los Angeles 00 000 66, 000 Supply needed from new plant = 66,000 - 45,000 = 21,000 units per month Estimated Production Cost Per Unit at Proposed Plants Seattle 50 52 Birmingham To From Detroit 20 Dallas 50 New York 50 Los Angeles 60 Cincinnati Salt Lake 40 30 50 40 Pittsburgh 30 45 26 66 Scattle 55 40 60 25 Birmingham 40 35 40 52 Using Excel solver answer the following question: Which new location will yield the lowest cost for the firm in combination with the existing plants and warehouses