Simon Benninga. Financial Modelling 3-rd edition

Подождите немного. Документ загружается.

388 Chapter 14

ST U VWX Y Z AAAB

%45.0 %94.0

Day

relative

to event

AAR T-stat

Cumulative

Abnormal

Returns

AAR T-stat

Cumulative

Abnormal

Returns

-10

-0.57%

-1.1615 -0.57% -0.09% -0.1632 -0.09%

-9

-0.48%

-0.9931 -1.05% 0.10% 0.1944 0.02%

-8

0.00%

0.0026 -1.05% 0.04% 0.0670 0.05%

-7

-0.39%

-0.8046 -1.44% 0.01% 0.0251 0.07%

-6

-0.57%

-1.1777 -2.01% -0.54% -1.0058 -0.48%

-5

0.05%

0.1043 -1.96% -0.55% -1.0278 -1.03%

-4

-0.18%

-0.3753 -2.14% 0.37% 0.6774 -0.67%

-3

-0.75%

-1.5410 -2.89% -0.34% -0.6376 -1.01%

-2

0.53%

1.0900 -2.36% 0.54% 0.9917 -0.47%

-1

0.85%

1.7437 -1.51% -0.74% -1.3685 -1.21%

2.65%

5.4406

1.13% -3.24%

-6.0062

-4.46%

0.46%

0.9409 1.59% -1.63%

-3.0190

-6.08%

0.02%

0.0335 1.61% 0.40% 0.7356 -5.69%

-0.63%

-1.2939 0.98% 0.22% 0.4121 -5.47%

0.02%

0.0355 0.99% 0.24% 0.4434 -5.23%

-0.30%

-0.6087 0.70% -0.52% -0.9620 -5.75%

-0.28%

-0.5690 0.42% 0.55% 1.0269 -5.19%

0.34%

0.6998 0.76% 0.47% 0.8643 -4.72%

-0.36%

-0.7326 0.41% 0.14% 0.2680 -4.58%

0.15%

0.3154 0.56% -0.45% -0.8382 -5.03%

0.12%

0.2425 0.68% -0.20% -0.3614 -5.23%

Cell X35 contains formula

=SUMIF($B$6:$Q$6,"<=0",B35:Q35)/COUNTIF($B

$6:$Q$6,"<=0")

Cell Y35 contains formula =X35/$Y$11

Cell Z35 contains formula =X35+Z34

Cell T35 contains formula

=SUMIF($B$6:$Q$6,">0",B35:Q35)/COUNTIF($B$6:$

Q$6,">0")

Cell U35 contains formula =T35/$U$11

Cell V35 contains formula =T35+V34

Positive-Earnings Announcements

Nonpositive Earnings

Announcements

Unadjusted cross-sectional errors -

Positive

Unadjusted cross-sectional errors -

Nonpositive

Cell U11 contains formula

{=SQRT(SUMPRODUCT(IF($B$6:$Q$6>0,$B$11:$Q$11),IF($

B$6:$Q$6>0,$B$11:$Q$11))*(1/COUNTIF($B$6:$Q$6,">0")^2)

)}

389 Event Studies

The test statistics for the positive and nonpositive announcements

have been computed by dividing the AAR for each day by the appropri-

ate cross-sectional error for the speciﬁ c type of return (cells U11 and

Y11):

Cell U11:

Sum of Steyx for positive announcements

Number of positiv

ee announcements

Cell Y11:

Sum of Steyx for negative announcements

Number of negativ

ee announcements

Graphing the CARs gives this graph:

Average Cumulative Abnormal Returns

–7

–6

–5

–4

–3

–2

–1

–10 –9 –8 –7 –6 –5 –4 –3 –2 –1 0 1 2 3 4 5 6 7 8 9 10

Event days

Returns (%)

Positive earnings announcements

Nonpositive earnings announcements

On average, there appears to have been little leakage prior to the

announcement date of either the good news announcements or the bad

news. The market appears to have absorbed the information in the

announcements rapidly: following the announcement date (“event day

0”), there appears to have been little additional response.

390 Chapter 14

14.5 Using a Two-Factor Model of Returns for an Event Study

The model used in section 14.4 assumes an equilibrium model r

= a

. This “one-factor” model assumes that the returns on the stocks in

question are driven only by one market index. In this section we illustrate

a two-factor model. We assume that returns are a function of both a

market and an industry factor: r

= a

+ b

i,Market

+ b

i,Industry

Industry,t

. We

then use this model to determine whether a speciﬁ c event inﬂ uenced the

returns and in which direction.

Our event: On 16 November 2006, Wendy’s announced the purchase

by tender of 22,418,000 shares at a price of $35.75 per share. This share

purchase represented approximately 19 percent of the ﬁ rm’s equity.

Wendy’s stock closed on 16 November at $35.66.

391 Event Studies

14.5.1 Did the Repurchase Affect Wendy’s Returns?

We start by regressing the daily returns on Wendy’s on the S&P 500 and

the S&P 500 Restaurant Index for the 252 days preceding the tender

date of 16 November 2006. We use the array function Linest to do this

computation.

The Linest box looks like this:

Wendy's Intl vs. S&P 500 and S&P 500 Restaurant Index

1/3/03–3/16/07

0.00

5.00

10.00

15.00

20.00

25.00

30.00

35.00

40.00

S&P 500

S&P 500 Restaurant

Index

Wendy's International

Note: The S&P 500 and the S&P 500 Restaurant Index are indexed to $12.04 the

closing price of Wendy's International on January 3, 2003.

16-Mar-07

Announced stock buyback

3-Jan-03

5-Jun-03

5-Nov-03

6-Apr-04

7-Sep-04

7-Feb-05

10-Jul-05

11-Dec-05

13-May-06

13-Oct-06

ABC D

Industry Market Intercept

Slope

> 0.4157 0.5095 0.0012

Standard Error

> 0.0851 0.1410 0.0007

0.3140 0.0103 #N/A

F statistic

> 56.9738 249 #N/A

0.0122 0.0266 #N/A

11. The use of Linest to perform multiple regressions is discussed in Chapter 33. It is not

the most user friendly of all Excel functions.

392 Chapter 14

From this box we can conclude that Wendy’s return is sensitive to both

the market and the industry.

tWendys,

Market reaction

coefficient

Standard

↑

0 0012 0 5095..

eerror:

0.1410

Industry reaction

coefficient.



∗+

↑

0 4157.

aandard error:

0.0851

Industry,



∗r

This Linest box is shown again in the next spreadsheet. Dividing the co-

efﬁ cients by their respective standard errors (row 9) shows that they are

both signiﬁ cant at the 1 percent level. Note that cell C5 gives the standard

error of the y-estimate; we use this in the analysis to determine the signiﬁ -

cance of the abnormal returns. Further analysis follows the spreadsheet.

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

ABC DEFGHIJ

Industry Market Intercept

Slope

> 0.4157 0.5095 0.0012

Standard Error

>0.0851 0.1410 0.0007

0.3140 0.0103 #N/A

F statistic

> 56.9738 249 #N/A

0.0122 0.0266 #N/A

t-stat 4.8818 3.6142 1.8367 <

=D3/D4

Days in estimation

window

252 <

=COUNT(A15:A266)

Date Wendy's Intl S&P 500

S&P 500

Restaurant Index

Expected

return

Abnormal

return (AR)

Cumulative

abnormal

return (CAR)

T-test of

15-Nov-05 0.08% -0.39% -1.26%

16-Nov-05 -0.37% 0.18% -0.93%

17-Nov-05 0.84% 0.94% 1.60%

18-Nov-05 0.27% 0.44% 0.19%

21-Nov-05 1.08% 0.53% 0.92%

9-Nov-06 -0.89% -0.53% -0.35%

10-Nov-06 1.46% 0.19% 0.95%

13-Nov-06 0.08% 0.25% -0.05%

14-Nov-06 0.73% 0.63% 0.03%

15-Nov-06 0.25% 0.24% 0.21% 0.33% -0.08% -0.08% <

=F267 -0.0741 <

=F267/$C$5

16-Nov-06 0.31% 0.23% 1.26% 0.76% -0.45% -0.53% <

=G267+F268 -0.4363

17-Nov-06 -6.44% 0.10% -1.78% -0.57% -5.87% -6.40% <

=G268+F269 -5.6805

20-Nov-06 -1.59% -0.05% -0.19% 0.02% -1.61% -8.01% -1.5582

21-Nov-06 0.15% 0.16% 0.07% 0.23% -0.08% -8.09% -0.0784

22-Nov-06 2.80% 0.23% 0.27% 0.35% 2.45% -5.64%

2/7/2005

2/8/2005

2/9/2005

2/10/2005

2/11/2005

2/14/2005

2/15/2005

2/16/2005

2/17/2005

2/18/2005

2/22/2005

2/23/2005

2/24/2005

2/25/2005

2/28/2005

3/1/2005

WENDY'S RETURNS: ESTIMATION WINDOW AND EVENT WINDOW

Cell F267 contains formula =B267-

E267

Cell E267 contains formula

=$D$3+$C$3*C267+$B$3*D267

Cells B3:D7 contain the array formula

{=LINEST(B15:B266,C15:D266,,TRUE)}

Cell C5 is the standard error of the y estimate,

used in the t-test of the abnormal returns

EVENT WINDOW

-10%

-8%

-6%

-4%

-2%

15nov06 16nov06 17nov06 20nov06 21nov06 22nov06

Expected return

Abnormal return (AR)

Cumulative abnormal return (CAR)

393 Event Studies

In rows 267–272 of the spreadsheet we use the two-factor model to

analyze the abnormal returns (AR) and the cumulative abnormal returns

(CAR) of the Wendy’s announcement. While there is little AR or CAR

on the days before the announcement, it is clear that the announcement

on 16 November had a considerable impact on Wendy’s returns on the

day following (−5.87% abnormal return on 17 November) and on the

next day (−1.63% AR on 20 November). Dividing the abnormal return

by the standard error in C5 shows that only the AR on the event day is

signiﬁ cant at the 5 percent level.

Furthermore, an analysis of the announcement broken down into the

market and the industry factors shows that on both of the two days after

the 16 November announcement date the effects of the market index on

Wendy’s returns were slight. On 17 November, however, there was a

signiﬁ cant impact of the S&P 500 Restaurant Index on Wendy’s that was

lacking on 20 November.

To see this point, we ﬁ rst discuss the day after the event, 17 November

2006:

266

267

268

269

270

271

272

ABC DEFGH

Date Wendy's Intl S&P 500

S&P 500

Restaurant Index

Expected

return

Abnormal

return (AR)

Cumulative

abnormal

return (CAR)

14-Nov-06 0.73% 0.63% 0.03%

15-Nov-06 0.25% 0.24% 0.21% 0.33% -0.08% -0.08% <

=F267

16-Nov-06 0.31% 0.23% 1.26% 0.76% -0.45% -0.53% <

=F267+F268

17-Nov-06 -6.44% 0.10% -1.78% -0.57% -5.87% -6.32% <

=F268+F269

20-Nov-06 -1.59% -0.05% -0.19% 0.02% -1.61% -7.48%

21-Nov-06 0.15% 0.16% 0.07% 0.23% -0.08% -1.69%

22-Nov-06 2.80% 0.23% 0.27% 0.35% 2.45% 2.37%

EVENT WINDOW

On 17 November, the S&P 500 rose by 0.10 percent and the S&P 500

Restaurant Index fell by 1.78 percent. Given the regression r

Wendy’s,t

0.0012 + 0.5095*r

+ 0.4157*r

Industry,t

, the change in the S&P 500 would

have affected Wendy’s returns by approximately +0.05 percent, and the

change in the industry index would have affected Wendy’s returns by

approximately −0.74 percent. But Wendy’s decreased by −6.44 percent

on the same day, well in excess of the impact of either of the two

factors.

Here are the data for 20 November:

394 Chapter 14

On 20 November, the S&P 500 fell by 0.05 percent and the S&P 500

Restaurant Index fell by 0.19 percent. Given the regression r

Wendy’s,t

0.0012 + 0.5095*r

+ 0.4157*r

Industry,t

, the change in the S&P 500 would

have affected Wendy’s returns by approximately −0.08 percent, and the

change in the industry index would have affected Wendy’s returns by

approximately −0.03 percent. But Wendy’s decreased by −1.59 percent

on the same day, which is again well in excess of the impact of either of

the two factors.

The impact of the announcement was felt even in the third day after

the event, but we leave this analysis to the reader.

14.6 Using Excel’s Offset Function to Locate a Regression in a Data Set

The analysis in section 14.4 requires us to do a regression of a speciﬁ c

stock’s returns on the returns of the S&P 500, where the starting point

of the regression is the 252 trading days before a speciﬁ c date. The tech-

nique in section 14.4 uses a number of Excel functions:

•

The functions Intercept, Slope, Rsq give the regression intercept, slope,

and r-squared. These functions have been illustrated in Chapter 2 and in

the previous portfolio chapters. The function Steyx gives the standard

deviation of the regression residuals.

•

The function Countif counts the number of cells in a range that meet

a speciﬁ c condition. Countif has the syntax Countif(data,condition).

However, the condition must be a text condition (which means that in

this example we will use the Excel function Text to translate a date to a

text number—more later).

•

The function Offset (see also Chapter 34) allows us to specify a cell or

a block of cells in an array.

266

267

268

269

270

271

272

ABC DEFGH

Date Wendy's Intl S&P 500

S&P 500

Restaurant Index

Expected

return

Abnormal

return (AR)

Cumulative

abnormal

return (CAR)

14-Nov-06 0.73% 0.63% 0.03%

15-Nov-06 0.25% 0.24% 0.21% 0.33% -0.08% -0.08% <

=F267

16-Nov-06 0.31% 0.23% 1.26% 0.76% -0.45% -0.53% <

=F267+F268

17-Nov-06 -6.44% 0.10% -1.78% -0.57% -5.87% -6.32% <

=F268+F269

20-Nov-06 -1.59% -0.05% -0.19% 0.02% -1.61% -7.48%

21-Nov-06 0.15% 0.16% 0.07% 0.23% -0.08% -1.69%

22-Nov-06 2.80% 0.23% 0.27% 0.35% 2.45% 2.37%

EVENT WINDOW

395 Event Studies

To illustrate the problem, consider the following data of returns for

General Mills (GIS) and the S&P 500. We want to run a regression of

the GIS returns on the S&P 500 returns for 10 dates before 29 January

1997:

GFEDCBA

Date

General

Mills

GIS

Return SP500 Return

3-Jan-97 57.96 748.03

6-Jan-97 58.19 0.0040 <

=LN(B4/B3) 747.65 -0.0005 <

=LN(E4/E3)

7-Jan-97 59.33 0.0194 753.23 0.0074

8-Jan-97 59.33 0.0000 748.41 -0.0064

9-Jan-97 59.91 0.0097 754.85 0.0086

10-Jan-97 59.91 0.0000 759.5 0.0061

13-Jan-97 59.68 -0.0038 759.51 0.0000

14-Jan-97 59.91 0.0038 768.86 0.0122

15-Jan-97 59.56 -0.0059 767.2 -0.0022

16-Jan-97 59.56 0.0000 769.75 0.0033

17-Jan-97 59.56 0.0000 776.17 0.0083

20-Jan-97 59.44 -0.0020 776.7 0.0007

21-Jan-97 60.71 0.0211 782.72 0.0077

22-Jan-97 61.4 0.0113 786.23 0.0045

23-Jan-97 62.09 0.0112 777.56 -0.0111

24-Jan-97 61.63 -0.0074 770.52 -0.0091

27-Jan-97 61.29 -0.0055 765.02 -0.0072

28-Jan-97 61.06 -0.0038 765.02 0.0000

29-Jan-97 62.09 0.0167 772.5 0.0097

30-Jan-97 62.21 0.0019 784.17 0.0150

31-Jan-97 62.44 0.0037 786.16 0.0025

3-Feb-97 62.09 -0.0056 786.73 0.0007

Starting date 29-Jan-97

Rows from top of data

to starting date

19 <

=COUNTIF(A3:A24,"<="&TEXT(B26,"0"))

Regression

Intercept 0.0022 <

=INTERCEPT(OFFSET(A3:F24,B27-11,2,10,1),OFFSET(A3:F24,B27-11,5,10,1))

Slope 0.5198 <

=SLOPE(OFFSET(A3:F24,B27-11,2,10,1),OFFSET(A3:F24,B27-11,5,10,1))

R-squared 0.1413 <

=RSQ(OFFSET(A3:F24,B27-11,2,10,1),OFFSET(A3:F24,B27-11,5,10,1))

Check

Intercept 0.0022 <

=INTERCEPT(C11:C20,F11:F20)

Slope 0.5198 <

=SLOPE(C11:C20,F11:F20)

R-squared 0.1413 <

=RSQ(C11:C20,F11:F20)

USING OFFSET, COUNTIF, AND TEXT

TO LOCATE A REGRESSION IN A DATA SET

To run this regression, we ﬁ rst use Countif(data,condition) to count

the row number of the data on which the starting date falls. Since condi-

tion must be a text entry, we translate the date in cell B26 to a text

by using Text(b26,≤0≤). The Excel function =Countif(A3:A24,≤<=

≤&Text(B26,≤0≤)) now counts the number of cells in the column A3:A24

396 Chapter 14

that are less than or equal to the date in cell B26. The answer, as you can

see in cell B27, is 19.

Next, we use Offset(A3:F24,B27-11,2,10,1) to locate the 10 rows of

GIS returns before the 19th row indicated by the starting date. This is a

tricky function!

Offset(A3:F24,B27-11

Reference

array

Starting

row in

refe

↑↑



rrence

array

2 columns

over from

top corner

of reference



↑

rray

Fix starting

corner in reference

array from which

data is cons



↑

iidered

10 rows

starting

corner

Only 1

colum

,10, 1

 



↑

below

of data

Data considered

)





↑

The functions Intercept, Slope, Rsq can now be used with Offset(A3:

F24,B27-11,2,10,1) and Offset(A3:F24,B27-11,5,10,1):

↑

Intercept(Offset(A3:F24,B27-11,2,10,1)

-datay

  

,Offset(A3:F24,B27-11,5,10,1))

-data

↑

14.7 Conclusion

Event studies, used to determine the impact of a particular market effect

on a speciﬁ c stock or a generic market effect on a set of stocks, are one

of the most widely used technologies in practical ﬁ nance. While Excel

may not be the optimal tool for performing an event study, we have used

it in this chapter to illustrate both uses of the event study. We have shown

that Excel can readily be used to perform either a one-factor or a two-

factor event study. The Excel techniques employed are easily acquired

by a sophisticated user.

Value at Risk

15.1 Overview

Value at risk (VaR) measures the worst expected loss under normal

market conditions over a speciﬁ c time interval at a given conﬁ dence

level. As one of our references states, “VaR answers the question: how

much can I lose with x% probability over a pre-set horizon” (J. P. Morgan,

RiskMetrics—Technical Document).

Another way of expressing this

deﬁ nition is that VaR is the lowest quantile of the potential losses that

can occur within a given portfolio during a speciﬁ ed time period. The

basic time period T and the conﬁ dence level (the quantile) q are the two

major parameters that should be chosen in a way appropriate to the

overall goal of risk measurement. The time horizon can differ from a few

hours for an active trading desk to a year for a pension fund. When the

primary goal is to satisfy external regulatory requirements, such as bank

capital requirements, the quantile is typically very small (for example, 1

percent of worst outcomes). However, for an internal risk-management

model used by a company to control the risk exposure, the typical number

is around 5 percent (visit the Internet sites in the references for more

details). A general introduction to VaR can be found in Linsmeier and

Pearson (1996) and Jorion (1997).

In the jargon of VaR, suppose that a portfolio manager has a daily VaR

equal to $1 million at 1 percent. This statement means that there is only

one chance in 100 that a daily loss bigger than $1 million occurs under

normal market conditions.

15.2 A Really Simple Example

Suppose a manager has a portfolio that consists of a single asset. The

return of the asset is normally distributed with a mean return of 20

percent and standard deviation of 30 percent. The value of the portfolio

today is $100 million. We want to answer various simple questions about

the end-of-year distribution of portfolio value:

1. This chapter is based on an article written with Zvi Wiener, “Value-at-Risk (VaR),”

which ﬁ rst appeared in Mathematica in Education and Research, Vol. 7, 1998,

pp. 39–45.

2. This and other valuable documents initially produced by J. P. Morgan can be found at

the site of the successor company, RiskMetrics, http://www.riskmetrics.com/techdoc

.html.