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

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.

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

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

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.

Regression Analyis to Forecast Sales.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!