Question 1) Ford has three assembly plants located in various parts of the country. The first plant (built in 1937 and located in Norwood, Ohio) requires 1 hours of labor and 2 hour of machine time to assemble one automobile. The second plant (built in 1958 and located in Bakersfield, California) requires 0.5 hours of labor and 2.5 hours of machine time to assemble one automobile. The third plant (built in 1981 and located in Kingsport, Tennessee) requires 1.1 hours of labor and 1.5 hours of machine time to assemble one automobile. The firm pays $32 per hour of labor and $10 per hour of machine time at each of its plants. The first plant has a capacity of 600 hours of machine time per day; the second, 1100 hours; and the third, 1,000 hours. The manufacturer's production target is 1400 automobiles per day. The production department sets each plant's schedule by solving a linear programming problem designed to identify the cost-minimizing pattern of assembly across the three plants. 1. (30 Points) Use EXCEL Solver to determine the cost-minimizing method of meeting Ford's daily production target. Discuss the solution. 2. (15 Points) Based on the optimum solution of your LP model, which plant do you recommend having more orders in the future in case of over-loaded plants or the firm is having more orders than usual? 3. (15 Points) Obtain the sensitivity report of EXCEL Solver and discuss the report. ONLY use the Sensitivity Report to answer tasks (5 to 7) Page 2 of 3 4. (10 Points) The UWA local in Norwood, Ohio, has proposed wage concessions at that plant to raise employment. What is the smallest decrease in the wage rate at that plant that would increase employment there? 5. (10 Points) (A) What is the cost of assembling an extra automobile given the current output level of 1400 automobiles? (B) Is it feasible to produce 1,600 automobiles? If possible, provide the new total cost, if not possible explain why not? 6. (10 Points) A team of production specialists has indicated that the auto manufacturer can achieve efficiencies at its Norwood plant by reconfiguring the assembly line. The reconfiguration has the effect of increasing the productivity of the labor at this plant from 1 hours to 0.8 hour per automobile. By how much will the firm's costs fall as a result of this change, assuming that it continues to produce 1400 automobiles? 7. (10 Points) The company introduced a new technology for the machines that will increase the total capacity in both Kingsport and Bakersfield plants by 20 hours, what changes in cost you expect to happen, explain your answer based on the sensitivity analysis report