I need to make a direct cash flow analysis.

Assignment 1: Direct Cash Flow Analysis

As an analyst in Operations Finance, your manager has asked you to create an Excel model with a direct cash flow forecast for the next generation Impala using the information below. Prepare yourself to walk your interviewer through your Excel model and calculations.

Assumptions:

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

1. Development cycle (design and engineering of the vehicle before going to market)

5 year development cycle

Investment in manufacturing equipment and tooling: $450M

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

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

3. Variable costs

Material costs

- Standard: $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

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

Assignment 2: Recommendation

Based on the projections you derive in Assignment 1, make a recommendation on whether or not to proceed with this discrete product program, the launch of the new generation Impala.

As part of your recommendation, calculate the project's NPV (using 15% discount rate). In addition to the projections derived in Assignment 1 and the NPV analysis, make sure to use additional analytical and/or qualitative arguments to support your position.

Next Generation Chevrolet Impala Instructions You have 1 hour and 30 minutes to complete the case below, which involves building an Excel model and preparing a written memo. You will walk through your analysis as part of the interview process. The GM team will ensure your case material makes its way to the proper interview room. By the end of the allotted time, you should: Write a memo in Word no longer than one page outlining your analysis of the case problem and your recommendation Be prepared to walk your interviewers through your Excel model and present your recommendation (you can use either the Excel or the Word document as back up for your discussion; no need to prepare a Powerpoint presentation).The discussion of the case with Q&A will take approximately 30 minutes. Type your name in top left corner of both the Excel and Word document Save the Excel and Word document on your deskstop with the following name: - Candidate Last Name-Candidate First Name-Case.xls - Candidate Last Name-Candidate First Name-Memo.doc Case Background It is January 2010 and General Motors is considering the development of the next generation Impala, a full-size family sedan. Full-size sedans are a mainstay in the American market. According to the US government classification, a fullsize car, or a \"large\" car, has 120 or more cubic feet of space for passengers and cargo. Adoption of the term \"fullsize\" in the early 1960's was necessitated by an increasingly larger variability of car sizes and body styles. Full-size sedan segment size fluctuated significantly over the last 50 years. Oil shocks of the 1970's impacted sales negatively, and so did the introduction of many other family-friendly car body styles in the 1980's and 1990's, such as minivans and family SUVs. The last five years have seen a renaissance in the budget-oriented fuel-efficient segment of full-size sedans with all the traditional entrants such as Ford Taurus, Chevrolet Impala, Toyota Avalon, and Nissan Maxima, as well as new players such as Hyundai Azera, all competing for the title of the ultimate large sedan. Other family-friendly car body styles like small and large cross-over vehicles (vehicles built on a car platform, but providing the utility of an SUV) have also increased in popularity in recent years. Chevrolet Impala has a storied history in the full-size sedan market. The first Impala launched as a 1958 model year vehicle. In its heyday in the 1960's, Impala was the best-selling car in the United States. Impala's current 9 th generation has market share of ~18% and consistently sold more than 100,000 units per year on average from 2008 to 2009, the latest full year of data available. Assignment 1: Direct Cash Flow Analysis As an analyst in Operations Finance, your manager has asked you to create an Excel model with a direct cash flow forecast for the next generation Impala using the information below. Prepare yourself to walk your interviewer through your Excel model and calculations. Assumptions: For simplicity, assume all cash flows occur at year end and there are no working capital requirements. 1. Development cycle (design and engineering of the vehicle before going to market) 5 year development cycle Investment in manufacturing equipment and tooling: $450M a. Investment 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) 2. 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 3. Variable costs Material costs - Standard: $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 4. 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 Assignment 2: Recommendation Based on the projections you derive in Assignment 1, make a recommendation on whether or not to proceed with this discrete product program, the launch of the new generation Impala. As part of your recommendation, calculate the project's NPV (using 15% discount rate). In addition to the projections derived in Assignment 1 and the NPV analysis, make sure to use additional analytical and/or qualitative arguments to support your position. Prepare a one page written memo outlining your analysis and be prepared to present your recommendation to your interviewer