Simon Benninga. Financial Modelling 3-rd edition

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

808 Chapter 33

M is the population size), this deﬁ nition can only be implemented if you

use the Excel function Stdevp for the standard deviation.

BACDEFG

Date

Stock

price

Return

Stock

price

Return

25-Jul-05 29.82 23.66

1-Aug-05 31.04 4.01% 21.63 -8.97% <-- =LN(E5/E4)

1-Sep-05 32.03 3.14% 20.72 -4.30%

3-Oct-05 30.23 -5.78% 21.44 3.42%

1-Nov-05 33.04 8.89% 23.39 8.71%

1-Dec-05 32.92 -0.36% 25.45 8.44%

3-Jan-06 34.18 3.76% 27.15 6.47%

1-Feb-06 34.08 -0.29% 26.74 -1.52%

1-Mar-06 33.54 -1.60% 28.67 6.97%

3-Apr-06 33.75 0.62% 28.54 -0.45%

1-May-06 32.38 -4.14% 27.92 -2.20%

1-Jun-06 32.8 1.29% 27 -3.35%

3-Jul-06 34.55 5.20% 27.87 3.17%

1-Aug-06 35.04 1.41% 29.68 6.29%

1-Sep-06 38.19 8.61% 31.12 4.74%

2-Oct-06 40.92 6.90% 34.31 9.76%

1-Nov-06 41.97 2.53% 32.38 -5.79%

1-Dec-06 44.33 5.47% 32.9 1.59%

3-Jan-07 44.35 0.05% 33.76 2.58%

1-Feb-07 43.69 -1.50% 31.95 -5.51%

1-Mar-07 45.05 3.07% 31.2 -2.38%

2-Apr-07 48.28 6.92% 37.58 18.61%

1-May-07 50.55 4.59% 40.13 6.57%

1-Jun-07 50.76 0.41% 36.75 -8.80%

2-Jul-07 52.5 3.37% 37.16 1.11%

Covariance 0.0009 <-- =COVAR(C5:C28,F5:F28)

Correlation

Using Excel

Correl

function

0.3620 <-- =CORREL(C5:C28,F5:F28)

Correlation = Covar(MCD,WEN)/(

MCD

WEN

)

0.3620 <-- =COVAR(C5:C28,F5:F28)/(STDEVP(C5:C28)*STDEVP(F5:F28))

COMPUTING COVARIANCE AND CORRELATION FOR MCDONALD'S (MCD)

AND WENDY'S (WEN)

MCD WEN

4. If this sentence confuses you, either ignore it altogether or refer back to section 8.2.

33.6 Doing Regressions with Excel

There are several techniques to produce an ordinary least-squares re-

gression with Excel. We illustrate two techniques using the data from the

previous section.

809 Excel Functions

Using these numbers, the best linear explanation of the relation between

y and x is

yx=−14 3285 0 0890..

About 82 percent of the variation in the data is explained by this linear

relation.

Another way that we can produce a simple regression is to graph the

data and let Excel calculate the ordinarily least squares (OLS) regression

coefﬁ cients. To do so:

•

First plot the data using an XY Scatter Plot.

•

Double-click on the data and then go to Insert|Trendline. As the fol-

lowing picture shows, this function allows us to choose several types of

regressions.

DCBA

Observation X Y

1 35.3 10.98

2 29.7 11.13

3 30.8 12.51

4 58.8 8.4

5 61.4 9.27

6 71.3 8.73

7 74.4 6.36

8 76.7 8.5

9 70.7 7.82

10 57.5 9.14

Regression intercept 14.3285 <

=INTERCEPT(C3:C12,B3:B12)

Regression slope -0.0890 <

=SLOPE(C3:C12,B3:B12)

Regression r-squared 0.8189 <

=RSQ(C3:C12,B3:B12)

REGRESSIONS WITH EXCEL

The ﬁ rst technique involves the functions Slope, Intercept, and Rsq;

these functions give the parameters for a simple regression of the data

in column C on column B.

810 Chapter 33

