6 Ways to Forecast Growth Rate in Excel

You can use historical time-based data to forecast growth rates if you have it. When you make the forecast, Excel generates a brand-new worksheet with a table of past and future values and a chart that visualizes the information. You can use a projection to anticipate upcoming sales, inventory needs, or consumer trends. In addition, you can use different functions and formulas to forecast growth rates. So, this article demonstrates 6 methods to forecast a growth rate in Excel.

📁 Download Excel File

You can download the Excel file used for the demonstration from the link below.

Learn to Forecast Growth Rate in Excel with These 6 Methods

First, familiarize ourselves with the dataset used as an example throughout the article. We want to forecast the company’s sales for 2020 using its yearly sales from 2010 to 2019. Therefore, we will use this dataset to illustrate 6 straightforward techniques to forecast a growth rate in Excel.

In this method, we will demonstrate the use of GROWTH function to forecast growth rate in Excel. The GROWTH function predicts exponential growth of the existing data. This function returns y-values for the existing set of x-values and y-values for a specified new x-value. Let us go through the steps to apply this function to calculate the Sales, Revenue, and Annual Sales in Excel.

This method calculates a year’s revenue based on previous years’ revenues using the GROWTH function in Excel. Let us go through the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

• First, select cell C18.
• Then, insert the formula.

• Then, press Enter.

Here, GROWTH function predicts the sales for the year in cell B18 which is the new x-value based on the previous x-values B6:B15 and y-values C6:C15.

This method predicts a year’s sales based on previous years’ sales using the GROWTH function in Excel. Let us go through the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

• First, select cell C18.
• Next, insert the formula.

• And, press Enter.

Here, GROWTH function predicts the revenues for the year in cell B18 which is the new x-value based on the previous x-values B6:B15 and y-values C6:C15.

This method calculates the annual sales for a set year based on the annual sales of previous years using GROWTH function in Excel. Let us go through the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

• First, select cell E6.
• Then, insert the formula.

• And, press Enter.

Here, GROWTH function predicts the annual sales for the years in cells D6:D16 which is the new x-value based on the previous x-values B6:B16 and y-values C6:C16 and TRUE is a constant that selects b normally.

📕 Read More: 4 Simple Ways to Calculate Forecast Accuracy Percentage in Excel

In this method, we will demonstrate the use of FORECAST function to forecast growth rate in Excel. The FORECAST function uses existing values to determine or forecast a future value. A y-value for a specific x-value is the future value. This function uses linear regression to predict the future value using known x-values and y-values for the current values. You can use these functions to forecast future sales, inventory needs, or consumer trends.

Let us go through the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

• First, select cell C18.
• Then, insert the formula.

• And, press Enter.

Here, FORECAST  predicts the sales for the year in cell B18 which is the new x-value based on the previous x-values B6:B15 and y-values C6:C15.

📕 Read More: 3 Ways to Add Percentage to Price in Excel with Formula

This method will demonstrate the use of TREND function to forecast growth rate in Excel. TREND returns values that follow a linear trend. The known y and known x values in the array are fitted to a straight line using the least squares method and return the y-values along that line for the array of new x values you provide.

Let us see the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

• First, select cell C18.
• Then, insert the formula.

• And, press Enter.

Here, TREND predicts the sales for the year in cell B18 which is the new x-value based on the previous x-values B6:B15 and y-values C6:C15.

📕 Read More: 5 Easy Methods to Add 10 Percent to a Number in Excel

This method will apply the Analysis Toolpak feature to forecast a growth rate in Excel. This method will use the Exponential Smoothing Data analysis tool from the Analysis Toolpak. Hence, we have created another column named Forecasted Sales for the output.

Let us see the steps to apply this feature.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, click on the File menu and select Options.

• Next, Excel Options named box will appear. Click on Add-ins. Then, under the Manage option, select Excel Add-ins and click on Go…

• Then, another box named Add-ins will appear. Click on the Analysis Toolpak option and press OK.

• Next, select Data Analysis from Data tab.

• After that, a box named Data Analysis will pop up. Select Exponential Smoothing as Analysis Tools and press OK.

• Then, the Exponential Smoothing box will appear. Insert $C$6:$C$15 as the Input Range, 5 as the Damping factor, $D$6:$D$16 as the Output Range, and press OK.

• Lastly, the result looks like this.

📕 Read More: 3 Quick Ways to Add 20 Percent to a Price in Excel

In this method, we will employ Exponential Smoothing manually using a formula. So, for applying the formula, we have taken α as a constant whose value is 0.5 and taken an initial forecast based on the sales of the first two years. Hence, we have created another column named Forecasted Sales for the output.

Now, let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select cell D7 and insert the formula.
• Then, press Enter.

• Lastly, drag down the Fill Handle, and we will have the sales for the year 2020.

📕 Read More: Calculate Percentage in Excel Using Absolute Cell Reference

Now, we will demonstrate how to Create a Forecast Sheet to forecast growth rate in Excel. So, let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select cells containing the data B6:C16.
• Next, go to Data tab, click on Forecast group and select Forecast Sheet.

• After that, Create Forecast Worksheet named box will pop up. Select the line chart option, insert 2021 in the Forecast End option, and click Create.

• Lastly, in a new worksheet, we will have a new table and a forecasting graph created.

📕 Read More: 2 Ways to Calculate Bacterial Growth Rate in Excel

📄 Important Notes

🖊️ The FORECAST.LINEAR function, which is now part of the new Forecast functions in Excel 2016, has replaced the FORECAST function. However, we suggest using new FORECAST.LINEAR function, the previous one is still accessible for backward compatibility.

🖊️ While using Forecast Sheet, we can use different options according to our need to forecast the growth. You can find more about this in Create a Forecast.

📝 Takeaways from This Article

📌  This article demonstrated 6 approaches to forecast growth rate in Excel.

📌  In the first method, we showed the use of GROWTH function for forecasting growth rates in Excel.

📌  Then, we demonstrated the FORECAST function to forecast.

📌  Next, we applied the TREND function for the same.

📌  After that, we demonstrated the Analysis Toolpak feature to forecast.

📌  Then, we employed the Exponentially Smoothing formula.

📌  Finally, we employed the Forecast Sheet to forecast growth rate in Excel.

Conclusion

This article has demonstrated 6 ways to forecast growth rate in Excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.

Related Articles

• 3 Easy Ways to Calculate Accuracy Percentage in Excel
• Calculate Percentage in Excel Based on Conditional Formatting
• 4 Easy Ways to Calculate Percentage in Excel Based on Cell Color
• Calculate Profit and Loss Percentage with Formula in Excel