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 left-hand menu) and complete the following:

• 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). 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. 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.
• 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 How well does the S&P/TSX Comp Index movement explain the variability of the return on BNS stock? What is the alpha of the BNS stock? Calculate the standard deviation of the stock return (using the equation for R

² =²_M²/²,and the individual regression results). Calculate systematic risk and firm specific risk for the stock