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.


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.

Using Regression Analysis to Forecast Sales in Excel

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 Addins 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.

Using Regression Analysis to Forecast Sales in Excel

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

Using Regression Analysis to Forecast Sales in Excel

  • From the Regression window,
    (i) choose the “Salescolumn 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.

Using Regression Analysis to Forecast Sales in Excel

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

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

Using Regression Analysis to Forecast Sales in Excel

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)

Using Regression Analysis to Forecast Sales in Excel

And in cell E2, write the following formula-

=SLOPE(B2:B12,A2:A12)

Output: We’ve got the coefficients for the linear regression equation.

Using Regression Analysis to Forecast Sales in Excel

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.

Using Regression Analysis to Forecast Sales in Excel

  • The above step added a graph to the worksheet.
  • Choose the Trendline option to add a linear regression line from the Chart Elements options.

Using Regression Analysis to Forecast Sales in Excel

  • Now rightclick 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.

Using Regression Analysis to Forecast Sales in Excel

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.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo