Reference no: EM131150103

An auto manufacturer produces 4 types of cars: subcompact, compact, intermediate, and luxury. It also produces trucks and vans. Vendor capacities limit the total annual production capacity to a most 1, 200,000 vehicles of all six vehicle types. Subcompacts and compacts are built together in a facility with a total annual capacity of 620,000 cars. Intermediate and luxury cars are produced together in another facility with capacity of 400,000, and the truck/van facility has a capacity of 275,000. The company’s strategy requires that subcompacts and compacts must constitute at least half the product mix for four cars types. Profit margins, market potential, and fuel efficiencies in miles per gallon (mpg) are given below. Subcompact Compact Intermediate Luxury Truck Van Profit Margin ($/Vehicle 150 225 250 500 400 200 Potential Sales (in thousands) 600 400 300 225 325 100 Fuel Efficiency (mpg) 40 34 15 12 20 25 The company’s environmental policy requires an average fuel efficiency for all vehicles types of at least 27 mpg a) Formulate a linear program to produce a production plan that maximizes profit and meets all requirements. Clearly define your decisions variables, objective function and constraints b) Implement your formulation in Excel and use solver to find the optimal solution. Provide answers for the optimal amounts. Include snapshots of your excel solution showing the values of the objective function and decision variables c) How much would annual profit drop if the fuel efficiency requirements was raised to 28 mpg. Include snapshots of your excel solution showing the values of the objective function and decision variables