# How to Forecast Sales Using Regression Analysis in Excel (3 Methods)

## Definition of Regression Analysis

Regression analysis is a powerful statistical method used to analyze the relationship between two or more variables in a dataset. Specifically, it examines how one or more independent variables influence a dependent variable and how these factors are interconnected. This analysis helps us decide which factors to include or ignore when creating a mathematical model.

The simple linear regression equation is:

y = b*x+ a

Where:

• (y) represents the dependent variable.
• (x) represents the independent variable.
• (a) is the intercept of the Y-axis for the regression line.
• (b) is the slope of the regression line.

## Dataset Overview

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

## Method 1 – Using Excel’s Data Analysis Tool

### Step 1 – Active the Data Analysis Tool

By default, the Analysis toolbox in the Data tab is disabled. To enabled it:

• Go to the Options from the File menu in Excel.
• Select Excel Add-ins and click Go.

• In the Add-ins window, check the Analysis ToolPak option and hit OK.

### Step 2 – Regression Analysis of the Dataset

• Go to the Data tab in the Excel ribbon.
• Click on Data Analysis.

• In the Data Analysis window, choose Regression and click OK.

• Configure the following settings:
• Input Y Range: Choose the Sales column.
• Input X Range: Choose the No of Adds column.
• Check the Labels checkbox.
• Click New Worksheet Ply.
• Check the Residuals option.
• Click OK.

Step 3 – Build the Linear Regression Equation
Use the coefficients Intercept and No of Ads from the analysis result to write the linear regression equation:

y= 71.98 * x + 8478.92

• Where:
• (y) represents sales.
• (x) represents the number 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

## Method 2 – Applying Formulas to Forecast Sales Using Regression Analysis in Excel

### 2.1 Use of LINEST Function

The LINEST function in Excel employs the least-squares regression method to analyze a dataset and calculate the best-fit straight line. It returns an array that describes this 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 enter the following formula:
`=LINEST(B2:B12,A2:A12)`
• Press Ctrl + Shift + Enter.

Formula Explanation: Since the INTERCEPT function returns an array of values, we use Ctrl + Shift + Enter to execute it.

Output: We obtain the coefficients for the linear regression equation:

y= 71.98 * x + 8478.92

Where:

• (y) represents sales.
• (x) represents the number of ads.

### 2.2 Combination of INTERCEPT and SLOPE Functions

• The INTERCEPT function provides the y-axis intercept value for the linear regression line based on given x-values and y-values (syntax: INTERCEPT(known_y’s, known_x’s)).
• The SLOPE function returns the slope of the linear regression line using the same x-values and y-values (syntax: SLOPE(known_y’s, known_x’s)).

Task: We want to find the coefficients for the linear regression equation y = b*x + a b the INTERCEPT and SLOPE functions.

Solution:

• In cell D2, insert the following formula:
`=INTERCEPT(B2:B12,A2:A12)`

• In cell E2, enter the following formula:
`=SLOPE(B2:B12,A2:A12)`

Output: We obtain the coefficients for the linear regression equation:

y= 71.98 * x + 8478.92

Where:

• (y) represents sales.
• (x) represents the number of ads.

## Method 3 – Drawing a Linear Regression Graph to Forecast Sales in Excel

Task: Create a graph to determine the linear regression line equation for sales forecasting.

Solution:

• Select the entire dataset.

• Go to the Insert tab.
• Click on the Scatter plot.

• This adds a graph to the worksheet.
• Choose the Trendline option from Chart Elements.

• Right-click the trendline and select Format Trendline.

• Check the Display Equation on chart option.

Output: The linear regression line equation is displayed on the plotted graph:

And the equation is-
y= 17.988 x + 8479

Where:

• (y) represents sales.
• (x) represents the number of ads.

## Related Articles

<< Go Back to Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF