This case is based on the Q.M. in Action, Pricing for Environmental Compliance in the Auto Industry.
Question:
Gas consumption = (1/30) + (1/20) = (5/60) = (1/12) gallon
AN EXCEL SPREADSHEET WITH A CAFÃ CALCULATION
Managerial Report
1. Using the formulas given in (a) and (b), develop an expression for the total profit contribution as a function of the price of cars and the price of light trucks. Assume the marginal cost for passenger cars is 15 and the marginal cost for light trucks is 17.
(a) Demand = 750 - PC
(b) Demand = 830 - PT
2. Using Excel Solver or LINGO, find the price for each car so that the total profit contribution is maximized.
3. Given the prices determined in Question 2, calculate the number of passenger cars sold and the number of light trucks sold.
4. Duplicate the spreadsheet in Figure. Your spreadsheet should have formulas in cells D3:D5 and B7 and be able to calculate the harmonic (CAFE) average for any MPG rating and any number of vehicles in each category.
5. Again, assume that passenger cars get 30 MPG and light trucks get 20 MPG calculate the CAFE average for the fleet size from Question 3.
6. If you do the calculation in Question 5 correctly, the CAFE average of the fleet is 23.57. Add a constraint that the fleet average must be 25 MPG and resolve the model to get the maximum total profit contribution subject to meeting the CAFEconstraint.
Step by Step Answer:
Quantitative Methods For Business
ISBN: 148
11th Edition
Authors: David Anderson, Dennis Sweeney, Thomas Williams, Jeffrey Cam