How to Forecast Revenue Growth in Excel (5 Handy Methods)

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.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


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.


5 Handy Methods to Forecast Revenue Growth in Excel

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.5 Handy Methods to Forecast Revenue Growth in Excel


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.

The FORECAST function returns the forecasted values for a specific future target date using the exponential smoothing method.

Just follow the steps below.

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.

Forecast Revenue Growth Using the FORECAST Function

  • Now, press ENTER. Finally, here is the forecast value for 2023.

Forecast Revenue Growth Using the FORECAST Function

Read More: FORECAST Function in Excel (with other Forecasting Functions)


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.

Estimate Revenue Growth Using the‘ Forecast Sheet’ Button

  • 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.

Estimate Revenue Growth Using the‘ Forecast Sheet’ Button

  • Finally, we will see a forecasting line graph as in the following screenshot.

Estimate Revenue Growth Using the‘ Forecast Sheet’ Button (Result)

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.

Estimate Revenue Growth Using the‘ Forecast Sheet’ Button

Read More: How to Forecast Growth Rate in Excel (2 Methods)


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.

The AVERAGE function returns the average (arithmetic mean) of its arguments, which can be numbers, names, arrays, or references that contain numbers.

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.

Using Moving Averages

  • Press ENTER. Finally, you will get the forecasting result for the year 2023.

Using Moving Averages

Read More: How to Forecast Sales in Excel (5 Easy Ways)


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.

The FORECAST.ETS function returns the forecasted value for a specific future target date using the exponential smoothing method.

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

Using Exponential Smoothing Technique

  • Hit on the ENTER key, and get the results.

Using Exponential Smoothing Technique

Read More: How to Forecast Sales Growth Rate in Excel (6 Methods)


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.

The FORECAST.LINEAR function calculates, or predicts, a future value along with a linear trend by using existing values.

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.

Use Linear Regression

After that, press ENTER. Finally, you will see the output of linear regression forecasting.

Use Linear Regression

Read More: How to Forecast Sales Using Regression Analysis in Excel (3 Methods)


Conclusion

In this tutorial, I have discussed 5 effective ways to forecast revenue growth in Excel. I hope you found this article helpful. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Hafiz Islam
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo