Question

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 car’s frame. Model CLB is a heavier roll bar that must be welded to the car’s 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.

Answer 1

a.

let,

x1 = number of CLA models to be produced
x2 = number of CLB models to be produced

Objective is to maximize profit = max 100x1+150x2

subject to,

10x1+30x2 <= 50000 (High-alloy steel)
30x1+90x2 <= 1500*60 (manufacturing time)
or, 30x1+90x2 <= 90000
80x1+40x2 <= 2000*60 (Assembly time)
or, 80x1+40x2 <= 120000

x1,x2 >= 0 (non-negativity constraint)

b. Solving in solver we get,

x1 = number of CLA models to be produced = 1200
x2 = number of CLB models to be produced = 600

maximized profit = 210000

Solver screenshot

Solver formula

Sensitivity report

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.

Yes F1 auto management should include this option as profit earned for 1 additional hour is 1.333333333 (Shadow price of manufacturing time constraint and this shadow price is valid for increase of 45000 minutes or 45000/60 = 750 hours ) i.e profit is more than cost of $1 per hour

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.

No F1 should not buy additional steel as even available steel is not completely utilized so no point is taking additional steel

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.

No the optimal solution will not change as reduction of profit to $75 is well within allowable decrease limit for CLA (allowable decrease lower limit = objective coefficient-allowable decrease = 100-50 = 50)

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

No shadow price for assembly time will not change as we can see upper limit of range of feasibility for assembly time is 80000 minutes = 80000/60 = 1333.333333 hours. So increase of 1000 hours will not change shadow price for available assembly time