ProjTeam1

# ProjTeam1 - Fin500 Advanced Topics in Finance Excel...

This preview shows pages 1–3. Sign up to view the full content.

Hints: You may use Excel AVERAGE function to find mean returns, STDEV function to find standard deviation, use CORREL function to find correlation coefficient Use Tools|Data Analysis|Regression to do regression analysis in order to find beta. Do not use slope function to find beta. I need to see regression results including R-squre, t-stat, p-value etc. After finishing this project, you will be able to mater some important Excel functions and tool. Your finished work should be submitted in Excel workbook format, i.e., .xls file. Fin500 Advanced Topics in Finance Excel Project: Risk Analysis , 10 points This project is due by 01/31/11 9:00 California Time . You should finish this project as a team work . Your finished work should be submitted in Excel workbook format, i.e., .xls file. If Team 1 finish this Excel Project and want to submit it, they should save it as a xls file and name the file as ProjTeam1. Students are provided historical returns of Stock DO, Stock GDT and S&P 500 index in Sheet 2, Where DO is symbol of Diamond Offshore Drilling Inc., and GDT stands for Guidant Corp. Students are asked to finish the following: 1. Distinguish between systematic and nonsystematic risk ( 1 point ) 2. Suppose a portfolio had been formed with 40% portfolio value in stock DO and 60% in stock GDT. Compute the mean and standard deviation of the individual stock returns, portfolio returns, and S&P 500 index returns over the sample period. Explain your results. Is the portfolio’s return riskier than the individual stock returns, than the index returns? ( 3 points ) 3. Explain what is correlation coefficient and calculate the correlation coefficient between stock DO and stock GDT’s returns. ( 1 point ) 4. Using regression analysis , estimate the beta of each of stock and the portfolio 3 points ). What is the relationship between portfolio’s beta and its’ component stocks’ betas? ( 0.5 point ) 5. will be 10%, what will be the required return on stock DO, on stock GDT and on the portfolio ( 1.5 points )? Hint: apply the CAPM model.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Date DO GDT portfolio Nov-00 0.180124 0.133824 0.004053 0.1523439514 SUMMARY OUTPUT Oct-00 -0.308548 -0.019563 -0.080069 -0.1351570708 Sep-00 -0.246940 0.049243 -0.004949 -0.0692300582 Regression Statistics Aug-00 0.076707 0.010668 -0.053483 0.0370834318 Multiple R 0.295718 Jul-00 0.261025 0.003979 0.060699 0.1067972212 R Square 0.087449 Jun-00 -0.090722 0.026144 -0.016341 -0.0206028292 Adjusted R 0.071715 May-00 -0.126912 -0.035107 0.023934 -0.0718291686 Standard E 0.203067
This is the end of the preview. Sign up to access the rest of the document.

## ProjTeam1 - Fin500 Advanced Topics in Finance Excel...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online