Individual Stock Valuation Project

Individual Stock Valuation Project

This assignment will produce two documents both of which must be submitted via Moodle before the due date. Late submission will not be accepted. The documents are:

– an Excel file where you perform all the calculations. Saved as YourName_ProjectCalculations.xlsx – a PDF report that will include summary results for your calculations as well as your discussion of these

results (every table & graph in your report must be followed by a brief discussion of the values and their comparison to one another). Saved as YourName_ProjectReport.pdf

All material turned in are considered business documents. Errors and/or sloppy work will result in loss of points.

The first page of your report should include the followings: title of the case, course number, term, student’s name, instructor’s name, and a brief summary. The summary should be single-spaced. The next double spaced pages should include your analysis of the case. Pages should be numbered. All exhibits, such as figures, and tables, should be clearly labeled and referenced within the paper. Times New Roman font 12 should be used.

Project description and steps

1. Find names of the companies that were assigned to you in a separate spreadsheet. 2. Download daily stock price data that has been adjusted for splits and dividends for the two companies

assigned to you and the Market Index. 3. Move this data to Excel. Calculate daily returns. 4. Use Daily Returns to calculate annual returns for the last 5 years for the two companies and the Market

Index, and then calculate average returns over the five-year period. Include these results in your PDF report, e.g.

Table 1. Annual rates of return

Years Company A Company B Market Index 2018 2017 2016 2015 2014 Average

5. Calculate the annual standard deviation of the returns for the two companies, and the Market Index. Include these results in your PDF report, e.g.

Table 2. Annual Standard Deviation of Returns

Company A Company B Market Index Std Dev of Returns

6. Construct a scatter diagram graph that shows annual rates of return for the two companies on the Y-axis and the Market Index’s returns on the X-axis (To make the graph, first select the range with the returns and the column heads, then click the chart wizard and choose the scatter diagram without connected lines. That will give you the data points. Use the drawing toolbar to make free-hand (“by eye”) regression lines. Make sure that information for the two companies is presented using different colors and include a legend that explains that.) Include the graph in your PDF report.

Graph 1. Stock Returns vs. Market Index

7. Calculate correlation for Company A and the Index, and Company B and the Index. Are they consistent

with your graph? Include these results in your PDF report, e.g.

Table 3. Correlation

Company A & Index Company B & Index Correlation

8. Using data for Company A and Company B, construct a minimum-variance portfolio (use 5% as a maximum-step). Construct an Investment Opportunity Set for the portfolio. Include the graph in your PDF report, e.g.

Graph 2. Investment Opportunity Set

9. Estimate Company A’s, Company B’s, and minimum-variance portfolio’s betas. Are they consistent with your graphs? Include these results in your PDF report, e.g.

Table 4. Betas

Company A Company B Portfolio Beta

10. The risk-free rate on long-term Treasury bonds is 6.04%. Assume that the market risk premium is 5%. What is the expected return on the market? Now use the SML equation to calculate the two companies’ and the minimum-variance portfolio’s required returns. Include these results in your PDF report, e.g.

Table 5. Required Return

Company A Company B Portfolio Required Return