Choosing Linear produces the following plot (the equation and the R

are displayed on the chart by clicking the appropriate boxes on the

Options tab of the dialogue box):

XY Scatter Plot of Data

y = –0.089x + 14.328

= 0.8189

25 35 45 55 65 75

811 Excel Functions

33.6.1 Index

The discussion of the Index function belongs in the section on statistics

only because we want to use it in our next subsection. We some-

times want to pick an individual value out of an array. In the follow-

ing example, the range of cells A2:C4 contains a mixture of numbers

and names. To pick out an individual item from this range, we use

=Index(A2:C4,row,column), where row and column are relative to the

range itself. Thus “Howie” appears in row 2 and column 3 of the range

A2:C4.

AB C

ab 3

Simon 6 Howie

q7Jack

Howie <

=INDEX(A2:C4,2,3)

USING THE INDEX

FUNCTION

In the next subsection we use the Index function to pick out a single

item in the Linest array.

33.6.2 Using Linest

Excel has an array function Linest whose output consists of a number of

regression statistics for an ordinary least-squares regression.

Here is a

picture of the spreadsheet and the Linest dialogue box:

5. There is also an Excel function Logest, whose syntax is exactly the same as that of

Linest. Logest calculates the parameters to ﬁ t an exponential curve.

812 Chapter 33

Linest is an array function (see next chapter); therefore, instead of

[Enter], we press [Control] + [Shift] + [Enter] at the same time. With the

data from this example, we can use Linest to produce the following

output:

BA DC

Observation X Y

1 35.3 10.98

2 29.7 11.13

3 30.8 12.51

4 58.8 8.4

5 61.4 9.27

6 71.3 8.73

7 74.4 6.36

8 76.7 8.5

9 70.7 7.82

10 57.5 9.14

slope intercept

Slope (also

=slope(C3:C12,B3:B12)

)

-0.0890 14.3285 <

Intercept

Standard error of slope

> 0.0148 0.8770 <

Standard error of intercept

(also =Rsq(C3:C12,B3:B12) )

0.8189 0.8117

Standard error of y values (also =Steyx(C3:C12,B3:B12) )

F statistic

> 36.1825 8 <

Degrees of freedom

= Slope*(summed product of observations from means)

23.8377 5.2705 <

SSE = Residual sum of squares

Slope -0.0890 <

=INDEX(LINEST(C3:C12,B3:B12,,1),1,1)

Intercept 14.3285 <

=INDEX(LINEST(C3:C12,B3:B12,,1),1,2)

0.8189 <

=INDEX(LINEST(C3:C12,B3:B12,,1),3,1)

t-statistic 16.3376 <

=C23/INDEX(LINEST(C3:C12,B3:B12,,1),2,2)

Slope -0.0890 <

=INDEX(LINEST(C3:C12,B3:B12,,TRUE),1,1)

Standard error of slope 0.0148 <

=INDEX(LINEST(C3:C12,B3:B12,,TRUE),2,1)

t-statistic -6.0152 <

=C27/C28

Linest output

USING LINEST FOR A SIMPLE REGRESSION

813 Excel Functions

Linest produces a block of output without column headers or row

labels that identify the output. Excel’s Help provides a good explanation

of the meaning of the output; in the preceding picture, we have added in

the explanations.

Note the syntax of this function: Linest(y-range,x-range,constant,sta-

tistics). The y-range is the range of dependent variables, and the x-range

is the range of the independent variables. If constant is omitted (as in

this case) or set to True, then the regression is calculated normally; if

constant is set to False, then the intercept is forced to be zero. If statistics

is set to True (as in this case), then the range of statistics is calculated;

otherwise only the slope and intercept are calculated.

Individual items of this output can be accessed by using the function

Index discussed earlier. Suppose, for example, that we want to do a

simple t-test on the slope; this task requires us to divide the slope value

by its standard error.

BACDEFGHI

Linest output

slope intercept

Slope (also

=slope(D4:D13,C4:C13)

)

-0.0890 14.3285 <

Intercept

Standard error of slope

> 0.0148 0.8770 <

Standard error of intercept

(also

=Rsq(D4:D13,C4:C13)

)

0.8189 0.8117

Standard error of y values (also

=Steyx(D4:D13,C4:C13)

)

F statistic

> 36.1825 8 <

Degrees of freedom

= Slope*(summed product of observations from means)

23.8377 5.2705 <

SSE = Residual sum of squares

Slope -0.0890 <

=INDEX(LINEST(D4:D13,C4:C13,,1),1,1)

Intercept 14.3285 <

=INDEX(LINEST(D4:D13,C4:C13,,1),1,2)

0.8189 <

=INDEX(LINEST(D4:D13,C4:C13,,1),3,1)

Slope -0.0890 <

=INDEX(LINEST(D4:D13,C4:C13,,TRUE),1,1)

S.e. of slope 0.0148 <

=INDEX(LINEST(D4:D13,C4:C13,,TRUE),2,1)

t-statistic -6.0152 <

=C31/C32

33.6.3 Multiple Regressions

Linest can also be used to do a multiple regression, as in the following

illustration:

814 Chapter 33

The predicted versus the actual y’s are as follows:

ABCDEF

Observation X

1 35.3 81.2 10.98

2 29.7 22.5 11.13

3 30.8 77.3 12.51

4 58.8 34.8 8.4

5 61.4 55.1 9.27

6 71.3 124.8 8.73

7 74.4 18.5 6.36

8 76.7 234.6 8.5

9 70.7 22.5 7.82

10 57.5 123.3 9.14

coeff. x

coeff.

intercept

Slope

> 0.0089 -0.0987 14.1705 <

Intercept

Standard error

> 0.0030 0.0110 0.6271

0.9196 0.5783 #N/A

F statistic

> 40.0228 7.0000 #N/A

26.7674 2.3408 #N/A

{=LINEST(E3:E12,C3:D12,,TRUE)}

USING LINEST TO DO A MULTIPLE REGRESSION

ABCDEFGHIJKLMN

Observation X

PYredicted Y

1 35.3 81.2 10.98 11.4071 <

=$E$15+$C$15*D3+$D$15*C3

2 29.7 22.5 11.13 11.4394

3 30.8 77.3 12.51 11.8166

4 58.8 34.8 8.4 8.6770

5 61.455.19.27 8.6004

6 71.3 124.8 8.73 8.2413

7 74.418.56.36 6.9932

8 76.7 234.6 8.5 8.6817

9 70.722.57.82 7.3937

10 57.5 123.3 9.14 9.5897

coeff. x

coeff.

intercept

Slope

> 0.0089 -0.0987 14.1705 <

Intercept

Standard error

>0.00300.01100.6271

0.9196 0.5783 #N/A

F statistic

> 40.0228 7.0000 #N/A

26.7674 2.3408 #N/A

{=LINEST(E3:E12,C3:D12,,TRUE)}

USING LINEST TO DO A MULTIPLE REGRESSION

The regression equation is

Y = 14.1705 - 0.0987*x

+ 0.0089*x

The chart shows the predicted versus the actual Y. If all the

predictions were exact (i.e., R2 = 100%), then the predicted points

would all fall on the 45-degree line (the dark line).

5791113

Actual Y

Predicted Y

815 Excel Functions

33.7 Conditional Functions

If, VLookup, and HLookup are three functions that allow you to put in

conditional statements.

The syntax of Excel’s If statement is If(condition, output if condition

is true, output if condition is false). In the following example, if the initial

number in B3 is less than or equal to 3, then the desired output is 15. If

B3 is greater than 3, then the output is 0:

AB C

Initial number 2

If statement 15 <

=IF(B2<=3,15,0)

Initial number 2

If statement Less than or equal to 3 <

=IF(B5<=3,"Less than or equal to 3","More than 3")

THE IF FUNCTION

As you can see in row 6, you can make If print text also, by enclosing

the desired text in double quotes.

Since VLookup and HLookup both have the same structure, we will

concentrate on VLookup and leave you to ﬁ gure out HLookup for your-

self. VLookup is a way to introduce a table search in your spreadsheet.

Here is an example: Suppose the marginal tax rates on income are given

by the following table (i.e., for income less than $8,000, the marginal tax

rate is 0 percent; for income above $8,000, the marginal tax rate is 15

percent; etc.). Cell B9 illustrates how the function VLookup is used to

look up the marginal tax rate.

AB C

Income

Tax

rate

00%

8,000 15%

14,000 25%

25,000 38%

Income 15,000

Tax rate 25% <

=VLOOKUP(B8,A3:B6,2)

VLOOKUP FUNCTION

816 Chapter 33

The syntax of this function is VLookup(lookup_value,table,column).

The ﬁ rst column of the lookup table, A3:A6, must be arranged in ascend-

ing (increasing) order. The lookup_value, in this case the income of

15,000, is used to determine the applicable row of the table. The row is

the ﬁ rst row whose value is less than or equal to the lookup_value; in

this case, this is the row that starts with 14,000. The column entry deter-

mines from which column of the applicable row the answer is taken; in

this case the marginal tax rates are in column 2.

33.8 Large and Rank, Percentile, and Percentrank

Large(array, k) returns the kth largest number of the array, and

Rank(number, array) returns the rank in array of number.

Here is an example of each function:

CBA

Data

10.98

11.13

12.51

8.40

9.27

8.73

6.36

8.50

7.82

9.14

Ranking, k 3

K-th largest 10.98 <

=LARGE(A3:A12,B14)

Specific number 9.27

Rank from top 4 <

=RANK(B17,A3:A12)

Rank from bottom 7 <

=RANK(B17,A3:A12,1)

Percentile rank 0.8

Percentile 11.01 <

=PERCENTILE(A3:A12,B21)

Specific number 9.27

Percentile ranking 0.666 <

=PERCENTRANK(A3:A12,B24)

LARGE, RANK, PERCENTILE,

PERCENTRANK

817 Excel Functions

Thus the third-largest number in the range A3:A12 is 10.98, and 9.27 is

the fourth-largest number in the range A3:A12. If, as in cell B19, you

specify an additional parameter in the function Rank, you will see that 9.27

is the seventh-ranking number from the bottom of the range A3:A12.

As illustrated, Excel has similar functions for percentiles: Percentile

and PercentRank.

33.9 Count, CountA, CountIf

As their names suggest, all three of these functions count:

•

Count: Counts the number of numeric entries in a range of cells.

•

CountA: Counts all the nonblank cells in a range.

•

CountIf: Counts cells that fulﬁ ll a speciﬁ c condition.

Examples of Count and CountIf follow:

BACDEF

Count

: Count only numerical values

=COUNT(E2:G4) 1 two 3

Count

: count all nonblank cells

=COUNTA(E2:G4) 4 six

seven 8 9

COUNT, COUNTA, COUNTIF

To use CountIf we have to specify a condition. The next spreadsheet gives

a year of Merck’s weekly stock returns (some rows have been hidden):

DCBA

Number of returns 52 <

=COUNT(C10:C61)

Returns over 2% 13 <

=COUNTIF(C10:C61,">2%")

Cutoff 5%

Returns over cutoff 2 <

=COUNTIF(C10:C61,">"&TEXT(B5,"0.00%"))

Date

Merck

price

Return

3-Jan-06 31.82

9-Jan-06 32.15 1.03% <

=LN(B10/B9)

17-Jan-06 31.94 -0.66%

23-Jan-06 33.33 4.26%

30-Jan-06 33.04 -0.87%

6-Feb-06 32.96 -0.24%

13-Feb-06 34.63 4.94%

21-Feb-06 33.72 -2.66%

27-Feb-06 33.81 0.27%

6-Mar-06 33.76 -0.15%

13-Mar-06 34.61 2.49%

USING COUNTIF ON MERCK'S WEEKLY STOCK

RETURNS