How to calculate sales growth in Excel? As the basis for our growth calculations, let’s use the numbers located over here: the revenue data for Tesla from 2012 through 2022, shown in millions of dollars. The revenue in 2012 was $413 million, and in 2022 nearly $81.5 billion, so the growth rates should be quite spectacular. In order to calculate 10 growth rates, we need 11 data points.

⏱️TIMESTAMPS⏱️
00:00 Calculating sales growth
00:37 Calculating growth in dollars
01:20 Calculating growth percentage
02:44 Sales growth formula
02:54 How to interpret sales growth rates
03:16 AAGR
03:31 CAGR
04:36 AAGR vs CAGR

The most robust way of calculating sales growth in Excel is by using two steps. First, calculate the growth in dollars from year to year. This is sometimes called the V$, or variance in dollars. For 2013 versus 2012 the formula is B4 – B3, this is simply the current year amount minus the prior year amount.

The sales growth percentage is calculated by dividing the revenue growth in dollars by the prior year revenue. For our first row, the formula for the percentage growth is C4 divided by B3. That’s the two step method of calculating sales growth in Excel: calculating the absolute growth in dollars first in column C, then taking that absolute growth number and dividing it by the prior year basis in column B.

I can also compress that growth rate calculation into one step. The #salesgrowth percentage for the first row equals open parentheses, B4 minus B3, close parentheses, divided by B3. The sales growth formula is current year amount minus prior year amount, which is subsequently divided by the prior year base.

How to interpret sales growth rates? What is the average growth rate? There are two answers to that question. The simple answer is the average annual growth rate, or AAGR. This is simply the average of the ten growth rates in cells E4 through E13. The AAGR is 86%.

The more complicated but more accurate answer is the compound annual growth rate, or CAGR. CAGR is the constant rate of return over a period of time, which only looks at the starting point (cell B3) and the ending point (cell B13), and then calculates what the compound annual growth rate needs to be to go exponentially from start to end point in ten steps. To calculate CAGR in Excel, use the Excel function POWER, which returns the results of a number raised to a power. Insert function. POWER. We need to fill two lines of function arguments. The number is B13 divided by B3. The power is 1 divided by 10, as we go from start point to end point in 10 steps. Hit OK. Now deduct 1 from the result, which is the same as deducting 100%, and you get to a CAGR of 70%.

The average annual growth rate (AAGR) at 86% is higher than the compound annual growth rate (CAGR) at 70%, which means that one or more outliers skew the AAGR. You have probably spotted the outlier: 387% growth from 2012 to 2013. In all of the other years, the growth rate is below the AAGR, so we are not dealing with a normal distribution of growth rates here.

Here is what CAGR enables you to do. What would the revenue numbers have been if Tesla’s revenue had been growing at a constant 70% each and every year? We will calculate this in column I. For the first year, cell I4, we take the actual starting revenue in B3 and multiply it by 1 plus the CAGR, which is in cell E15. For the second year, we take the value in cell I4 and multiply it by 1 plus E$15: the CAGR, with a $ sign inserted to lock row 15 in the formula. Copy that formula down the column, and let’s display the formulas using FORMULATEXT. If we go to the Formulas tab, and use “Trace Precedents” on a couple of the cells, we can have the formula sink in. In this example, we basically multiply the prior year number by 1.7 (1 + 70%) time and time again.

Actual revenue is in column B, and the simulated revenue using a constant sales growth rate of 70% is in column I. For each of the years, the amount in column B is higher than the amount in column I, and only in 2022 does the simulated revenue using CAGR catch up to get to the same ending point of the data set. This is the effect of the outlier of 387% growth at the start of the data set. Being aware of how #AAGR differs from #CAGR is key to interpret sales growth numbers!

Philip de Vroe (The Finance Storyteller) aims to make accounting, finance and investing enjoyable and easier to understand. Learn the business and accounting vocabulary to join the conversation with your CEO at your company. Understand how financial statements work in order to make better investing decisions. Philip delivers #financetraining in various formats: YouTube videos, livestreams, classroom sessions, and webinars. Connect with me through Linked In!

Want to get access to bonus content, and/or express your gratitude by buying me a cup of tea? Join my channel as a member through https://www.youtube.com/channel/UCQQJnyU8fALcOqqpyyIN4sg/join