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.

Using Regression Analysis to Forecast Sales in Excel

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.
Open the Add–ins tab.
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.

Using Regression Analysis to Forecast Sales in Excel

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

Using Regression Analysis to Forecast Sales in Excel

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.

Using Regression Analysis to Forecast Sales in Excel

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:

Using Regression Analysis to Forecast Sales in Excel

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: