sensitivity report

Background 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.1 hours of labor and 1 hour of machine time to assemble one automobile. The second plant (built in 1958 and located in Bakersfield, California) requires 1.5 hours of labor and 1.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 2 hours of machine time to assemble one automobile. The firm pays $35 per hour of labor and $11 per hour of machine time at each of its plants. The first plant has a capacity of 500 hours of machine time per day; the second, 1500 hours; and the third, 2,200 hours. The manufacturer's production target is 1,800 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. Tasks: 3. Obtain the sensitivity report of EXCEL Solver and explain the report. ONLY use the Sensitivity Report to answer tasks (4 to 8) 4. The UWA local in Bakersfield, California, 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. (A) What is the cost of assembling 5 extra automobiles given the current output level of 1,800 automobiles? (B) Is it feasible to produce 2,100 automobiles? If possible, provide the new total cost, if not possible explain why not? 6. 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.1 hours to 0.8 Variable Cells Cell $E$5 $F$5 $G$5 Name values X1 values X2 values X3 Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 500 0 49.5 19.5 1E+30 200 0 69 1E+30 8.5 1100 0 60.5 8.5 1E+30 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease SH$12 Constraint4 LHS 1800 69 1800 800 200 SH$9 Constraint1 LHS 500 -19.5 500 200 500 SH$10 Constraint2 LHS 300 0 1500 1E+30 1200 SH$11 Constraint3 LHS 2200 -4.25 2200 400 1600