. 13
( 100 .)


to optimize the regression equation it is best to examine overlapping data
sets to determine which gives the best results.

Substantial Changes in Competition or Product/Service
Although regression analysis is applicable in most situations, substantial
structural changes in a business may render it inappropriate. As men-
tioned previously, the appraiser can often compensate for changes in the
competitive environment by making pro forma adjustments to historical
sales, keeping costs the same. However, when a company changes its
business, the past is less likely to be a good indicator of what may occur
in the future, depending on the signi¬cance of the change.

Table 2-5 is an example of using regression techniques to forecast sales.
In order to do this, it must be reasonable to assume that past performance
is a reasonable indicator of future expectations. If there are fundamental
changes in the industry that render the past a poor indicator of the future,
then regression may useless and even quite misleading. As cautioned by
Pratt, Reilly, and, Schweihs (1996), blind application of regression, where
past performance is the sole indicator of future sales, can be misleading
and incorrect. Instead, careful analysis is required to determine whether
past income generating forces will be duplicated in the future. Neverthe-
less, regression analysis is often useful as a benchmark in forecasting.
In our example in Table 2-5, the primary independent variable is
gross domestic product (GDP), which we show for the years 1988“1998
in billions of dollars in cells B5:B15 (the cell references separated by a
colon will be our way to indicate contiguous spreadsheet ranges). In C5:
C15, we show the square of GDP in billions of dollars, which is our
second potential independent variable.15 Our dependent variable is sales,
which appears in D5:D15.

15. Another variation of this procedure is to substitute the square root of GDP for its square.

PART 1 Forecasting Cash Flows
T A B L E 2-5

Regression Analysis of Sales as a Function of GDP [1]


4 Year GDP Sales
5 1988 5,049.6 25,498,460.2 $1,000,000
6 1989 5,438.7 29,579,457.7 $1,090,000
7 1990 5,743.0 32,982,049.0 $1,177,200
8 1991 5,916.7 35,007,338.9 $1,259,604
9 1992 6,244.4 38,992,531.4 $1,341,478
10 1993 6,558.1 43,008,675.6 $1,442,089
11 1994 6,947.0 48,260,809.0 $1,528,614
12 1995 7,269.6 52,847,084.2 $1,617,274
13 1996 7,661.6 58,700,114.6 $1,706,224
14 1997 8,110.9 65,786,698.8 $1,812,010
15 1998 8,510.7 72,432,014.5 $1,929,791


19 Regression Statistics

20 Multiple R 0.999156207
21 R square 0.998313125
22 Adjusted R square 0.997891407
23 Standard error 13893.80997
24 Observations 11


27 df SS MS F Signi¬cance F

28 Regression 2 9.13938E 11 4.5697E 11 2367.24925 8.0971E 12
29 Residual 8 1544303643 193037955.4
30 Total 10 9.15482E 11

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

33 Intercept 824833.1304 182213.8131 4.526732175 0.001932674 1245019.209 404647.0522 1245019.209 404647.0522
34 GDP 412.8368996 54.65310215 7.553768832 6.5848E-05 286.8065386 538.8672622 286.8065386 538.8672607
35 0.010625314 0.004016833 2.64519663 0.029474667 0.019888154 0.001362473 0.019888154 0.001362473

[1] GDP, Gross Domestic Product, is in billions of dollars. GDP is a proxy for the overall economy.
Spreadsheet Procedures to Perform Regression
It is mandatory to put the variables in columns and the time periods in
rows. Electronic spreadsheets will not permit you to perform regression
analysis with time in columns and the variables in rows. In other words,
we cannot transpose the data in Table 2-5, cells A4:D15 and still perform
a regression analysis.
Another requirement is that all cells must contain numeric data. You
cannot perform regression with blank cells or cells with alphanumeric
data in them. Also, you will receive an error message if one of your
independent variables is a multiple of another. For example, if each cell
in C5:C15 is three times the corresponding cell in B5:B15, then the x var-
iables are perfectly collinear and the regression produce an error message.
We will explain regression procedures in Microsoft Excel ¬rst, then
in Lotus 123.
In Excel, the procedure to perform the regression analysis is as fol-
1. Select Tools Data Analysis Regression. This will bring up a
dialog box and automatically places the cursor in Input Y
2. For the Y range (which is the dependent variable, sales in our
example), click on the range icon with the red arrow
immediately to the right. Doing so minimizes the dialog box
and enables you to highlight the cell range D4:D15 with your
mouse.17 Note that we have included the label Sales in D4 in
this range. Click again on the range icon again to return to the
dialog box.
3. For the X range, which are the independent variables GDP and
GDP2 in our case, repeat the procedure in (2) and highlight the
range B4:C15.
4. Click on the box Labels, which will put a check mark in the
5. Click on Output Range. Click on the box to the right, click on
the range icon with the red arrow, and then click on cell A17.
This tells the spreadsheet to begin the regression output at that
6. Click OK.
Excel now calculates the regression and outputs the data as shown
in the bottom half of Table 2-5.
The instructions for Lotus 123 are almost identical. The only differ-
ences are:
1. The command is Range Analyze Regression.
2. The ranges for the dependent and independent variables should
not include the label in Row 4. Thus they are D5:D15 and B5:
C15, respectively.

16. If Data Analysis is not yet enabled in Excel, you must select add-ins and then select
Analysis ToolPak.
17. Excel actually shows the range with dollar signs, e.g., $D$4:$D$15

PART 1 Forecasting Cash Flows
3. Lotus 123 does not compute t-statistics for you.18 You will have
to do that manually by creating a formula. Divide the regression
coef¬cient by its standard error. Unfortunately, Lotus 123 does
not calculate the p-values either. You will have to look up your
results in a standard table of t-statistics. We will cover that later.

Examining the Regression Statistics
Once again, we look at the statistical measures resulting from the regres-
sion to determine how strong is the relationship between sales and time.
Adjusted R 2 is 99.8% (B22), a near-perfect relationship. The t-statistics for
the independent variables, GDP and GDP2, are 7.55 (D34) and “2.65
(D35), both statistically signi¬cant. The easiest way to determine the level
of statistical signi¬cance is through the p-value. One minus the p-value
is the level of statistical signi¬cance. For GDP, the p-value is 6.5848 10 5
(E34), which is much less than 0.1%. Thus GNP is statistically signi¬cant
at a level greater than 100% 0.1% 99.9%. The square of GDP has a
p-value of 0.029 (E35), which indicates statistical signi¬cance at the 97.1%
level. We normally accept any regressor with signi¬cance greater than or
equal to 95%, and we may consider accepting a regressor that is signi¬-
cant at the 90% to 95% level.
The standard error of the y-estimate, i.e., sales, is $13,894 (B23). Our
approximate 95% con¬dence interval is two standard errors
$27,788, which is less than 2% of the mean of sales.
In actual practice, adjusted R 2 for a regression of sales of mature
¬rms is often above 90% and frequently around 98%.

Adding Industry-Speci¬c Independent Variables
One should also consider adding industry-speci¬c independent variables.
For example, when valuing a jeweler, we should try adding the price of
gold and silver (and the nonlinear transformations, i.e., squares, square
roots, and logarithms) as independent variables. When valuing a ¬rm in
the oil industry, we should try using the price of a barrel of oil (and its
nonlinear transformations).
When valuing a coffee producer, we would want to have not only
the average price of coffee as an independent variable, but also the price
of tea and perhaps even sugar. The analyst should look to the prices of
the product itself, complements, and substitutes.
Once again, it is important to examine the statistical validity of the
relationship and use professional judgment to determine the usefulness
of the equation. Sales forecasts obtained from regression analysis can
serve as a benchmark from which adjustments can be made based on
qualitative factors that may in¬‚uence future sales.
One should also keep in mind that just because a less quantitative
method of forecasting sales does not have an embarrassingly low R 2 star-
ing the analyst in the face does not mean that it is superior to the re-

