Rows 5-6 and 9-10 show that the revenue and variable costs have been multiplied by 1000 to convert from thousands to millions but that doesn't make sense to me - wouldn't you divide by 1000? However, without this conversion, the NPV for this case study is terrible (losing money). Help! Files attached.

General Motors Impala Case Study Fall 2015 Instructions Please complete the case study below, which involves completing an Excel model (using the template provided) and preparing a written memo. The case below contains data and information needed to complete the Excel model. Use the information below to enter appropriate values in the shaded cells in the template. Background sh is ar stu ed d vi y re aC s o ou urc rs e eH w er as o. co m It is January 2011, and GM is considering execution of a plan for the development of the next generation Chevrolet Impala. Such a plan is often called a vehicle program. If the vehicle program is approved, development would begin in January 2012 and take 3 years to complete. The new vehicle would be produced for 6 years. The Impala is classified as a full-sized car, competing against the likes of the Ford Taurus, Toyota Avalon, and Nissan Maxima. This critical segment of the car market has been growing in recent years as customers focus more on fuel economy, and replace their aging trucks and SUVs with more fuel efficient cars. GM wishes to make the new Impala stand out by introducing an optional fuel efficient hybrid engine. The existing gas engine will be used in the base model of the new car, while the hybrid engine will be used in a hybrid version of the car. The first Impala launched as a 1958 model year vehicle. In its heyday in the 1960's, Impala was the bestselling car in the United States. Impala's current 9th generation has market share of ~18% of the fullsized sedan market, and GM intends to increase this with the new model. The full-sized sedan market is about 5% of the total vehicle market in North America. As an analyst in Operations Finance, your manager has asked you to quickly determine the NPV of the new base model, the new hybrid model, and the total of the two models. Financials Assume that all cash flows occur at year end. Th A. Development/Upfront Costs (manufacturing and engineering costs before going to market) The current generation of the Impala cost a total of $400M to develop before starting production in 2005. As usual, Marketing is asking for significantly more features and content in the new model, including a new hybrid engine. Engineering estimates that the new car and hybrid engine will cost $600M in total and take 3 years to develop. Engineering expense should be assumed flat for the 3 years. The investment in manufacturing equipment and tooling was about $500M for the current model. Manufacturing estimates that the new model will cost about $600M to tool. Twenty-five percent of this cost is in the first year, 30% in the second year, and 45% in the final year. Of these engineering, manufacturing and tooling costs, 40% should be applied to the hybrid car, and the remainder to the base (so, for example, the hybrid's development costs will be $140M in the first year). Adapted for Econ 372/572 by Emma Rasiel https://www.coursehero.com/file/12790423/Impala-Case-Study-Rasiel-F2015/ Page 1 of 3 General Motors Impala Case Study Fall 2015 B. Sales 2010 vehicle sales in North America were 12.1M units, and are expected to grow at 4% annually for the next 2 years, and 2% per year thereafter. The full-sized (\"sedan\") car segment was 5% of the market in 2010, and is expected to increase at 0.1% increments throughout both the development period and sales life of the new vehicle. (The current Impala started production at 20.5% market share of the full size segment, but has been losing share at 0.5% per year ever since, and this trend is expected to continue.) The new vehicle will start production at 22% market share of the full size segment and then lose share at a rate of 0.2 percentage points per year. C. Costs sh is ar stu ed d vi y re aC s o ou urc rs e eH w er as o. co m The average MSRP1 of the current Impala is $25,000. The new Impala will have an MSRP of $27,500 with the base engine in the first year of sales (2015), and the hybrid will add $2,500 to the MSRP. The actual transaction price of a vehicle (i.e., the price paid by the customer) will be 100% of MSRP at launch; the transaction prices for both the base and hybrid will decrease by $500 per year. GM's revenue is 90% of the transaction price, with dealer incentives taking the other 10%. The hybrid model will start production at 20% of the Impala's sales, with the base version representing the other 80%. The hybrid's share of the Impala sales will increase by 2% per year thereafter. (For example, in the 2nd year of sales, the hybrid would make up 22% of the Impala's sales, and the base would make up 78%.) Material costs for the current vehicle averaged $15,000 per unit at launch in 2005, and have been decreasing at 1% per year due to improvements in supplier efficiency. Materials costs for the new vehicle are expected to average $16,500 at launch, with the same annual reduction rate throughout the life of the new Impala. Manufacturing costs have held constant at $4,000 per vehicle since launch of the current model, and are not expected to change for the new model. The base gas engine represents 20% of these material and manufacturing costs. Because of its advanced technology, the hybrid engine will be 150% of the cost of the base engine. (Any vehicle made will have either the base engine or the hybrid - never both.) The remaining 80% of total material and manufacturing costs will be the same for both the base and hybrid vehicles. Annual warranty related costs started at $250 per vehicle at launch of the current vehicle (in 2005), but have decreased $10 per vehicle per year since then. Estimates suggest that annual warranty costs on the new vehicle will also be around $250 at launch, with a similar annual deduction as the current vehicle. Th Marketing has a total budget of $500M to market the next generation Impala over its lifecycle. Marketing expense is typically higher at vehicle launch in order to inform customers about the new vehicle. After the first two years of of sales of the new Impala, marketing expense per annum is typically ~10% of the total marketing budget for the remainder of the lifecycle. The distribution of marketing costs between the base and hybrid models should be proportional to relative sales volumes for the two vehicle types. Estimate the costs of the next generation Impala for both the base and hybrids models through its 6 year life. Explain your estimates in your model. 1 Manufacturer's Suggested Retail Price Adapted for Econ 372/572 by Emma Rasiel https://www.coursehero.com/file/12790423/Impala-Case-Study-Rasiel-F2015/ Page 2 of 3 General Motors Impala Case Study Fall 2015 D. Cost of Capital In 2011, GM has $6B of debt, and a market cap of $50B, with a cost of debt of 5%. Because of GM's fortress balance sheet priority, the company expects to have no debt by 2015, at which time it also anticipates having a beta of 1.3. As of 2011 (when this NPV is being estimated), the yield on 10yr US Treasury bonds was 3.5%, and the market risk premium was assumed to be 7%. Use the CAPM model to estimate a discount rate to apply to your model. Provide a brief explanation of your estimate in the written memo.2 Recommendation sh is ar stu ed d vi y re aC s o ou urc rs e eH w er as o. co m Based on the projections you derive in Part 1, make a recommendation on whether or not to proceed with the launch of the next generation Impala using qualitative and quantitative arguments for to support your position. In formulating your recommendation, consider employing additional analysis or calculations in addition to the base case NPV and IRR. To do this, test alternative assumptions about key inputs to the model (e.g., discount rate, market share), and see what impact these changes have to your NPV estimate. Be sure to consider the NPVs of the base and hybrid models as well as the combination, and bear in mind that identifying worst case scenarios is at least as important as best case scenarios. Remember that your written response must be no longer than 2 pages, not including exhibits. Please submit the following: A printout of the Excel model of your business case and NPV calculations.I have already set the \"print area\" on the spreadsheet: from cell A1 - N30, which is outlined with a heavy blue border. I have set it to print to a single page. This is the only printout that you need to submit. Please do NOT submit a printout of the entire model. A written memo (in Word) no longer than 2 pages 1.5 spacing. Your written memo should focus on your analysis and recommendations. Th 2 The CAPM is the \"standard\" market model for estimating the expected (or required) rate of return, E[re], for corporate valuation purposes. The CAPM model states that: E[re] = rf + e (E[rm] - rf ) where rf E[rm] e = the risk free rate = the expected rate of return on the \"market\" portfolio = the assumed average equity beta of the company's peer group Note: (E[rm] - rf ) is sometimes called the \"market risk premium\" since it reflects the difference in required return between the risky market portfolio and the riskless rate. Adapted for Econ 372/572 by Emma Rasiel https://www.coursehero.com/file/12790423/Impala-Case-Study-Rasiel-F2015/ Powered by TCPDF (www.tcpdf.org) Page 3 of 3 2010 -1 SUMMARY 2011 0 Pre-Devel Pre-Devel $ in Millions Impala base net revenues Impala hybrid net revenues TOTAL REVENUES Impala base variable costs Impala hybrid variable costs Impala base fixed costs Impala hybrid fixed costs Impala base total costs Impala hybrid total costs TOTAL COSTS BASE NET CASH FLOWS HYBRID NET CASH FLOWS ALL NET CASH FLOWS Discount factor 100.00% NPV IRR BASE PV CASH FLOWS HYBRID PV CASH FLOWS ALL NET CASH FLOWS Discount Rate 12.60% Per unit revenues & costs $ in '000 Revenues Data Impala base transaction price Impala base revenue per unit GM revenue % of transaction price (base) Impala Hybrid transaction price Impala hybrid revenue per unit Hybrid add'nl transaction price vs base GM revenue % of transaction price (hybrid) Costs data Material Costs Manufacturing (per unit) Total materials & manufacturing Base engine $$ Materials & Manufacturing Hybrid engine $$ Materials & Manufacturing Remainder of M&M costs (same for base & hybrid) Warranty cost (assume same for both vehicles) Base engine % of M&M Hybrid engine M&M relative to base Impala base unit costs Impala hybrid unit costs US Vehicle sales data Units and $ in Millions Vehicle Sales US Vehicle sales growth (given in case) 0.00 Sedans % of total US auto market 0.00% Total Sedan Vehicle sales 0.00 New Impala sales data Units and $ in Millions New Impala share Total Impala sales Base Impala sales as % of total Hybrid Impala sales % of total Impala base sales Impala hybrid sales New Impala Devel & Marketing Costs $ in Millions Engineering expense (3 years flat) Engineering Expense (total) Tooling base (timing given) Tooling $ Mill base per year Total Engineering, Tooling & Dev. expenses Base Development costs Hybrid Development costs Assumed lifecycle distribution of marketing costs Total Marketing cost Base Marketing costs Hybrid Marketing costs Base model total Fixed Costs Hybrid model total fixed costs Total Fixed Costs Discount Rate (using CAPM) Riskfree rate Market Risk Premium (E[rm] - rf) Total lifecycle spend Total lifecycle spend % of total for base % of total for hybrid Total lifecycle spend 0.00 Equity Beta Return on equity (CAPM) 2012 1 2013 2 2014 3 2015 4 2016 5 2017 6 2018 7 2019 8 Devel Devel Devel sell sell sell sell sell 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 88.81% 78.87% 70.05% 62.21% 55.25% 49.06% 43.57% 38.70% 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 % of transaction price (base) - - - - - nl transaction price vs base % of transaction price (hybrid) - - - - - 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 ne M&M relative to base 0.00 0.00 0.00 0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 0.00 0% 0.00 0.00 0% 0.00 0% 0.00 0% 0.00 0% 0.00 0% 0.00 0% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100% 0.00% 0.00 0% 100% 0.00% 0.00 0% 100% 0.00% 0.00 0% 100% 0.00% 0.00 0% 100% 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.00 0 0 0.00 0 0 0.00 0 0 0.00 0 0 0.00 0.00 0 0% 0 0% 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.00 0 0 0.00 0 0 0.00 2020 9 Y-on-Y sell changes Notes 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 34.37% 0.00 0.00 0.00 - $ transaction price decrease p.a. from 2016 onward (both models) - 0.00 Material costs change p.a. 0.00 0.00 0.00 0.00 0.00 0.00 Assumed warranty cost reduction p.a. $0.00 $0.00 0.00 0% 0.00% Sedan segment share growth p.a. 0.00 0.00% 0.00 0% 100% 0.000 0.000 0 0 0 0 0 0.00 New Impala share shrinkage after 2015 Hybrid share increase p.a. SUMMARY 2010 -1 2011 0 2012 1 2013 2 2014 3 2015 4 Pre-Devel Pre-Devel Devel Devel Devel sell $ in Millions Impala base net revenues Impala hybrid net revenues TOTAL REVENUES 3,327.39 907.47 4,234.85 Impala base variable costs Impala hybrid variable costs 2,789.63 766.31 Impala base fixed costs Impala hybrid fixed costs 210.00 140.00 228.00 152.00 282.00 188.00 140.00 35.00 Impala base total costs Impala hybrid total costs TOTAL COSTS 210.00 140.00 350.00 228.00 152.00 380.00 282.00 188.00 470.00 2,929.63 801.31 3,730.93 BASE NET CASH FLOWS HYBRID NET CASH FLOWS ALL NET CASH FLOWS -210.00 -140.00 -350.00 0.00 88.81% -228.00 -152.00 -380.00 0.00 78.87% -282.00 -188.00 -470.00 0.00 70.05% 397.76 106.16 503.92 503.92 62.21% -186.50 -124.33 -310.83 -179.83 -119.89 -299.71 -197.53 -131.69 -329.22 247.44 66.04 313.48 Discount factor BASE PV CASH FLOWS HYBRID PV CASH FLOWS ALL NET CASH FLOWS Discount Rate NPV IRR 535.02 5.49 540.51 34% 13% 26% 12.60% $540.51 100.00% $540.51 Per unit revenues & costs $ in '000 Revenues Data Impala base transaction price Impala base revenue per unit 90% GM revenue % of transaction price (base) 27.50 24.75 Impala Hybrid transaction price Impala hybrid revenue per unit 2.5 Hybrid add'nl transaction price vs base 90% GM revenue % of transaction price (hybrid) 30.00 27.00 Costs data Material Costs 16.50 Manufacturing (per unit) Total materials & manufacturing Base engine $$ Materials & Manufacturing Hybrid engine $$ Materials & Manufacturing Remainder of M&M costs (same for base & hybrid) 4.00 20.50 4.10 6.15 16.40 20% Base engine % of M&M 150% Hybrid engine M&M relative to base Warranty cost (assume same for both vehicles) 0.25 Impala base unit costs Impala hybrid unit costs $20.75 $22.80 US Vehicle sales data Units and $ in Millions Vehicle Sales US Vehicle sales growth (given in case) Sedans % of total US auto market Total Sedan Vehicle sales 12.1 12.58 4% 13.09 4% 13.35 2% 13.62 2% 13.89 2% 5.00% 5.10% 5.20% 5.30% 5.40% 5.50% 0.61 0.64 0.68 0.71 0.74 0.76 New Impala sales data Units and $ in Millions New Impala share Total Impala sales Base Impala sales as % of total Hybrid Impala sales % of total 22.00% 0.17 80.00% 20% Impala base sales Impala hybrid sales 0.134 0.034 New Impala Devel & Marketing Costs $ in Millions Engineering expense (3 years flat) Engineering Expense (total) Tooling base (timing given) Tooling $ Mill base per year Total Engineering, Tooling & Dev. expenses Base Development costs Hybrid Development costs Assumed lifecycle distribution of marketing costs Total Marketing cost Base Marketing costs Hybrid Marketing costs 600 Total lifecycle spend 33% 200 33% 200 33% 200 600 Total lifecycle spend 25% 150 30% 180 45% 270 350 210 140 380 228 152 470 282 188 60% % of total for base 40% % of total for hybrid Base model total Fixed Costs Hybrid model total fixed costs Total Fixed Costs 210 140 350.00 Discount Rate (using CAPM) Riskfree rate 35% 175 140 35 500 Total lifecycle spend 3.5% 228 152 380.00 282 188 470.00 140 35 175.00 Market Risk Premium (E[rm] - rf) Equity Beta Return on equity (CAPM) 7.0% 1.3 12.600% 2016 5 2017 6 2018 7 2019 8 2020 9 sell sell sell sell 3,277.92 1,010.15 4,288.07 3,224.66 1,114.38 4,339.04 3,167.69 1,219.99 4,387.68 3,107.11 1,326.78 4,433.89 3,043.02 1,434.57 4,477.58 2,775.44 860.18 2,758.42 957.21 2,738.55 1,057.37 2,715.82 1,160.66 2,690.21 1,267.08 97.50 27.50 38.00 12.00 37.00 13.00 36.00 14.00 35.00 15.00 2,872.94 887.68 3,760.62 2,796.42 969.21 3,765.63 2,775.55 1,070.37 3,845.92 2,751.82 1,174.66 3,926.48 2,725.21 1,282.08 4,007.29 404.98 122.46 527.44 527.44 55.25% 428.24 145.17 573.41 573.41 49.06% 392.14 149.62 541.76 541.76 43.57% 355.29 152.12 507.41 507.41 38.70% 317.81 152.48 470.29 470.29 34.37% 223.74 67.66 291.40 210.11 71.23 281.34 170.87 65.20 236.07 137.49 58.87 196.36 109.22 52.41 161.63 Y-on-Y sell changes Notes 27.00 24.30 26.50 23.85 26.00 23.40 25.50 22.95 25.00 22.50 29.50 26.55 29.00 26.10 28.50 25.65 28.00 25.20 27.50 24.75 16.34 16.17 16.01 15.85 15.69 4.00 20.34 4.07 6.10 16.27 4.00 20.17 4.03 6.05 16.14 4.00 20.01 4.00 6.00 16.01 4.00 19.85 3.97 5.95 15.88 4.00 19.69 3.94 5.91 15.75 0.24 0.23 0.22 0.21 0.20 $20.58 $22.61 $20.40 $22.42 $20.23 $22.23 $20.06 $22.04 $19.89 $21.86 14.17 2% 14.45 2% 14.74 2% 15.03 2% 15.33 2% 5.60% 5.70% 5.80% 5.90% 6.00% 0.79 0.82 0.85 0.89 0.92 -0.5 $ transaction price decrease p.a. from 2016 onward (both models) -1% Material costs change p.a. 0.01 Assumed warranty cost reduction p.a. 0.10% Sedan segment share growth p.a. 21.80% 0.17 78% 22% 21.60% 0.18 76% 24% 21.40% 0.18 74% 26% 21.20% 0.19 72% 28% 21.00% 0.193 70% 30% 0.135 0.038 0.135 0.043 0.135 0.048 0.135 0.053 0.135 0.058 25% 125 97.5 27.5 10% 50 38 12 10% 50 37 13 10% 50 36 14 10% 50 35 15 97.5 27.5 125.00 38 12 50.00 37 13 50.00 36 14 50.00 35 15 50.00 0.20% New Impala share shrinkage after 2015 2% Hybrid share increase p.a. Impact of Impala Market Share and Discount Rate on Total NPV Discount rate Impala 2015 12.6% 14.1% 15.6% 17.1% 18.6% share 16.0% 17.5% 19.0% 20.5% 22.0% Impact of Impala Market Share and Discount Rate on Hybrid NPV Discount rate Impala 2015 12.6% 14.1% 15.6% 17.1% 18.6% share 16.0% 17.5% 19.0% 20.5% 22.0% Impact of Impala Market Share and Share Shrinkage Rate on Total NPV Share Reduction Rate Impala 2015 0.20% 0.30% 0.40% 0.50% 0.60% share 16.0% 17.5% 19.0% 20.5% 22.0% .a. from 2016 onward (both models) tion p.a. fter 2015