Researchers have widely used linear regression analysis to analyze the effect of a variable on other variables. Linear regression analysis consists of the dependent variable and the independent variable. The difference between the two is that the dependent variable is the affected variable, while the independent variable is the influencing variable.
Linear regression analysis can be divided into simple and multiple linear regression analyses. In simple linear regression analysis, only one dependent variable and one independent variable, whereas in multiple linear regression analysis, the number of independent variables consists of at least two variables.
Due to the need for researchers to understand linear regression analysis and its interpretation, Kanda Data will write a tutorial on performing linear regression using Excel data analysis.
As previously known, Excel can also be used to perform inferential statistical analysis. One of the inferential statistical tests that can be used is the linear regression analysis of the OLS method.
Mini research case examples
An example of a mini research case study on this occasion is simple linear regression. Researchers aim to determine the effect of price on product sales.
The data used is monthly time series data for the last 12 months. The product sales variable is the dependent variable, and the price is the independent variable. The data that researchers have collected can be seen in the table below:
Based on the data above, researchers can use the linear regression analysis of the OLS method. Due to the use of the OLS regression method, there are several assumptions that researchers must meet.
The minimum assumption tests that need to be carried out by researchers are the normality test, linearity test, non-heteroscedasticity test, and autocorrelation test. In this tutorial, Kanda Data does not write an OLS assumption test but will immediately provide a tutorial on how to test a simple linear regression using data analysis in Excel.
The stages of the linear regression test in Excel
The steps for simple linear regression analysis in Excel can use in the data analysis menu in Excel. After Excel is opened, the researcher can click on the “Data” menu, then the researcher selects the “Data Analysis” menu, which is in the upper right corner; which in detail can be seen in the image below:
Suppose the researcher does not find the Data Analysis menu after clicking on Data menu. In that case, the researcher must first activate the Data Analysis menu in Excel. For a tutorial to activate the Data Analysis Toolpak menu in Excel, you can read the previous article entitled: “How to Enable Data Analysis Button for t-test in Excel“.
After the researcher clicks on the Data Analysis menu, the Data Analysis window will then appear. In the Data Analysis window, several analysis tools are provided by Excel.
The next step that needs to be done by researchers is to look for regression and then click ok. These steps in detail can be seen in the image below:
After the researcher clicks on regression, the regression window will appear. In the next stage, the researcher needs to input the dependent variable data, namely product sales, and the independent variable, namely price.
Researchers can input all existing data, including the label. Then, the researcher must enable the label, which in detail can be seen in the image below:
Based on the picture above, researchers can set a confidence level of 95%. It shows that the maximum error rate in the study is 5%.
Next, in the output option, Excel provides an option for researchers to save the output of the analysis results. Will the analysis output be saved on the same Excel sheet, a different Excel sheet, or a new Excel file?
The analysis output is saved on the same Excel sheet based on the picture above. The last step that researchers need to do is click ok.
Interpretation of Linear Regression Analysis Output
If all the analysis steps have been carried out correctly, then the analysis output will appear. In the default analysis provided by Excel, the output will be divided into three tables which can be seen in the image below:
Based on the output of the linear regression analysis using Excel above, it can be seen that there are three tables. The first table presents the value of the coefficient of determination, then the second table presents the ANOVA table, and the third table presents the T-statistic and P-value.
The results of the analysis show that the coefficient of determination is 0.7322. It can be interpreted that the price variable’s variance can explain the variance of the product sales variable of 73.22%. The remaining 26.78% is explained by other variables not included in the model.
Furthermore, the ANOVA table shows that the F-statistic value is 27.3415 with a p-value smaller than 0.05. It can be interpreted that simultaneously the independent variables significantly affect the dependent variable. The value of the F-statistic also shows that the model is a model that meets the Goodness of Fit.
Furthermore, the T-statistic value can be used to test the statistical hypothesis, namely the effect of the price variable on product sales. Based on the third table, statistical hypothesis testing can be carried out.
Based on the case examples above, the hypotheses can be arranged into null and alternative hypotheses. The null hypothesis can be created: “price does not partially have a significant effect on product sales.” Then the alternative hypothesis, “price partially has a significant effect on product sales” can be created.
Based on the analysis results, the t-statistic value is -5.2289, with a p-value less than 0.05. It can be seen that based on the p-value, the null hypothesis is rejected.
Due to rejecting the null hypothesis, the alternative hypothesis is accepted. Therefore, it can be concluded that prices partially significantly affect product sales.
It is an article that Kanda Data can write on this occasion. Hopefully helpful and provide new insights for all.
