Event Study Using Excel and Capital IQ



in this video I'm going to explain and demonstrate how we would conduct an event study and then we're going to use an event for Microsoft several years ago to see how they work and how we would download the information and use that information to make decisions so what is an event study an event study is a test that attempts to measure the valuation effects of a corporate event such as a merger or earnings announcement by examining the response of the stock price around the announcement of the event the main requirement of event studies is that the stock market is efficient that is that stock prices react quickly and accurately to new information event studies are kind of seen as a joint test of both market efficiency and whether or not an event actually impacted the value of a firm so what are they used for we use event studies for determining how a proposed policy acquisition etc would likely affect the value of the firm by examining similar events in the past so for example if we wanted to determine how an acquisition might impact the the value the stock value of the acquire and the acquired firm we can look at historically what happened with similar acquisitions in the past we can also evaluate how a previous event affected the value of the firm the timeline of an event study looks like this the interval t0 to t1 is the estimation period for us this is going to be about a year the interval t1 to t2 is the event window and this is a six seven day period sometimes a little bit more than that around the actual event date and then finally t2 to t3 is the post-event window and this can actually be over several years so the estimation window is used to determine the normal behavior of the stock market factors most often we use the market model and the market model is that it's the returns for the firm are equal to some intercept plus the beta the relationship between the returns for the firm and the returns for the market to determine the normal behavior we use that information then to determine as we'll talk about soon abnormal returns for the event window we use the data from this time period in conjunction with the intercept or the Alpha and the beta of the stock or stocks to determine whether the event announcement was anticipated early so we'll look a few days beforehand and the post announcement affect how long it took for the event information to be absorbed by the market sometimes we'll even see a reversal where we had on one day a significant response in the stock market to an event but then it turns around a day or two later as market participants realize that they overreacted finally we have a post event window and that's used in this gate longer-term company performance following the event so the regression of the market model looks like this we have a single factor model that says the returns for the firm are equal to some intercept plus the beta multiplied by the returns for the market we can use that to estimate the abnormal returns and the cumulative normal returns around the event date the abnormal return is the difference between the firm's actual return and the predicted return on a specific date it's computed using the following equation the abnormal return is equal to the return on a specific date – and then we have our intercept plus our beta x re terms for the market the cumulative abnormal return is the measure of the total abnormal returns during the event period it is calculated as the sum of the abnormal returns during the event period so will gets a bunch of numbers and we need to determine whether or not those returns around the event date are actually statistically significant significant or if they're just due to random error so we'll test to see if the ars are statistically significantly different from zero that is do the ARS indicate that the event had a real effect on the price of the firm stock the test statistic is the abnormal return divided by the standard error of the predicted what Y value for each X in Excel the denominator that's the standard error is calculated using the ste YX function if the absolute value of the test statistic is greater than 1.96 so it could be greater than positive 1.96 or less than negative 1.96 then the AR is statistically significant at the 5% level so what that means but the term statistically significant at the 5% level means that there is a PI percent or less chance that that test statistic is due to random error so there's a 1 in 20 shot that on that date that the test statistic or the the abnormal return was due to randomness and a 95% chance that it was due to probably the event that happened so that's kind of our explanation of it now what I want to do is take a look at an actual event it's a little bit dated but it's what I like to talk about because it has implications for how companies make decisions so on January 16 2003 Microsoft was the first major technology internet or computer firm to announce the initiation of a regular dividend the announcement of a dividend increase usually receives a positive reaction in the marketplace so stock returns go up as it communicates increasingly stable income to investors likewise the announcement of a dividend decrease usually results in a significant negative return on the date of the announcement what I don't have in the year is that a dividend initiation can have a mixed response sometimes it's positive response sometimes it's in a negative response a little bit more information about this situation was that at this time Microsoft had a lot of cash and a lot of liquid assets their cash in near cash assets or about we're worth about seventy six billion dollars so there was some pressure from some investors to start getting rid of some of that cash so that's we're going to look at today is how did investors react to this information so our event date is our announcement date and that was January 16th 2003 our event period was two days before that because that information could have gotten out a few days early through January 21st 2003 the dates are five days apart because there was a weekend in there so that that's why there's a week-long event period the estimation period is for the previous year January 15th 2002 through January 13 2003 so now what I'm going to do is open up Capital IQ I'll show you in Capital IQ how to download the information so I'm here Capital IQ and I want to look up first Microsoft so I can type in your MSFT that's their ticker symbol and I'm in Chrome right now in the other browsers Internet Explorer and Firefox this is going to look a little bit different but I'll explain to you how you can get to where I'm at so if you click on sorry click on chart builder you're going to get the chart if you're not using Chrome it's going to look a little slicker here and I'll show you I'll explain to you what's going on Capital IQ uses Microsoft Silverlight to generate the new charts that are out there this is the classic view but in those other browsers you can actually choose Classic View or you don't have to the setup is pretty similar between the two let me demonstrate here all right so now I'm in Internet Explorer I go to chart builder and my chart looks a little bit different I can go to the classic chart builder it's right here pull that up and so this is identical to what is available in Chrome I prefer this one you don't have to use this one you can use the other one we're just using this to get the information for the for the company so I'm going to go to custom because I want a specific selection of dates so my my first dates gonna be January 15th 2002 so I'm gonna select from 115 2002 and I want to go through the end of the event period the end of the vid period is January 21st 2003 I have daily I want daily everything else looks good I'm going to update the chart so we can see what's going on here and then I'm going to download this information by selecting the Excel icon there and it downloads my chart my Excel chart looks like this enable the editing you can see the first tab is just the charts we actually don't need those take that out the next table is worksheet is what we needed to chart data it's the prices and the dates and then the last one is the volume on different dates we don't need to see there so we'll take that out what I'm going to use for the market return is the S&P 500 and you need to look this up separately if you put them on the same chart what you'll end up downloading is a percentage change in both of their prices from the first date that you have on the chart till whatever date you're looking at so it's it's not very useful so I'm going to put SP 500 in here it's going to give me the ticker and up up carrot or an uptick SPX so I'm going to select that and it gives me the message here again again if you're looking if you're an explorer or firefox you can select select the classic view and you'll get to where I'm at select custom again we're gonna go from January 15th 2002 to January 21st 2003 again I have daily frequency gonna update my chart I'm gonna download it now again by selecting the Excel icon its chart 23 in this case it's it's important that you get that you select both so I'm gonna go to the second worksheet here select both the pricing date and the share pricing because we're comparing an index to an individual firm that dates don't always align because as we see here we see the value of the index on Christmas Day Microsoft wasn't trading in the markets on Christmas Day so we're gonna have to make a little adjustment here so I'm gonna copy and paste this information and our dates aren't going to align again if you look right here we have January 21st 2002 that was Martin Luther King Day and the Microsoft wasn't traded on the Nasdaq on that day because the Nasdaq wasn't trading on that day so what I'm going to do is put a function in here to tell me if the two dates match so I type in equals if the date for Microsoft equals the date for the S&P 500 then give me a zero I don't need to do anything if it's not true if they're different give me a one and it alerts me that I need to fix something so those match and what I'm gonna do is just pull this all the way down for all my dates see you can see here there's several dates that the S&P 500 traded or there we have a value for the index that Microsoft was not traded so our first date here is January 21st that would have been Martin Luther King Day in 2002 delete that shift the cells up and then I'm gonna pull this down again our next state would have been February 18 2002 I'm gonna guess that was Presidents Day you can see the value of the index didn't actually change on those days so I'm gonna delete that again I'm gonna grab here where the last year was and pull it back down and I'm doing that because when we pull the dates up it changes what's in the cell and so what I'll show it doesn't match anyway this would have been Memorial Day 2002 and again we see that I'm sorry not Memorial Day probably Easter we see it again there's no change in the index but it's in the information so we have to take it out and I'm going down and taking that out May 27th that would be Memorial Day delete that my cells up our next date is July 4th shift cells up I was down our next date is Labor Day 2002 shift the cells up take out that the next one would be Thanksgiving 2002 the next day would be Christmas 2002 almost there the next state would be New Year's Day and then finally our last state is going to be Martin Luther King Day 2003 so now I've got everything aligned all my dates match up I can take out this row or excuse me this column the pricing date for the S&P 500 and take out this for this column now I can calculate my returns but what I want to do first I want to sort these where the most recent date is up here because that's what we're interested in we're interested in that event that event window so I'm going to highlight everything by selecting the corner here and then going to data and then sort and my data has headers so select that sort by the pricing date newest to oldest and then it's going to give me the most recent date that we're looking at and here's our event date is January 16th so now we're going to calculate the returns for both so Microsoft the SNP but I'm just gonna put the market yeah our return for January 21st is equal to the price at the end of the day on January 21st minus the price the day before divided by the price the day before and then we want to make that look nice by making it a percent and drag that over here pull that down not to the last row here because it would just be an error term now I'm going to highlight my event period so my event period was is January 14th through January 21st so that's what I'm interested in I need to select this out so I don't include it in my estimation period I'm gonna pull that out like this highlight it so I don't forget now what I want to do is calculate my Interceptor my alpha my beta the standard error s tyx the standard error and then I also include the r-squared just to get an idea of the explanatory value of the market model in this case so I'm going to highlight these returns notice I don't include the ones in yellow just the ones below it I'm going to name this column MSFT you can name it whatever you want again I'm gonna do the same thing here I'm gonna highlight that then you call that the market so you can run the regression using the data analysis but it's actually a little more helpful to put it on the page here the way you would calculate the intercept in this case will be equals intercept our Y so our dependent variable is going to be the return for Microsoft so that's MSFT and then the independent variable is going to be the market return and put in market and close parentheses and it tells me the Alpha is point zero zero zero four for my beta this is going to be my slope equals slope are known wise and our Microsoft returns are known X's are the market returns so it's one point two eight one eight eight five the standard error is ste Y X our known Y's are Microsoft our known X's are the market and then our our square equals RSQ Microsoft market and it's actually pretty high for an individual stock the model explains sixty-two point four percent of the variation of the variability of Microsoft stock so I have that information now now I can calculate my abnormal return for each day I'm going to highlight this and name it alpha and then I'm going to highlight or select the next cell down and I'm going to name that Vega so my abnormal return equation says that the individual return on the day – the alpha plus the market return multiplied by the beta is the abnormal return and I'm gonna pull that down we can see that so far there seems to be something going on here the actual return for Microsoft on the 17th was negative 7.0 3% a portion of that was explained by the fact that the market return also went down on that day so the abnormal return that the part of the return that was not due to the market was five point negative five point two eight percent so the event date was January 16th however the announcement came out after the end of the close of the market on that day so my abnormal return is in the first column there the second column I'm gonna put in my test statistic and the test statistic was equal to the abnormal return divided by the standard error and I'll just log in the standard error by pressing I'm gonna press the key f4 you can see the dollar signs in front of the agent in front of the the twelve and that just locks it in for when I want to pull that down here so you can see that it stays the abnormal return changes in each of the in each of the cells but the test the standard error does not change my next column I'm just going to say is this significant question mark so we're testing to see if it's significant remember the rule was 1.96 what I'm going to put in here is equals if the absolute value of the test statistic is greater than 1.96 , well then yea it's statistically significant we want to say yes if it's not it's not statistically significant at the 5% level so we're gonna say no and so in the first case it's not statistics statistically significant on the 21st but it is on the 17th in fact it's the abnormal return is is quite statistically significant beyond that 5% point and then finally we're going to calculate the cumulative abnormal return and that's calculated as being equal to the sum of the abnormal returns during the event period or the event window so I'm just adding each day the abnormal return during the period and so we end up with a cumulative abnormal return during the period of negative three point nine two percent and really the effect we saw here but a little bit of it may be a turn around here but it not statistically significant a five point six four percent excuse me five point two eight percent for the add no more in turn a really kind of on the event date so you might be asking yourself well why was the return negative and that's because at the time it was 2003 beginning of 2003 we were not too long in the past had gone through the big bubble burst for the internet and computer companies and people who had been invested in Microsoft had really invested into a gross company a company that was making a lot of profit and gaining market share in a growing market and whenever they announced a dividend payment what they were signaling to the market was that their amount of good investments were going away they didn't have as many good investments they were not going to be growing as fast as they had in the past so they couldn't justify keeping all this money all this cash on hand so they started paying it out in fact they they paid out a one-time a fairly large one-time dividend around the same time so I hope this has been useful for you please let me know if you have any questions

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *