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.


Download Practice Workbook


6 Methods to Forecast Revenue in Excel

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.

Yearly Revenue Forecasting

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.

Using TREND Function

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

How to Forecast Revenue in Excel Using TREND Function

Read More: Forecasting in Excel Using FORECAST Function & Auto Fill Handle Tool


2. Applying FORECAST Function

Similarly, you can utilize the FORECAST function or 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.

How to Forecast Revenue in Excel Applying FORECAST Function

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.

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


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.

How to Forecast Revenue in Excel Using GROWTH Function

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!

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


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.

Monthly Revenue Forecasting

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.

Add Data Analysis Feature

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

Add Data Analysis Feature

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.

Simple Moving Average Method

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

Simple Moving Average Method

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

Simple Moving Average Method

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.

How to Forecast Revenue in Excel Simple Moving Average Method

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

Simple Moving Average Method

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


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.

Exponential moothing Method

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

Exponential moothing Method

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.

How to Forecast Revenue in Excel Exponential Smoothing Method

Moreover, the chart will be as follows.

How to Forecast Revenue in Excel Exponential Smoothing Method

Read More: How to Do Budgeting and Forecasting in Excel (2 Suitable Ways)


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.

Date-wise Revenue Forecasting

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.

Creating Forecast Sheet

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.

How to Forecast Revenue in Excel Creating Forecast Sheet1

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

How to Forecast Revenue in Excel Creating Forecast Sheet

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

How to Forecast Revenue in Excel Creating Forecast Sheet

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


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)

Conclusion

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


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo