- Email: [email protected]
Growth rates as measures of financial performance
Journal of
Accounting Education
J. of Acc. Ed. 23 (2005) 67–78
www.elsevier.com/locate/jaccedu
Growth rates as measures of financial performance Laurence R. Paquette
*
Unit A-4, 272 Sweet Allen Farm, Wakefield, RI 02879, United States Received 1 March 2004; accepted 1 February 2005
Abstract This teaching note provides a vehicle to help students understand, calculate, and communicate growth rates as part of the process of financial performance evaluation. The note also makes use of the graphic capabilities and functions available within Excel. Graphics are widely used by companies in their annual reports to highlight financial performance and to focus on change over time. Many companies also report 3, 5, and 10 year growth rates for selected financial data in their annual reports. Alternative approaches for calculating growth rates are presented with a focus on understanding the differences that can exist in the resulting calculated values. The underlying biases and the advantages of each approach are discussed. A project appropriate for classroom use in a Financial Accounting or Financial Statement Analysis course is included in an Appendix. Ó 2005 Elsevier Ltd. All rights reserved.
1. Introduction This teaching note focuses on sales and earnings growth as financial performance measures. Many companies now report selected 3, 5, and 10 year growth rates in the financial highlights section of their annual reports. In addition to *
Tel.: +1 401 789 6724. E-mail address: [email protected].
0748-5751/$ - see front matter Ó 2005 Elsevier Ltd. All rights reserved. doi:10.1016/j.jaccedu.2005.02.002
68
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
finding this summary information in a companyÕs annual report, many popular financial web sites, such as Bloomberg.com, Forbes.com, and MergentOnline.com, provide summary measures for sales and earnings growth rates. The importance of growth rates for sales and earnings is illustrated by the following statements. General Mills in a press release presenting the companyÕs growth model for 2000–2010, included the following goals for the next decade: To generate 7 to 8 percent compound annual sales growth. To generate continued double-digit earnings per share growth. The Motley Fool lists the following when outlining the marks of great companies: Consistent, reliable earnings and sales growth, and robust profit margins. Look for sales and earnings to have increased steadily over past years, reflecting capable management. Penman (2003) argues that investors buy earnings and that they pay more for earnings growth. Although the growth rates of sales and earnings have emerged as valuable measures of financial performance, most accounting texts are deficient in showing students how to calculate these rates. Students, especially those with an accounting or finance concentration, should be equipped to generate and present this information. The Vision Project of the AICPA specifies communication skills and technological proficiency as two of the top five competencies that students should possess. This teaching and educational note attempts to further develop studentsÕ ability to communicate as well as to use current technology to increase their functional knowledge. The graphical and statistical capabilities available in spreadsheets allow for a realistic and enriched approach to studying growth rates. Microsoft Excel is used extensively to both communicate sales and earnings growth and to calculate average and compound growth rates. Because exponential functions can be used to model growth that is characterized by a constant rate, a pattern that many corporations attempt to achieve with sales and earnings results, they are of particular interest when calculating compound growth rates. The remainder of this teaching note is organized in such a way that it serves as a solution to the project included in Appendix A of this teaching note. The project, designed for classroom use, makes use of sales and earnings data extracted from the 2003 Annual Report for Autozone, the number one auto parts chain in the US. The project requires the student to effectively communicate sales and earnings performance graphically and numerically, using average and compound growth rates. The instructor may choose to modify this project by having each student work with a different company, and be responsible for locating the required information on the Internet. One may also want to integrate this material into a broader financial analysis of a company.
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
69
2. Communicating sales and earnings data The use of graphics to communicate key financial information is one of the major goals of this teaching note and is the objective of the first two questions of the project found in Appendix A. In order to give financial information meaning, it must be put in a context that makes it meaningful. One way to do that is to look at information over time, so you know not only where the reporting entity stands at that point in time, but also how it got there. That is one of the objectives of the selected financial data or financial highlights section of a companyÕs annual report. This section of an annual report usually presents key data for a 10-year period and is often accompanied by graphs. Appendix A contains net sales and net income data extracted from the 2003 Autozone annual report. This information can be accessed by clicking on the investorsÕ relations tab of the companyÕs web page at http://www.autozone.com. Although it is not the case for Autozone, many companiesÕ annual reports are now interactive, in that information can be downloaded directly into an Excel spreadsheet. There is no substitute for a well designed graphic to communicate to a reader how sales and earnings data compare across a number of years. When working with time series data, either a column chart or a horizontal line chart would be an appropriate selection to make from the chart types found under the Standard types tab within Excel. When working with two sets of time series data, as is the case here, the Line-Column on Two axis selection found under the Custom types tab allows the use of both types of charts simultaneously, one for each data set. Exhibit 1 is the result of using a line-column on two axis combination chart where columns are plotted $6,000,000
$600,000 Net Income
$5,000,000
$500,000
$4,000,000
$400,000
$3,000,000
$300,000
$2,000,000
$200,000
$1,000,000
$100,000
$0
$0 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 Year
Exhibit 1. The use of a combination chart.
Net Income
Net Sales
Net Sales
70
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
using the primary Y-axis and lines are plotted using the secondary Y-axis. The use of this combination chart allows one to compare the pattern of each time series, and at the same time allows both data sets to make full use of the plot area with no distortion of the growth patterns. Examination of the chart shown in Exhibit 1 reveals that both net sales and net income are growing over time. If net sales and net income are increasing by a constant amount, then a linear function can be used to model growth and one would conclude that the series is growing, but at a decreasing rate. If net sales and net income appear to be growing by increasing amounts over time, then the data series may in fact be increasing at a constant rate and an exponential function can be used to model growth. The topic of growth rates is considered next.
3. Calculating the average annual growth rate The calculation of growth rates to communicate key financial information is the second major goal of this teaching note and is the objective of questions three, four and five of the project found in Appendix A. If one were to ask a student what the annual growth rate for a data series is, they would most likely calculate the average growth rate or AGR. This approach is perhaps the most intuitive, but it should not be confused with a compound growth rate or CGR. Question 3 of the project asks students to compute annual growth relatives for the net sales and net income data series for Autozone. The values shown in columns three and five of Exhibit 2 are called growth relatives because they show net sales or net income in one period (t) relative to net sales or net income in the prior period (t 1). In effect, the growth relative is simply an index number that shows the change in the data value relative to its value in the prior period expressed as a percent. This relation can be expressed as follows: Growth relative for net sales ðPeriod tÞ ¼ 100
Net sales ðPeriod tÞ . Net sales ðPeriod t 1Þ
If one is working with MS Excel, one simply has to write the required formulas for the 1994 growth relatives, and then copy them to the remaining cells. The average of the growth relatives can then be calculated by using the average function and specifying the range of cells containing the annual growth relatives. The results are shown in Exhibit 2. Growth relatives rather than growth rates are being calculated in the table because a growth relative is always greater than zero. This characteristic will be important when calculating compound growth rates in the following section. A growth rate is calculated by first determining the increase in sales [Salest Salest 1] and then dividing by the sales figure of the prior period [Salest 1]. The growth rate for sales can be expressed algebraically as follows: Growth rate ¼
Salest Salest1 Salest Salest1 Salest ¼ ¼ ¼ 1. Salest1 Salest1 Salest1 Salest1
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
71
Exhibit 2. Average of the annual growth relatives.
It should be clear from the above equation that the growth rate is simply the growth relative minus 1. Exhibit 2 reveals an average growth rate of 16.5% for net sales and 25.4% for net income. However, the average or arithmetic mean of the annual growth relatives tends to overstate the actual growth rate. This is best illustrated by considering the following hypothetical data: Year
Sales
Growth relative
2002 2003 2004
$200 $160 $200
– 80% 125%
Average
102.5%
In the above example, at the end of the two-year period sales is the same as beginning sales yet the arithmetic mean is 1.025%. If sales actually increased 2.5% then the ending value for sales would be $200(1.025)(1.025) or $210.125. Calculating a geometric mean or compound growth rate eliminates this shortcoming and is the topic of the next section. 4. Calculating compound growth rates This section of the teaching note is still focused on the importance of growth rates, however attention is now given to compound growth rates rather than
72
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
average growth rates. Two different approaches will be used to calculate or estimate the compound growth rate of net sales and net income and address the fifth and last question of the project included in Appendix A. The first approach to be used is what some might consider a traditional approach and involves computing the geometric mean. Depending on the data type, different formulas are used and alternate formulations are presented. Another approach involves fitting an exponential function to the data. This latter approach considers all of the data points in the series rather than simply fitting a function that considers only the first and last data points in the series. 4.1. Geometric mean – Formulation 1 As discussed in Section 3 of the teaching note, the average annual growth rate or arithmetic mean of the growth relatives can have an upward bias or can overstate the actual growth rate. The geometric mean measures the compound rate of growth over time and is a better measure of the growth rate over multiple periods. The geometric mean can be calculated using the following steps: 1. Multiply each of the growth relatives together. 2. Take the root of the product in step 1. The root number is equal to the number of growth relatives used in step 1. The geometric mean of the growth relatives for net sales would be calculated as follows: pffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffi Geometric mean for sales ¼ 10 ð123.9Þð119.9Þð124.0Þ . . . ð102.5Þ or 1=10
Geometric mean for sales ¼ ½ð123.9Þð119.9Þð124.0Þ . . . ð102.5Þ
.
This calculation can be performed using a calculator that has a power function or yx key. If one is working with an Excel spreadsheet the calculation can be performed using the GEOMEAN function as shown in Exhibit 3. Cells C19 and E19 contain the following formulas: C19 :¼ GEOMEANðC3:C12Þ; E19 :¼ GEOMEANðE3:E12Þ. The compound growth rates of 16.2% and 19.5% for net sales and net income are less than the average annual growth rates of 16.5% and 25.4%. The geometric mean will always be less than the arithmetic mean unless the growth relatives are identical. The greater the variation that exists in the growth relatives, then the greater will be the difference between the average and compound growth rates. The greater spread between the average and compound growth rates for net income than for net sales results from the greater variability of the income data.
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
73
Exhibit 3. Calculating compound growth rates.
4.2. Geometric mean – Formulation 2 Note that when computing the geometric mean in the prior section growth relatives were used in the calculation. The alternative formulation makes use of the original net sales and net income data. The geometric mean or compound growth rate is the rate of growth in net sales or net income that needs to be achieved each and every year to get from point A (1993) to point B (2003). This compound growth rate for net sales can be calculated using the following steps: 1. Divide the ending value for net sales by the beginning value for net sales 2. Take the root of the division performed in step 1. The root number is the number of time periods. pffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffi Geometric mean for net sales ¼ 10 $5; 457; 123=$1; 216; 793 ¼ 116.1916%. The cells C23 and E23 in Exhibit 3 contain the following formulas: C23 :¼ ðB12=B2Þ ^ ð1=10Þ; E23 :¼ ðD12=D2Þ ^ ð1=10Þ. Note that both formulations yield equal results, which will always be the case. This second or shortcut formulation informs us that to get from a sales figure in 1993 of $1,216,793 to a sales figure of $5,457,123 in 2003, a company would have to achieve a compound annual growth of 16.192% for the 10 years.
74
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
Sales in 2003 ¼ $1; 216; 793ð1.16192Þð1.16192Þð1.16192Þ . . . ð1.16192Þ ¼ $1; 216; 793ð1.16192Þ10 ¼ $1; 216; 793ð4.484998Þ ¼ $5; 457; 314 *
Discrepancy due to rounding. More importantly, this shortcut formulation using the original sales data, points out the fact that the geometric mean is determined by the first and last data points, as well as the number of time periods. The values of the intermediate data points have no impact on the calculation of the compound growth rates using this approach and are in fact ignored in arriving at a rate that is supposed to be descriptive of the 10year period. Although the first formulation appears to consider the individual data points, it is simply one of many ways to get from point A to point B. The next approach that is used to estimate the compound growth rate will consider all data points. 4.3. Exponential fit In this section, we estimate the compound annual growth rate by fitting an exponential function to the data sets for net sales and net income using regression. In the previous section we saw that to get from a sales figure of $1,216,793 in 1993 to a sales figure of $5,457,123 in 2003, a compound annual growth rate of 16.192% had to be achieved for each of the 10 years. This was expressed algebraically as: Sales in 2003 ¼ $1; 216; 793ð1.16192Þ10 . The above equation is an exponential function, which is commonly used to model situations when there is a constant rate of change. The general form of an exponential function is y = (a)(b)x, where (a) and (b) are constants. Perhaps the most familiar application of this function is for modeling the growth of an amount of money assuming compound interest. In that application, the coefficient (a) represents the initial amount of money or the principal (P) and the coefficient (b) represents the growth relative (1 + r), where r is the interest rate or the rate by which the principal will grow from year to year. In our particular case, the coefficient (a) represents the initial net sales or net income figure and the coefficient (b) is the growth relative (1 + r) where r is simply the growth rate. If it is determined that the data series behaves in a manner such that an exponential function would be an appropriate model; i.e., a constant growth rate, then regression can be used to arrive at an exponential function that best fits the data. Examination of the annual growth relatives in Exhibit 3 indicates that an exponential function would not be an appropriate choice to model either data series. However, it is important to know how to execute this approach when it is relevant and this is presented next. The process of arriving at the exponential function that best fits the data used to be a fairly tedious process. However, the analysis tools available with MS Excel
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
75
have made the process quite simple. In fact, the approach used below will yield the best fitting function without one even being aware of the tedious mathematics of regression analysis. All that is required is to request that a trend line be added to the graph with the equation displayed. The following steps can be followed to fit an exponential trend line to the data set and estimate the compound growth rate: 1. Fit an exponential trend line to your data. Prepare an XY plot for both net sales and net income. Left click on a data point to make the data set active. Right click and choose Add TrendLine from the menu. Select the Exponential choice under the Type tab. Next click on the Options tab and check the boxes Display equation on chart and Display R squared value on chart. Click on OK. 2. Calculate the growth rate. Identify the coefficient b of the exponential function that results. Calculate the value of b. Note e is a mathematical constant equal to 2.7183. . . When attempting to estimate the growth rate one must be aware that b = (1 + r), where r is the growth rate. Solve for r by subtracting 1 from b. Exhibit 4 shows the result of applying this two part process to the net sales and net income data for Autozone.
Exhibit 4. Fitting an exponential function.
76
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
Note that the results here indicate a 17.0% compound growth rate for net sales and a 15.7% compound growth rate for net income. It should be noted that the regression result for the growth rate of the net income data series was less than when the geometric mean was used to determine the compound growth rate. This is most likely due to the low value for net income in 2001. Although the regresYear (t) 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003
Net Income $86,935 $116,386 $138,781 $167,165 $195,008 $227,903 $244,783 $267,590 $175,526 $428,148 $517,604
Grow at 25.4% $86,935 $109,016 $136,707 $171,430 $214,973 $269,577 $338,049 $423,914 $531,588 $666,611 $835,930
Grow at 19.5% $86,935 $103,887 $124,145 $148,354 $177,283 $211,853 $253,164 $302,531 $361,525 $432,022 $516,266
Grow at 15.7% $86,935 $100,584 $116,375 $134,646 $155,786 $180,244 $208,543 $241,284 $279,165 $322,994 $373,704
$600,000 $500,000 $400,000
Net Income Grow at 25.4%
$300,000 $200,000 $100,000 $0 1990
1995
2000
2005
$600,000 $500,000 $400,000
Net Income
$300,000
Grow at 19.5%
$200,000 $100,000 $0 1990
1995
2000
2005
$600,000 $500,000 $400,000
Net Income
$300,000
Grow at 15.7%
$200,000 $100,000 $0 1990
1995
2000
2005
Exhibit 5. Actual versus projected net income using the three growth rates.
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
77
sion process used in arriving at these results is not readily observed, the reader should be aware that each and every data point is used when estimating the compound growth rate using this approach. This is in marked contrast to the approach of computing a geometric mean, where only the first and last data points of a data series are used.
5. Evaluation of the different approaches In Exhibit 5, the actual data series for net income is compared to the projected series that result when using the growth rates arrived at from each of the three different approaches used to estimate growth rates. It should be evident when comparing the three different projections in Exhibit 5, that the compound growth rate computed using the approach of calculating the geometric mean, i.e., 19.5% does the best job of reflecting reality for this data set. The reader is cautioned, however, that this rate is determined by the first and last data points in a series. In effect, management can significantly impact the growth rate by managing the value that they are currently reporting for net income. If the last data point for net income is uncharacteristically large, then perhaps a better approach would be to determine the growth rate by fitting an exponential function to the data series, in that this approach attends to all the data points in the series. It is also quite apparent that the projections made using the rate arrived at by averaging the annual growth rates, i.e., 25.4% in Exhibit 5, result in very unrealistic projections for net income. This confirms the tendency of this approach to have an upward bias especially with a data series that exhibits considerable variability.
6. Summary In this teaching note, graphics are suggested as a method to communicate the growth of net sales and net income for a company and then three different approaches of calculating growth rates were outlined. These three approaches yielded three different growth rate values for both data series. As a reader of financial information, one should be aware of these discrepancies in that there may be a bias to report the highest possible growth rate, given that they are key performance measures used to evaluate management. In addition, individuals often either mistakenly or deliberately report the average growth rate when reporting the compound growth rate.
Appendix A. Project for classroom use The following information on net sales and net income has been extracted from the 10 year selected financial data section of the 2003 annual report for Autozone, the USÕs #1 auto parts chain. (See company web page at http://www.Autozone.com).
78
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
Year (t)
Net sales ($000)
Net income ($000)
1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003
$1,216,793 $1,508,029 $1,808,131 $2,242,633 $2,691,440 $3,242,922 $4,116,392 $4,482,696 $4,818,185 $5,325,510 $5,457,123
$86,935 $116,386 $138,781 $167,165 $195,008 $227,903 $244,783 $267,590 $175,526 $428,148 $517,604
1. Use the graphic chart wizard available in MS Excel to design a chart/graph that you feel best communicates the above financial information. You may find the (Line-Column on two axis) under the custom types tab helpful for this data set. 2. Identify the pattern that best describes net sales and net income. 3. Calculate the yearly growth relatives for both net sales and net income. A worksheet similar to the one shown below may be helpful. Year (t)
Net sales ($000)
Salest/ Salest 1
Net income ($000)
Net incomet/ net incomet 1
1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003
$1,216,793 $1,508,029 $1,808,131 $2,242,633 $2,691,440 $3,242,922 $4,116,392 $4,482,696 $4,818,185 $5,325,510 $5,457,123
NA
$86,935 $116,386 $138,781 $167,165 $195,008 $227,903 $244,783 $267,590 $175,526 $428,148 $517,604
NA
4. What is the average growth rate for net sales and net income over the 10 years? 5. Compute the compound growth rate for net sales and net income over the 10 years.
Reference Penman, S. H. (2003). The quality of financial statements: perspectives from the recent stock market bubble. Accounting Horizons, 17(Suppl.), 77–96.
Accounting Education
J. of Acc. Ed. 23 (2005) 67–78
www.elsevier.com/locate/jaccedu
Growth rates as measures of financial performance Laurence R. Paquette
*
Unit A-4, 272 Sweet Allen Farm, Wakefield, RI 02879, United States Received 1 March 2004; accepted 1 February 2005
Abstract This teaching note provides a vehicle to help students understand, calculate, and communicate growth rates as part of the process of financial performance evaluation. The note also makes use of the graphic capabilities and functions available within Excel. Graphics are widely used by companies in their annual reports to highlight financial performance and to focus on change over time. Many companies also report 3, 5, and 10 year growth rates for selected financial data in their annual reports. Alternative approaches for calculating growth rates are presented with a focus on understanding the differences that can exist in the resulting calculated values. The underlying biases and the advantages of each approach are discussed. A project appropriate for classroom use in a Financial Accounting or Financial Statement Analysis course is included in an Appendix. Ó 2005 Elsevier Ltd. All rights reserved.
1. Introduction This teaching note focuses on sales and earnings growth as financial performance measures. Many companies now report selected 3, 5, and 10 year growth rates in the financial highlights section of their annual reports. In addition to *
Tel.: +1 401 789 6724. E-mail address: [email protected].
0748-5751/$ - see front matter Ó 2005 Elsevier Ltd. All rights reserved. doi:10.1016/j.jaccedu.2005.02.002
68
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
finding this summary information in a companyÕs annual report, many popular financial web sites, such as Bloomberg.com, Forbes.com, and MergentOnline.com, provide summary measures for sales and earnings growth rates. The importance of growth rates for sales and earnings is illustrated by the following statements. General Mills in a press release presenting the companyÕs growth model for 2000–2010, included the following goals for the next decade: To generate 7 to 8 percent compound annual sales growth. To generate continued double-digit earnings per share growth. The Motley Fool lists the following when outlining the marks of great companies: Consistent, reliable earnings and sales growth, and robust profit margins. Look for sales and earnings to have increased steadily over past years, reflecting capable management. Penman (2003) argues that investors buy earnings and that they pay more for earnings growth. Although the growth rates of sales and earnings have emerged as valuable measures of financial performance, most accounting texts are deficient in showing students how to calculate these rates. Students, especially those with an accounting or finance concentration, should be equipped to generate and present this information. The Vision Project of the AICPA specifies communication skills and technological proficiency as two of the top five competencies that students should possess. This teaching and educational note attempts to further develop studentsÕ ability to communicate as well as to use current technology to increase their functional knowledge. The graphical and statistical capabilities available in spreadsheets allow for a realistic and enriched approach to studying growth rates. Microsoft Excel is used extensively to both communicate sales and earnings growth and to calculate average and compound growth rates. Because exponential functions can be used to model growth that is characterized by a constant rate, a pattern that many corporations attempt to achieve with sales and earnings results, they are of particular interest when calculating compound growth rates. The remainder of this teaching note is organized in such a way that it serves as a solution to the project included in Appendix A of this teaching note. The project, designed for classroom use, makes use of sales and earnings data extracted from the 2003 Annual Report for Autozone, the number one auto parts chain in the US. The project requires the student to effectively communicate sales and earnings performance graphically and numerically, using average and compound growth rates. The instructor may choose to modify this project by having each student work with a different company, and be responsible for locating the required information on the Internet. One may also want to integrate this material into a broader financial analysis of a company.
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
69
2. Communicating sales and earnings data The use of graphics to communicate key financial information is one of the major goals of this teaching note and is the objective of the first two questions of the project found in Appendix A. In order to give financial information meaning, it must be put in a context that makes it meaningful. One way to do that is to look at information over time, so you know not only where the reporting entity stands at that point in time, but also how it got there. That is one of the objectives of the selected financial data or financial highlights section of a companyÕs annual report. This section of an annual report usually presents key data for a 10-year period and is often accompanied by graphs. Appendix A contains net sales and net income data extracted from the 2003 Autozone annual report. This information can be accessed by clicking on the investorsÕ relations tab of the companyÕs web page at http://www.autozone.com. Although it is not the case for Autozone, many companiesÕ annual reports are now interactive, in that information can be downloaded directly into an Excel spreadsheet. There is no substitute for a well designed graphic to communicate to a reader how sales and earnings data compare across a number of years. When working with time series data, either a column chart or a horizontal line chart would be an appropriate selection to make from the chart types found under the Standard types tab within Excel. When working with two sets of time series data, as is the case here, the Line-Column on Two axis selection found under the Custom types tab allows the use of both types of charts simultaneously, one for each data set. Exhibit 1 is the result of using a line-column on two axis combination chart where columns are plotted $6,000,000
$600,000 Net Income
$5,000,000
$500,000
$4,000,000
$400,000
$3,000,000
$300,000
$2,000,000
$200,000
$1,000,000
$100,000
$0
$0 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 Year
Exhibit 1. The use of a combination chart.
Net Income
Net Sales
Net Sales
70
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
using the primary Y-axis and lines are plotted using the secondary Y-axis. The use of this combination chart allows one to compare the pattern of each time series, and at the same time allows both data sets to make full use of the plot area with no distortion of the growth patterns. Examination of the chart shown in Exhibit 1 reveals that both net sales and net income are growing over time. If net sales and net income are increasing by a constant amount, then a linear function can be used to model growth and one would conclude that the series is growing, but at a decreasing rate. If net sales and net income appear to be growing by increasing amounts over time, then the data series may in fact be increasing at a constant rate and an exponential function can be used to model growth. The topic of growth rates is considered next.
3. Calculating the average annual growth rate The calculation of growth rates to communicate key financial information is the second major goal of this teaching note and is the objective of questions three, four and five of the project found in Appendix A. If one were to ask a student what the annual growth rate for a data series is, they would most likely calculate the average growth rate or AGR. This approach is perhaps the most intuitive, but it should not be confused with a compound growth rate or CGR. Question 3 of the project asks students to compute annual growth relatives for the net sales and net income data series for Autozone. The values shown in columns three and five of Exhibit 2 are called growth relatives because they show net sales or net income in one period (t) relative to net sales or net income in the prior period (t 1). In effect, the growth relative is simply an index number that shows the change in the data value relative to its value in the prior period expressed as a percent. This relation can be expressed as follows: Growth relative for net sales ðPeriod tÞ ¼ 100
Net sales ðPeriod tÞ . Net sales ðPeriod t 1Þ
If one is working with MS Excel, one simply has to write the required formulas for the 1994 growth relatives, and then copy them to the remaining cells. The average of the growth relatives can then be calculated by using the average function and specifying the range of cells containing the annual growth relatives. The results are shown in Exhibit 2. Growth relatives rather than growth rates are being calculated in the table because a growth relative is always greater than zero. This characteristic will be important when calculating compound growth rates in the following section. A growth rate is calculated by first determining the increase in sales [Salest Salest 1] and then dividing by the sales figure of the prior period [Salest 1]. The growth rate for sales can be expressed algebraically as follows: Growth rate ¼
Salest Salest1 Salest Salest1 Salest ¼ ¼ ¼ 1. Salest1 Salest1 Salest1 Salest1
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
71
Exhibit 2. Average of the annual growth relatives.
It should be clear from the above equation that the growth rate is simply the growth relative minus 1. Exhibit 2 reveals an average growth rate of 16.5% for net sales and 25.4% for net income. However, the average or arithmetic mean of the annual growth relatives tends to overstate the actual growth rate. This is best illustrated by considering the following hypothetical data: Year
Sales
Growth relative
2002 2003 2004
$200 $160 $200
– 80% 125%
Average
102.5%
In the above example, at the end of the two-year period sales is the same as beginning sales yet the arithmetic mean is 1.025%. If sales actually increased 2.5% then the ending value for sales would be $200(1.025)(1.025) or $210.125. Calculating a geometric mean or compound growth rate eliminates this shortcoming and is the topic of the next section. 4. Calculating compound growth rates This section of the teaching note is still focused on the importance of growth rates, however attention is now given to compound growth rates rather than
72
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
average growth rates. Two different approaches will be used to calculate or estimate the compound growth rate of net sales and net income and address the fifth and last question of the project included in Appendix A. The first approach to be used is what some might consider a traditional approach and involves computing the geometric mean. Depending on the data type, different formulas are used and alternate formulations are presented. Another approach involves fitting an exponential function to the data. This latter approach considers all of the data points in the series rather than simply fitting a function that considers only the first and last data points in the series. 4.1. Geometric mean – Formulation 1 As discussed in Section 3 of the teaching note, the average annual growth rate or arithmetic mean of the growth relatives can have an upward bias or can overstate the actual growth rate. The geometric mean measures the compound rate of growth over time and is a better measure of the growth rate over multiple periods. The geometric mean can be calculated using the following steps: 1. Multiply each of the growth relatives together. 2. Take the root of the product in step 1. The root number is equal to the number of growth relatives used in step 1. The geometric mean of the growth relatives for net sales would be calculated as follows: pffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffi Geometric mean for sales ¼ 10 ð123.9Þð119.9Þð124.0Þ . . . ð102.5Þ or 1=10
Geometric mean for sales ¼ ½ð123.9Þð119.9Þð124.0Þ . . . ð102.5Þ
.
This calculation can be performed using a calculator that has a power function or yx key. If one is working with an Excel spreadsheet the calculation can be performed using the GEOMEAN function as shown in Exhibit 3. Cells C19 and E19 contain the following formulas: C19 :¼ GEOMEANðC3:C12Þ; E19 :¼ GEOMEANðE3:E12Þ. The compound growth rates of 16.2% and 19.5% for net sales and net income are less than the average annual growth rates of 16.5% and 25.4%. The geometric mean will always be less than the arithmetic mean unless the growth relatives are identical. The greater the variation that exists in the growth relatives, then the greater will be the difference between the average and compound growth rates. The greater spread between the average and compound growth rates for net income than for net sales results from the greater variability of the income data.
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
73
Exhibit 3. Calculating compound growth rates.
4.2. Geometric mean – Formulation 2 Note that when computing the geometric mean in the prior section growth relatives were used in the calculation. The alternative formulation makes use of the original net sales and net income data. The geometric mean or compound growth rate is the rate of growth in net sales or net income that needs to be achieved each and every year to get from point A (1993) to point B (2003). This compound growth rate for net sales can be calculated using the following steps: 1. Divide the ending value for net sales by the beginning value for net sales 2. Take the root of the division performed in step 1. The root number is the number of time periods. pffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffi Geometric mean for net sales ¼ 10 $5; 457; 123=$1; 216; 793 ¼ 116.1916%. The cells C23 and E23 in Exhibit 3 contain the following formulas: C23 :¼ ðB12=B2Þ ^ ð1=10Þ; E23 :¼ ðD12=D2Þ ^ ð1=10Þ. Note that both formulations yield equal results, which will always be the case. This second or shortcut formulation informs us that to get from a sales figure in 1993 of $1,216,793 to a sales figure of $5,457,123 in 2003, a company would have to achieve a compound annual growth of 16.192% for the 10 years.
74
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
Sales in 2003 ¼ $1; 216; 793ð1.16192Þð1.16192Þð1.16192Þ . . . ð1.16192Þ ¼ $1; 216; 793ð1.16192Þ10 ¼ $1; 216; 793ð4.484998Þ ¼ $5; 457; 314 *
Discrepancy due to rounding. More importantly, this shortcut formulation using the original sales data, points out the fact that the geometric mean is determined by the first and last data points, as well as the number of time periods. The values of the intermediate data points have no impact on the calculation of the compound growth rates using this approach and are in fact ignored in arriving at a rate that is supposed to be descriptive of the 10year period. Although the first formulation appears to consider the individual data points, it is simply one of many ways to get from point A to point B. The next approach that is used to estimate the compound growth rate will consider all data points. 4.3. Exponential fit In this section, we estimate the compound annual growth rate by fitting an exponential function to the data sets for net sales and net income using regression. In the previous section we saw that to get from a sales figure of $1,216,793 in 1993 to a sales figure of $5,457,123 in 2003, a compound annual growth rate of 16.192% had to be achieved for each of the 10 years. This was expressed algebraically as: Sales in 2003 ¼ $1; 216; 793ð1.16192Þ10 . The above equation is an exponential function, which is commonly used to model situations when there is a constant rate of change. The general form of an exponential function is y = (a)(b)x, where (a) and (b) are constants. Perhaps the most familiar application of this function is for modeling the growth of an amount of money assuming compound interest. In that application, the coefficient (a) represents the initial amount of money or the principal (P) and the coefficient (b) represents the growth relative (1 + r), where r is the interest rate or the rate by which the principal will grow from year to year. In our particular case, the coefficient (a) represents the initial net sales or net income figure and the coefficient (b) is the growth relative (1 + r) where r is simply the growth rate. If it is determined that the data series behaves in a manner such that an exponential function would be an appropriate model; i.e., a constant growth rate, then regression can be used to arrive at an exponential function that best fits the data. Examination of the annual growth relatives in Exhibit 3 indicates that an exponential function would not be an appropriate choice to model either data series. However, it is important to know how to execute this approach when it is relevant and this is presented next. The process of arriving at the exponential function that best fits the data used to be a fairly tedious process. However, the analysis tools available with MS Excel
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
75
have made the process quite simple. In fact, the approach used below will yield the best fitting function without one even being aware of the tedious mathematics of regression analysis. All that is required is to request that a trend line be added to the graph with the equation displayed. The following steps can be followed to fit an exponential trend line to the data set and estimate the compound growth rate: 1. Fit an exponential trend line to your data. Prepare an XY plot for both net sales and net income. Left click on a data point to make the data set active. Right click and choose Add TrendLine from the menu. Select the Exponential choice under the Type tab. Next click on the Options tab and check the boxes Display equation on chart and Display R squared value on chart. Click on OK. 2. Calculate the growth rate. Identify the coefficient b of the exponential function that results. Calculate the value of b. Note e is a mathematical constant equal to 2.7183. . . When attempting to estimate the growth rate one must be aware that b = (1 + r), where r is the growth rate. Solve for r by subtracting 1 from b. Exhibit 4 shows the result of applying this two part process to the net sales and net income data for Autozone.
Exhibit 4. Fitting an exponential function.
76
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
Note that the results here indicate a 17.0% compound growth rate for net sales and a 15.7% compound growth rate for net income. It should be noted that the regression result for the growth rate of the net income data series was less than when the geometric mean was used to determine the compound growth rate. This is most likely due to the low value for net income in 2001. Although the regresYear (t) 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003
Net Income $86,935 $116,386 $138,781 $167,165 $195,008 $227,903 $244,783 $267,590 $175,526 $428,148 $517,604
Grow at 25.4% $86,935 $109,016 $136,707 $171,430 $214,973 $269,577 $338,049 $423,914 $531,588 $666,611 $835,930
Grow at 19.5% $86,935 $103,887 $124,145 $148,354 $177,283 $211,853 $253,164 $302,531 $361,525 $432,022 $516,266
Grow at 15.7% $86,935 $100,584 $116,375 $134,646 $155,786 $180,244 $208,543 $241,284 $279,165 $322,994 $373,704
$600,000 $500,000 $400,000
Net Income Grow at 25.4%
$300,000 $200,000 $100,000 $0 1990
1995
2000
2005
$600,000 $500,000 $400,000
Net Income
$300,000
Grow at 19.5%
$200,000 $100,000 $0 1990
1995
2000
2005
$600,000 $500,000 $400,000
Net Income
$300,000
Grow at 15.7%
$200,000 $100,000 $0 1990
1995
2000
2005
Exhibit 5. Actual versus projected net income using the three growth rates.
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
77
sion process used in arriving at these results is not readily observed, the reader should be aware that each and every data point is used when estimating the compound growth rate using this approach. This is in marked contrast to the approach of computing a geometric mean, where only the first and last data points of a data series are used.
5. Evaluation of the different approaches In Exhibit 5, the actual data series for net income is compared to the projected series that result when using the growth rates arrived at from each of the three different approaches used to estimate growth rates. It should be evident when comparing the three different projections in Exhibit 5, that the compound growth rate computed using the approach of calculating the geometric mean, i.e., 19.5% does the best job of reflecting reality for this data set. The reader is cautioned, however, that this rate is determined by the first and last data points in a series. In effect, management can significantly impact the growth rate by managing the value that they are currently reporting for net income. If the last data point for net income is uncharacteristically large, then perhaps a better approach would be to determine the growth rate by fitting an exponential function to the data series, in that this approach attends to all the data points in the series. It is also quite apparent that the projections made using the rate arrived at by averaging the annual growth rates, i.e., 25.4% in Exhibit 5, result in very unrealistic projections for net income. This confirms the tendency of this approach to have an upward bias especially with a data series that exhibits considerable variability.
6. Summary In this teaching note, graphics are suggested as a method to communicate the growth of net sales and net income for a company and then three different approaches of calculating growth rates were outlined. These three approaches yielded three different growth rate values for both data series. As a reader of financial information, one should be aware of these discrepancies in that there may be a bias to report the highest possible growth rate, given that they are key performance measures used to evaluate management. In addition, individuals often either mistakenly or deliberately report the average growth rate when reporting the compound growth rate.
Appendix A. Project for classroom use The following information on net sales and net income has been extracted from the 10 year selected financial data section of the 2003 annual report for Autozone, the USÕs #1 auto parts chain. (See company web page at http://www.Autozone.com).
78
L.R. Paquette / J. of Acc. Ed. 23 (2005) 67–78
Year (t)
Net sales ($000)
Net income ($000)
1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003
$1,216,793 $1,508,029 $1,808,131 $2,242,633 $2,691,440 $3,242,922 $4,116,392 $4,482,696 $4,818,185 $5,325,510 $5,457,123
$86,935 $116,386 $138,781 $167,165 $195,008 $227,903 $244,783 $267,590 $175,526 $428,148 $517,604
1. Use the graphic chart wizard available in MS Excel to design a chart/graph that you feel best communicates the above financial information. You may find the (Line-Column on two axis) under the custom types tab helpful for this data set. 2. Identify the pattern that best describes net sales and net income. 3. Calculate the yearly growth relatives for both net sales and net income. A worksheet similar to the one shown below may be helpful. Year (t)
Net sales ($000)
Salest/ Salest 1
Net income ($000)
Net incomet/ net incomet 1
1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003
$1,216,793 $1,508,029 $1,808,131 $2,242,633 $2,691,440 $3,242,922 $4,116,392 $4,482,696 $4,818,185 $5,325,510 $5,457,123
NA
$86,935 $116,386 $138,781 $167,165 $195,008 $227,903 $244,783 $267,590 $175,526 $428,148 $517,604
NA
4. What is the average growth rate for net sales and net income over the 10 years? 5. Compute the compound growth rate for net sales and net income over the 10 years.
Reference Penman, S. H. (2003). The quality of financial statements: perspectives from the recent stock market bubble. Accounting Horizons, 17(Suppl.), 77–96.