The following article illustrates 3 different methods to **forecast sales** by using regression analysis in Excel. We’ll find the regression line equation of the existing dataset. This equation can be used to find the forecast data easily.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**3 Easy Methods to Use Regression Analysis to Forecast Sales in Excel **

**Regression analysis** is a powerful statistical method. We use it for analyzing the relationship between two or more variables in a dataset. Basically, it examines the influence of one or more independent variables on a specific dependent variable and how these factors are connected to each other. This helps us to decide on which factors to keep or ignored to make a mathematical model.

A **simple linear regression equation **is-

**y = b*x+ a**

Where

y= the **dependent **variable.

x= the **independent **variable.

a= the **intercept **of the **Y-axis** for the regression line.

b = the **slope **of the regression line.

We’re going to show several methods to find this linear regression line that can forecast based on the relationship of the existing data.

For this article, to show **sales prediction **we’ll use the following sample **dataset **that represents the **sales amount **with the **number **of **advertisements**.

We want to run a **regression analysis **of the above dataset and then **predict sales **based on the **regression equation **developed from the **analysis**.

**1. ****Use of Data Analysis Tool to Forecast Sales by Using Regression Analysis in Excel **

In this illustration, we’ll use the **Data Analysis tool **of **Excel **to run a **regression analysis **for the dataset. Follow the steps to accomplish the task.

**Step 1: Active the Data Analysis Tool**By default the Analysis toolbox in the Data tab is

**disabled**. To make it

**enabled,**let’s follow the instructions.

**Go**to the**Options**from the**File menu items**of**Excel**.**Open**the**Add**–**ins**tab.**Select**the**Excel Add-ins**- Then
**click**on the**Go**button**.**

- From the
**Add-ins**window,**check**the**Analysis ToolPak**option and hit**OK.**

The above steps will make the **Analysis toolbox **appear in the** Data tab **in the **Excel Ribbon.**

**Step 2: Regression Analysis of the Dataset**To run a regression analysis, do the following-

**Go**to the**Data**tab in the**Excel Ribbon**.**Click**on the**Data Analysis**option.

- In the
**Data Analysis**window,**choose**the**Regression**option and hit**OK**.

- From the
**Regression window**,

(i) choose the “**Sales**”**column**as the**Input Y Range.**(ii) choose the “**No of Adds**” column as the**Input X Range.**(iii) check the**Labels checkbox**.

(iv) click on the**New Worksheet Ply**option.

(v) check the**Residuals**option.

(vi) finally hit the**OK**button.

**Step 3: Build the Linear Regression Equation with the Coefficients**From the analysis result, use the

**coefficients Intercept**and

**No of Ads**to write the Linear regression equation as-

**y= 71.98 * x + 8478.92**Where

**y = Sales**

**x= No of Ads**

Now, with the regression equation, we can predict sales for a given no of ads.

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

**2. ****Apply Formulas to Forecast Sales by Using Regression Analysis in Excel **

**2.1 Use of LINEST Function **

The** LINEST function **in Excel uses the** least-squares regression method **to **analyze **a **dataset **and **calculates **a **best fit straight line. **Then it returns an **array **that **describes **that **straight line**. The syntax of the function is-

**LINEST(known_y’s, [known_x’s], [const], [stats])**

**Task**: we want to find the **coefficients **for the **linear regression equation y = b*x + a **to **forecast sales **using the **LINEST **function.**Solution**: **Select two adjacent cells **and put the **following formula**–

`=LINEST(B2:B12,A2:A12)`

And **press Ctrl + Shift + Enter. **

**Formula Explanation:** As we know that the **INTERCEPT **function **returns **an **array of values **as **output**, we have to **press Ctrl + Shift + Enter** altogether to execute the function.**Output**: We’ve got the **coefficients **for the **linear regression equation**.

The equation turns into-**y= 71.98 * x + 8478.92**Where

**y = Sales**

**x= No of Ads**

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

**2.2 Combination of INTERCEPT and SLOPE Functions **

The **INTERCEPT function **outputs the **value **of the **y-axis interception** for the **linear regression line **based on the **given x_values** and** given y_values. **The syntax is-

**INTERCEPT(known_y’s, known_x’s)**

On the other hand, the** SLOPE function** returns the **slope **of the **linear regression line **based on the **given x_values** and** given y_values. **The syntax is-

**SLOPE(known_y’s, known_x’s)**

**Task**: we want to find the **coefficients **for the **linear regression equation y = b*x + a **to **forecast sales **using the **INTERCEPT **and **SLOPE **functions.**Solution**: In **cell D2**, put the following formula-

`=INTERCEPT(B2:B12,A2:A12)`

And in **cell E2**, write the following formula-

`=SLOPE(B2:B12,A2:A12)`

**Output**: We’ve got the **coefficients **for the **linear regression equation**.

The equation turns into-**y= 71.98 * x + 8478.92**Where

**y = Sales**

**x= No of Ads**

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

**3. ****Draw a Liner Regression Graph to Forecast Sales in Excel **

**Task**: **Draw **a **graph **to get the **linear regression line equation **to **forecast sales**.**Solution**: Follow the simple steps below-

**Select**the whole**dataset.**

- Then
**go**to the**Insert**tab. **Click**on the**Scatter plot**.

- The above step added a
**graph**to the worksheet. - Choose the
**Trendline option**to**add**a**linear regression line**from the**Chart Elements**options.

- Now
**right**–**click**on the**trendline**and select**Format Trendline.**

- Check the “
**Display Equation on chart”**option.

**Output**: All these steps showed the **linear regression **line **equation **on the **plotted graph**.

And the **equation **is-

y= 17.988 x + 8479

Where**y = Sales**

**x= No of Ads**

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

**Notes**

We’ve found the equation for the dataset using all three methods as they all follow the same regression analysis for calculation.

**Conclusion**

Now, we know how to forecast sales in Excel using regression analysis with suitable examples. Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.