How to Forecast Revenue in Excel (6 Methods)

Dataset Overview

Revenue simply refers to the earnings of an organization or company. For instance, consider the revenue (in cells E5:E14) of a company from 2014 to 2021. In this case, revenue is calculated by multiplying the unit price and quantity.

Yearly Revenue Forecasting

Now, let’s explore methods for forecasting revenue based on the given data. We’ll cover three methods for yearly revenue forecasting, followed by two methods for monthly forecasting, and one method for date-wise forecasting.

Method 1 – Using TREND Function for Yearly Revenue Forecasting

Suppose you want to forecast revenue for 2022, 2023, and 2024 based on historical revenue data from 2014 to 2021.

Using TREND Function

Apply the TREND function:

The syntax of the TREND function is:

=TREND(known_y’s, [known_x’s], [new_x’s], [const])

For this dataset, the formula would be:

=TREND(C5:C12,B5:B12,B13:B15)

Here:

C5:C12 represents historical revenue (known_y’s argument).
B5:B12 corresponds to the corresponding years (known_x’s).
B13:B15 represents the years for which you want to forecast revenue (new_x’s).

How to Forecast Revenue in Excel Using TREND Function

Method 2 – Applying the FORECAST Function

Similarly, you can use the FORECAST function or the FORECAST.LINEAR function to forecast revenue for the upcoming year. The formula is as follows:

=FORECAST(B13:B15,C5:C12,B5:B12)

Here:

new_x’s argument is located at the first position, where it was in the last position in the TREND function.

Though there is a slight difference in the case of placing the arguments, the output remains the same.

How to Forecast Revenue in Excel Applying FORECAST Function

Note: The order of arguments is slightly different from the TREND function, but the output remains the same. The key difference is that the TREND function can predict new values and determine current trends based on historical data, while the FORECAST function predicts only new values.

Method 3 – Using the GROWTH Function for Yearly Revenue Forecasting

The function allows you to forecast revenue based on an exponential relationship. The formula for this dataset would be:

=GROWTH(C5:C12,B5:B12,B13:B15)

How to Forecast Revenue in Excel Using GROWTH Function

The output will differ from the TREND function because GROWTH considers exponential relationships.

Method 4 – Simple Moving Average Method for Monthly Revenue Forecasting

If you have historical revenue data on a monthly basis, you cannot directly use the TREND, FORECAST, or GROWTH functions. Instead, consider the Moving Average method:

Monthly Revenue Forecasting

Go to File and select Options to open the Excel Options dialog box.
Hover over Add-ins, select Analysis ToolPak, and click Go.

Add Data Analysis Feature

Check the box next to the Analysis ToolPak add-in and press OK.

Add Data Analysis Feature

Go to the Data tab and click on Data Analysis from the Analysis ribbon.

Simple Moving Average Method

Choose the Moving Average tool and click OK.

Simple Moving Average Method

Specify the Input Range as $C$5:$C$14, set the Interval to 3, and define the Output Range as $D$5:$D$14.
Check the box for Chart Output and press OK.

Simple Moving Average Method

Note: The interval of 3 means calculating the Moving Average over 3 months. For example, if you want to calculate the Moving Average for 5 months, input the value 5.

You’ll get the predicted revenue for May ’22 in cell D14.

How to Forecast Revenue in Excel Simple Moving Average Method

For better visualization, create a chart.

Simple Moving Average Method

Method 5 – Exponential Smoothing for Monthly Revenue Forecasting

To achieve more accurate revenue forecasts, consider using the Exponential Smoothing or Exponential Moving Average (EMA) method. Follow these steps:

Select the Exponential Smoothing tool from the Data Analysis pop-up and click OK.

Exponential moothing Method

Define the Input Range as $C$5:$C$14, set the Damping factor to 0.3, and specify the Output Range as $D$5:$D$14.
Check the box for Chart Output and click OK.

Exponential moothing Method

Note: The default damping factor is 0.3, but for more reliable output, you can calculate a specific damping factor.

The resulting EMA values will appear in cells D5:D14, with the last cell showing the upcoming revenue.

How to Forecast Revenue in Excel Exponential Smoothing Method

Visualize the EMA using the chart.

How to Forecast Revenue in Excel Exponential Smoothing Method

Method 6 – Forecast Sheet for Date-Wise Revenue Forecasting

If your historical revenue data is provided on a date-wise basis (as shown in the dataset), Excel offers an excellent Forecast Sheet feature:

Date-wise Revenue Forecasting

Hover over any cell within the dataset.
Go to the Data tab and choose Forecast Sheet from the Forecast ribbon.

Creating Forecast Sheet

In the dialog box that appears, specify the Forecast End timeline.
Set the Timeline Range to $B$4:$B$14 and the Value Range to $C$4:$C$14.
Choose the Aggregate Duplicate Using method (e.g., Average).
By default, the Confidence Interval is 95%, meaning that 95% of the forecasted revenues will fall within the range.
Click Create.