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.

Predict Growth Rate in Excel.xlsx

Table of Contents

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)

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.

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.

Final Result >

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

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.

Notice the values have increased by $500M.

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

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)

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.

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.

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.

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.

Final Result >

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

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.

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