I am in the weeds this semester and I need help/guidance with this case.
Below is all the information given by the instructor.
The template to be used is also attached.
I need help setting up excel sheet, with the correct formulas
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 2. Determine the cost of equity ry and the cost of debt ro- a. For the cost of equity TE, 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/Text View.aspx?data=yield). Select the following data and time period.Select type of Interest Rate Data Daily Treasury Yield Curve Rates Go Select Time Period Current Month Go Find the 10-Year Treasury Rate on Nov 02, 2020. Enter that yield as the risk-free rate. ii. Equity beta. Find the equity beta for Toyota from Yahoo! Finance (https://finance.yahoo.com/). On the middle right of the 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 Curl+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 computer using the CAPM in excel (include your work for easy tracing. E.g., type the formula in Excel and no hardcoding). b. For the cost of debt, rD: i. Find ratings. Go to 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 r, 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 CCC Avg. Bera
