Please help with this project.

I am stuck

10-Year Treasury Rate on Nov 02, 2020 (Risk Free rate)

Toyota Tokyo stock price Mar 31 (Close)

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 2oyota Template - Excel Philip Bacaa - X Home Insert Page Layout Formule Data Beiru VIEN Hels Tell me what ynu want to do Share 12 Calib General | Lat Autosum - Good Calculation Neutral X X. Ort [Copy Format Panta === 9 Wrap lest = = = US Murge & Cunur. S. Normal Cordeonsi Tera Check Cell Furmaling. Tall Explomtory... input Nul Insert Delete Format Sort & lind & Flu - Sedat Editing Chpber Font Agent Number Call 327 TE D H 1 M N F R 5 T U W Y AA AK AL AD AP AF 1 2 A 1 Pesumptions Capital perditur INITY 1 Depreciation Year 11 Increase in der Working Capital Yew Increase in der Working Capital Yes! Tax Race 4 3 million Un million USO million USD million USD million 9 10 FCF's Growth Rae 25 5% 2 2% 4 28 8 D 1 3 30 12 14 15 16 17 18 19 ERIT Lusalus UnlerdNet inom Ad: Depression Les Capital Expenditure Lain Terpin wr. FRE Dlerauer Re. Preren Ver NPV risk free rate 10 year Tressury Yield Ratel llevily bela i expected market risk premium 29 20 Moody's Rating 31 2920 Cash & Short Term investments ST Debt & Current Portion LT Debt Long-Term Dei Net Debt PY Brillian PYillions PY millinn .PY Gallura 35 30 37 Duted Shares Outstanding billions 20 56 Stock Price in JPY Market value of ulty Enterprbe Value 42 .NY iure JPY billions 24 4 WACC NPV Calculation + Type here to search O du 2:11 PM 11/2/20120 Select type of Interest Rate Data Select type of Interest Rate Data Daily Treasury Yield Curve Rates Select Time Period Select Time Period Date 1 Mo 2 Mo 3 Mo 6 Mo 11/02/20 0.09 0.09 0.09 0.11 11/03/20 0.09 0.10 0.10 0.12 11/04/20 0.08 0.09 0.10 0.10 11/05/20 0.09 0.10 0.10 0 10 11/06/20 0.10 0.10 0.10 0.11 1 Yr 2 Yr 3 Yr 5 Yr 7 Yr 10 Yr 20 Yr 30 Yr 0.13 0.16 0.20 0.38 0.63 0.87 1.41 1.63 0.14 0.17 0.21 0.39 0.65 0.90 1.44 1.66 0.12 0.14 0.18 0.33 0.55 0.78 1.33 1.55 0.12 0.14 0.18 0.33 0.56 0.79 1.32 1.54 0.12 0.16 0.21 0.36 0.59 0.83 1.37 1.60 Assets Fiscal year is April-March. All values JPY millions. 2016 2017 2018 2019 2020 5-year trend + Cash & Short Term Investments 5.48T 5.9T 5.72T 5.83T 5.77 Cash Only 2.94T 3T 3.05T 3.57T 4.19T + Total Current Liabilities 16.12T 17.32T 17.8T 18.23T 17.9T Long-Term Debt 9.777 9.91T 10.017 10.55T 10.93T Long-Term Deht eycl Capitalized leases 975T 9 99T 9991 1054T 10 671 Ratings IR, Stock Information & Ratings f in + (As of September 11, 2020) Institution Rating Standard & Poor's (S&P) A+ Moody's A1 Rating and Investment Information, Inc. (R&I) AAA Japan Credit Rating Agency, Ltd. (JCR) AAA * These ratings apply to long-term bonds issued by Toyota Motor Corporation. Liabilities & Shareholders' Equity 2016 2017 2018 2019 2020 5-year trend ST Debt & Current Portion LT Debt 8.52T 9.24T 9.34T 9.67 9.9T Short Term Debt 4.77 4.957 5.15T 5.34T 5.33T Current Portion of Long Term Debt 3.821 4.29T 4.19T 4.25T 4.57T Toyota Motor Corporation (TM) NYSE - NYSE Delayed Price. Currency in USD Add to watchlist 137.20 +0.13 (+0.09%) 137.20 0.00 At close: 4:00PM EST After hours: 4:16PM ES Summary Company Outlook Chart Conversations Sta Previous Close 137.07 187.128B Open 135.86 Market Cap Beta (5Y Monthly) PE Ratio (TTM) 0.78 Bid 135.08 x 1000 8.39 Ask 139.93 x 800 EPS (TTM) 16.36 Day's Range 136.11 - 138.20 Earnings Date N/A 52 Week Range 108.01 - 145.41 Forward Dividend & Yield 4.05 (2.96%) Volume 163,727 Ex-Dividend Date Mar 30, 2020 Avg. Volume 155,830 1y Target Est 160.61 Fair Value XX.XX -6% Est. Return Near Fair Value Related Research The Argus Global Dividend Mode... Analyst Report: Toyota Motor C... View more View details Apr 01, 2020 6,430.00 6,603.00 6,260.00 6,331.00 6,331.00 11,154,300 Mar 31, 2020 6,776.00 6,797.00 6,486.00 6,501.00 6,501.00 11,769,200 Mar 30, 2020 6,709.00 6,817.00 6,589.00 6,797.00 6,797.00 11,469,100 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 2oyota Template - Excel Philip Bacaa - X Home Insert Page Layout Formule Data Beiru VIEN Hels Tell me what ynu want to do Share 12 Calib General | Lat Autosum - Good Calculation Neutral X X. Ort [Copy Format Panta === 9 Wrap lest = = = US Murge & Cunur. S. Normal Cordeonsi Tera Check Cell Furmaling. Tall Explomtory... input Nul Insert Delete Format Sort & lind & Flu - Sedat Editing Chpber Font Agent Number Call 327 TE D H 1 M N F R 5 T U W Y AA AK AL AD AP AF 1 2 A 1 Pesumptions Capital perditur INITY 1 Depreciation Year 11 Increase in der Working Capital Yew Increase in der Working Capital Yes! Tax Race 4 3 million Un million USO million USD million USD million 9 10 FCF's Growth Rae 25 5% 2 2% 4 28 8 D 1 3 30 12 14 15 16 17 18 19 ERIT Lusalus UnlerdNet inom Ad: Depression Les Capital Expenditure Lain Terpin wr. FRE Dlerauer Re. Preren Ver NPV risk free rate 10 year Tressury Yield Ratel llevily bela i expected market risk premium 29 20 Moody's Rating 31 2920 Cash & Short Term investments ST Debt & Current Portion LT Debt Long-Term Dei Net Debt PY Brillian PYillions PY millinn .PY Gallura 35 30 37 Duted Shares Outstanding billions 20 56 Stock Price in JPY Market value of ulty Enterprbe Value 42 .NY iure JPY billions 24 4 WACC NPV Calculation + Type here to search O du 2:11 PM 11/2/20120 Select type of Interest Rate Data Select type of Interest Rate Data Daily Treasury Yield Curve Rates Select Time Period Select Time Period Date 1 Mo 2 Mo 3 Mo 6 Mo 11/02/20 0.09 0.09 0.09 0.11 11/03/20 0.09 0.10 0.10 0.12 11/04/20 0.08 0.09 0.10 0.10 11/05/20 0.09 0.10 0.10 0 10 11/06/20 0.10 0.10 0.10 0.11 1 Yr 2 Yr 3 Yr 5 Yr 7 Yr 10 Yr 20 Yr 30 Yr 0.13 0.16 0.20 0.38 0.63 0.87 1.41 1.63 0.14 0.17 0.21 0.39 0.65 0.90 1.44 1.66 0.12 0.14 0.18 0.33 0.55 0.78 1.33 1.55 0.12 0.14 0.18 0.33 0.56 0.79 1.32 1.54 0.12 0.16 0.21 0.36 0.59 0.83 1.37 1.60 Assets Fiscal year is April-March. All values JPY millions. 2016 2017 2018 2019 2020 5-year trend + Cash & Short Term Investments 5.48T 5.9T 5.72T 5.83T 5.77 Cash Only 2.94T 3T 3.05T 3.57T 4.19T + Total Current Liabilities 16.12T 17.32T 17.8T 18.23T 17.9T Long-Term Debt 9.777 9.91T 10.017 10.55T 10.93T Long-Term Deht eycl Capitalized leases 975T 9 99T 9991 1054T 10 671 Ratings IR, Stock Information & Ratings f in + (As of September 11, 2020) Institution Rating Standard & Poor's (S&P) A+ Moody's A1 Rating and Investment Information, Inc. (R&I) AAA Japan Credit Rating Agency, Ltd. (JCR) AAA * These ratings apply to long-term bonds issued by Toyota Motor Corporation. Liabilities & Shareholders' Equity 2016 2017 2018 2019 2020 5-year trend ST Debt & Current Portion LT Debt 8.52T 9.24T 9.34T 9.67 9.9T Short Term Debt 4.77 4.957 5.15T 5.34T 5.33T Current Portion of Long Term Debt 3.821 4.29T 4.19T 4.25T 4.57T Toyota Motor Corporation (TM) NYSE - NYSE Delayed Price. Currency in USD Add to watchlist 137.20 +0.13 (+0.09%) 137.20 0.00 At close: 4:00PM EST After hours: 4:16PM ES Summary Company Outlook Chart Conversations Sta Previous Close 137.07 187.128B Open 135.86 Market Cap Beta (5Y Monthly) PE Ratio (TTM) 0.78 Bid 135.08 x 1000 8.39 Ask 139.93 x 800 EPS (TTM) 16.36 Day's Range 136.11 - 138.20 Earnings Date N/A 52 Week Range 108.01 - 145.41 Forward Dividend & Yield 4.05 (2.96%) Volume 163,727 Ex-Dividend Date Mar 30, 2020 Avg. Volume 155,830 1y Target Est 160.61 Fair Value XX.XX -6% Est. Return Near Fair Value Related Research The Argus Global Dividend Mode... Analyst Report: Toyota Motor C... View more View details Apr 01, 2020 6,430.00 6,603.00 6,260.00 6,331.00 6,331.00 11,154,300 Mar 31, 2020 6,776.00 6,797.00 6,486.00 6,501.00 6,501.00 11,769,200 Mar 30, 2020 6,709.00 6,817.00 6,589.00 6,797.00 6,797.00 11,469,100