MANAGERIAL FINANCE ASSIGNMENT: Risk and Return
In this assignment, students will continue to use the data of the three companies including S&P 500 in Assignment #3 to determine the correlation and beta of individual stocks. To do so, students are required to use Analysis Tool Bar.
Instruction on how to use Analysis Tool Bar.
If the computer you are using do not have "Data Analysis".
1. Using the regression function in Excel, determine the beta of each stock by using return series on a particular stock as the dependent variable and the S&P 500 return series as the explanatory variable. Repeat the same procedure for each stock to obtain beta of each stock. You also need to attach your regression output when submitting your work.
2. Which of the stocks would you classify as defensive? Which would be classified as aggressive? Why?In a boom, which stock you would recommend to invest? In a recession, which stock you would recommend to invest?
3. Assume that you are currently holding a well-diversified portfolio, which of the three stocks would be a good candidate to include in a well-diversified portfolio? (you are well-diversified investor). Hint: a well-diversified investor does not concern about unique risk but the market risk which is measured by beta?
4. Using Excel Correlation function (CORREL), find the correlation between each pair of three stocks and S&P 500. Which pair of stock has the highest/lowest correlation?
5. Now, you can find estimates of stock beta by logging on to finance.yaho.com and looking at a company's profile of three stock chosen. Use the capital asset price model (CAPM) to estimate expected return of each stock. You will need a figure for the current Treasury bill and you can find this also on finance.yaho.com by clocking on Bonds-Rates. You can use 30-year or 10-year bond as a risk-free rate since stocks are long-term investment. Assume that the market risk premium of 7%.
6. Compare the expected return of three stocks based on CAPM with the average returns you obtained. Which stock(s) is (are) overvalued or undervalued? Why? Fill in the following table based on the numbers you calculated.
Use the following Tables 1 and 2 to present your finding.
Table 1:
|
|
Stock A
|
Stock B
|
Stock C
|
S&P500
|
|
Average
|
|
|
|
|
|
Variance
|
|
|
|
|
|
Standard Deviation
|
|
|
|
|
|
Beta
|
|
|
|
|
Table 2:
|
Stock
|
CAPM Return
|
>, =, <
|
Average Returns
|
Over/Under/Correctly Priced
|
Recommendations (Buy/Sell)
|
|
A
|
|
|
|
|
|
|
B
|
|
|
|
|
|
|
C
|
|
|
|
|
|
Following shows the U.S. Treasury Bond Rates on August 18, 2015.
Bonds
U.S. Treasury Bonds Rates
|
Maturity
|
Yield
|
Yesterday
|
Last Week
|
Last Month
|
|
3 Month
|
0.05
|
0.06
|
0.05
|
0.01
|
|
6 Month
|
0.22
|
0.22
|
0.21
|
0.10
|
|
2 Year
|
0.71
|
0.70
|
0.67
|
0.67
|
|
3 Year
|
1.06
|
1.06
|
1.00
|
1.05
|
|
5 Year
|
1.57
|
1.56
|
1.52
|
1.67
|
|
10 Year
|
2.17
|
2.17
|
2.14
|
2.35
|
|
30 Year
|
2.84
|
2.82
|
2.81
|
3.08
|
Instructions on How to Determine the Beta of Each Stock
1. On the Excel Spreadsheet, go to Tools, Add-Ins, choose Analysis Toolpak.
2. Go to Tools, Data Analysis, choose Regression.
3. The Input X-Range will be the return on market portfolio-Highlight this column
4. The Input Y-Range will be the return on individual stock-Highlight this column
Instructions on How to Use Excel Spread Sheet to Determine the CorrelationAmong Stocks
1. Place the return series of Stock A, B, C and S&P 500 next to each other
2. On the Excel Spreadsheet, go to Tools, Add-Ins, choose Analysis Toolpak.
3. Go to Tools, Data Analysis, choose Correlation-Highlight the three columns of the returns.
Instructions:
1. Prepare a write-up of your report not more than 2 pages 12 points font and double space. The write-up should be a report format, summarizing all your findings and support your findings with numbers (using the questions asked as a guideline to form the report format). There is no need to explain the calculation in the report.
The report should contain the main Tables 1 and 2. Attached all data and calculation to the end of project.
The introduction should state the purpose of the paper. The body should consist of your findings with the support of the numbers. Place the tables of your finding and graphs where appropriate. The conclusion should provide a summary of your key findings.