Calculating Simple Linear Regression - Excel Template

I developed an excel template that generates linear regression analysis. It also writes summary report which is based on correlation coefficient, p-value and beta coefficient. This spreadsheet can handle up to 10,000 cases.

Download link : Linear Regression Analysis

Meaning :
In simple linear regression, we predict scores on one variable from the scores on a second variable.

The variable we predict is called the dependent or outcome variable and is referred to as Y. The variable we base our predictions on is called the independent or predictor variable and is referred to as X. 

When there is only one independent or predictor variable, the prediction method is called simple regression.

Examples :
  1. To predict employees' salary on the basis of their years of experience.
  2. To predict students' mathematics aptitude scores based on the number of hours they studied.

Assumptions :
  1. There must be a linear relationship between dependent and independent variable.
  2. Your two variables should be measured at the interval or ratio level.
  3. For any value of X, Y is normally distributed and the variance of Y is the same for all possible values of X.
  4. The Y values are statistically independent of one another.

Regression Equation :

The regression equation is written as :

Y = a + bX +e

Y: It is the value of the dependent variable, what is being predicted or explained

a : It is the value of Y when the value of X=0

b : How much Y changes for per unit change in X.

X: It is the value of the independent variable, what is predicting or explaining the value of Y

e: It is the error in predicting the value of Y, given the value of X

Interpretation :

Assume you want to run a regression of wage on work experience.

Regression Equation :  Wage = a + b*(years of service)

Suppose the intercept is 1.1 and slope coefficient (b) of years of service is 0.65.

Wage = 1.1 + (0.65) (Years of Service)

An employee having 3 years of experience would be predicted to get wage of 3.05 thousand dollars.

Standard Output of Linear Regression Analysis

1. R and R Square value

The R value indicates degree of  linear relationship between two variables (Simple Correlation). The R square value indicates how much of the dependent variable can be explained by the independent variable.

2. Significance Testing

This is to test whether the regression model predicts the outcome variable significantly well. 

Rule : If p value is less than 0.05, that implies the model applied can statistically significantly predict the outcome variable.

3. Beta Coefficient

It represents how much dependent variable changes for per unit change in independent variable.

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.

While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

0 Response to "Calculating Simple Linear Regression - Excel Template"

Post a Comment

Next → ← Prev