|Microsoft Office Excel 2003
This article was adapted from Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston. Visit Microsoft Learning to learn more about this book.
This classroom-style book was developed from a series of presentations by Wayne Winston, a well known statistician and business professor who specializes in creative, practical applications of Excel. So be prepared — you may need to put your thinking cap on.
In this article
Sample files You can download the sample files that relate to excerpts from Microsoft Excel Data Analysis and Business Modeling from Microsoft Office Online. This article uses the files CiscoExpo.xls and ExponentialData.xls.
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 on the x-axis the year. (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 whenever 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 = aebx. Here x is the value of the independent variable (in this example, the year), while y is the value of the dependent variable (in this case, annual revenue). The number e (approximately 2.7182) is the base of natural logarithms. If we select Exponential from the Excel trendline options, Excel calculates the values of a and b that best fit the data. Let’s look at an example.
The file CiscoExpo.xls, shown in the following figure, contains the revenues for Cisco for the years 1990–1999. All revenues are in millions of dollars. In 1990, for example, Cisco’s revenues were $103.47 million.
To fit an exponential curve to this data, begin by selecting the cell range A3:B13. Next, click Chart on the Insert menu (or click the Chart Wizard button). To create the scatter plot shown in the following figure, I selected the first chart subtype option for the xy (scatter) chart type.
Fitting a straight line to this data would be ridiculous. The slope of the graph is rapidly increasing. In this situation, 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 of the points will turn gold), and then click Add Trendline. In the Add Trendline dialog box, select the Exponential option on the Type tab, and then click the Options tab. Select the options Display Equation On Chart and Display R-Squared Value On Chart. After you click OK, you’ll see the trend curve shown in the following figure.
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 predicted 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 R2 value (.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.4 percent per year, which is the best estimate of Cisco’s annual growth rate for the years 1990–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 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 you use x = 1 instead of x = 1990? If we used x = 1990, Excel would have to juggle numbers around the size of e1990. A number this large causes Excel a great deal of difficulty.
Top of Page
The file ExponentialData.xls contains annual sales revenue for Staples, Wal-Mart, and Intel. Use this data to work through the following problems.
- For each company, fit an exponential trend curve to their sales data.
- For which company does exponential growth have the best fit to revenue growth?
- For which company does exponential growth have the worst fit to revenue growth?
- For each company, estimate the annual percentage growth rate for revenues.
- For each company, use your trend curve to predict 2003 revenues.
Top of Page