The article will provide you with some methods on how to **forecast sales **in Excel. This is important while you consider predicting your business status. **Forecasting **your **sales **may concern you about whether you need to take a step for the improvement of your business.

We have sales information about a company for the **first 6 months** of the year in the dataset.

**Table of Contents**hide

## Download Practice Workbook

## 5 Ways to Forecast Sales in Excel

**1. Using Excel FORECAST Function to Forecast Sales**

The simplest way to **forecast sales **in Excel would be to use **the FORECAST Function**. Say, you want to **forecast sales **for the next **3 months**. Let’s go through the process below.

**Steps:**

- First, make a column for
**forecasting sales**and type the following formula in cell**C13**.

`=FORECAST(B13,$C$5:$C$10,$D$5:$D$10)`

Here, **the FORECAST Function **returns the **forecasted sales **of the **7th month **by taking a **linear **relation between **sales** and **periods**.

- Press the
**ENTER**button and you will see the**forecasted sales**for the**7th month**.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

You can also use another **FORECAST Function **known as **the FORECAST.ETS function**. It will return the **forecasted sales**, taking **exponential **change from the previous **sales**.

- Use the formula given below in cell
**C13**.

`=FORECAST.ETS(B13,$C$5:$C$10,$D$5:$D$10)`

- Then, press
**ENTER**.

Here, **the FORECAST.ETS Function **returns the **forecasted sales **for the next **3 months **by taking an **exponential **relation between **sales **and **periods**.

Thus you can **forecast sales **by using Excel **FORECAST **and **FORECAST.ETS Functions**.

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

**2. Using Excel Forecast Sheet Feature to Forecast Sales**

The best way to visualize **forecast sales **for the following months is to use **the Forecast Sheet**. Let’s go through the procedure below for a better understanding.

**Steps:**

- First, I define the
**months**as**numbers**kept in the**Period**

- Then select the
**range C4:D10**and go to**Data**>>**Forecast**>>**Forecast Sheet**

- This operation will open the
**Create Forecast Worksheet Window**. You will see it will create a**Forecast Sheet**for the next**4 months**by default. In the window, we see**3 forecast lines**which include**Forecast(Sales)**,**Lower Confidence Bound(Sales)**and**Upper Confidence Bound(Sales)**.

- You can change the
**Forecast Sheet Options**if you want. I brought the following changes to this sheet. - I set the
**Confidence Interval**to**90%**,**Forecast Start**to**6**as I want to see the**forecast**of**sales**from the**6th**month, and also set the**Forecast End**to**11**as I wish to**forecast sales**upto the**11th** - After that, click on
**Create**.

You will see the **forecast **of **sales **on a table along with a **graph** in a new sheet.

Thus you can visualize **sales forecasts **using the **Forecast Sheet**.

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

**3. Applying LINEST Function to Forecast Sales in Excel**

Another way to **forecast sales **is to apply **the LINEST Function**. We know that the basic equation for a straight line is **y = mx + c**. By using this function, we will determine the value of **slope **(**m**) and the constant **c** from the given data of **sales**. We will determine the **forecasted sales **for the following **3 **months from **June**. Let’s see the process below.

**Steps:**

- First, make some necessary columns in the dataset.

- Type the following formula in cell
**B13**.

`=LINEST(D5:D10,C5:C10,TRUE,FALSE)`

Here, **the LINEST Function **returns the **constant values **of the **linear regression **between **sales **and **periods**.

- Hit
**ENTER**and you will see the values of the constants of the equation.

Here, **the LINEST function** calculates the constant values **m **and **c** by using the **least squares** method that best fits the data.

- Now to find the
**Trend**values, type another formula in cell**E5**.

`=$B$13*C5+$C$13`

- Press
**ENTER**to see the**Trend Value**for the**first month**.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

- To calculate the monthly
**deviation**of the**trend values**from the**sales values**, type the following formula in cell**F5**, press**ENTER**and**AutoFill**the lower cells.

`=D5/E5`

- Regarding the determination of the
**Seasonality Index**, first, calculate the**average**of the**Sales**with the following formula. We are assuming that the**Seasonality Index**becomes the same after every**6 months**. So the**Seasonality Index**of the**1st month**will be equal to the**Seasonality Index**of the**7th month**.

`=AVERAGE(D5:D10)`

- Then type the following formula in cell
**G5**, press**ENTER**and**AutoFill**the lower cells.

`=D5/$D$13`

- After that, you will need the
**trend values**for the next**3 months**. To find the values of**trends**, type the following formula in cell**F13**, press**ENTER**and**AutoFill**the lower cells

`=$B$13*E13+$C$13`

- Later, just type the following formula in cell
**G13**, press**ENTER**and**AutoFill**the lower cells.

`=G5*F13`

Thus you can **forecast sales **by using **the** **LINEST Function**.

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

**4. Using Mathematical Formula to Forecast Sales in Excel**

In this section, we will use a mathematical formula to determine **sales forecasts**. We are going to use the following formula for this purpose.

**F = KS**_{Actual }**+ (1 – K)S**_{Forecast}

Where, **F = Forecasted Sales**

**K = Constant**that

**Smooth**the change in

**function**

**S**

_{Actual }**= Actual sales**of the previous months

**S**

_{Forecast}**= Forecasted sales**of the previous months

Let’s go through the section for a better understanding.

**Steps:**

- We need to assume some things first. Let’s assume the
**forecasted sales**of the**2nd month**(**S**) is_{Forecast}**29,580 dollars**. - Also, we consider
**K**equal to be**4**. We will find the**forecast**for**next month**.

- Type the following formula in cell
**D6**.

`=$B$14*C5+(1-$B$14)*D5`

- Hit the
**ENTER**button and you will see the**forecasted sales**for**February**.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

Thus you can **forecast sales **by using a **mathematical formula**.

**Read More:** **How to Calculate Forecast Accuracy Percentage in Excel (4 Easy Methods)**

**5. Utilizing Moving Average Concept to Forecast Sales in Excel with Trendline**

Another way to **forecast sales **in Excel is to use the **moving average concept**. We will be doing **forecasts **on account of **averages **of sales every **3 **months. Let’s follow the description below.

**Steps:**

- First, make some necessary
**columns**and type the following formula in cell**E7**.

`=AVERAGE(C5:C7)`

Here, **the AVERAGE Function** will return the **average **of the data in the **range C5:C7**.

- Press
**ENTER**and you will see the**average**for the first**3 months**.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

- Now, select the
**range C4:E10**and go to**Insert**>>**Line Chart**>>**2D Line**

- After that, a
**chart**will appear

- Click on the
**plus icon**of the chart and then select**Trendline**>>**More Option**

- A
**dialog box**will appear. Select**Moving Averages**and click**OK**.

- You will see the
**Format Trendline**window at the right side. Select**Moving Average**and set the**Period**to**3**.

- After that, you will see the
**forecasted sales**by**moving average**.

- If you want to see the
**forecast**for the next**two months**, go to**Format Trendline**again and then select any**Trendline**option (I chose**Exponential**) and set the**Forward Forecast**to**2**.

After that, you will see the **forecasted sales **for the next **2 **months in the chart.

Thus, you can **forecast sales **by **moving average **concept and **Trendline**.

**Read More:** **How to Forecast Revenue in Excel (6 Simple Methods)**

## Practice Section

Here, I’m giving you the dataset of this article so that you can practice on your own.

## Conclusion

The bottom line is that you can learn some ideas about how to **forecast sales **in Excel after finishing this article. There are various ways to do this, but not every method will give you the best probability. In my opinion, using **Forecast Sheet **would be the best idea to **forecast sales **in Excel because it returns results according to the last data changes. If you have any other ideas or any feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming article.