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.
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.
➤ 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.
Finally, we can see the Forecasted Sale for the year 2022 in cell F5.
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.
Finally, we can see the Forecasted Sale for year 2022 in cell F5.
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.
Finally, we can see the Forecasted Sale for year 2022 in cell F5.
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.
We can also see a chart has been created where we can see the sales for the year 2022, 2023 and 2024.
Method-5: 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.
➤ 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.
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.
➤ 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.
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.
The post How to Forecast Sales Growth Rate in Excel (6 Methods) appeared first on ExcelDemy.
