17 Standard deviation 44,558,275 5,233,919 1.1212 1.0814 4.8135 0.0252 1.9954

20 SUMMARY OUTPUT

22 Regression Statistics

23 Multiple R 0.997820486

24 R square 0.995645723

25 Adjusted R square 0.994557153

26 Standard error 0.082720079

27 Observations 11

29 ANOVA

30 df SS MS F Signi¬cance F

31 Regression 2 12.51701011 6.258505055 914.6369206 3.59471E-10

32 Residual 8 0.054740892 0.006842611

33 Total 10 12.571751

35 Coef¬cients Standard t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper

Error 95.0%

36 Intercept 3.430881701 0.390158993 8.79354767 2.19714E-05 2.531172869 4.330590533 2.531172869 4.330590533

37 ln NI 0.957081978 0.024655341 38.81844378 2.13125E-10 0.900226622 1.013937333 0.900226622 1.013937333

38 1/g 0.056021702 0.005538834 10.11434967 7.79687E-06 0.068794284 0.04324912 0.068794284 0.04324912

47

48

T A B L E 2-6 (continued)

Regression Analysis of Guideline Companies

A B C D E F G H I

40 Valuation

41 NI 100,000 200,000 300,000 400,000 500,000 1,000,000

42 In NI 11.5129 12.2061 12.6115 12.8992 13.1224 13.8155

43 X coef¬cient-NI 0.957081978 0.957081978 0.957081978 0.957081978 0.957081978 0.957081978

44 In NI X coef¬cient 11.01881347 11.68221215 12.07027549 12.34561082 12.55917749 13.22257672

45 g 0.05 0.055 0.06 0.065 0.07 0.075

46 1/g 20 18.18181818 16.66666667 15.38461538 14.28571429 13.33333333

47 X coef¬cient-1 / g 0.056021702 0.056021702 0.056021702 0.056021702 0.056021702 0.0560217

48 1/g X coef¬cient 1.120434033 1.018576394 0.933695028 0.861872333 0.800310024 0.746956022

49 Add intercept 3.430881701 3.430881701 3.43088176 3.430881701 3.430881701 3.430881702

50 Total ln FMV 13.329261101 14.09451745 14.56746217 14.91462019 15.18974917 15.90650185

51 FMV $614,928 $1,321,816 $2,121,136 $3,001,492 $3,952,067 $8,092,934

52 PE Ratio 6.149284138 6.609082291 7.070452024 7.50373099 7.904133036 8.09293361

54 95% Con¬dence Intervals

55 2 Standard errors 0.165440158

e2 Std Err

56 1.179912352

e-2 Std Err

57 0.847520579

First we will brie¬‚y describe the regression results for the regression

of FMV against net income. The regression yields an adjusted R 2 of 94.6%

and a t-statistic for the x-coef¬cient of 12.4, which seems to indicate a

successful regression. The regression equation obtained for the complete

data set is:

FMV $1,272,335 (8.3 Net Income)

If we were to use to value a ¬rm with net income of $100,000, the re-

gression would produce a value of $442,000. Something is wrong!

The problem is that the full regression equation is:

FMV a b Net Income ui (2-10)

where ui is an error term, assumed to be normally distributed with an

expected value of zero. Our speci¬c regression equation is:

$1,272,335 (8.3 Net Income) ui (2-11)

The problem is that this error term is additive and likely to be cor-

related to the size of the ¬rm. When that occurs, we have a problem called

˜˜heteroscedasticity.™™

There are two possible solutions to the problem. The ¬rst is to use

weighted least squares (WLS) instead of ordinary least squares regression.

In WLS, we weight the extreme values less than the more mainstream

values. This usually will not produce a usable solution for a privately

held ¬rm that is much smaller than the publicly traded guideline com-

panies.

The second possible solution is to use a log“log speci¬cation. In do-

ing so, we regress the natural logarithm of market capitalization as a

function of the natural logarithm of net income. Its form is:

ln FMVi a bi ln NI ui, i guideline company 1, 2, 3, . . . n

(2-12)

When we take antilogs, the original equation is:

A NIib vi

FMVi (2-13)

e a, vi e ui is Euler™s constant, and the expected value of

where A

vi 1.

In equation (2-13), the regression equation x-coef¬cient, bi, from equa-

tion (2-12) for net income thus becomes an exponent to net income. If

b 1, then size has no scaling effect on the FMV, and we would expect

price earnings ratios to be uncorrelated to size, all other things being

constant. If b 1, then the price earnings multiple should rise with net

income, and the opposite is true of b 1. Relating this to the log size

model in Chapter 4, we would thus expect to ¬nd b 1 because over

long periods of time large ¬rms have lower discount rates than small

¬rms, which means larger values relative to earnings.

Using equation (2-13), consider two identical errors of 20% for ¬rms

i and j, where ¬rm i has net income of $100,000 and ¬rm j has net income

of $200,000. In other words, the error terms vi and vj are both 1.2.19 For

19. This means the error terms ui and uj in equation (2-12) are equal to ln (1.2) 0.182.

CHAPTER 2 Using Regression Analysis 49

simplicity, suppose that b 1 for both ¬rms. The same statistical error in

the log of the fair market value of both ¬rms produces an error in fair

market value that is twice as large in ¬rm j as in ¬rm i. This is a desirable

property, as it corresponds to our intuition that large ¬rms will tend to

have larger absolute deviations from the regression determined values.

Thus, this form of regression is likely to be more successful than equation

(2-10) for valuing small ¬rms.

Equation (2-10) is probably ¬ne for valuing ¬rms of the same size as

the guideline companies. When we apply equation (2“10) to various lev-

els of net income, we ¬nd the forecast FMVs are $442,000, $0 (rounded),

$2.9 million, and $7.0 million for net incomes of $100,000, $154,000,

$500,000, and $1 million. Obviously equation (3-10) works poorly at the

low end. We would also have a similar, but opposite, scaling problem

forecasting value for a ¬rm with net income of $5 billion. The additive

error term restricts the applicability of equation (2-10) to subject compa-

nies of similar size to the guideline companies.

There is an important possible enhancement to the regression equa-

tion, and that is the introduction of forecast growth as an independent

variable. The emergence of the Internet makes it easier to obtain growth

forecasts, although frequently there are no such estimates for smaller pub-

licly traded ¬rms.

For a ¬rm with constant forecast growth, a midyear Gordon model

is its proper valuation equation.

1 r

FMV CFt (2-14)

1

r g

In Chapter 4, we show that New York Stock Exchange returns are nega-

tively related to the natural logarithm of market capitalization (which can

also be referred to as fair market value or size), which means that there

is a nonlinear relationship between return and size. Therefore, the dis-

count rate, r, in equation (2-14) impounds a nonlinear size effect. To the

extent that there is a nonlinear size effect in equation (2-13), we should

hopefully pick that up in the b coef¬cient.

Note that in equation (2-14) there is a growth term, g, which appears

in the denominator of the Gordon model multiple. Thus, it is reasonable

to try 1/g as an additional independent variable in equation (2-13).

Continuing our description of Table 2-6, Column C is net income and

Columns D and E are the natural logarithms of FMV and net income.

These are actual data from a real valuation. Column G shows a growth

rate, and it is not actual data (which were unavailable). Column F is the

inverse of Column G, i.e., 1/g. Thus, Column D is our dependent variable

and Columns E and F are our independent variables.20

Adjusted R 2 is 99.5% (B25), an excellent result. The standard error of

the y-estimate is 0.08272 (B26). The y-intercept is 3.43 (B36) and the x-

coef¬cients for ln NI and 1/g are 0.95708 and “0.05602 (B37, B38), re-

spectively.

20. Electronic spreadsheets require that the independent variables be in contiguous columns.

PART 1 Forecasting Cash Flows

50

On page 2 of Table 2-6, we show valuations for subject companies

with differing levels of net income and expected growth. Row 41 shows

¬rms with net incomes ranging from $100,000 to $1 million. Row 42 is

the natural log of net income.21 We multiply that by the x-coef¬cient for

net income in Row 43, which produces a subtotal in Row 44.

Row 45 contains our forecast of constant growth for the various sub-

ject companies. We are assuming growth of 5% per year for the $100,000

net income ¬rm in Column B, and we increase the growth estimate by

0.5% for each ¬rm. Row 46 is one divided by forecast growth.

In Row 47 we repeat the x-coef¬cient for 1/g from the regression,

and we multiply Row 46 Row 47 Row 48, which is another subtotal.

In Row 49 we repeat the y-intercept from the regression. In Row 50

we add Rows 44, 48, and 49, which is the natural logarithm of the forecast

FMV (at the marketable minority interest level). We must then exponen-

tiate that result, i.e., take the antilog. The Excel formula for B51 is

EXP(B50).22 Finally, we calculate the P/E ratio in Row 52 as Row 51 di-

vided by Row 41.

The P/E ratio rises because of the increase in the forecast growth rate

across the columns. If all cells in Row 45 were equal to 0.05, then the PE

ratios in Row 52 would actually decline going to the right across the

columns. The reason for this is that the x-coef¬cient for ln NI is 0.95708

(page 1, B37) 1. This is contrary to our expectations. If B38 were greater

than 1, then P/E ratios would rise with ¬rm size, holding forecast growth

constant. Does this disprove the log size model? No. While all the rest of

the data are real, these growth rates are not actual. They are made up.

Also, one small sample of one industry at one point in time does not

generalize to all ¬rms at all times.

In the absence of the made-up growth rates, the actual regression

yielded an adjusted R 2 of 93.3% and a standard error of 0.2896 (not

shown).

95% Con¬dence Intervals

We multiply the standard error in B26 by 2 0.16544 (B55). To convert

the standard error of ln FMV to the standard error of FMV, we have to

exponentiate the two standard errors. In B56 we raise e, Euler™s constant,

to the power of B55. Thus, e0.16544 1.1799, which means the high side of

our 95% con¬dence interval is 18% higher than our estimate.23 To calcu-

late the low side of our 95% con¬dence interval, we raise e to the power

of two standard errors below the regression estimate. Thus B57 e 0.16544

0.8475, which is approximately 15% below the regression estimate.

Thus our 95% con¬dence interval is the regression estimate 18% and

15%. Using only the actual data that were available at the time, the

same regression without 1/g yielded con¬dence intervals of the regres-

21. The Excel formula for cell B42, for example, is ln(B41). The Lotus 123 formula would be

@ln(B41).

22. In Lotus 123 the formula would be @exp(B50)

23. The Excel formula for cell B56 is EXP(B55) and the Lotus 123 formula is @EXP(B55).

Similarly, the Excel formula for B57 is EXP( B55), and the Lotus 123 formula is

@EXP( B55).

CHAPTER 2 Using Regression Analysis 51

sion estimate 78% and 56%. Obviously, growth can make a huge dif-

ference. Also, without growth, the x-coef¬cient for ln NI was slightly

above one, indicating increasing P/E multiples with size.

SUMMARY

Regression analysis is a powerful tool for use in forecasting future costs,

expenses, and sales and estimating fair market value. We should take care

in evaluating and selecting the input data, however, to arrive at mean-

ingful answer. Similarly, we should carefully scrutinize the regression out-

put to determine the signi¬cance of the variables and the amount of error

in the Y-estimate to determine if the overall relationship is meaningful.

BIBLIOGRAPHY

Bhattacharyya, Gouri K., and Richard A. Johnson. 1977. Statistical Concepts and Methods.

New York: John Wiley & Sons.

Pratt, Shannon P., Robert F. Reilly, and Robert P. Schweihs. 1996. Valuing a Business: The

Analysis and Appraisal of Closely Held Companies; 3d ed. New York: McGraw-Hill.

Wonnacott, Thomas H., and Ronald J. Wonnacott. 1981. Regression: A Second Course in

Statistics. New York: John Wiley & Sons.

PART 1 Forecasting Cash Flows

52

APPENDIX

The ANOVA table (Rows 28“32)

We have already discussed the importance of variance in regression anal-

ysis. The center section of Table A2-1, which is an extension of Table

2-1B, contains an analysis of variance (ANOVA) automatically generated

by the spreadsheet. We calculate the components of ANOVA in the top

portion of the table to ˜˜open up the black box™™ and show the reader

where the numbers come from.

First, we calculate the regression estimate of adjusted costs in Col-

umn D using the regression equation:

Costs $56,770 (0.80 Sales) (B35, B36)

Next, we subtract the average actual adjusted cost of $852,420 (C18) from

the calculated costs in Column D to arrive at the deviation from the mean

in Column E. Note that the sum of the deviations is zero in cell E17, as

expected.

In Column F we square each deviation term in Column E and total

them in F17. The total, 831,414,202,481, is known as the sum of squares

and measures the amount of variation explained by the regression. In the

absence of a regression, our best estimate of costs for any year during the

1988“1997 period would be Y, the mean costs. Therefore, the difference

between the historical mean and the regression estimate (Column E) is

the absolute deviation explained by the regression. The square of that

(Column F) is the variance explained by the regression. This term appears