Information about lpp using excel solver

Reference no: EM1398407

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.

Reference no: EM1398407

Questions Cloud

Rade point distribution at graduation : What id Steve's grade point average at graduation and what is Steve's median grade point at graduation

Intercept of slope : Assume that another counselor decided to extend the aforementioned study by collecting data on 14 children. His study yielded the following results:

Explain ways to be unified and disjointed : Franklin intended the Autobiography to be unified work; he drew up outline and attempted to stick with it. In what ways does book appear to be unified and in what ways disjointed?

Describe the significance of each investigators work : Name several of the key investigators who made discoveries important to the development of bacterial genetics and describe the significance of each investigator's work.

Information about lpp using excel solver : 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.

Using probability formulas-proportion of employees : Using the probability formulas, find the proportion of employees who either have MBAs or are managers.

Regulatory gene encodes a histone deacetylase enzyme : A scientist clones a regulatory gene that is involved in controlling the expression of other genes, and discovers that the regulatory gene encodes a histone deacetylase enzyme.

Describe the bacterial chromosome : Describe the bacterial chromosome and outline the process by which it replicates by the semi-conservative method of replication.

Assume that the locus is tightly linked to the centromere : Assume that the locus is tightly linked to the centromere and that the trisomic chromosome forms a bivalent and a univalent (monovalent) at random.

User Account

All Pages