Please, help me revise one task on finance. I already solved it. The task and my solution are in the document, as well as professor's comments.

Problem 4 (50 marks) Using the Yahoo! Finance website, search the Bank of Nova Scotia (BNS.TO) by finding its stock symbol. If you are unable to locate the prices for BNS.TO, use prices for BNS (the Bank of Nova Scotia observed in US dollars at the New York Stock Exchange). For the purpose of this question, assume that the Canadian dollar and the US dollar had been exchanged one for one. Find historical prices for the stock (on the lefthand menu) and complete the following: 1. Download historical data for the stock prices (adj. close) from January 1, 2004 through January 1, 2012, on a monthly basis. You will also need to download corresponding monthly prices for the S&P/TSX Comp index (also available on the Yahoo! Finance site) as well as 3-month T-Bill rates (download this attachment: T-Bill Rates.xlsx). 2. Calculate returns for both series of prices downloaded from Yahoo site (BNS and S&P /TSX Comp Index). Prior to that, make sure the data is sorted in ascending order (i.e., first row has the oldest data). The final spreadsheet should have the two series of returns you downloaded and calculated from Yahoo! Finance. Make sure all data is expressed in same units. 3. Using the Tools menu in EXCEL, (Tool Pack has to be installed if EXCEL does not show it) perform regression analyses using the Market Model for BNS. 4. Clearly provide the regression results in a table with an explanation for the coefficients obtained, and clear interpretation. Specifically, for each regression provide: Dependent Variable Independent Variable Intercept Beta Value Firm Specific Risk i. How well does the S&P/TSX Comp Index movement explain the variability of the return on BNS stock? ii. What is the alpha of the BNS stock? iii. Calculate the standard deviation of the stock return (using the equation for R 2 =2M2/2, and the individual regression results). iv. Calculate systematic risk and firm specific risk for the stock. Solution: The regression results: The dependent variable is the return on BNS.TO The independent variable is the return on GSPTSE Looking at the regression P-values, we see that the return on GSPTSE is a significant predictor of the return on BNS.TO, since the P-value0.05, suggesting that the constant term (alpha) is not a significant predictor of the returns in BNS.TO The intercept in the alpha = 0.00552. This represents an excess return above the normal or average return that will be expected from an unskilled investor. Another way of looking at this is that when the return on GSPTSE is zero, the return on BNS.TO will be 0.00552=0.552%. Beta Value r i= i + ir M +e i BNS.TO=0.7109*GSPTSE+0.00552 Comparing the above two equations, we see that: =0.7109 One interpretation of beta is that when the return on GSPTSE increases by 1 unit (percent), we can expect the return on BNS.TO to increase by 0.7109 units. Beta is a measure of volatility. Stock with a beta=1 has the same volatility as the market, while stocks with beta>1 are more volatile than the market. Beta reflects the sensitivity of an asset's price to changes in the value of the market portfolio. Systematic risks arise through market-wide events, such as unexpected changes in real economic activity or investor sentiment regarding asset values. In the CAPM, as asset's beta measured against the market portfolio is the only measure of systematic risk. Firm Specific Risk The residuals from the regression represent the firm specific risk. The expected value of the residuals from the regression is zero. The residual ei captures all variation in ri that is unrelated to the market portfolio; that is, that portion of individual security risk that can be diversified away by holding the security in a portfolio with many securities. The residual return e i then measures risk-adjusted performance over the period. Because E[e i]=0, abnormal investment performance is measured by the difference of ei from zero. i. The R-squared value of 37.9% suggests the about 37.9% of the variation in BNS stock returns is explained by the variation in S&P/TSX returns. This is not a very high value, so the movement in S&P/TSX Comp Index is not a very strong predictor of BNS stock returns. The correlation coefficient between BNS.TO and GSPTSE is below. The correlation coefficient of 0.615, while positive, is not very strong (close to 1). The R-squared is the square of the correlation coefficient, and it is only 37.9%. Therefore the models prediction capability is not very strong. ii. r i= i + ir M +e i BNS.TO=0.7109*GSPTSE+0.00552 Comparing the above two equations, we see that: =0.00552=0.552 BNS . 2 =R 2 GSPTSE 2 iii. 2 BNS . 0.71092 =0.37854 2 2 =0.050062 0.0433272 BNS . =5.01 iv. Total Variance = Explained Variance + Unexplained Variance Total Risk = Systematic Risk + Unsystematic Risk 2 2 2 2 ( r j )= j ( r M ) + ( j) Systematic Risk =2j 2M Systematic Risk = 0.710922*0.0433272=0.0949% Total R isk= 2 ( r j )= 2j 2 ( r M ) + 2 ( j) BNS . 2 Total Risk= 2 =0.005062 =0.002506 Firm Specific Risk = Total - Systematic Risk = 0.002506-0.000949=0.001558 Comments, according to which the task should be revised: Problem 4 Part 1: you need to supply the data Problem 4 Part 4: Systematic risk for the stock Systematic risk: Variance = 2M2 Firm Specific Risk: standard error from regression output and covert it to an annual value. What is the alpha of the BNS stock: R(BNS) mean - [T-bill mean return converted to decimal / 12 + Beta (Mean Return (S&P/TSX)- T-bill mean return converted to decimal /12))