How to Forecast Growth Rate in Excel (2 Methods)

The forecasting of growth rate is quite an important part of the financial and statistical analysis in Business. Excel offers some amazing features that can forecast growth rates based on historical chronological data. In this article, you will learn 2 ways to forecast growth rate in Excel.

How to Forecast Growth Rate in Excel: 2 Methods

1. Forecast Growth Rate in Excel Using the GROWTH Function

We can use the GROWTH function to forecast any kind of growth rate in Excel.

The GROWTH function predicts an exponential growth rate which follows the formula below:

Here, the function will return a “y” value based on the “x” values.

Function syntax:

GROWTH(known_y’s, [known_x’s], [new_x’s], [const])

Function Arguments:

• Known_y’s: This field is mandatory. Here you need to insert a set of past, y values according to the formula, y = b*m^x.
• Known_x’s: Optional This is the set of past x values from the formula y=b*m^x.
• New_x’s: This is optional Here you need to insert a new value of x for which you want to get the value of y.
• const: It’s also optional If const is TRUE, b is calculated normally. But if const is FALSE, b is set to 1. Thus, y = b*m^x becomes y=m*x.

1.1 Forecast Sales Growth Rate in Excel

To forecast the sales growth rate, you will need historical time-based data. One date or time entry for creating the timeline and another column of their corresponding values.

I’m using a table of two columns for demonstration. The first column is the Year column that depicts the chronological timeline. The second column contains year-based corresponding sales in dollars.

Based on these data, we will predict the sales amount for the year 2014 in dollars.

Now,

❶ Click on cell C14 and insert the following formula:

In the formula above,

• C5:C13 is the Known_y’s.
• B5:B13 is the Known_x’s.
• B14 is the

❷ After that hit the ENTER button.

Now we can predict the sales amount for the year 2014 will be $12,952.

Read More: How to Forecast Sales Using Historical Data in Excel

1.2 Forecast Revenue Growth Rate in Excel

We have a database of revenue growth rate in percentage from the year 2005 to 20013. Based on these data, we are going to forecast the revenue growth rate for the year 2014, 2015, and 2016.

To do that,

❶ Select cell C14, C15, and C16 to insert the following formula:

In the formula above,

• C5:C13 is the Known_y’s.
• B5:B13 is the Known_x’s.
• B14:B16 is the New_x’s.

❷ Now press CTRL + SHIFT + ENTER to insert the above formula as an array formula.

After executing the formula, we can see that the revenue growth in percentage has been forecasted. Which are 7.89%, 7.57%, and 7.27% respectively.

Read More: How to Forecast Revenue Growth in Excel

1.3  Calculate and Forecast Annual Sales Growth Rate in Excel

In this tutorial, we will forecast the annual sales growth rate for the next 10 years based on past history.

To do that,

❶ Select  cell F5 to F10 to insert the following formula:

In the above formula:

• C5:C14 is the Known_y’s.
• B5:B14 is the Known_x’s.
• D5:D14 is the New_x’s.
• TRUE is the const

❷ After that, press CTRL + SHIFT + ENTER to insert the above formula as an array formula.

❸ Then select cell F11 to F14 to insert the following formula:

• C5:C14 is the Known_y’s.
• B5:B14 is the Known_x’s.
• D5:D14 is the New_x’s.
• FALSE is the const

❹ After that, press CTRL + SHIFT + ENTER to insert the above formula as an array formula.

When you are done with the steps above, you will see the forecast result of the annual sales growth rate for the next 10 years as in the picture below:

Read More: How to Forecast Sales in Excel

2. Use the Forecast Sheet to Forecast the Annual Revenue Growth Rate in Excel

Excel has embedded an amazing feature that can forecast annual growth rates using graphs.

To use this feature,

❶ Create a set of data that represents data or a time-based timeline. I will use the Year column for this purpose.

❷ Then take a set of corresponding values for the data or time-based timeline. For this case, I have created the Revenue column.

❸ Now select the whole data table.

❹ Go to the Data tab from the main ribbon.

❺ From the Forecast group, choose the Forecast Sheet.

❻ Create Forecast Worksheet dialog box will appear. Select line graph or column chart as you like.

❼ From the Forecast End box, select a timeline to limit the prediction period.

❽ Now hit the Create button to generate a forecast growth rate graph.

After all the steps above, you will see a forecasting line graph as in the picture below:

Read More: How to Forecast Revenue in Excel

Things to Remember

• Press CTRL + SHIFT + ENTER to insert an array formula.

Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

Conclusion

To sum up, we have discussed 2 easy ways to forecast growth rate in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.

Related Articles

• How to Forecast in Excel Based on Historical Data

<< Go Back to Forecasting in Excel |Excel for Finance | Learn Excel