How to Calculate Sales Growth in Excel?

Sales growth is a crucial measure of business performance, showing the percentage increase or decrease in revenue over a particular period. In Excel, calculating sales growth can be achieved through a two-step process, which involves determining absolute growth in dollars and then dividing it by the revenue in the prior year to get the percentage growth. This article provides a step-by-step guide on how to calculate sales growth in Excel.

Step 1: Determine Absolute Growth in Dollars

To calculate absolute growth in dollars, we need to use revenue data and apply a formula for each year. In this example, we'll use Tesla's revenue data from 2012 through 2022, as shown in millions of dollars. To calculate 10 growth rates, we require 11 data points, so ensure that we have all the necessary data.

For 2013 versus 2012, the formula is B4 – B3, where B4 represents the current year amount, and B3 represents the prior year amount. Use this formula for all the other years by double-clicking on the bottom right of cell C4 to copy the formula into the cells below it.

Step 2: Calculate Sales Growth Percentage

The percentage growth formula is obtained by dividing the revenue growth in dollars by the prior year's revenue. In our example, the formula for the percentage growth in the first row is C4 divided by B3. We can display the formula as a percentage by clicking the percent style button on the Home tab of Excel. Double click the bottom right of cell E4 to copy that formula into all the cells of column E.

Using the FORMULATEXT function in Excel, we can display the formula for each year and check for errors. To clear the Excel error messages, we can click ignore error.

Alternatively, we can use a one-step method of calculating sales growth percentage. This method involves running the open parentheses formula for the first row, B4 minus B3, close parentheses, divided by B3. This formula should yield the same percentages in column G as in column E.

Interpreting Sales Growth Rates

Understanding sales growth rates is essential to determine business performance. In examining the growth rates, we can determine whether they are above, at, or below average. As shown in the example, the growth rates in column E for Tesla range from 15% in 2019 to 387% in 2013.

Average Annual Growth Rate (AAGR)

The AAGR is the simple average of the ten growth rates in cells E4 through E13, which is 86% in this example.

Compound Annual Growth Rate (CAGR)

The CAGR is the compound rate of return over a period of time, which uses the starting point and ending point to calculate the exponential growth rate in ten equal steps. To calculate CAGR in Excel, we can use the function POWER.

Using the Excel function POWER, we can fill in the two function arguments; the number is B13 divided by B3, and the power is 1 divided by 10. After hitting OK, we then deduct 1 from the result to arrive at a CAGR of 70%.

The AAGR of 86% is higher than the CAGR of 70%, indicating that one or more outliers skew the AAGR. In this example, the outlier is the 387% growth rate from 2012 to 2013.

Once aware of how AAGR differs from CAGR, we can make informed interpretations of sales growth numbers.

Simulating Revenue with a Constant Sales Growth Rate

Finally, we can use the simulated revenue to get the effect of the outlier of 387% growth at the start of the data set. By calculating the revenue growth that would have been at a constant 70% each year and using it to simulate revenue, we can compare the actual numbers with the simulated ones.

By multiplying the prior year revenue by 1.7 (1 + 70%), we can simulate the sales growth for each year. As shown in the example, the actual revenue numbers are higher than the simulated figures up until 2022 when the simulated figures finally catch up.

Conclusion

Excel provides a robust platform for calculating sales growth, using either a two-step or one-step method. As shown in this article, we can determine the absolute growth in dollars, the percentage growth, and interpret the sales growth rates. Additionally, we can calculate the AAGR and CAGR and simulate revenue to get the effect of outliers. These methods provide valuable insights into business performance and can form the basis for informed decision-making.