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

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

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 in Excel

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

**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 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:Â **How to Forecast Sales Using Historical Data in Excel

**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 Sales Growth Rate in Excel

**Notes**

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

**Download Practice Workbook**

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

**Conclusion**

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

## Related Articles

- How to Calculate Forecast Accuracy Percentage in Excel
- How to Calculate Accuracy and Precision in Excel
- Time Series Forecasting Methods in Excel
- How to Forecast Call Volume in Excel
- How to Do Budgeting and Forecasting in Excel

**<< Go Back to Forecasting in ExcelÂ |Excel for FinanceÂ |Â Learn Excel**