With Excel...

1. Please fill out the model (including proper graph(s) to visualize the comparison between hours used and hours unused). Please show the formulas used.

2. Answer and Sensitivity Report

3. What-If analyses

One of PLE's manufacturing facilities produces metal engine housings from sheet metal for both mowers and tractors. Production of each product consists of five steps: stamping, drilling, assembly, painting, and packaging to ship to its final assembly plant. The production rates in hours per unit and the number of production hours avail- able in each department are given in the following table: Mower Tractor Production Department Housings Housings Hours Available Stamping 0.03 0.07 200 Drilling 0.09 0.06 300 Assembly 0.15 0.10 300 Painting 0.04 0.06 220 Packaging 0.02 0.04 100 In addition, mower housings require 1.2 square feet of sheet metal per unit and tractor housings require 1.8 square feet per unit, and 2,500 square feet of sheet metal is available. The company would like to maximize the total number of housings they can produce during the planning period. Formulate and solve a linear optimiza- tion model using Solver and recommend a production plan. Illustrate the results visually to help explain them in a presentation to Ms. Burke. In addition, conduct what- ever what-if analyses you feel are appropriate to include in your presentation (for example, run different scenarios or systematically change model parameters). Summarize your results in a well-written report. Engine Housings Department Stamping Drilling Assembly Painting Packaging Hours per unit Hours per unit Mower Housings Tractor Housings Production Hours Available 0.03 0.07 200 0.09 0.06 300 0.15 0.1 300 0.04 0.06 220 0.02 0.04 100 Sq. ft. per unit Sq. ft. per unit Sq. ft. Available 1.2 1.8 2500 Mathematical model Max MH +TH .03MH +.07TH