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).