Event Studies in Excel

For conducting event studies with Excel, you may refer to Simon Benninga's book Financial Modeling. Further, the following zip-archive entails a workbook that illustrates how event studies can be implemented in Excel. Once you open the zip-archive above, you will find a step-by-step example of how event studies are executed in Excel. The following is a brief overview of the different steps implied.

1. Calculate the returns of the firm's stock, as well as the returns of the reference index.
2. Match these two time series of returns together.
3. For each event, identify the sequences of firm and market returns you want to be included in the estimation window.
4. Next, calculate the alpha, beta and sigma coefficients (for each event) using the Excel formulas intercept, slope, and steyx respectively.
5. Based on the actual market returns on the event date and the other dates in the event window, use the alpha and beta value of the event to calculate expected returns throughout the event window. These returns represent the hypothetical returns one would expect had the event not have taken place.
6. By deducting these expected returns from the actual returns of the firm's stock throughout the event window, you will receive the abnormal returns.
7. Dividing the abnormal returns through the root mean square error (i.e., the Steyx-value) will yield the t-values you need for significance testing.

For a hands-on example of what these steps imply, you may want to watch this excellent (third party) YouTube-video:

As the video implicitly conveys, event studies with larger numbers of events (or firms and indices) are very tedious to implement in Excel. While you may automatize some parts of the process (i.e., the re-location of the estimation and event windows) using the Excel formulas countif and offset and iterate through your events by means of VBA, larger analyses require significant time - also for the recalculation of formulas Excel needs to perform per iteration (you have to assign some idle/sleep-time for this recalculation step in your macro to prevent errors). For initial guidance on how to use these formulas, please kindly refer to Benninga (2008).

References

Benninga, S. 2008. Financial modeling (3 ed.). Boston, MA: MIT Press.