There are two assignments listed below. DO NOT DO BOTH! Choose only one assignment to complete. Pick the one you most feel competent and confident in doing. The class these assignments are from is a corporate finance class. There were two other attachments besides this one. The attachment labeled "This is for option 1" is material for option #1, and the attachment called "This is for option 2" is material for option #2.
Option #1: Risk and Return Study
Attached is a spreadsheet that has the annual return measured for 12 different stock investments. The spreadsheet shows the average return and standard deviation of the return for the past 15 years. Use this spreadsheet and spreadsheet commands to do the following:
1. Compute the return for each year on a portfolio that contains an equal investment in all 12 securities.
2. Compute the 15-year average return and standard deviation of return for the portfolio that consists of all 12 securities with equally weighted investment.
3. Compute the correlation and covariance between the return on company #12 and the return on the equally-weighted portfolio. Hint: There is a spreadsheet command that does this calculation.
4. Compute the beta of Company #12 using the information you have collected.
5. Now using the beta you created for Company #12, compute the required rate of return using the Capital Asset Pricing Model (CAPM), assuming that the average market return is the return of your equally-weighted portfolio and the risk-free rate of return is 2.5%.
6. If you were told analysts estimate that Company #12 will have a 5% rate of return next year, would you buy the stock? Why or why not?
Your submission should meet the following requirements:
• Complete your work on the Excel sheet provided.
• Be sure to keep your formulas or commands in the cells on your Excel sheet.
• Include any brief explanatory remarks needed, on the Excel sheet.
Option #2: Stock Beta
Compute the beta for a stock that is included in the Dow Jones Industrial Average using regression analysis in Excel.
Complete the following:
• Find the beta reported at a financial website such as Yahoo Finance or Google Finance.
• Describe how this beta compares with the beta you have calculated.
• Discuss the R-squared value of your beta and the statistical predictive value of beta.
• Describe what the empirical financial research indicates regarding the accuracy of beta as a predictor of risk.
• Include an appendix containing all calculations.
Your paper should meet the following requirements:
• Be approximately 2 pages in length, not including the cover page and reference page.
• There should be an introduction, a body with at least two fully developed paragraphs, and a conclusion.
• Support your answers with at least one scholarly journal article.
• Be clear and well written, concise, and logical, using excellent grammar and style techniques. You are being graded in part on the quality of your writing.