Reference no: EM132944416
Estimate the weighted average cost of capital for Novartis, AG (ticker NVS), using the income statement and balance sheet data for NVS from www.morningstar.com, and using the historical stock price data and the market capitalization data for NVS and VFINX from www.finance.yahoo.com. (Do not use balance sheet and income statement from finance.yahoo.com, it has errors in it. To access the balance sheet and income statement data on Morningstar.com, first type in the ticker NVS; after pulling the main quote screen for Novartis click on the Key Ratios tab (it's a little below the tabs at the top of the page), and then on Full Key Ratios Data a little below; then click on Financials; make sure you use year 2020 data from the balance sheet and income statement on Morningstar - do not use the TTM column.) Note: For the balance sheet and income statement you will need the interest expense item, and for debt add together the "short term debt" and "long term debt" items. You will also need income before taxes and provision for income taxes to calculate the tax rate. To get the market value of equity use the market cap from the summary page on Yahoo Finance.
Problem 1. Create a sheet in your Excel file (Q5a) that estimates the weighted average cost of capital, listing the necessary inputs at the top of your sheet. Obtain any necessary data from the sources listed above. Link to other sheets in your file that will calculate the cost of equity and beta for NVS.
Problem 2. Create a second sheet (Q5b), where you use the capital asset pricing model to estimate the cost of equity for NVS, assuming 2.5% risk-free rate and 5.5% market risk premium. Link to a separate sheet with beta estimation.
Problem 3. Create a third sheet (Q5c), where you estimate a beta for NVS, using historical prices with WEEKLY frequency for the following dates (May 2016 to May 2021): starting date 05/01/2016, and ending date 05/01/2021 (note: input the dates 5/1 using the calendar icon, when you click "done" the dates showing may be 4/30, that's okay; make sure you hit "APPLY" once you input the dates). Use finance.yahoo.com to download the prices for NVS and VFINX (make sure you use the ADJUSTED CLOSE price). Use the SLOPE function to estimate beta. Insert a scatter chart that shows the trendline from regressing returns of NVS on the VFINX returns, and displays the estimated equation.