Suppose a portfolio consists of 50% of stock Do and 50% of stock GDT, calculate the beta of this portfolio (use stock betas estimated from 5 to compute the portfolio's beta (1 point). What is the expected return on this portfolio (assume risk-free rate and S&P 500 index return are the same as in Question 6) (1 point)

The attached file has the data sets.

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 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 relative to the S&P500 index. Interpret and discuss the betas (3 points the relationship between portfolio's beta and its' component stocks' betas? ( 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. find standard deviation, R-squre, t-stat, p-value etc. Date Nov-00 Oct-00 Sep-00 Aug-00 Jul-00 Jun-00 May-00 Apr-00 Mar-00 Feb-00 Jan-00 Dec-99 Nov-99 Oct-99 Sep-99 Aug-99 Jul-99 Jun-99 May-99 Apr-99 Mar-99 Feb-99 Jan-99 Dec-98 Nov-98 Oct-98 Sep-98 Aug-98 Jul-98 Jun-98 May-98 Apr-98 Mar-98 Feb-98 Jan-98 Dec-97 Nov-97 Oct-97 Sep-97 Aug-97 Jul-97 Jun-97 May-97 Apr-97 Mar-97 Feb-97 Jan-97 DO 0.180124 -0.308548 -0.246940 0.076707 0.261025 -0.090722 -0.126912 0.026446 0.217659 0.381892 0.047537 -0.075729 -0.047519 -0.093551 -0.183478 0.035947 0.312163 0.171960 -0.172018 -0.128737 0.395241 0.416916 -0.124926 0.036883 -0.314610 -0.120667 0.381470 -0.323356 -0.587637 -0.368938 -0.215687 0.050444 0.103415 0.019039 -0.043273 -0.109091 -0.283208 -0.084322 0.139462 0.156751 0.337769 0.285986 0.189807 0.040777 0.083942 0.040510 0.024572 GDT 0.133824 -0.019563 0.049243 0.010668 0.003979 0.026144 -0.035107 -0.006017 0.010444 0.061934 0.058743 0.257034 -0.061193 0.125001 -0.028741 -0.090689 0.085427 -0.074638 0.097996 -0.013032 0.001812 0.017699 -0.113726 0.073745 0.098428 0.040384 0.122032 0.128953 -0.170534 -0.028433 0.147239 0.049526 0.022709 -0.023531 0.040393 -0.026312 0.099476 0.097703 -0.075917 0.042877 0.035814 0.078625 0.094593 0.068592 0.114164 -0.038609 -0.054743 S&P 500 0.004053 -0.080069 -0.004949 -0.053483 0.060699 -0.016341 0.023934 -0.021915 -0.030796 0.096720 -0.020108 -0.050904 0.057844 0.019062 0.062539 -0.028552 -0.006254 -0.032046 0.054438 -0.024970 0.037944 0.038794 -0.032283 0.041009 0.056375 0.059126 0.080294 0.062396 -0.145797 -0.011615 0.039438 -0.018826 0.009076 0.049946 0.070449 0.010150 0.015732 0.044587 -0.034478 0.053154 -0.057466 0.078146 0.043453 0.058577 0.058406 -0.042614 0.005928 portfolio 0.1523439514 -0.135157071 -0.069230058 0.0370834318 0.1067972212 -0.020602829 -0.071829169 0.0069684641 0.0933298219 0.1899170203 0.0542606567 0.1239288948 -0.055723296 0.0375806637 -0.090635416 -0.040034623 0.1761213164 0.0240010506 -0.010009595 -0.059313854 0.1591836029 0.1773861611 -0.1182061 0.0590002933 -0.066787266 -0.024036526 0.2258070464 -0.051970742 -0.337375101 -0.16463489 0.002068397 0.0498932279 0.0549917435 -0.006503353 0.0069270114 -0.059423526 -0.053597822 0.0248928278 0.010234929 0.0884261422 0.1565960411 0.1615691222 0.13267872 0.0574662418 0.1020753354 -0.006961296 -0.023017105 1) DO vs S&P 500 SUMMARY OUTPUT Regression Statistics Multiple R R Square Adjusted R Standard E Observatio ANOVA Regressio Residual Total Coefficients Intercept X Variable 2 ) GTD vs S&P 500 SUMMARY OUTPUT Regression Statistics Multiple R R Square Adjusted R Standard E Observatio ANOVA Regressio Residual Total Coefficients Intercept X Variable 3)Portfolio vs S&P 500 SUMMARY OUTPUT Regression Statistics Multiple R R Square Dec-96 Nov-96 Oct-96 Sep-96 Aug-96 Jul-96 Jun-96 May-96 Apr-96 Mar-96 Feb-96 Jan-96 Dec-95 mean standard deviation correlation coefficient (DO&GDT) 0.041665 0.008736 0.061317 -0.060782 0.026415 -0.021505 0.143740 0.029128 0.073376 0.179546 0.005415 0.026101 0.151960 0.233169 0.054203 -0.126987 0.097625 0.018814 -0.010966 -0.152583 -0.045748 0.151440 -0.004440 0.002257 0.100501 0.058823 0.022853 0.306118 -0.108031 0.013431 0.129693 0.039220 0.007917 0.075413 0.004377 0.006934 0.274073 0.023648 0.032617 0.028666 0.028265 0.013856 0.2107656071 0.0813144939 0.0463237032 0.0862429326 0.021907705 -0.008463955 0.0749724957 0.0750674795 0.2006857301 0.0077805194 -0.095935949 0.0579120347 0.0754939861 0.0576285434 0.0754093259 0.032791158 0.1238181948 0.028426 0.1009819137 Adjusted R Standard E Observatio ANOVA Regressio Residual Total Coefficients Intercept X Variable 1) DO vs S&P 500 SUMMARY OUTPUT Regression Statistics 0.295718 0.087449 0.071715 0.203067 60 df SS MS F Significance F 1 0.229196 0.229196 5.558092 0.02179 58 2.391711 0.041236 59 2.620906 Coefficients Standard Error t Stat P-value Lower 95%Upper 95% Lower 95.0% Upper 95.0% 0.010023 0.027383 0.366051 0.715659 -0.04479 0.064836 -0.04479 0.064836 1.34547 0.570704 2.357561 0.02179 0.203081 2.487858 0.203081 2.487858 DO regression 2 ) GTD vs S&P 500 SUMMARY OUTPUT Regression Statistics 0.432348 0.186924 0.172906 0.073951 60 df SS MS F Significance F 1 0.072921 0.072921 13.33408 0.000561 58 0.31719 0.005469 59 0.390111 Coefficients Standard Error t Stat P-value Lower 95%Upper 95% Lower 95.0% Upper 95.0% 0.01775 0.009972 1.779949 0.080323 -0.00221 0.037711 -0.00221 0.037711 0.758923 0.207834 3.651586 0.000561 0.342898 1.174947 0.342898 1.174947 GDT REGRESSION 3)Portfolio vs S&P 500 SUMMARY OUTPUT Regression Statistics 0.45577 0.207726 0.194066 0.090655 60 df SS MS F Significance F 1 0.124977 0.124977 15.20703 0.000253 58 0.476666 0.008218 59 0.601643 Coefficients Standard Error t Stat P-value Lower 95%Upper 95% Lower 95.0% Upper 95.0% 0.014659 0.012224 1.199168 0.235339 -0.00981 0.039129 -0.00981 0.039129 0.993542 0.254779 3.899619 0.000253 0.483546 1.503538 0.483546 1.503538 1. Systematic risk vs Nonsystematic risk Systematic risk is the risk associated with the market, so is also called market risk or non-diversified risk .It is the risk which is due to the factors which are beyond the control of the people working in the market. This risk will affect to the entire market. This risk will not be avieded; interest rate, recession, war are the examples of systematic risk. Nonsystematic risk is associated with one firm or particular industry. This risk is controllable by the people working in market. By contrast of systmatic risk, nonsystematic risk can be elimated by diversfication. The examples are poor earing, strike amongst a company's employees. 2. Mean and standard deviation DO Mean 2.87% GDT 2.83% Portfolio 2.84% S&P 500 1.39% standard deviation 21.08% 8.13% 10.10% 4.63% High volatility in historical returns is an indication of future return volatility. The higher the standard deviation, the greater the volatility in the stock Portfolio has less risky than DO, and higher risk than GDT and S&P 500. In other word, Portfolio has more return than S & P 500 and GDT. This is based on the standard deviation percentage. The higher the standard deviation, the more volatile the historical returns. The more the return the more risky the stock tends to be. Also S&P 500 risk is lower due to more diversification (500 stocks) compared to portfolio (2 stocks). Whereas DO has more return than portfolio and any other stocks and is most risk due to highest standard deviation. Also optimal diversification lead to higher return and less overall risk and that is the reason individual DO stock is more riskier than the index. , 3. Corelation coefficient Correlation refers to the way stock prices move in relation to each other. Correlation coefficient determines the degree to which two stock movements are associated. Correlation coefficient is used to determine how much diversification and thus risk reduction one can achieve by combining the stocks. For example, if the numbers are stock prices, and the price of one stock goes up at the same time the price of another stock goes up, the two stock prices are positively correlated. If the price of one stock drops when the price of the other goes up, the two stock prices are negatively correlated. If there is no consistent pattern in the variation of the two stock prices, they are uncorrelated. If two stock prices have perfect positive correlation, their correlation coefficient will have the value of +1. If they have perfect negative correlation, their correlation coefficient is -1. Otherwise, their correlation coefficient will have a value between -1 and +1. If the two stock prices do not vary together in a consistent manner, their correlation coefficient will have a value close to zero. corelation coefficient DO &GDT 0.0862429326 This value being close to 0 means that the movement of these two returns over time are unrelated to one another. 4. Regression analysis DO vs S&P 500 Beta GDT S&P 500 Portfolio S&P 500 1.3454697 0.758922934 0.9935416 The beta of the overall market is 1; therefore, stocks with a beta value greater than 1 are considered riskier or aggressive. Stocks with betas less than 1 are considered less risky and are called defensive stocks. So, investors will demand a higher return to invest in a stock with a high beta. According to regression analysis, DO stock is considered risker than the market protfolio because its beta is greater than 1 which suggests that it has high market risk and is sensitive to overall economies strength. Its beta means 35% more sensitive than the overall market. Contrary, GDT is less risky than market portfolio since it has less than 1 of the beta. This suggests that it has low market risk and the demand for the products or services of GDT is not as sensitive to economic conditions. Portfolio beta is close to 1 which indicates that it has the same market risk as the market portfolio. The relationship between portfolio's beta and its components stock betas is that portfolio beta is the weighed average of the GDT and DO stock's betas. DO beta is risky and adding GDT to the portfolio has decreased the total market risk. Once all S & P 500 stocks have been added the portfolio beta will reach 1. 5. Required return on stock DO, GDT and Portfolio risk free rate 0.05 Required return S&P index return 0.1 DO GDT Portfolio 11.73% 8.79% 9.97% Solution Beta of portfolio = W1B1+W2B2 Where; W1 is the weight of investment in DO W2 is the weight of investment in GDT B1 is Beta of DO B2 is Beta of GDT W1 W2 B1 B2 Beta of portfolio = 50% 50% 1.34547 0.758923 1.052196 Expected return of portfolio = W1R1+W2R2 Where; W1 is the weight of investment in DO W2 is the weight of investment in GDT R1 is expected return of DO R2 is the expected return of GDT W1 W2 R1 R2 Beta of portfolio = 50% 50% 11.73% 8.79% 0.1026