In business management, linear regression can be used to predict sales. Estimating the regression equation based on empirical data owned by the company can be used to predict product sales the next time.
For example, a company aims to observe the effect of price on product sales. The intercept value and the estimated coefficient of the price variable are obtained based on the estimation results using time series data.
Furthermore, using the estimation from the linear regression analysis, we can construct the regression estimation equation. Based on the regression estimation equation, it can be used to predict product sales in the next period.
The accuracy and precision of the product sales prediction depend on the P-value and the coefficient of determination of the equation created. Furthermore, researchers can enter the value of the price variable, and then product sales predictions can be obtained.
Predict product sales using linear regression
Product sales predictions can be estimated using linear regression. Several approaches can be used to predict product sales using regression.
On this occasion, Kanda Data will discuss product sales prediction estimates based on a simple linear regression equation. Therefore, product price data is needed because the goal is to determine product sales predictions.
Here, researchers can collect time series data consisting of product sales and price variables. In this case study, it is estimated that product prices affect product sales. Therefore, product sales are created as the dependent variable and product prices are created as the independent variable.
For example, researchers have collected annual time series data from 2011 to 2020. The data collected includes product sales measured in units and prices measured in thousand USD per unit. In detail, the data that has been collected can be seen in the table below:
The researcher needs to create a regression equation specification based on the data the researcher has collected according to the table above. Based on the research objectives and the data that has been collected, the specifications of the regression equation are as follows.
Y = b0 + b1X + e
Y = Variable product sales (units)
b0 = Intercepts
b1 = Estimated coefficient of the Price variable
X = Price variable (thousand USD)
e = Error terms
Based on the specifications of the regression equation, the next step is to find the intercept value and the estimated coefficient of the price variable. On this occasion, I will determine the intercept value (b0) and b1 using Excel.
How to determine intercept (b0) and b1 in Excel
Several ways can be used to find the intercept value (b0) and b1. Even in Excel, we can calculate manually or use the data analysis tools provided by Excel. On this occasion, I used the data analysis tool in Excel.
If you do not have this menu yet, please enable the data analysis toolpak following the tutorial in my previous article entitled: “How to Activate and Load the Data Analysis Toolpak in Excel“.
For those of you who want to calculate bo and b1 values manually, you can follow the tutorial in my article entitled: “Calculate Coefficients bo, b1, and R Squared Manually in Simple Linear Regression“
To determine b0 and b1 using Excel data analysis, you click data, then click Data Analysis. Next, several choices of data analysis tools provided by Excel will appear. You select regression, and then the regression window will appear.
After the regression window appears, the next step is inputting data for variables Y and X. For input variable Y, please input all variable data for sales products, including their labels. In the same way, input for variable X is also done by inputting all data for variable X, including its label.
Next, enable the label and confidence level with a value of 95%. Here, I save the analysis output on the same Excel sheet. In detail, the stages of analysis can be seen in the image below:
After you have followed all the steps in linear regression analysis using Excel, click ok. After you click ok, the analysis output will appear on the same Excel sheet. Of the various outputs in Excel, I will only use the intercept value (b0) and the estimated coefficient (b1). The results of the analysis using Excel can be seen in the image below:
How to Calculate Predicted Y in Linear Regression Equations
Based on the results of linear regression analysis using Excel in the previous paragraph, the values b0 = 218.385 and b1 = -18.161 are obtained. Based on the estimated coefficient value, the regression equation can be arranged as follows:
Y = 218.385 – 18.161X
Based on these equations, we can determine the predicted value of Y. In order to be able to compare the actual value (Y) and the predicted Y, we can create a calculation template in excel, as shown in the table below:
For example, I will show you how to calculate 2011, 2012 and 2013. To calculate predicted Y in 2011, you can calculate it in the following way:
Y = 218.385 – 18.161X
Y = 218.385 – 18.161(0.46)
Y = 210.00
In the same way, the calculation of predicted Y for 2012 and 2013 is as follows:
Y = 218.385 – 18.161X
Y = 218.385 – 18.161(0.69)
Y = 205.81
Y = 218.385 – 18.161X
Y = 218.385 – 18.161(0.54)
Y = 208.61
This calculation method will be easier and faster to calculate using Excel following the template above. The results of predicted Y calculations for 2011-2020 in detail can be seen in the table below:
Based on the table above, we can compare the actual value (Y) and the predicted Y. The difference between the actual value (Y) and the predicted Y is called the residual.
Furthermore, researchers can also apply it to business management. For example, researchers will increase the price by 100 USD/unit, so the impact on product sales can be estimated.
Researchers can also perform elasticity calculations to see the sensitivity of price changes to changes in product sales. It is the article I can write on this occasion. Hopefully useful for all of you. Thank you!
