Details of this Paper

Using Excel Solver by completing sensitivity analy...

Description

Solution

Question

Using Excel Solver by completing sensitivity analysis...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 cars frame. Model DRW is a heavier roll bar that must be welded to the cars frame. Model DRB requires 20 pounds of 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. Deegans 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 z = 200DRB + 280DRW Subject to: 20DRB + 25DRW 40,000 Steel available 40DRB + 100DRW 120,000 Manufacturing minutes 60DRB + 40DRW 96,000 Assembly minutes DRB, DRW 0 Solve the problem using Excel Solver. I need help with solving this problem with Excel solver 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 steel alloy? Explain c) Deegan is considering using overtime to increase the available assembly time? What would you advise Deegan to fo regarding this option?

Paper#12676 | Written in 18-Jul-2015

Price : \$25