Reference no: EM131647583

Create Excel model with a direct cash flow forecast for the next generation Impala using the information below. 

Assumptions:

For simplicity, assume all cash flows occur at year end and there are no working capital requirements.

• Development cycle (design and engineering of the vehicle before going to market)
• 5 year development cycle
• Investment in manufacturing equipment and tooling: $450MInvestment Timing (15% of the total spend in 2010, 20% in 2011, 20% in 2012, 20% in 2013 and 25% in 2014):
• Engineering budget: $390M (timing same as investment spend)
• Sales
• Assume product is launched the year right after the end of the development cycle
• Life cycle of the product: 5 years of sales after vehicle is launched
• Total market for full-size sedans in the U.S.: 600K units a year
• Expected market share: 18%
• Two different trims are planned for the product:Standard: 65% of total sales; selling price (MSRP) of $27,000 with a 4-cylinder engine (27 MPG fuel economy)
• Luxury: MSRP of $37,000 with a 6-cylinder engine (16 MPG fuel economy)
• GM's revenue per vehicle is after 10% dealer discount from MSRP which dealers typically receive on every sale
• The pricing unit is planning to reduce the price of the vehicle by 2% per year as the new generation of Impala matures in the marketplace
• Variable costs
• Material costsStandard: $16,000 per vehicle
• Luxury: $19,000 per vehicle
• Logistics and Warranty cost (includes freight in and out of the plant and to the dealer lot as well as warranty expenses): additional $1,500 average per car
• Manufacturing cost per unit is calculated at $4,500 per unit, irrespective of the model manufactured
• Assume 3% reduction in material, logistics, and manufacturing cost per year over the lifecycle of the car as the supplier base becomes more efficient and raw material and manufacturing costs are optimized
• Other costs
• Total advertising and marketing budget for the car is $150M in the first year of launch, $75M in each of years 2 to 4, and $125M in year 5

As part of your recommendation, calculate the project's NPV (using 15% discount rate).