MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

• How can I model the growth of a company’s revenue over time?

If you want to value a company, it’s important to have some idea about its future revenues. Although the future may not be like the past, we often begin a valuation analysis of a corporation by studying the company’s revenue growth during the recent past. Many analysts like to fit a trend curve to recent revenue growth. To fit a trend curve, you plot the year on the x-axis. (For example, the first year of data is year 1, the second year of data is year 2, and so on.) On the y-axis, you plot the company’s revenue.

Usually, the relationship between time and revenue will not be a straight line. Recall that a straight line always has the same slope, which implies that when the independent variable (in this case, year) is increased by 1, our prediction for the dependent variable (revenue) increases by the same amount. For most companies, revenue grows by a fairly constant percentage each year. If this is the case, as revenue increases, the annual increase in revenue will also increase. After all, revenue growth of 10 percent of $1 million means revenue grows by $100,000. Revenue growth of 10 percent of $100 million means revenue grows by $10 million. This analysis implies that a trend curve for forecasting revenue should grow more steeply and have an increasing slope. The exponential function has the property that as the independent variable increases by 1, the dependent variable increases by the same percentage. This relationship is exactly what we need to model revenue growth.

The equation for the exponential function is y=ae^bx. Here, x is the value of the independent variable (in this example, the year), whereas y is the value of the dependent variable (in this case, annual revenue). The value e (approximately 2.7182) is the base of natural logarithms. If we select Exponential from the Microsoft Office Excel 2007 trendline options, Excel calculates the values of a and b that best fit the data. Let’s look at an example.

• How can I model the growth of a company’s revenue over time?

• The file Ciscoexpo.xlsx, shown in Figure 44-1 on the next page, contains the revenues for Cisco for the years 1990 through 1999. All revenues are in millions of dollars. In 1990, for example, Cisco’s revenues were $103.47 million.

  Figure 44-1: Cisco’s annual revenues for the years 1990 through 1999

• To fit an exponential curve to this data, begin by selecting the cell range A3:B13. Next, on the Insert tab in the Charts group, click Scatter. Selecting the first chart option (Scatter With Only Markers) creates the chart shown in Figure 44-2.

  Figure 44-2: Scatter plot for the Cisco trend curve

• Fitting a straight line to this data would be ridiculous. When a graph’s slope is rapidly increasing as in this example, an exponential growth will usually provide a good fit to the data.

• To obtain the exponential curve that best fits this data, right-click a data point (all the points turn blue), and then click Add Trendline. In the Format Trendline dialog box, select the Exponential option in the Trendline Options section, and check the Display Equation On Chart and Display R-Squared Value On Chart boxes. After you click Close, you’ll see the trend curve shown in Figure 44-3.

  Figure 44-3: Exponential trend curve for Cisco revenues

• Our estimate of Cisco’s revenue in year x (remember that x=1 is the year 1990) is computed from the formula

  Estimated Revenue=58.552664^*e^.569367x

• I computed estimated revenue in the cell range C4:C13 by copying from C4 to C5:C13 the formula =58.552664*EXP(0.569367*A4). For example, our estimate of Cisco’s revenue in 1999 (year 10) is $17.389 billion.

• Notice that most of the data points are very close to the fitted exponential curve. This pattern indicates that exponential growth does a good job of explaining Cisco’s revenue growth during the 1990s. The fact that the R² value (0.98) is very close to 1 is also consistent with the visual evidence of a good fit.

• Remember that whenever x increases by 1, the estimate from an exponential curve increases by the same percentage. We can verify this fact by computing the ratio of each year’s estimated revenue to the previous year’s estimated revenue. To compute this ratio, copy from D5 to D6:D13 the formula=C5/C4. We find that our estimate of Cisco’s growth rate is 76.7 percent per year, which is the best estimate of Cisco’s annual growth rate for the years 1990 through 1999.

• Of course, to use this estimated annual revenue growth rate in a valuation analysis, we need to ask ourselves whether it’s likely that this growth rate can be maintained. Be forewarned that exponential growth cannot continue forever. For example, if we use our exponential trend curve to forecast revenues for 2005 (year 16), we would predict Cisco’s 2005 revenues to be $530 billion. If this estimate were realized, Cisco’s revenues would be triple the 2002 revenues of the world’s largest company (Wal-Mart). This seems highly unrealistic. The moral is that during its early years, the revenue growth for a technology company follows exponential growth. After a while, however, the growth rate slows down. If Wall Street analysts had understood this fact during the late 1990s, the Internet stock bubble might have been avoided.

• Note that during 1999, Cisco’s actual revenue fell well short of the trend curve’s estimated revenue. This fact may well have indicated the start of the technology slowdown, which began during late 2000.

• By the way, why must we use x=1 instead of x=1990? If we used x=1990, Excel would have to juggle numbers around the size of e¹⁹⁹⁰. A number this large causes Excel a great deal of difficulty.