3 Ways to Forecast Growth Rate in Excel

If you have a record of the performance of something, for instance, your company’s sales report or your YouTube channel’s view report, you can easily predict future results using Excel. The main features of Excel for predicting the growth rate are the FORECAST function and the FORECAST SHEET command. Now, I am going to discuss 3 different ways to forecast growth rates in Excel.

Introduction to the Dataset

Here I have a dataset of the Sales record (Column B) of a retail shop from 2010 to 2018 (Column A). I will use this dataset to forecast (Column C) the growth rate of the company for the next 6 years, from 2019 to 2024.

Quickest Method: Use FORECAST.ETS Function to Forecast Growth Rate in Excel

In most cases, you are not going to see the historical values in a linear trend. The values will rise sometimes, and drop sometimes. For these cases, using the Exponential Tripple Soothing (ETS) algorithm will be appropriate. So now, I am going to use FORECAST.ETS function here.

Syntax

=FORECAST.ETS (target date, values, timeline, [seasonality], [data completion], [aggregation])

Usage Guide

Step_1: Type the following formula in cell C11:

=FORECAST.ETS(A11,$B$2:$B$10,$A$2:$A$10)

Use FORECAST.ETS Function to Forecast Growth Rate In Excel


Formula Explanation

  • A11 is our target date for finding the outcome.
  • $B$2:$B$10 is our chosen value range for the formula. And the $ signs refer to the Absolute Cell Reference.
  • $A$2:$A$10 is the timeline of the selected value range.
  • Seasonality is optional, you can skip this part. If seasonality is not mentioned, Microsoft Excel will automatically assume the seasonality is 0.
  • Data Completion is also optional. Excel will allow a maximum of 30% of missing or neglected data. You can either enter 1 or skip entering the Data Completion.
  • Aggregation is also optional. The Aggregation function is used to comprise functions like AVERAGE, SUM, COUNT, COUNTA, MIN, MAX, MEDIAN, etc.

Step_2: Press ENTER to apply the formula.

Step_3: Now double-click on the Fill Handle icon to copy down the formula.

Step_4: Select the cell range from C11 to C16.

Step_5: Press CTRL+C to copy the cell range.

Step_6: Right-click on cell B11 to open the context menu.

Step_7: Under the context menu, from the Paste Options, select the second icon (Paste Values).

All the sales data are in Column B now.

Result of FORECAST.ETS Function to Forecast Growth Rate In Excel

Generate a Forecast Graph

Step_8: Select Column A and Column B.

Or press the SHIFT+ENTER+↓ arrow key.

Step_9: From the Quick Analysis button, click on the Charts section and then select the Scatter option.

Creating Scatter chart for FORECAST.ETS Function to Forecast Growth Rate In Excel

Final Result >

The values are represented in a scatter chart now and are easy to understand.

Scatter Chart for FORECAST.ETS Function to Forecast Growth Rate In Excel

Alternative Method #1: Use FORECAST.LINEAR Function to Forecast Growth Rate in Excel

Another method is to use FORECAST.LINEAR Function. The FORECAST.LINEAR Function will successfully give output for the same dataset. But the FORECAST.LINEAR Function works most suitably for the linearly progressed values.

Use of FORECAST.LINEAR Function in Excel

Notice the values have increased by $500M.

In this sort of case, the FORECAST.LINEAR Function will deliver the right result.

Example of using FORECAST.LINEAR Function

Syntax

=FORECAST.LINEAR(x, known_ys, known_xs)

Usage Guide

Step_1: Type the following formula in cell C11:

=FORECAST.LINEAR(A11,$B$2:$B$10,$A$2:$A$10)

Using FORECAST.LINEAR Function to Forecast Growth Rate In Excel


Formula Explanation

The values (ys) are depended upon the timeline (xs) in this formula.

  • A11 is the target time for forecasting the growth rate.
  • $B$2:$B$10 is the known ys or the given values in the data table.
  • $A$2:$A$10 is the known xs. It is placed against the known ys.

Step_2: Press ENTER.

Step_3: Copy down the formula using the Fill Handle icon.

Step_4: Select the cells from C11 to C16.

Step_5: Press CTRL+C to copy the values of the cells.

Step_6: Press CTRL+V to paste the values in cell B11.  

Result of FORECAST.LINEAR Function to Forecast Growth Rate In Excel

Generate a Forecast Graph

Step_7: Now select the two columns: Column A as the x-axis and Column B as the y-axis.

Or press the SHIFT+ENTER+↓ arrow key).

Step_8: Click on the Quick Analysis button.

Step_9: From the Charts section, select the Scatter command.

Final Result >

You will find the results illustrated in a line chart. But the line has a sharp fall after 2018. After 2019, the line is showing linear growth.

Scatter chart of FORECAST.LINEAR Function for Forecasting Growth Rate In Excel

This transition of values is not practical. The linear growth occurred because of the FORECAST.The LINEAR function shows a linear increase.

🔖 Note: The FORECAST.LINEAR function performs best for the linear progression patterns.

Alternative Method #2: Use FORECAST SHEET to Forecast Growth Rate in Excel

The Forecast Sheet is a great tool that presents forecasting with the Upper Confidence Bound and the Lower Confidence Bound. Sadly, this feature is new in Microsoft Excel and available in Excel 2016 and later versions.

Usage Guide

Step_1: Create a column for the timeline (Column A) or the dates.

Step_2: Create a column for the values (Column B).

Step_3: Now select Column A and Column B.

Step_4: Select the Data tab.

Step_5: From the Forecast group, click on the Forecast Sheet option.

Using the FORECAST SHEET to Forecast Growth Rate In Excel

The Create Forecast Worksheet window will come along on the screen.

Step_6: From the Forecast End box, choose the ending time of the forecast.

Step_7: Hit on the Create button.

Using the Forecast worksheet window of FORECAST SHEET for Forecasting Growth Rate In Excel

Final Result >

You will see the forecasted sheet is delivered in another sheet.

The Forecast growth table with Lower Confidence Bound and Upper Confidence Bound in Excel

Also, the result is presented in a tabular form with the Lower Confidence Bound and Upper Confidence Bound in the table.

Besides the table, the forecast is also shown in a chart.

The Forecast growth rate chart in Excel

Conclusion

The three methods I have discussed regarding forecasting growth rate can give you result for any kind of dataset. But each one has a slight difference from the other and has specific features. I hope the article may help you to solve your problem about forecasting the growth rate in Excel. Thank you.

Related Articles

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *