How to Forecast Sales Growth Rate in Excel (6 Methods)

Get FREE Advanced Excel Exercises with Solutions!

If you want to forecast sales growth rate in Excel, this article is for you. Here, we will demonstrate to you 6 simple, easy and effective methods to do the task effortlessly.

Download Workbook

6 Methods to Forecast Sales Growth Rate in Excel

The following Product Sales List table shows Year and Sales columns. We want to forecast the sales for the year 2022. We will use 6 different methods to do the task. Here, we used Excel 365. You can use any available Excel version.

How to Forecast Sales Growth Rate in Excel


Method-1: Using FORECAST Function to Forecast Sales Growth Rate

Here, to calculate the sales for the year 2022, we will use the FORECAST function.

How to Forecast Sales Growth Rate in Excel

➤ First of all, we will type the following formula in cell F5.

=FORECAST(E5,C5:C12,B5:B12)

Here,

The FORECAST function calculates or predicts a future value by using the existing values.

E5 is the x for which we want to know the future value. Here, E5 is the year 2022 and we want to forecast the sales for this year.

C5:C12 is known y’s, which is known Sales in this case.

B5:B12 is the known x’s, and in this case, they are known Year.

➤ After that, press ENTER.

Finally, we can see the Forecasted Sale for the year 2022 in cell F5.

How to Forecast Sales Growth Rate in Excel

Read More: FORECAST Function in Excel (with other Forecasting Functions)


Method-2: Using TREND Function to Forecast Sales Growth Rate

In this method, we will use the TREND function to forecast the sales for the year 2022.

➤ First of all, we will type the following formula in cell F5.

=TREND(C5:C12,B5:B12,E5,)

Here, the TREND function returns the value of a linear trend.

C5:C12 is the known y’s, which is known Sales.

B5:B12 is the known x’s, which is known Year.

E5 is the new x’s, here the new x is 2022 for which we want to know the sales.

➤ After that, press ENTER.

How to Forecast Sales Growth Rate in Excel

Finally, we can see the Forecasted Sale for year 2022 in cell F5.

How to Forecast Sales Growth Rate in Excel

Read More: How to Forecast Sales in Excel (5 Easy Ways)


Methos-3: Using GROWTH Function

We can use the GROWTH function as well to forecast sales. In this section, we will use the GROWTH function to forecast sales for the year 2022.

➤ First of all, we will type the following formula in cell F5.

=GROWTH(C5:C12,B5:B12,E5)

Here,

The GROWTH function predicts exponential growth by using existing data.

C5:C12 is the known y’s, which is known Sales.

B5:B12 is the known x’s, which is known Year.

E5 is the new x’s, here the new x is 2022 for which we want to know the sales.

How to Forecast Sales Growth Rate in Excel

➤ Afterwards, press ENTER.

Finally, we can see the Forecasted Sale for year 2022 in cell F5.

Read More: How to Forecast Revenue in Excel (6 Simple Methods)


Method-4: Using Forecast Sheet

If we want to forecast in a quick and easy way, this is the best method. Moreover, we can forecast for as many years as we want in this method.

Here, we want to know the forecasted sales for the year 2022, 2023 and 2024 in the following Product List table.

➤ First of all, we will select the entire dataset of the Year and Sales columns > go to the Data tab > click on Forecast > select Forecast Sheet.

A Create Forecast Worksheet window will appear.

➤ After that, we will select a year in the Forecast End box, here we selected 2024.

➤ Click on Create.

Afterwards, we can see the Forecast Sales for 2022, 2023 and 2024.

how to forecast sales growth rate in excel

We can also see a chart has been created where we can see the sales for the year 2022, 2023 and 2024.

how to forecast sales growth rate in excel


Method-5: Forecast Sales Growth Rate Using Data Analysis Tool

Excel offers different analysis tools to do several tasks, forecasting is not an exception either. Here, we want to forecast the sales for the year 2022. To do so, we will use the Data Analysis tool.

Here, first of all, we will add the Data Analysis tool in our Excel Data tab.

➤ First of all, we will go to the File tab in the ribbon.

How to Forecast Sales Growth Rate in Excel

➤ After that, select Options.

An Excel Options window will appear.

➤ Select Add-ins > in the Manage box select Excel Add-ins > click on Go.

➤ Afterwards, from the Add-ins window, select Analysis Toolpak > click on OK.

Now, if we go to the Data tab, we will see the Data Analysis tool.

Now, we will use the Data Analysis tool to forecast sales for the year 2022.

➤ First of all, we will go to the Data tab > select Data Analysis.

A Data Analysis window will appear.

➤ We will select the Analysis Tool as Exponential Smoothing > click OK.

An Exponential Smoothing window will appear.

➤ After that, we will select the Input Range from cell C5 to C13 > give Damping factor as 0.5 > give Output Range from cell E5 to E13.

Finally, we can see the sales for the year 2022 in the Forecasted Sales column.

How to Forecast Sales Growth Rate in Excel

Read More: How to Forecast Sales Using Regression Analysis in Excel (3 Methods)


Method-6: Using Exponential Smoothing Formula

In this method, we will use an exponential smoothing formula to forecast the sales for the year 2022.

We take the smoothing constant as 0.5 and Initial Forecast as $12270.

How to Forecast Sales Growth Rate in Excel

➤ First of all, we will type the following formula in cell E6.

=($C$15*C5)+(1-$C$15)*E5

➤ After that, press ENTER.

Afterwards, we can see the result in cell E6.

➤ We will drag down the formula with the Fill Handle tool.

Finally, in cell E13 we can see the Forecasted Sales for the year 2022.

How to Forecast Sales Growth Rate in Excel

Read More: Forecasting in Excel Using FORECAST Function & Auto Fill Handle Tool


Conclusion

Here, we tried to show you 6 methods to forecast sales growth rate in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, feel free to know us in the comment section.


Related Articles

Afia Kona

Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo