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

WE WRITE ESSAYS FOR STUDENTS

Tell us about your assignment and we will find the best writer for your paper.

Write My Essay For Me

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.

Place your order now for a similar paper and have exceptional work written by our team of experts to guarantee you A Results

The post excel solver appeared first on Custom Nursing Term Papers.