This assignment is to be completed by the same teams that worked on the case study project. You should answer each question completely and provide the Excel files, tables, calculations, plots, etc. that you assembled to answer the questions. Each question needs to write at least one page.
3. Go to the site https://www.portfoliovisualizer.com/efficient-fron… and enter ticker symbols (you can locate ticker symbols at finance.yahoo.com) for eight securities and select 2011 to 2016 as your time period. Do not use the securities used to answer Questions 1 and 2. What is expected return for the minimum variance portfolio on the efficient frontier?
4. Download daily adjusted closing prices for one of the stocks in Problem 1 and the S&P 500 Composite Index for 2015 and 2016. In an Excel spreadsheet, calculated the daily returns of the stock and the S&P 500 Composite Index for the full two years. Go to https://www.federalreserve.gov/datadownload/Choose… to download daily observations for the secondary market four-week Treasury bill rates, which will be your measure of the risk-free rate.
a. For each day use returns for the stock and the returns for the S&P 500 Composite Index, along with the Treasury bill rates to calculate excess returns for your stock and the S&P 500 Composite. Note that excess return for an asset is equal to the daily return less the risk-free rate.
b. Use the excess return data in part a. to produce an x-y plot with y as the excess return on the stock and x as the excess return on the market. Make sure your data is sorted by date from earliest to latest when you do this. Use the Data Analysis tools in Excel to calculate a regression of the stock’s excess return on the excess return of the composite index. The intercept of the regression equation is alpha and the slope is beta.
c. Repeat the procedures in part b separately on data from 2015 and on data from 2016. Are your alphas and beta different for each year?
5. For the firm that you used in Question 4, use Bloomberg to obtain the following: a. Return on equity (ROE), the number of shares outstanding, the dividends per share, and the net income. Record them in a spreadsheet.
b. Calculate the total amount of dividends paid (dividends per share x number of shares outstanding), the dividend payout ratio (total dividends paid/net income), and the plowback ratio (1 – dividend payout ratio).
c. Compute the sustainable growth rate, g = b X ROE, where b equals the plowback ratio.
d. Find the price-to-book, price-to-sales, and price-to-cash-flow ratios
e. Compare the three-year growth rate of earnings per share with the sustainable growth rate calculated above.
f. Use Bloomberg to obtain price-to-book, price-to-sales and price-to-cash flows for the industry in which your firm operates. Based on comparing the industry ratios with the firm ratios calculated in part d, does your firm appear to be overvalued or undervalued?
6. Go to the website of Moody’s at www.moodys.com.register Register at the site so that you can get free data. Find the ratings on bonds of at least 5 companies. Then access Bloomberg to get the most recent annual data for the following financial ratios of the five companies: EBIT interest coverage (earnings before interest and taxes divided by interest expense), Total debt/(total debt + equity), and return on assets. What is the relationship between bond rating and these ratios?
Hi there! Click one of our representatives below and we will get back to you as soon as possible.