How to Forecast Revenue in Excel (6 Simple Methods)

Frequently, you might need to forecast the revenue of your company in the upcoming time. Luckily, Microsoft Excel provides some functions as well as features to forecast based on historical data efficiently. In this article, I’ll discuss 6 methods on how to forecast revenue in Excel based on yearly, monthly, and date wise with proper explanation.

How to Forecast Revenue in Excel: 6 Methods

Firstly, let me clarify one thing. What do you know about revenue?

Simply revenue is the earning amount of an organization or company. For example, the Revenue (in the E5:E14 cells) of a company from 2014 to 2021 is provided. Here, the revenue is generated through the multiplication of Unit Price and Quantity.

Now, you need to forecast based on the given revenue. The first 3 methods are useful to forecast yearly revenue. Then, the next two methods deal with monthly forecasting. And, the rest method focuses on date-wise forecasting.

Let’s dive into the methods.

1. Using TREND Function to Forecast Revenue on Yearly Basis

Let’s say, you want to forecast the revenue in 2022, 2023, and 2023 based on the historical revenue data from 2014 to 2021.

In the beginning, I’ll show you the application of the TREND function to forecast revenue.

The syntax of the TREND function is-

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

In the case of this dataset, the formula will be as follows.

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

Here, C5:C12 is the cell range representing historical revenue (known_y’s argument), B5:B12 refers to the corresponding year (known_x’s), and B13:B15 represents the year for what you want to forecast revenue (new_x’s).

2. Applying FORECAST Function

Similarly, you can utilize the FORECAST function or the FORECAST.LINEAR function to forecast revenue in the upcoming year.

Just insert the following formula.

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

here, new_x’s argument is located at the first position, where 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.

Note: there is another difference between TREND and FORECAST functions. The first one is able to predict new values as well as determine current trends based on historical data whereas the FORECAST can predict only new values based on the given data.

3. Using GROWTH Function for Forecasting Yearly Revenue

Again, you can forecast revenue using the GROWTH function. The formula regarding the given dataset will be-

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

So, the output will be like the following.

You might be confused by the fact that the syntax of the GROWTH and TREND function is the same but the output is different. Actually, the TREND function forecasts based on the linear relationship while the GROWTH function considers exponential relation.

That’s the main difference!

4. Simple Moving Average Method to Forecast Revenue (Monthly)

Assuming that you have historical revenue on a monthly basis instead of the year. In such a situation, you cannot use the TREND, FORECAST, and GRWTH functions as the month value are available. Look at the below dataset.

Fortunately, you can use the Moving Average method to do that. Please follow the below steps.

Firstly, go to File > Options to open the Excel Options dialog box.
Next, move the cursor over the Add-ins option> Analysis ToolPak > Go.

Later, check the box before the Analysis ToolPak add-in and press OK.

Now, you’re ready to work with the Moving Average.

Just go to the Data tab > click on the Data Analysis option from the Analysis ribbon.

Right now, select the Moving Average tool and press OK.

Specify the Input Range as $C$5:$C$14, Interval as 3, and Output Range as $D$5:$D$14.
Also, check the box before Chart Output and press OK.

Note: Here, Interval 3 means the period for determining Moving Average. For example, if you want to calculate the Moving Average for 5 months, the input will be 5.

Finally, you’ll get the following output where the D14 cell represents the predicted revenue in May’22.

For a better visual representation, you may also use the following chart.

5. Utilizing Exponential Smoothing Method for Forecasting Monthly Revenue

If you want to forecast more accurate revenue, you may employ the Exponential Smoothing Or Exponential Moving Average (EMA) method.

Initially, select the Exponential Smoothing tool from the Data Analysis pop-up, and press OK.

Define the Input Range as $C$5:$C$14, Damping factor as 0.3, and Output Range as $D$5:$D$14.
Lastly, check the box before Chart Output and press OK.

Note: Here, I used the Damping factor as 0.3 by default. For getting more reliable output, you can calculate the Damping factor.

Eventually, you’ll get the following EMA in the D5:D14 cells where the last cell shows the upcoming revenue.

Moreover, the chart will be as follows.

6. Creating Forecast Sheet to Forecast Revenue

Sometimes, your historical revenue data can be given on the date-wise as shown in the below dataset.

In that case, Excel provides you with an outstanding Forecast Sheet feature.

Keep your cursor over any cell within the given dataset and go to the Data tab > choose the Forecast Sheet option from the Forecast ribbon.

Immediately, you’ll see the following dialog box.

Primarily, specify the Forecast End timeline.
Check the Timeline Range as $B$4$B$14, Value Range as $C$4$C$14, Aggregate Duplicate Using as Average method.
Here, the Confidence Interval is 95% (by default) which means that 95% of the forecasted revenues would be within the range.
Lastly, press the Create button.

Then, you’ll get the following chart forecasting revenue in the upcoming days.

Besides, you’ll get the following table including the Forecast (Revenue).

Things to Remember

If you’re not a Microsoft 365 user, don’t forget to press CTRL + SHIFT + ENTER in case of using an array formula (e.g. GROWTH function)

Download Practice Workbook

Methods to Forecast Revenue.xlsx

Conclusion

So, this is how you can forecast revenue in Excel. Now, choose any method based on your requirements. Anyway, don’t forget to share your thoughts in the comment section below.

<< Go Back to Forecasting in Excel |Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!