An event study is an empirical analysis that examines the impact of a particular event on the value of a subject. This task employs this method to assess the effect of earnings announcements on returns of a portfolio of 10 stocks. Returns data of each stock is provided with one announcement date in October 1998. In particular, you are required to compute the cumulative abnormal returns (CARit) of each stock given its announcement date and the cross-sectional average of the cumulative abnormal return (CARt) of the portfolio. There are a number of models and methods to determine the expected returns and undertake such event study. Below are specific instructions for you to complete the task. First, for each announcement date, t, estimate the expected returns rit and the abnormal returns it by using the market model (1), assuming that those assumptions under the market model are satisfied. rit = i + irmt + it (1) i and i are obtained by running the regression model (1) using stock returns during the estimation period for that announcement date. Second, the announcement date is defined as day 0. The estimation period goes from day t = 110 to day t = 11, while the event window goes from day t = 1 to day t = 10. 1 Third, the cumulative abnormal returns (CARit) of each stock on its announcement date are computed as follows: CARit = X t =1 i (2) Last, the cross-sectional average of the cumulative abnormal return (CARt) of the portfolio is computed by the formula: CARt = 1 10 X 10 i=1 CARit (3) More information about event studies can be found here: Eckbo, B. E. (Ed.). (2008). Handbook of empirical corporate finance set. Elsevier. Chapter 1 - Econometrics of Event Studies. MacKinlay, A. C. (1997). Event studies in economics and finance. Journal of economic literature, 35(1), 13-39. 2 Spreadsheet Guidance You are provided the daily returns of 10 stocks for 134 days from 7 May 1998 to 13 November 1998 in the spreadsheet named PEAD. This sheet (only) will be marked. With the given (stock and market) returns data and announcement dates, two functions have been (poorly) written in the Module1, following the instructions in the Background session, to compute the (CARit) of each stock and the (CARt) of the portfolio. Therefore, after fixing the provided code for the two functions, you will need to call the function to compute the (CARit) of each stock in the first 10 green cells and call the function to compute the (CARt) of the portfolio in the last green cell as shown in the attached picture below. 2 The function to compute the (CARit) of each stock in the first 10 green cells will have the form as: CAR(StockRet, M arketRet, Eventdate, Dates) where StockRet is the one-dimensional array storing 134 returns of each stock and already labelled in the Spreadsheet by the stock ticker, for example BES, BUR, FOS, HAL, etc. MarketRet is the one-dimensional array storing all the market returns for given 134 days and already labelled in the Spreadsheet as mrktret. Eventdate is the event date of each stock and given in the column before the green cells as in above picture. The event date for each stock is labelled as Eventdate BES, Eventdate BUR, Eventdate FOS, etc. Dates is the one-dimensional array storing all the available (134) dates including the estimation period, event window, and days outside these two periods, and already labelled as dates in the Spreadsheet. mrktret and dates are the same for all stocks. For example, the function will be called to compute the (CARit) of stock BES is: CAR(BES, mrktret, Eventdate BES, dates) and the function to compute the (CARt) of the portfolio in the last green cell will have the form: 3 PortfolioCAR(AllCAR) where AllCAR is the area storing all the CAR of 10 stocks and labelled as AllCAR. Note: The addition sheet named Sample provides the same set of data for you to practise. This sheet will not be graded. You may use this sheet to debug the functions. A VBA subroutine is attached to this sheet which solves a similar question in the Spreadsheet PEAD. If you successfully debug the subroutine (get the data to draw the graph attached in that sheet), you will get 90% of the logic to debug the functions. Please note that the graph shows the changes in AAR and CAAR, not (CARit) and (CARt ), but these terms are all relevant and share a large part of the study