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 ToolsData AnalysisRegression to do regression analysis in order to find beta.
Do not use slope function to find beta. I need to see regression results including Rsqure, tstat, pvalue 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 DocumentDate
DO
GDT
portfolio
Nov00
0.180124
0.133824
0.004053
0.1523439514
SUMMARY OUTPUT
Oct00
0.308548
0.019563
0.080069 0.1351570708
Sep00
0.246940
0.049243
0.004949 0.0692300582
Regression Statistics
Aug00
0.076707
0.010668
0.053483
0.0370834318
Multiple R
0.295718
Jul00
0.261025
0.003979
0.060699
0.1067972212
R Square
0.087449
Jun00
0.090722
0.026144
0.016341 0.0206028292
Adjusted R
0.071715
May00
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.
 Spring '10
 YI
 Finance

Click to edit the document details