The forecasting of Revenue Growth is a very important tool for the financial and statistical analysis in the industry. Based on historical chronological data, Excel offers some interesting features that can forecast revenue growth. In this article, you will learn 5 effective ways to forecast revenue growth in Excel.
What Is Revenue Growth?
Revenue growth is the amount of money your industry makes over a pre-planned time compared to the previous, same amount of time. For example, the revenue growth is how much money you have made this month compared to the last month. The companies use revenue growth to measure how fast their business is expanding.
How to Forecast Revenue Growth in Excel: 5 Handy Methods
Let’s first introduce our sample dataset. Column B represents the chronological timeline. Column C depicts the revenue growth in percentage. Our goal is to forecast revenue growth for 2023.
1. Forecast Revenue Growth Using the FORECAST Function
The FORECAST function is a Statistical function in Excel. It calculates or predicts a future value based on existing value. The existing values are referred to as x-values and y-values in the function arguments, and the future value is forecasted by applying linear regression. For instance, you can predict future numeric values of sales, earnings, expenses, inventory, consumer trends, measurements, etc.
Steps:
- First, go to cell C11 and type the following formula.
=FORECAST(B11,C5:C10,B5:B10)Here,
- B11 is the New_x’s.
- C5:C10 is the Known_y’s.
- B5:B10 is the Known_x’s.
- Now, press ENTER. Finally, here is the forecast value for 2023.
Read More: How to Forecast Revenue in Excel
2. Estimate Revenue Growth Using ‘Forecast Sheet’ Button in Excel 2016 and Later Versions
Excel 2016 and later versions have a built-in feature that can forecast annual revenue growths using charts. It’s the Forecast Sheet button in the Data tab. It’s an alternative to using the FORECAST.ETS function. Just go with the steps below.
Steps:
- First of all, select any cell in your data.
- Then, go to the Data tab. From the Forecast group (Forecast drop-down in the image), click on the Forecast Sheet button. A Create Forecast Sheet window will pop up.
- Select a line graph or rectangular chart whatever you feel is suitable for you.l In this example, we picked a line graph. At the Forecast End box, choose a year (in this example, we took 2023) to limit the forecast period. Finally, click on Create.
- Finally, we will see a forecasting line graph as in the following screenshot.
Note:
In the Create Forecast Worksheet, you will find the following terms that you should know before using the Forecast Sheet button to predict revenue growth.
1. Forecast Start: The initial date for forecasting. You can select a date either by typing manually or choosing from the drop-down list.
- In the case of seasonal data, you should start a forecast before the final historical data.
- Choose a date before the last of the historical data to display how well the forecasts match the known data. In this situation, only values before the start date should be used for forecasting which is known as hindcasting.
2. Confidence Interval: It is the range in which the forecasts are wanted to fall. It is represented by the two smooth lines on both sides of the forecast line. A smaller interval depicts more confidence for a particular point. The 95% level is the by-default value which means that 95% of future data are expected within the range.
3. Seasonality: It is the length of the seasonal pattern where regular and anticipated data fluctuations happen. For instance, in a yearly period where each data indicates a month, it has a seasonality of 12.
4. Include Forecast Statistics: You can check this box if you need to create a table of additional statistics such as error metrics (SMAPE, MASE, RMSE, MAE) and smoothing constants (Beta, Alpha, Gamma). The FORECAST.ETS.STAT function determines all these values.
5. Timeline Range: This range is used for timeline series. It includes all dates in your source dataset, but you can customize it here.
6. Values Range:Â It is used for the value series. It should be the same as the timeline range.
7. Fill Missing Points Using: Handling of missing points is controlled by it. Excel uses the interpolation approach. You treat the missing points as zero values by selecting zeros.
8. Duplicate Aggregates Using: It calculates how multiple data with the same timestamp are counted. Average is the default option, but you can pick other methods from the list, such as Median, Min, or Max.
Read More: How to Forecast in Excel Based on Historical Data
3. Using Moving Averages
The Moving Average forecasting helps to get a clear picture of future revenue growths. As time passes, the previous data drifts off to forecast new revenue growth for the upcoming time period. That’s why it’s called Moving Average. The easiest way to calculate the moving average in Excel is to use the AVERAGE function.
To apply this simple method, just follow the steps below.
Steps:
- In cell C11, write down the following formula:
=AVERAGE(C8:C10)Here, C8:C10 contain the revenue growth for a 3 years time period.
- Press ENTER. Finally, you will get the forecasting result for the year 2023.
Read More: How to Forecast Growth Rate in Excel
4. Use FORECAST.ETS Function to Apply Exponential Smoothing and Get Revenue Growth
For univariate data, Exponential smoothing is a forecasting method based on a time series. The data can be expanded to assist data with seasonal or systematic trends. This method will smooth out your data by accounting for seasonal patterns in revenue growth.
It is useful to predict long-term revenue growth. To apply this, we are going to use the FORECAST.ETS function in Excel.
Now, go with the steps below to apply this method.
Steps:
- Copy the following formula, and paste it into cell C11.
=FORECAST.ETS(B11,C5:C10,B5:B10)Here,
- B11 is the target_date
- C5:C10 are the values
- B5:B10 is the timeline
- Hit on the ENTER key, and get the results.
Read More: How to Forecast Sales Growth Rate in Excel
5. Use Linear Regression
This method analyzes the relationship between 2 variables without accounting for other real-world factors. We will apply FORECAST.LINEAR function to perform it.
Follow the steps below to do linear regression forecasting in Excel.
Steps:
- First, type the following formula in cell C11.
=FORECAST.LINEAR(B11,C5:C10,B5:B10)Here,
- B11 is the New_x’s.
- C5:C10 is the Known_y’s.
- B5:B10 is the Known_x’s.
After that, press ENTER. Finally, you will see the output of linear regression forecasting.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
Conclusion
In this tutorial, I have discussed 5 effective ways to forecast revenue growth in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.
