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.