Business Finance Homework Help

Investment Analysis Team Project

INSTRUCTIONS: 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.

1. Use either finance.yahoo.com or Bloomberg and download five years of monthly closing prices for five stocks and the S&P 500 Index (ticker symbol “GSPC”). Do not use Apple or Monsanto. Download the data into an Excel file and use the Adjusted-Close prices, which adjust for dividend payments, to calculate the monthly rate of return for each price series. Use an XY Scatter Plot chart with no line joining the points to plot each stock’s returns against the S&P 500. Now for each scatter plot chart, select one of the data points, and right-click to obtain a shortcut menu allowing you to enter a trend line. This is the company’s characteristic line, and the slope of the line is the company’s beta. You can use the slope function In Excel to compute the slope of the line, which is beta. Compile the betas of each company in a table and provide copies of each company’s characteristic line (scatter plot). Based on your results, how are your companies similar and different? Which companies are aggressive, and which are defensive?

2. Use the monthly returns data for the eight stocks and the S&P 500 Composite Index in Question 1. a. Using the Excel functions for average (AVERAGE) and sample standard deviation (STDEV), calculate the average and the standard deviation of the returns for each of the firms and the S&P 500 Composite Index. Prepare a table showing the results of your calculations

b. Using Excel’s correlation function (CORREL), construct the correlation matrix for the five stocks based on their monthly returns for the entire period. What are the lowest and the highest individual pairs of correlation coefficients? (Alternative: You may use Excel’s Data Analysis Tool to generate the correlation matrix.)

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.

2

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?