Forecasting in Excel
Predictive Analytics

Forecasting in Excel using Linear Regression

In this article, we have explained Excel formula and Linear Regress to forecast sales in upcoming month. We have used 1st Jan 2019 to 31st Mar’19 sales data to do the forecasting for Apr’19.

Historical Sales data
Historical Sales data

 

We have used for different method to do the forecasting-

Forecast formula:

We have used forecast formula to do the forecasting for 1st APR’19 till 30th APR’19. Below is the Syntax of Forecast formula

Syntax

FORECAST(x, known_y's, known_x's)

The FORECAST function syntax has the following arguments:

  • X:  The data point for which you want to predict a value.
  • Known_y’s :  Required. The dependent array or range of data.
  • Known_x’s:  Required. The independent array or range of data.
Forecast formula in Excel
Forecast formula in Excel

Trend formula:

The TREND function returns values along a linear trend. It fits a straight line (using the method of least squares) to the array’s known_y’s and known_x’s. TREND returns the y-values along that line for the array of new_x’s that you specify.

Syntax

TREND( known_y's, [known_x's], [new_x's], [const] )
TREND Formula in Excel
TREND Formula in Excel

Linear regression equation using Excel formula:

We have used Excel formulas to get the foretasted sales using linear regression equation.

Linear Regression Equation  Y = mx +c

Where x is an independent variable, Y is a dependent variable, m is the slope and c is intercept. So we have used excel formula Y = SLOPE * x + INTERCEPT

m  =SLOPE($B$2:$B$91,$A$2:$A$91)

c =INTERCEPT($B$2:$B$91,$A$2:$A$91)
Linear regression equation using Excel formula
Linear regression equation using Excel formula

Linear regression equation using Excel Chart:

Just create the scatter chart or line chart for Actual sales data and add a linear regression trend line and check the Display Equation on the chart and Display R-squired value on the chart. Now Equation and R-squired value will be available on the chart.

Linear regression equation using Excel Chart
Linear regression equation using Excel Chart

Copy the equation and put in the excel cell and change the x value with cell reference like we have taken below-

=1.0558*A92 – 45744
Forecast using Linear regression equation using Excel Chart
Forecast using Linear regression equation using Excel Chart

Click here to download this practice file.

Watch the step by step video tutorial:

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com