stock price excel work

/in /by

Part 1

  • You should begin to collect the adjusted closing stock price of the company on a daily basis for an eight week period.Pick any consecutive eight week period (I would suggest the most recent eight week period). You will have 40 prices. Format so that all stock prices fit on one page in your spreadsheet.
  • Do the same (i.e., collect adjusted stock prices for the same 40-day period) for the market. I would suggest using the S&P 500, which can be found on Yahoo Finance using the ticker ^GSPC.
  • Using the prices, calculate the daily holding period returns for your stock (stock returns) and for the market (market returns). Be sure to show the calculation for at least one date.
  • In Excel, use the chart function to create a table showing the stock price movements over time. You only need to graph your company’s stock price over time (not the market). Observe from the chart any significant peaks and valleys in price. Clearly label your graph (dates on the x-axis, prices on the y-axis. Include a title).
  • Find at least 2 news articles during the time period of your stock price collection and explain how the news events described in the articles impact the observed movements in share price. The articles should reference your company specifically. You need to point out exactly where on your graph you think the news impacted the stock price. They must be from valid news sources—not press releases, analyst opinions, blogs, etc. Use only news from actual publications such as the Wall Street Journal, New York Times, or other newspapers or business magazines. Most general information finance websites will not work here (like Investopedia) or analyst opinion pieces and press releases (Motley Fool, Seeking Alpha, The Street, PR Newswire). You need to include hard copies of these articles with your final project (the actual articles, not website links).
  • Everything in Part 1 should fit on 1 printed Excel page.

Part 2

  • You must calculate the required rate of return for your stock. To do this, you will use the capital asset pricing model. This process is covered in detail in class. You will need several pieces of publicly available information – risk free rate, company’s beta, and the rate of return on a selected stock index.To calculate the required rate of return for your assigned company, follow the instructions below.
    • Calculate the beta for your company. To do this, use the daily holding period returns that you calculated in Part 1 (pull the prices and return data over to the Part 2 spreadsheet where highlighted). Graph these returns on a scatter plot (market returns on the x-axis and your company’s returns on the y-axis). Insert a linear trendline (the characteristic line) with the equation into the graph. Beta is the slope of this line. Be sure to include this graph and the accompanying characteristic line. Label axes.What does beta indicate about your company’s return versus market returns? Explain.
    • Find the most recent return on a 10-year Treasury security. http://www.treasury.gov/resource-center/data-chart-center/interest-rates/ (or elsewhere). This is in percent!
    • Assume that the market risk premium is 7.87%. This is according to the most recent Damodaran estimates (http://pages.stern.nyu.edu/~adamodar/).
    • What is a reasonable required rate of return for your company? Explain your results and show all calculations. Please show the calculation (formula) in Excel so that I can see all numbers and inputs into the model.
    • Please document all of the sources for each piece of data (including beta, risk-free rate, and market risk premium), including web addresses and dates.
  • Everything in Part 2 should fit on 1 printed Excel page.