Assignment
Company: Macy's Inc
1. Using the ein.ie company you chose in Week 2, and completed Final Project Part 1 for, download on a spreadsheet three months worth of daily stock prices from the "Historical Data" tab of Yahoofinance website. Use the "Close" prices:
2. Search your company's index (NYSE or NASDAQ), and download the same three months of daily index prices from the "Historical Data" tab of Yahoofinance.com on the same spreadsheet, next to your stock prices;
3. On the spreadsheet calculate stock returns AND index returns, by performing the following calculation: (today's price minus yesterday's price) divided by yesterday's price. For example, if today's stock is worth $1.10 and yesterday it was worth $1.00. The return is: (1.10 - 1.00)/1.00 = 0.10. Calculate the daily returns for both the stock and index over the three month period.
4. Total risk assessment: Calculate the Mean of the stock returns using the =AVERAGE (function in Excel. Do the same for the Mean of the index returns. Calculate the Standard Deviation of the stock returns using the =STDEV (function in Excel. Do the same for the Standard Deviation of the index returns. Calculate the Coefficient of Variation for both the stock and index returns by dividing The Standard Deviation by the Mean. Make a brief Total Risk assessment;
5. Evaluate Market Risk: Calculate your company's beta by regressing your stock's returns on the index returns using the = LINEST (function in Excel. Look up your company's beta on Yahoofinance. It is also available on other Finance web sites. Be sure to document where you picked up your beta.
6. Estimate Cost of Equity by using the Capital Asset Pricing Model: Assume risk free rate is 2.87% and the Market Risk Premium is 4.2%, and combine it with your beta.
7. Estimate the Cost of equity using the DCF model: rS = D1/P0+ g. : For your estimate of the growth rate use the analyst's forecast of earnings growth that you have to research. Alternatively you can also calculate growth with the formula: g= X(ROE) where X is the earnings retention ratio (percentage of Net Income that is not paid out in Dividends) and ROE is the Return on Equity,
8. Estimate the Cost of Equity using the bond yield plus risk premium approach. You should be able to find the company's cost of debt in the footnotes to the financial statements, or with a rough estimate by dividing interest expense by all interest bearing debt (Long term Debt + Short Term debt, but not Accounts Payable). Vie will assume the risk premium is 4.2%;
9. Estimate the Cost of Equity by averaging all three methods;
10. Calculate WACC using the formula in the textbook.
11 Finally, write an analysis. Start by documenting everything you did, where you got your data and any assumptions you made. Then, answer the following questions: What time period does your data cover and why? How does your computed beta compare with the published beta? Why do you think they differ? Which beta represents a better estimate of the market risk of your' stock? How does the beta compare to the total risk you calculated with Standard Deviation and CV? Does your company appear to have a lot of company specific risk or a little? Is it possible for a company to have lots of company specific risk and little market risk? Explain. How do your three Cost of Equity estimates compare? Which do you feel is the best Cost of Equity estimate? Do you think your firm's recent performance is a good indicator of expected future returns? Explain your WACC calculation. Justify and explain how you decided on the Cost of Debt estimate. Graph your stocks price performance against the market index.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also include a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also Include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.