Excel required to solve.

Problem 3 - Sensitivity

Martin Automotive is a smaller firm that manufactures automobiles and wants to maximize profit. They are restricted by federal regulations that limits the number of gas guzzlers they can produce. The companys CEO, Armando, likes safe vehicles and makes the company manufacture a set minimum of 5-star crash test rated vehicles each period. Armando assembled his finance and operations departments to determine the ideal product mix to maximize profit.The company is also met by constraints on different production mixes, automobile components like headlights and transmissions, and others that you can see below.

The linear programming output and the sensitivity analysis for their maximized profit are presented below. Please note that the optimal values of the decision variables are not integer values (they do not have to be integers to answer the questions regarding sensitivity analysis).

Use the picture for reference. If the optimal solution was 100, 300, 400, 0, 200, and 500 (it is not, but assume it is and assume it is a feasible solution) for Sports Cars, Pick-Ups, SUVs, Crossovers, Sedans, and Coups, respectively, what would be the LHS value for the Max #HID headlights constraint?

Sports Cars Pick-upsSUV's CrossoversSedans Coups Solution Maximize Profit Constraints $12,000.00 $10,000.00 $9,000.00$6,000.00 $3,000.00 ($1,200.00) LHS 2,437.5 1,375.0 2,000.0 50,000.0 8,000.0 3,625.0 39,812.5 2,000.0 22,000.0 2,000.0 2,000.0 SignRHS S 3,000 s 3,000 4,500 Max # Gas Guzzlers Max Manual Transmission Max Auto Transmission Max Labor (hours) Max # HID Headlights Max # Traditional Headlights Max # Airbags Min # 5-Star Crash Test Min Union Labor (hours) Min # 4-Door Min # Pick-ups or SUV's 18 2 2 14 20 16 15 14 S 50,000 S 8,000 S 6,500 s 42,000 22,000 2 22,000 2800 2 1,000 4 4 2 10 2 16 7 4 10 Microsoft Excel 16.0 Sensitivity Report Worksheet: [REVISED PE 2- Version C_key.xlsx]Sensitivity -Auto Manfct Report Created: 12/3/2018 2:36:32 PM Variable Cells Reduced Cost Objective Coefficient Allowable Allowable Decrease Cell SC$3 Solution Sports Cars SD$3 Solution Pick-ups E$3 Solution SUV's SF$3 Solution Crossovers SG$3 Solution Sedans H$3 Solution Coups Name Value Increase 437.5 875 1125 0 12000 8685.714286 10000 673.6842105 800 1216 1000 673.6842105 1E+30 1E+30 1200 5866.666667 18133.33333 -1900 3400 6000 1900 3400 937.5 Constraints Shadow Final Value Constraint R.H. Side Allowable Increase Allowable Decrease Cell $I $13 Min # 5-Star Crash Test LHS I$14 Min Union Labor (hours) LHS SI$15 Min # 4-Door LHS $I $16 Min # Pick-Ups or SUV's LHS $I $6 Max # Gas Guzzlers LHS SI$7 Max Manual Transmission LHS I$8 Max Auto Transmission LHS I$9 Max Labor (hours) LHS $1$10 Max # HID Headlights LHS $1$11 Max # Traditional Headlights LHS $I $12 Max # Airbags LHS Name Price 666.6666667 733.3333333 2000 22000 2000 2000 2437.5 1375 2000 2000279.2553191 22000 4038.461538 1200 1000 1E+30 1E+30 E+30 10800 80002038.834951 1E+30 1E+30 1000 7500 1E+30 1E+30 562.5 1625 2500 8400 2842.105263 2875 2187.5 800 1000 4500 766.6666667 266.6666667 8000 6500 42000 39812.5