Benz-Merc Cars produces four classes of cars: subcompact, compact, intermediate and luxury. BM also produces trucks and vans. Vendor capacities limit total production capacity to at most 1.2 million 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 capacity of 400,000; and the truck/van facility has a capacity of 275,000. Profit margins, market potential (in terms of maximum sales), and fuel efficiencies are summarized below:

(this can be put into a simple table also to compute)

Type of Vehicle:

1. Subcompact:

Profit Margin ($ /vehicle): 150

Market Potential (sales in '000): 600

Fuel Economy (mpg): 40

2. Compact

Profit Margin ($/vehicle): 225

Market Potential (sales in '000): 400

Fuel Economy (mpg): 34

3. Intermediate

Profit Margin ($/vehicle): 250

Market Potential (sales in '000): 300

Fuel Economy (mpg): 15

4. Luxury

Profit margin ($/vehicle): 500

Market Potential (sales in '000): 225

Fuel Economy (mpg): 12

5. Truck

Profit Margin ($/vehicle): 400

Market potential (sales in '000): 325

Fuel Economy (mpg): 20

6. Van:

Profit margin ($/vehicle): 200

Market potential (sales in '000): 100

Fuel economy (mpg): 25

a) Develop a linear optimization spreadsheet model (using Solver in Microsoft Excel) to determine the optimal vehicle mix so as to maximize profit.

b) What is the optimal profit

c) What is the optimal combination of vehicles to produce

d) The US government has just passed legislation that requires the average fuel economy of the vehicles manufactured by an automobile manufacturer to be at least 28 miles per gallon. Will this affect the current production plan? If so, modify your practice model and determine the new optimal profit and production plan.