The accompanying spreadsheet captures the profit-maximizing decisions of a carmaker facing stricter fuel-efficiency standards as discussed earlier

Question:

The accompanying spreadsheet captures the profit-maximizing decisions of a carmaker facing stricter fuel-efficiency standards as discussed earlier in the chapter. The carmaker in question must decide how much aluminum to use in its trucks and sedans so as to lower their weight and improve their fuel efficiency. Cells C8 and C9 list these decision variables (expressed as percentages in decimal form, so that a value such as .10 means that the proportion of aluminum has increased by 10 percentage points). The company also must determine its mix of trucks and sedans by setting the proportion of trucks (again as a decimal) in cell D8 with cell D10 computed as: = 1 − D8.

Because of aluminum’s extra cost, each vehicle’s contribution per unit declines as more aluminum is used. Truck contribution (cell E8) is given by the Excel formula: =10 − 80*H8*C8^2. The cost impact of using aluminum depends on the relative cost of aluminum (cell H8) and increases quadratically as more and more aluminum is used. In turn, sedan contribution is given by the Excel formula: = 6 − 80*H8*C8^2. These formulas imply that current contribution margins for trucks and sedans (with cells C8 and C10 set to zero) are $10 thousand and $6 thousand, respectively. At the same time, each vehicle’s fuel efficiency increases directly with its aluminum content. For trucks (cell F8), the Excel formula is: =18 + 60*C8, while for sedans (cell F10), the formula is: 38 + 80*C10. These formulas imply that current fuel efficiency ratings for trucks and sedans (with cells C8 and C10 set to zero) are 18 miles per gallon and 38 miles per gallon, respectively. Finally, the fleet averages in row 12 are computed by weighting the vehicle values by the fleet shares in column D.

a. The company seeks to maintain a fleetwide average of 32 miles per gallon. To maximize its fleetwide average contribution (cell E12), how much aluminum should the maker add to each vehicle, and what mix of vehicles should it produce?
b. Re-answer the questions in part (a) if price cuts mean that aluminum is only 10 percent more costly than steel, that is, cell H8 takes the value 1.1.
c. Finally, what is the company's optimal production response if the fuel-efficiency standard is raised to 36 miles per gallon?

Contribution Margin
Contribution margin is an important element of cost volume profit analysis that managers carry out to assess the maximum number of units that are required to be at the breakeven point. Contribution margin is the profit before fixed cost and taxes...