Aung Shine
Business sectors preserve their necessary information in Excel. They have to perform various operations according to their requirements. The sales sector stores the Sales records of different products over the years in the Excel worksheet. Excel makes it easy to track records of large amounts of data. Users can easily understand and analyze the data. Apart from keeping the Sales records, it’s also equally important to assess the Sales Growth. You can determine the growth annually or over a couple of years. In this article, we’ll give you suitable examples to Calculate Sales Growth over 5 Years in Excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
3 Ideal Examples to Calculate Sales Growth over 5 Years in Excel
This article will demonstrate the Sales Growth over 5 Years. Here we’ll assume the Net Sales amount is greater for the later years than the previous ones. Thus, we’ll calculate the growth. To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset displays the Net Sales of a product for the period of 5 Years (2017-2021) of a company. Sales amount in 2017 is the lowest, whereas, the amount in 2021 is the highest. Now, you’ll see 3 different examples to compute Sales Growth over 5 Years in Excel.
1. Calculate Sales Growth over 5 Years in Excel with Simple Formula
We can create many different formulas in Excel as per our requirements using different cells and built-in functions. To get the Sales Growth over 5 years, you need to deduct the year 2015 Sales amount from the year 2021 Sales amount. But, from this value, one can’t tell whether the growth is satisfactory or not. So, we need the growth rate. That’s why the subtracted result needs to be divided by the 2021 sales amount. Using this fact, we’ll generate a simple formula for calculating Sales Growth. Therefore, follow the steps below to carry out the operation.
STEPS:
- First, select cell C11.
- Then, type the formula:
=(C9-C5)- After that, press Enter to return the value.
- Thus, you’ll get the growth amount.
- Now, click cell C12.
- Next, choose the Percentage number format from the Number section of the Home tab.
- Again, select cell C12.
- Afterward, insert the below formula:
=C11/C5- Subsequently, return the result by pressing Enter.
- At last, the growth rate over 5 years will appear in cell C12.
Read More: How to Calculate Sales Growth Percentage in Excel (2 Easy Approaches)
Similar Readings
- Forecast Sales Using Regression Analysis in Excel (3 Methods)
- How to Forecast Sales in Excel (5 Easy Ways)
- Calculate Percentage of Sales in Excel (5 Suitable Methods)
2. Create Excel Formula to Get Compound Growth Rate of Sales over 5 Years
Suppose, we want to get the Compound Growth Rate. Most investment sectors prefer the compound growth rate over the average growth rate. Because the average growth rate doesn’t take account of the compounding. Therefore, it overestimates the growth sometimes. Hence, it can cause an error in the return values. On the other hand, the compound growth rate is more accurate. So, learn the following steps to perform the task.
STEPS:
- Firstly, choose cell C11 to input a formula.
- Next, type the formula:
=((C9/C5)^(1/4))-100%- Consequently, press Enter to get the value.
- As a result, it’ll return the compound growth rate.
Read More: How to Forecast Sales Growth Rate in Excel (6 Methods)
3. Compute Compound Annual Growth Rate Using Excel XIRR Function
We know that Excel provides numerous Functions to perform a multitude of tasks. Such a kind is the XIRR function. This function generates the internal rate of return for the cash flows in a period. We can apply this function to calculate the Compound Annual Sales Growth Rate over 5 Years. Hence, follow the process to carry out the operation.
STEPS:
- First of all, you have to place the year 2015 (Start Value) sales and year 2021 (End value) sales in the new cells as it’s shown in the following picture.
- See the below image for a better understanding.
- However, don’t forget to insert a Minus sign before the Start value (2017 Sales) for the formula to function without errors.
- Then, select cell C11 and type the formula:
=XIRR(E6:F6,E5:F5)- After that, press Enter. It’ll return the output of the equation.
- Now, choose the % (Percentage) number format for the output value in cell C11.
- Thus, you’ll get the compound annual sales growth rate.
Read More: How to Calculate Annual Sales in Excel (4 Useful Methods)
Conclusion
Henceforth, you will be able to Calculate Sales Growth over 5 Years in Excel following the above-described examples. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
