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.

**Table of Contents**hide

## 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**.

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 (

**, and**

*known_x’s)***B13:B15**represents the year for what you want to forecast revenue (

**).**

*new_x’s***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.

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

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.

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.

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

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

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

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

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