18. That is true of version 5, which is already at least four years old. If Lotus has added that
feature in a later version, I would not be aware of that.

CHAPTER 2 Using Regression Analysis 45
gression. It means we have no clue as to the reliability of the forecast. We
should always be uncomfortable with our ignorance.

Try All Combinations of Potential Independent Variables
It is important to try all combinations of independent variables. With a
statistics package, this is done automatically in using automated forward
or backward regression. However, statistics packages have their draw-
backs. They are not very user friendly in communicating with spread-
sheet programs, which most appraisers use in valuation analysis. Most
appraisers will ¬nd the spreadsheet regression capabilities more than ad-
Therefore, it is important to try all combinations of potential inde-
pendent variables in the regression process. For example, in regressing
sales against both GDP and GDP2, it is not at all unusual to ¬nd both
independent variables statistically insigni¬cant when regressed together,
i.e., p-values greater than 0.05. However, they still may be statistically
signi¬cant when regressed individually. So it is important to regress sales
against GDP and perform a second regression against GDP2. This process
becomes more complicated with additional candidates for independent

Valuation using the guideline company method involves the use of ratios
of stock price to: earnings (P/E multiples), cash ¬‚ow (P/CF or P/EBIT
multiples), book value (P/BV multiples), sales (P/Sales), or other mea-
sures of income, cash ¬‚ow, or value. The stock prices typically are those
of public companies in the same or similar business as the company.
Consideration is therefore given to the opinion of the informed investor
and what he or she is willing to pay for the stock of comparative public
companies adjusted for the speci¬c circumstances of the company being
valued. While the use of ratios is common in valuation, regression anal-
ysis is more sophisticated and informative because it provides us with
statistical feedback on the strength of the relationship. Pratt, Reilly, and
Schweihs (1996) present a comprehensive chapter on use of the guideline
company method, so we will only discuss it within the context of regres-
sion analysis.

Table 2-6: Regression Analysis of Guideline Companies
Table 2-6 shows data from an actual guideline company analysis, with
the company names disguised in Column A. Column B contains the fair
market values (FMVs) (market capitalization) for 11 companies, ranging
from slightly over $3 million (B5) to over $150 million (B15). The average
FMV is $41.3 million (B16), with a standard deviation of $44.6 million
(B17). Net income (Column C) averages about $5.1 million (C16), with a
range of $600,000 to $16.9 million. We had to exclude companies A and
B, which were outliers with price earnings (PE) ratios over 60.

PART 1 Forecasting Cash Flows
T A B L E 2-6

Regression Analysis of Guideline Companies


4 Company FMV Net Income ln FMV ln NI 1/g g PE Ratio
5 C 3,165,958 602,465 14.9680 13.3088 20.0000 0.0500 5.2550
6 D 6,250,000 659,931 15.6481 13.3999 10.0000 0.1000 9.4707
7 E 12,698,131 1,375,000 16.3570 14.1340 10.5263 0.0950 9.2350
8 F 24,062,948 2,325,000 16.9962 14.6592 9.0909 0.1100 10.3497
9 G 23,210,578 2,673,415 16.9601 14.7989 12.1951 0.0820 8.6820
10 H 16,683,567 2,982,582 16.6299 14.9083 20.0000 0.0500 5.5937
11 I 37,545,523 4,369,808 17.4411 15.2902 12.5000 0.0800 8.5920
12 J 46,314,262 4,438,000 17.6510 15.3057 9.3023 0.1075 10.4358
13 K 36,068,550 7,384,000 17.4009 15.8148 20.8333 0.0480 4.8847
14 L 97,482,000 12,679,000 18.3952 16.3555 9.5238 0.1050 7.6885
15 M 150,388,518 16,865,443 18.8287 16.6408 9.0909 0.1100 8.9170


. 13
( 100 .)