PLEASE USE EXCEL SOLVER AND SETUP BELOW TO FIND THE MAXIMAL PROFIT AND CORRESPONDING OPTIMAL SOLUTION

THE CONTEXT IS AS FOLLOWS:

Production Planning for Automobiles The Auto Company of America (ACA) produces four types of cars: subcompact, compact, intermediate, and luxury. ACA also produces trucks and vans. Vendor capacities limit total production capacity to at most 1,200,000 vehicles per year. Subcompacts and compacts are built together in a facility with a total annual capacity of 620,000 cars. Intermediate and luxury cars are produced in another facility with a capacity of 400,000, and the truck/van facility has a capacity of 275,000. ACAs marketing strategy requires that subcompacts and compacts must constitute at least half of the product mix for the four-car types. Profit margins, market potential, and fuel efficiencies are summarized below.

Type	Profit Margin ($/vehicle)	Potential Sales (in 000s)	Fuel Efficiency (MPG)
Subcompact	150	600	40
Compact	225	400	34
Intermediate	250	300	15
Luxury	500	225	12
Truck	400	325	20
Van	200	100	25

The Corporate Average Fuel Efficiency (CAFE) standards require an average fleet fuel efficiency of at least 27 MPG. ACA would like to use a linear programming model to understand the implications of government and corporate policies on its production plans.

(a) What is the optimal annual profit for ACA?

(b) How much would annual profit drop if the fuel efficiency requirement were raised to 28 MPG?

PLEASE USE EXCEL SOLVER AND SETUP BELOW TO FIND THE MAXIMAL PROFIT AND CORRESPONDING OPTIMAL SOLUTION

Hint: If you build your linear program correctly, your optimal objective value should be $358,000,000 Profit MPG Production Profit Margin $150 $225 PotentialSales 600000 400000 40 Subcompact Compact Intermediate Luxury Truck Van Constraints: 1. About TotalCap 2. About PlantCap 3. Subcompacts and compacts must constitute at least half of the product mix for the four car types 4. About PotentialSales 5. About average fleet fuel efficiency (be careful about linearity) $250 34 15 12 300000 225000 325000 $500 $400 $200 20 100000 25 Planta Subcom_Compact Intermediate_Luxury Truck_Van PlantCap 620000 400000 275000 HalfCars >= Totala TotalCap 1200000 Fleet MPG_Total ReqMPG_Total