This case is based on the Q.M. in Action, Pricing for Environmental Compliance in the Auto Industry. In this case we build a model similar to the one built for General Motors. The CAFE requirement on fleet miles per gallon is based on an average. The harmonic average is used to calculate the CAFE requirement on average miles per gallon. In order to understand the harmonic average, assume that there is a passenger car and a light truck. The passenger car gets 30 miles per gallon (MPG) and the light truck gets 20 miles per gallon (MPG). Assume each vehicle is driven exactly one mile. Then the passenger car consumes 1/30 gallon of gasoline in driving one mile and the light truck consumes 1/20 gallon of gasoline in driving one mile. The amount of gasoline consumed in total is
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.

Number of Vehicles CAFE Weight MPG 30 20 0.100) 0.100) 0.2000 Passenger Cars 4 Light Trucks CAFE Average 25