Excel详解事件分析法fm3-chapter14

T0T1T1+10StartdateforestimationwindowEnddateforestimationwindowStartdateforeventwindowEventdateTHEEVENTSTUDYTIMELINEESTIMATIONWINDOWTheestimationwindowisusedtodeterminethenormalbehaviorofthestockwrtmarketfactors.MostoftenweusetheregressionRit=a+bRmttodeterminethisnormalbehavior.EVENTWINDOWWeusedatafromthiswindow,inconjunctionwiththeaandbofthestockorstockstodeterminewhether:i)Theeventannouncementwasanticipatedorleaked.ii)Thepost-announcementeffect:Howlongittookfortheeventinformationtobeabsorbedbythemarket.T2T2+1T3EnddateforeventwindowStartdateforpost-eventwindowEnddateforpost-eventwindowTHEEVENTSTUDYTIMELINEWeusedatafromthiswindow,inconjunctionofthestockorstockstoi)Theeventannouncementwasanticipatedorannouncementeffect:HowlongittookfortheeventinformationtobeabsorbedPOST-EVENTWINDOWUsedtoinvestigatelonger-termcompanyperformancefollowingtheevent.DRAFT:2019/11/14PrivilegedandConfidential:AttorneyWorkProduct$30$35$40$45$50$55$602004/6/302004/8/232004/10/172004/12/112005/2/42005/3/312005/5/252005/7/192005/9/122005/11/62005/12/31StockPriceGilletteCompanyandProctor&GambleClosingStockPrice6/30/04–12/31/05Procter&GambleGilletteCompany1/28/05AnnouncementDate10/3/05CompletionDate123456789101112131415258259260261262263264265266267268269270271272273274275276277278279280281282283284ABCDEFIntercept0.0007--=INTERCEPT($C$11:$C$262,$B$11:$B$262)Slope0.6364--=SLOPE($C$11:$C$262,$B$11:$B$262)R-squared0.1315--=RSQ($C$11:$C$262,$B$11:$B$262)Steyx0.0113--=STEYX($C$11:$C$262,$B$11:$B$262)Daysinestimationwindow252--=COUNT(A11:A262)DateNYSEGilletteExpectedreturnAbnormalreturn(AR)Cumulativeabnormalreturn(CAR)27-Jan-04-0.48%-0.42%28-Jan-04-1.26%-1.27%29-Jan-040.00%-0.94%30-Jan-04-0.06%-1.39%2-Feb-040.26%-0.74%19-Jan-05-0.78%-0.09%20-Jan-05-0.69%-0.56%21-Jan-05-0.20%-1.50%24-Jan-05-0.18%0.57%25-Jan-050.21%1.44%26-Jan-050.68%0.07%0.50%-0.44%-0.44%27-Jan-050.04%1.89%0.09%1.80%1.36%28-Jan-05-0.24%12.94%-0.09%13.03%14.39%31-Jan-050.82%-1.71%0.59%-2.30%12.09%1-Feb-050.80%-0.83%0.57%-1.40%10.69%2-Feb-050.32%0.80%0.27%0.52%11.21%3-Feb-05-0.29%-0.59%4-Feb-050.97%-1.29%7-Feb-05-0.23%-0.38%8-Feb-050.09%-0.75%9-Feb-05-0.63%-1.20%10-Feb-050.68%0.37%11-Feb-050.71%1.58%14-Feb-050.26%0.67%15-Feb-050.32%1.68%16-Feb-050.04%-0.41%17-Feb-05-0.47%0.18%18-Feb-050.21%0.10%22-Feb-05-1.05%-1.82%23-Feb-050.45%2.05%24-Feb-050.55%0.81%25-Feb-051.08%0.57%EVENTWINDOWCellD263containsformula=$B$2+$B$3*B263CellE263containsformula=C263-D263GILLETTERETURNS:ESTIMATIONWINDOWANDEVENTWINDOW4%6%8%10%12%14%16%ExpectedreturnAbnormalreturn(AR)Cumulativeabnormalreturn(CAR)285286287288289290291292293294295296297298299300301302303304305ABCDEF28-Feb-05-0.55%-2.33%1-Mar-050.41%1.19%x-axisforchart26jan0527jan0528jan0531jan051feb052feb05-4%-2%0%2%4%26jan0527jan05123456789101112131415258259260261262263264265266267268269270271272273274275276277278279280281282283284GHIJKLM--=E263--=F263+E264--=F264+E265CellE263containsformula=C263-D263GILLETTERETURNS:ESTIMATIONWINDOWANDEVENTWINDOWCumulativeabnormalreturn(CAR)285286287288289290291292293294295296297298299300301302303304305GHIJKLM27jan0528jan0531jan051feb052feb05123456789101112131415258259260261262263264265266267268269270271272273274275276277278279280281282283ABCDEIntercept0.0007--=INTERCEPT($C$11:$C$262,$B$11:$B$262)Slope0.6364--=SLOPE($C$11:$C$262,$B$11:$B$262)R-squared0.1315--=RSQ($C$11:$C$262,$B$11:$B$262)Steyx0.0113--=STEYX($C$11:$C$262,$B$11:$B$262)Daysinestimationwindow252--=COUNT(A11:A262)DateNYSEGilletteAbnormalreturn(AR)ARt-test27-Jan-04-0.48%-0.42%28-Jan-04-1.26%-1.27%29-Jan-040.00%-0.94%30-Jan-04-0.06%-1.39%2-Feb-040.26%-0.74%19-Jan-05-0.78%-0.09%20-Jan-05-0.69%-0.56%21-Jan-05-0.20%-1.50%24-Jan-05-0.18%0.57%25-Jan-050.21%1.44%26-Jan-050.68%0.07%-0.44%-0.3927-Jan-050.04%1.89%1.80%1.5928-Jan-05-0.24%12.94%13.03%11.5631-Jan-050.82%-1.71%-2.30%-2.041-Feb-050.80%-0.83%-1.40%-1.242-Feb-050.32%0.80%0.52%0.463-Feb-05-0.29%-0.59%4-Feb-050.97%-1.29%7-Feb-05-0.23%-0.38%8-Feb-050.09%-0.75%9-Feb-05-0.63%-1.20%10-Feb-050.68%0.37%11-Feb-050.71%1.58%14-Feb-050.26%0.67%15-Feb-050.32%1.68%16-Feb-050.04%-0.41%17-Feb-05-0.47%0.18%18-Feb-050.21%0.10%22-Feb-05-1.05%-1.82%23-Feb-050.45%2.05%24-Feb-050.55%0.81%EVENTWINDOWGILLETTERETURNS:THESIGNIFICANCEOFTHEABNORMALRETURNSCellD263containsformula=C263-($B$2+$B$3*B263)284285286287288289290291292293294295296297298299300301302303304305ABCDE25-Feb-051.08%0.57%28-Feb-05-0.55%-2.33%1-Mar-050.41%1.19%x-axisforchart26jan0527jan0528jan0531jan051feb052feb05123456789101112131415258259260261262263264265266267268269270271272273274275276277278279280281282283FGHIARsignicant?nonoyesyesnonoCellF263containsformula=IF(ABS(E263)1.96,no,yes)EVENTWINDOWGILLETTERETURNS:THESIGNIFICANCEOFTHEABNORMALRETURNSCellD263containsformula=C263-($B$2+$B$3*B263)CellE263containsformula=D263/$B$5123456789101112131415258259260261262263264265266267268269270271272273274275276277278279280281282283284ABCDEFIntercept0.0004--=INTERCEPT($C$11:$C$262,$B$11:$B$262)Slope0.5877--=SLOPE($C$11:$C$262,$B$11:$B$262)R-squared0.1872--=RSQ($C$1