CP

412 CHAPTER 11 Financial Planning and Forecasting Financial Statements

FIGURE 11-1 MicroDrive Inc.: 2002 Sales Projection (Millions of Dollars)

3,000

2,000

1,000

0

Net Sales

($)

1998 1999 2000 2001 2002 2003

Regression Line

Year Sales Annual Growth Rate

1998 $2,058

1999 2,534 23.1%

2000 2,472 2.4

2001 2,850 15.3

2002 3,000 5.3

Average 10.3%

and it can be found by solving the equation or with a financial calculator ,entering

N4 ,PV2058 ,PMT0 ,FV3000 ,and then pressing I to get g9.9 percent.

The preceding approaches are simple, but both can be poor representations of past

growth. First, the averaging procedure generally produces numbers that are too high.

To illustrate why, suppose sales grew by 100 percent one year and then fell by  50

percent the next year. There would actually be zero growth over the two years, but the

calculated average growth rate would be 25 percent. Similarly, the point-to-point pro-

cedure is not reliable because if either the beginning or ending year is an “outlier” in

the sense of being above or below the trend line shown in Figure 11-1, then the calcu-

lated growth rate will not be representative of past growth. The solution to these

problems is to use a regression approach, where a curve is fitted to the historic sales

data and then the slope of that curve is used to measure historic growth. If we expect a

constant growth rate (as opposed to a constant dollar amount, which would mean a

declining growth rate), then the regression should be based on the natural log of sales,

not sales itself. With a spreadsheet, this is not a difficult calculation, but by far the eas-

iest way to calculate the growth rate is with Excel’s LOGEST function. Simply type

the years and sales into a spreadsheet, click fxon the menu bar, select Statistical func-

tions, and then choose the LOGEST function. Highlight the sales range for the Y

variable and the years range for X in the function dialog box, and then click OK. The

result will be 1 
g, so you finish by subtracting 1 to get the growth rate. For Micro-

Drive, the growth rate is 9.1 percent.^2

(^2) These approaches are demonstrated in the Ch 11 Tool Kit.xls. Also, the Chapter 6 Web Extension illus-
trates these approaches when estimating dividend growth rates.

Financial Planning and Forecasting Financial Statements 409