Simple linear regression analysis is a useful statistical technique for measuring and understanding the relationship between two variables. In this analysis, one variable (independent variable) is used to predict or explain the other variable (dependent variable).
Excel is one of the most commonly used tools due to its widespread availability. With Excel, users can easily perform simple linear regression analysis. In this article, Kanda Data will explain in detail the steps on how to perform simple linear regression analysis using Excel easily.
Assumptions of Simple Linear Regression
The assumptions of simple linear regression must be met for the results of the regression analysis to be interpreted validly. The assumption of normality of residuals is the assumption that the prediction errors (residuals) in the linear regression model have a normal distribution.
A residual is the difference between the observed value of the dependent variable and the value predicted by the regression model. If the assumption of normality of residuals is not met, it can lead to problems in interpreting the regression results.
The assumption of homoscedasticity is the condition that the variance of prediction errors (residuals) is constant across values of the independent variable. This means that there is no systematic pattern in the distribution of prediction errors as the values of the independent variable change. In other words, this assumption implies that the distribution of residuals does not vary significantly across the range of values of the independent variable.
If homoscedasticity is not met (heteroscedasticity), it can cause hypothesis tests to be inconsistent. Therefore, if there are indications of heteroscedasticity, corrective steps such as data transformation or the use of regression methods more robust to heteroscedasticity can be applied to ensure more accurate regression analysis results.
The assumption of linearity is made to ensure that the relationship between the independent variable and the dependent variable is linear. This means that changes in the independent variable will be followed by proportional changes in the dependent variable, in accordance with a straight line. The assumption of linearity is important because it forms the basis for the linear regression model.
Example of a Simple Linear Regression Analysis Case Study
Suppose we want to test whether household income affects household consumption in a XYZ region. The collected data shows household income (independent variable) in thousands of dollars and household consumption (dependent variable) also in thousands of dollars.
By using simple linear regression analysis, we can model the relationship between these two variables. Let’s assume that the collected data produces the following simple linear regression model:
Household consumption (Y) = b0 + b1 household income (X) + e
In this model, b0 is the intercept, b1 is the regression coefficient indicating the magnitude of the effect of household income on household consumption, and e is the random error.
Based on the equation specification, the collected data can be seen in the table below:
How to Perform Simple Linear Regression Analysis Using Excel
To conduct simple linear regression analysis on the data, you can use Excel. The steps to follow are as follows: open Excel and create two columns for household income and household consumption variables. Enter the household income and household consumption data according to their variable names.
Next, select the “Data” menu and choose “Data Analysis” (if not available, you may need to add the “Data Analysis ToolPak” first). Select “Regression” from the list of available analyses. In the Regression window, enter the entire data range for the independent variable (household income) and the dependent variable (household consumption).
Check the “Labels” option if you have included labels for each variable. Choose the location for the regression output. Click “OK” to see the regression analysis results. These steps in detail can be seen in the Figure below:
Interpreting the Analysis Results
After the process is complete, you will see regression output that includes regression coefficients, R-squared values, and others. From there, you can interpret the results and conclude whether there is a significant influence of household income on household consumption based on the analyzed data. Detailed results of simple linear regression analysis can be seen in the Figure below:
Based on the above figure, the regression coefficient value for the household income variable is 0.995. The p-value associated with the regression coefficient for household income is 2.6E-18, which is much smaller than the significance level of 0.05. This indicates that the regression coefficient is statistically significant. This means that for every one-unit increase in household income (in thousands of dollars), household consumption (also in thousands of dollars) increases by 0.995 units.
The R-squared value is 0.9366. This indicates that approximately 93.66% of the variation in household consumption can be explained by variation in household income. This indicates that the simple linear regression model is quite good at predicting household consumption based on household income. Well, this is the article that Kanda Data can write at this opportunity, hopefully it is useful. Look forward to Kanda Data’s article update next week.
