General Motors Corporation (GMCO) produces three (3) types of cars: compact, sedan, and SUV. The variable cost per car and production capacity (per production year) for each time is given as:

Variable costs and production capacities

Cost Capacity

Compact $13500 2000

Sedan $16000 1500

SUV $22000 1000

The annual demand for each type of car depends on the prices of the three types of cares (also given below)

Demand Function

Compact 2500-100P_c + 3P_s

Sedan 1800 - 30P_S + 2P_C + P_SUV

SUV 1300 - 20P_SUV + P_S

Variable definitions for the Project Template spreadsheet:

P_C is the price charged for a compact car (in thousands of dollars)

P_S is the price charged for a sedan (in thousands of $)

P_SUV is the price for an SUV (thousands of $)

Next, suppose that each car's miles per gallon (mpg) is as follows:

Car type MPG

Compact 28

Sedan 22

SUV 17

GMCO is concerned about how its vehicles impact the environment. While GMCO wants to maximize its profit, it also wants to maximize the average miles per gallon attained by the cars it sells. Construct a trade-off curve between these two objectives. (using EXCEL Solver).

Deliverables:

Spreadsheet model

• Make a spreadsheet model.
• The model should be clearly organized and labeled (note the decision variables, the objective variables, and the output variables)
• Use range names and comments to clarify and explain the model.
• Provide a solution to the problem with the model. Be sure to include the graph of the trade-off curve developed.
• Provide data and results for a sensitivity analysis. Choose which parameters to be altered and show their effects. Make on a new tab of the spreadsheet. Choose any format (Solver, Solver Table, own table, graphs, etc.).

Paper.

• Generate a brief explaination to accompany the spreadsheet model. The explaination should be limited to a couple of pages. In the explaination, respond to the following:
• Where do the numbers come from? where the information provided may come from, what impacts these numbers, and what other factors are NOT considered in the model (but may be typical or realistic).
• Explain the solution developed in the spreadsheet (just explain the outcome, not how the model was developed) and analyze the trade-off curve. Explain what this means for GMCO and how they consider their environmental impact while watching the bottom line.
• Sensitivity analysis. Discuss the data/results of the sensitivity analysis. Clearly reference any data tables or charts developed. What input variables was chosen to alter and how? Any notable results over the input variation?
• Discuss how practical the model is in a real-life application. What might be missing? Is a linear model correct, and are there typical inputs for which the model does not account?