How to Forecast Growth Rate in Excel [3 Ways]
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.
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.
Forecast Growth Rate in Excel Using FORECAST.ETS Function
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])Here is the formula to forecast the growth rate:
=FORECAST.ETS(A11,$B$2:$B$10,$A$2:$A$10)Here,
- 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.
Steps to forecast the growth rate with FORECAST.ETS function:
- Select a cell C11.
- Type the following formula: =FORECAST.ETS(A11,$B$2:$B$10,$A$2:$A$10)
- Press ENTER to apply the formula.
- Now double-click on the Fill Handle icon to copy down the formula.
- Select the cell range from C11 to C16.
- Press CTRL+C to copy the cell range.
- Right-click on cell B11 to open the context menu.
- 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
Now, go through these steps to generate a forecast graph:
- Select Column A and Column B.
Or, press the SHIFT+ENTER+↓ arrow key. - From the Quick Analysis button, click on the Charts section and then select the Scatter option.
The values are represented in a scatter chart now and are easy to understand.
Forecast Growth Rate in Excel with FORECAST.LINEAR Function
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)Formula with FORECAST.LINEAR:
=FORECAST.LINEAR(A11,$B$2:$B$10,$A$2:$A$10)The values (ys) depend on 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.
Steps to forecast growth rate using FORECAST.LINEAR function:
- Select an empty cell.
- Type the following formula: =FORECAST.LINEAR(A11,$B$2:$B$10,$A$2:$A$10)
- Press ENTER.
- Copy down the formula using the Fill Handle icon.
- Select the cells from C11 to C16.
- Press CTRL+C to copy the values of the cells.
- Press CTRL+V to paste the values in cell B11.
Generate a Forecast Graph
To generate a forecast graph in Excel, follow these steps:
- Select the two columns: Column A as the x-axis and Column B as the y-axis.
Or, press the SHIFT+ENTER+↓ arrow key).
- Click on the Quick Analysis button.
- From the Charts section, select the Scatter command.
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.
Forecast Growth Rate in Excel Using FORECAST SHEET
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.
Here are the steps below to do that:
- Create a column for the timeline (Column A) or the dates.
- Create a column for the values (Column B).
- Now select Column A and Column B.
- Select the Data tab.
- From the Forecast group, click on the Forecast Sheet option.
The Create Forecast Worksheet window will come along on the screen. - From the Forecast End box, choose the ending time of the forecast.
- Hit on the Create button.
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 results 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.
Frequently Asked Questions
What is the formula for forecasting?
The formula for forecasting in Excel involves using the Exponential Smoothing (ETS) algorithm, often implemented with the FORECAST.ETS functions. This algorithm considers both trend and seasonality in time series data, providing a reliable method for predicting future values based on historical patterns.
What is the average growth rate in Excel?
In Excel, you can calculate the average growth rate using the formula: =RATE(N, PMT, PV, FV, Type)
Here, N is the number of periods, PMT is the payment made each period, PV is the present value, FV is the future value, and Type specifies whether the payment is made at the beginning or end of the period. This formula helps determine the average growth rate over cash flows or investments.
What forecasting method is used in Excel?
In Excel, the forecasting method commonly used is the Exponential Smoothing (ETS) algorithm. ETS is a time series forecasting technique that considers both trend and seasonality in data, providing accurate predictions for future values. The FORECAST.ETS functions in Excel facilitate the application of this method for forecasting in various scenarios.
Related Articles
- How to Calculate Annual Growth Rate in Excel (5 Different Cases)
- Calculate Dividend Growth Rate in Excel [2+ Formulas]
- 2 Ways to Calculate Bacterial Growth Rate in Excel
- 2 Ways to Calculate Revenue Growth Rate in Excel
- 3 Scenarios of Exponential Growth Formula in Excel
- 2+ Ways to Calculate Monthly Growth Rate in Excel
