Note: Use Excel Solver for Sensitivity Analysis. Extract the relevant Excel reports for the answer script.

The Mercedes Club of Singapore sponsors driver education events that provide high performance driving instruction on actual race tracks. Because safety is a primary consideration at such events, many owners elect to install roll bars in their cars for the multilink suspension system. F1 Auto manufactures two types of roll bars for Mercedes. Model CLA is bolted to the car using existing holes in the cars frame. Model CLB is a heavier roll bar that must be welded to the cars frame. Model CLA requires 10 pounds of a special high alloy steel, 30 minutes of manufacturing time, and 80 minutes of assembly time. Model CLB requires 30 pounds of the special high alloy steel, 90 minutes of manufacturing time, and 40 minutes of assembly time. F1 Auto steel supplier indicated that at most 50,000 pounds of high-alloy steel will be available next quarter. In addition, F1 estimates that 1,500 hours of manufacturing time and 2,000 hours of assembly time will be available next quarter. The profit contributions are $100 per unit for model CLA and $150 per unit for model CLB.

a. Formulate the Linear Programming model for the production problem.

b. Solve the LP model using Excel and show the relevant reports. What are the optimal solution and total profit contribution?

c. F1 is considering using overtime for manufacturing time to meet a new order. The Union proposed a rate of $1 over the normal rate per hour for additional 700 hours of manufacturing time. What would you advise F1 Auto Management to do regarding this option? Explain.

d. F1 is considering to purchase additional steel from other suppliers to increase the steel available. What would you advise F1 to do regarding this option? Explain.

e. Because of increased competition, F1 is considering reducing the price of model CLA such that the new contribution to profit is $75 per unit. How would this change in price affect the optimal solution? Explain.

f. If the available assembly time is increased by 1000 hours, will the shadow price for the available assembly time change? Explain.