Calculating Simple Linear Regression - Excel Template

Deepanshu Bhalla 1 Comment
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.
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

1 Response to "Calculating Simple Linear Regression - Excel Template"
Next → ← Prev