Show me the steps to solve$-$ Using Excel Solver

The Porsche Club of America sponsors driver education events that provide high$-$performance driving instruction on actual racetracks. Because safety is a primary consideration at such events, many owners elect to install roll bars in their cars. Deegan Industries manufactures two types of roll bars for Porsches. Model DRB is bolted to the car using existing holes in the car$$s frame. Model DRW is a heavier roll bar that must be welded to the car$$s frame. Model DRB requires $20$ pounds of a special high$-$alloy steel, $40$ minutes of manufacturing time, and $60$ minutes of assembly time. Model DRW requires $25$ pounds of the special high$-$alloy steel, $100$ minutes of manufacturing time, and $40$ minutes of assembly time. Deegan$$s steel supplier indicated that at most $40,000$ pounds of the high$-$alloy steel will be available next quarter. In addition, Deegan estimates that $2000$ hours of manufacturing time and $1600$ hours of assembly time will be available next quarter. The profit contributions are $$200$ per unit for model DRB and $$280$ per unit for model DRW$.$ The linear programming model for this problem is as follows:

$$

Max $200$DRB$+280$DRW

s$.$t$.$

$20$DRB $+25$DRW$<40,000$ Steel Available

$40$DRB $+100$DRW $<120,000$ Manufacturing Minutes

$60$DRB $+40$DRW$<96,000$ Assembly Minutes

DRB$,$ DRW$>0$

A$)$ What are the optimal solution and the total profit contribution?

$$

B$)$ Another supplier offered to provide Deegan Industries with an additional $500$ pounds of the steel alloy at $$2$ per pound. Should Deegan purchase the additional pounds of the steel alloy? Explain.

$$

C$)$ Deegan is considering using overtime to increase the available assembly time. What would you advise Deegan to do regarding this option? Explain.

$$

D$)$ Because of increased competition, Deegan is considering reducing the price of model DRB such that the new contribution to profit is $$175$ per unit. How would this change in price affect the optimal solution? Explain.

$$

E$)$ If the available manufacturing time is increased by $500$ hours, will the shadow price for the manufacturing time constraint change? Explain.