Please help with this project. I am in the weeds this semester
Case 2 - Toyota's Hybrid Drive Systems The main goal of this case is to reinforce your understanding of how to estimate the cost of capital in capital budgeting Once you finish learning Lecture 10 - Estimating the Cost of Capital, you will be able to answer the questions 1-3 below and case 2 discussion questions in the separate file"3. Case 2 Discussion Forum Instructions). Once you finish Lecture 11 - Capital Budgeting and Valuation with Leverage, you will be able to answer the rest questions (#4 and #5) and completely solve the case. Introduction As of late 2019, Toyota Motor Corporation has twelve engineering and manufacturing facilities in the United States. Recently, Toyota has been considering expanding the production of their gas- electric hybrid drive systems in the U.S. To enable the expansion, they are contemplating investing $1.5 billion at the end of 2020 (Year 0) in a new plant with an expected 10-year life. In the meantime, they also need to invest $30 million upfront in net working capital before the production can take place. The projected financials of the new project for the Year 1 operation (2021) are as follows: Earnings before interest and taxes (EBIT): $170 million Depreciation expense: $150 million Increase in net working capital: $40 million The anticipated unlevered free cash flows from the new plant will grow by 5% for each of the next two years (Years 2 and 3) and then 2% per year for the remaining seven years. As a newly hired MBA in the capital budgeting division you have been asked to evaluate the new project using the WACC method. You will estimate the cash flows and compute the appropriate cost of capital and the net present values. You must seek out the information necessary to value the free cash flows but will be provided some directions to follow. Assume that marginal corporate tax rate is 40%. 1. In excel, estimate the project's unlevered free cash flows from Year 0 to Year 10 (or equivalently from year 2020 to 2030, the end year of the project). A template is provided on blackboard. a 2. Determine the cost of equity rg and the cost of debt rp For the cost of equity re, i. Risk-free rate. Get the yield on the 10-year U.S. Treasury Bond from the U.S. Department of the Treasury (https://www.treasury.gov/resource-center/data-chart-center/interest- rates/pages/TextView.aspx?data=vield). Select the following data and time period. 1 Y Go Select type of Interest Rate Data Daily Treasury Yield Curve Rates Select Time Period Current Month Go Find the 10-Year Treasury Rate on Nov 02, 2020. Enter that yield as the risk-free rate. 11. Equity beta Find the equity beta for Toyota from Yahoo! Finance (https://finance.yahoo.com). On the middle right of web page, find the search box and enter the symbol for Toyota (TM). Under the Summary tab, you can find the beta for Toyota (if you use Windows, press Ctrl+F to search for the beta). iii. Expected market risk premium. Use the historical approach to estimate the expected market risk premium (see the slides 7 and 8 in Lecture 10). The historical market risk premium in U.S. is in a range of 3-5%. Determine what the expected market risk premium you should choose. Select one from the lower bound, the upper bound, and the average (this is one of case discussion questions)? An important factor to consider is the Covid-19 pandemic, which seems to get worse recently. How does that affect investors' expected return? Use your choice of market risk premium to compute rg using the CAPM in excel (include your work for easy tracing. E.g., type the formula in Excel and no hardcoding). to b. For the cost of debt, : 1. Find ratings. Go the Toyota's investor relation web page (https://global.toyota/en/ir/stock/rating and find its latest S&P rating. As noted in the ratings table, these ratings apply to long-term bonds issued by Toyota Motor Corporation 11. Debt beta. If you find that Toyota has a rating of A or above, then you can make the approximation that the cost of debt ro is the risk-free rate. If Toyota's credit rating has slipped, use Table 12.3 below to estimate its debt beta based on the credit ratings and use the CAPM to estimate ro in excel. TABLE 12.3 Average Debt Betas by Rating and Maturity 15 By Rating A and above BBB BB B Avg. Beta 15 Year Avg. Beta 0.01 0.06 0.07 0.14 Source: S. Schaefer and I. Strebulaev, "Risk in Capital Structure Arbitrage," Stanford GSB working paper, 2009, 3. Determine the values for E and D a. Net debt 2 Go to MarketWatch (https://www.marketwatch.com/) and get the following financial statement data for Toyota (symbol: TM). Find the search icon on the top right corner and search for TM. Do NOT obtain the financial statement data from Yahoo! Finance as it has been unstable in the past few days (gave me wrong financials and mislabelled accounting terms, maybe because Yahoo is updating its Financials function). Go to Financials tab and click the Balance Sheet tab. Copy and paste the following 2020 financial data into your Excel. Note the values are in JPY trillions. Cash & Short Term Investments ST Debt & Current Portion LT Debt Long-Term Debt To compute the net debt for Toyota, add the long-term debt and the short-term debt and subtract cash and short-term investments. When estimating the WACC, in theory, we should use the market value of debt, but here we use the book value of debt as a proxy for the market value of debt. The reason is that the market value of debt is generally not very different from its book value for firms with high credit quality. Go to Income Statement tab, copy and paste the 2020 Diluted Shares Outstanding into excel. b. Stock Price To find the market value of equity (price x shares outstanding) and compare it to the financials from the balance sheet, we need to find the stock price in JPY in the Tokyo Stock Exchange. Go to Yahoo! Finance, enter the Toyota' symbol in Tokyo Stock Exchange (7203.T). Under Historical Data tab, find the Toyota's stock price on March 31, 2020 (the last trading date prior to its latest fiscal year end) and enter the close price (NOT Adjusted Close) in Excel. Again, the price is in JPY Sidenote: Why should we not find the stock price in USD? Toyota stock is offered in the form of American Depositary Receipts (ADRs) in the New York Stock Exchange. Each ADR represents two shares of common stock and is based on Toyota shares traded in Japan. Thus, if you enter the Toyota's symbol TM, then the price shown is the price per ADR in USD, not per share of common stock. To get the price per share of common stock in JPY, you need to divide the price per ADR by two and convert it to JPY, which involves the exchange rate. To make things simple, we just obtain the price per common stock in JPY by searching its symbol in Tokyo Stock Exchange (7203.T). Multiply the stock prices by the number of diluted shares outstanding you collected to compute Toyota's market value of equity at the end of latest fiscal year. Report it in JPY trillions in Excel c. Compute Toyota's enterprise value on March 31, 2020 by combining its market value of equity and net debt. 3 4. Determine the WACC using the below formula. Note that D is net debt, not total debt. E D ace TE + p(1 - 1) E + D E + D 5. Assume Toyota maintains a constant leverage ratio. Compute the NPV of the hybrid engine expansion project given the free cash flows you calculated in question 1 using the WACC method of valuation 4 2. Case 2 Toyota Template - Excel Phillip Baeza File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do & Share Ie> AutoSum Calibri 12 AA ab Wrap Text General Bad Good Neutral Calculation & Cut e Copy *Format Painter Ay BO Fill- Paste BIU- Merge & Center - $ % Normal Conditional Format as Check Cell Formatting Table 8 .00 Explanatory... Input Linked Cell Note Insert Delete Format Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Styles Cells B27 X f TE C D F F H J K L M N o P Q R s U v w X Y Z AB AC AD AE AF 3 A B 1 Assumptions Capital Expenditure EBIT (Year 1) Depreciation (Year 1) Increase in Net Working Capital (Year 1) Increase in Net Working Capital (Year O) Tax Rate 4 USD million USD million USD million USD million USD million 5 6 7 8 9 10 5% 2 5% 3 2% 4 2% 5 2% 6 2% 7 2% 8 2% 9 2% 10 0 1 11 12 13 14 15 16 FCF's Growth Rate Year EBIT Less: Taxes Unlevered Net Income Add: Depreciation Less: Capital Expenditures Less: Increase in NWC FCF 5 Discount Rate Present Value of FCF NPV 17 18 19 20 21 22 23 24 25 26 27 28 2a risk free rate (10-year Treasury Yield Rate) ii equity beta iii expected market risk premium re 29 2b Moody's Rating rD 30 31 32 33 34 3a 2020 Cash & Short Term Investments ST Debt & Current Portion LT Debt Long-Term Debt Net Debt JPY trillions JPY trillions JPY trillions JPY trillions 35 36 37 Diluted Shares Outstanding billions 38 39 40 41 3b Stock Price (in JPY) Market Value of Equity 3c Enterprise Value JPY trillions JPY trillions 42 43 44 4 WACC NPV Calculation + 859 Type here to search o E 2:11 PM 11/2/2020 Case 2 - Toyota's Hybrid Drive Systems The main goal of this case is to reinforce your understanding of how to estimate the cost of capital in capital budgeting Once you finish learning Lecture 10 - Estimating the Cost of Capital, you will be able to answer the questions 1-3 below and case 2 discussion questions in the separate file"3. Case 2 Discussion Forum Instructions). Once you finish Lecture 11 - Capital Budgeting and Valuation with Leverage, you will be able to answer the rest questions (#4 and #5) and completely solve the case. Introduction As of late 2019, Toyota Motor Corporation has twelve engineering and manufacturing facilities in the United States. Recently, Toyota has been considering expanding the production of their gas- electric hybrid drive systems in the U.S. To enable the expansion, they are contemplating investing $1.5 billion at the end of 2020 (Year 0) in a new plant with an expected 10-year life. In the meantime, they also need to invest $30 million upfront in net working capital before the production can take place. The projected financials of the new project for the Year 1 operation (2021) are as follows: Earnings before interest and taxes (EBIT): $170 million Depreciation expense: $150 million Increase in net working capital: $40 million The anticipated unlevered free cash flows from the new plant will grow by 5% for each of the next two years (Years 2 and 3) and then 2% per year for the remaining seven years. As a newly hired MBA in the capital budgeting division you have been asked to evaluate the new project using the WACC method. You will estimate the cash flows and compute the appropriate cost of capital and the net present values. You must seek out the information necessary to value the free cash flows but will be provided some directions to follow. Assume that marginal corporate tax rate is 40%. 1. In excel, estimate the project's unlevered free cash flows from Year 0 to Year 10 (or equivalently from year 2020 to 2030, the end year of the project). A template is provided on blackboard. a 2. Determine the cost of equity rg and the cost of debt rp For the cost of equity re, i. Risk-free rate. Get the yield on the 10-year U.S. Treasury Bond from the U.S. Department of the Treasury (https://www.treasury.gov/resource-center/data-chart-center/interest- rates/pages/TextView.aspx?data=vield). Select the following data and time period. 1 Y Go Select type of Interest Rate Data Daily Treasury Yield Curve Rates Select Time Period Current Month Go Find the 10-Year Treasury Rate on Nov 02, 2020. Enter that yield as the risk-free rate. 11. Equity beta Find the equity beta for Toyota from Yahoo! Finance (https://finance.yahoo.com). On the middle right of web page, find the search box and enter the symbol for Toyota (TM). Under the Summary tab, you can find the beta for Toyota (if you use Windows, press Ctrl+F to search for the beta). iii. Expected market risk premium. Use the historical approach to estimate the expected market risk premium (see the slides 7 and 8 in Lecture 10). The historical market risk premium in U.S. is in a range of 3-5%. Determine what the expected market risk premium you should choose. Select one from the lower bound, the upper bound, and the average (this is one of case discussion questions)? An important factor to consider is the Covid-19 pandemic, which seems to get worse recently. How does that affect investors' expected return? Use your choice of market risk premium to compute rg using the CAPM in excel (include your work for easy tracing. E.g., type the formula in Excel and no hardcoding). to b. For the cost of debt, : 1. Find ratings. Go the Toyota's investor relation web page (https://global.toyota/en/ir/stock/rating and find its latest S&P rating. As noted in the ratings table, these ratings apply to long-term bonds issued by Toyota Motor Corporation 11. Debt beta. If you find that Toyota has a rating of A or above, then you can make the approximation that the cost of debt ro is the risk-free rate. If Toyota's credit rating has slipped, use Table 12.3 below to estimate its debt beta based on the credit ratings and use the CAPM to estimate ro in excel. TABLE 12.3 Average Debt Betas by Rating and Maturity 15 By Rating A and above BBB BB B Avg. Beta 15 Year Avg. Beta 0.01 0.06 0.07 0.14 Source: S. Schaefer and I. Strebulaev, "Risk in Capital Structure Arbitrage," Stanford GSB working paper, 2009, 3. Determine the values for E and D a. Net debt 2 Go to MarketWatch (https://www.marketwatch.com/) and get the following financial statement data for Toyota (symbol: TM). Find the search icon on the top right corner and search for TM. Do NOT obtain the financial statement data from Yahoo! Finance as it has been unstable in the past few days (gave me wrong financials and mislabelled accounting terms, maybe because Yahoo is updating its Financials function). Go to Financials tab and click the Balance Sheet tab. Copy and paste the following 2020 financial data into your Excel. Note the values are in JPY trillions. Cash & Short Term Investments ST Debt & Current Portion LT Debt Long-Term Debt To compute the net debt for Toyota, add the long-term debt and the short-term debt and subtract cash and short-term investments. When estimating the WACC, in theory, we should use the market value of debt, but here we use the book value of debt as a proxy for the market value of debt. The reason is that the market value of debt is generally not very different from its book value for firms with high credit quality. Go to Income Statement tab, copy and paste the 2020 Diluted Shares Outstanding into excel. b. Stock Price To find the market value of equity (price x shares outstanding) and compare it to the financials from the balance sheet, we need to find the stock price in JPY in the Tokyo Stock Exchange. Go to Yahoo! Finance, enter the Toyota' symbol in Tokyo Stock Exchange (7203.T). Under Historical Data tab, find the Toyota's stock price on March 31, 2020 (the last trading date prior to its latest fiscal year end) and enter the close price (NOT Adjusted Close) in Excel. Again, the price is in JPY Sidenote: Why should we not find the stock price in USD? Toyota stock is offered in the form of American Depositary Receipts (ADRs) in the New York Stock Exchange. Each ADR represents two shares of common stock and is based on Toyota shares traded in Japan. Thus, if you enter the Toyota's symbol TM, then the price shown is the price per ADR in USD, not per share of common stock. To get the price per share of common stock in JPY, you need to divide the price per ADR by two and convert it to JPY, which involves the exchange rate. To make things simple, we just obtain the price per common stock in JPY by searching its symbol in Tokyo Stock Exchange (7203.T). Multiply the stock prices by the number of diluted shares outstanding you collected to compute Toyota's market value of equity at the end of latest fiscal year. Report it in JPY trillions in Excel c. Compute Toyota's enterprise value on March 31, 2020 by combining its market value of equity and net debt. 3 4. Determine the WACC using the below formula. Note that D is net debt, not total debt. E D ace TE + p(1 - 1) E + D E + D 5. Assume Toyota maintains a constant leverage ratio. Compute the NPV of the hybrid engine expansion project given the free cash flows you calculated in question 1 using the WACC method of valuation 4 2. Case 2 Toyota Template - Excel Phillip Baeza File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do & Share Ie> AutoSum Calibri 12 AA ab Wrap Text General Bad Good Neutral Calculation & Cut e Copy *Format Painter Ay BO Fill- Paste BIU- Merge & Center - $ % Normal Conditional Format as Check Cell Formatting Table 8 .00 Explanatory... Input Linked Cell Note Insert Delete Format Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Styles Cells B27 X f TE C D F F H J K L M N o P Q R s U v w X Y Z AB AC AD AE AF 3 A B 1 Assumptions Capital Expenditure EBIT (Year 1) Depreciation (Year 1) Increase in Net Working Capital (Year 1) Increase in Net Working Capital (Year O) Tax Rate 4 USD million USD million USD million USD million USD million 5 6 7 8 9 10 5% 2 5% 3 2% 4 2% 5 2% 6 2% 7 2% 8 2% 9 2% 10 0 1 11 12 13 14 15 16 FCF's Growth Rate Year EBIT Less: Taxes Unlevered Net Income Add: Depreciation Less: Capital Expenditures Less: Increase in NWC FCF 5 Discount Rate Present Value of FCF NPV 17 18 19 20 21 22 23 24 25 26 27 28 2a risk free rate (10-year Treasury Yield Rate) ii equity beta iii expected market risk premium re 29 2b Moody's Rating rD 30 31 32 33 34 3a 2020 Cash & Short Term Investments ST Debt & Current Portion LT Debt Long-Term Debt Net Debt JPY trillions JPY trillions JPY trillions JPY trillions 35 36 37 Diluted Shares Outstanding billions 38 39 40 41 3b Stock Price (in JPY) Market Value of Equity 3c Enterprise Value JPY trillions JPY trillions 42 43 44 4 WACC NPV Calculation + 859 Type here to search o E 2:11 PM 11/2/2020
