Understanding Linear Regression in Excel

Linear regression is a technique used to model the relationships between observed variables. The idea behind simple linear regression is to “fit” the observations of two variables into a linear relationship between them. For example, you can use linear regression to understand how exam performance (dependent variable) changes with increased study hours (independent variable).

To find the linear relationship, linear regression uses a mathematical equation of the form $y = m x + b$ , where $y$ is the dependent variable, $x$ is the independent variable, $m$ is the slope of the line, and $b$ is the intercept of the line. The slope and the intercept are called the regression coefficients, and they are determined by using a method called the least squares method. This method minimizes the sum of the squared errors between the observed values of $y$ and the predicted values of $y$ using the equation.

Linear regression can help you answer questions such as:

How strong is the relationship between two variables? You can measure the strength of the relationship by using a statistic called the correlation coefficient, which ranges from -1 to 1. A correlation coefficient close to 1 means that there is a strong positive relationship between the variables, while a correlation coefficient close to -1 means that there is a strong negative relationship. A correlation coefficient close to 0 means that there is no linear relationship between the variables.
What is the value of the dependent variable for a given value of the independent variable? You can use the equation of the line to predict the value of $y$ for any given value of $x$ . For example, if you know that the equation of the line is $y = 2 x + 5$ , you can predict that the value of $y$ when $x = 10$ is $y = 2 (10) + 5 = 25$ .
How accurate are your predictions? You can measure the accuracy of your predictions by using a statistic called the coefficient of determination, or $R^{2}$ , which ranges from 0 to 1. A $R^{2}$ close to 1 means that the equation of the line explains a large proportion of the variation in the dependent variable, while a $R^{2}$ close to 0 means that the equation of the line explains very little of the variation. A high $R^{2}$ indicates that your predictions are more accurate.

Linear regression is a simple and powerful tool for analyzing the relationships between variables, but it also has some limitations. For example, linear regression assumes that the relationship between the variables is linear, which may not always be the case. Linear regression also assumes that the errors are normally distributed and have constant variance, which may not be true for some data sets. Therefore, it is important to check the assumptions of linear regression before applying it to your data.

Basic Theory

The fundamental equation for a simple linear regression is given by:

$Y = mx + b$

where:

$Y$ is the dependent variable,
$X$ is the independent variable,
$m$ is the slope of the regression line, and
$b$ is the y-intercept.

Procedures for Linear Regression in Excel

Step 1: Data Preparation

Organize your data into two columns in an Excel spreadsheet – one for the independent variable (X) and another for the dependent variable (Y).

Step 2: Scatter Plot

Create a scatter plot to visualize the relationship between the variables. This step is crucial for understanding the nature of the relationship before applying regression.

Step 3: Calculate Slope and Intercept

Use the SLOPE and INTERCEPT functions in Excel to calculate the slope ( $m$ ) and y-intercept ( $b$ ) of the regression line.

=SLOPE(dependent_range, independent_range) =INTERCEPT(dependent_range, independent_range)

Step 4: Regression Line Equation

Combine the slope and intercept to form the regression line equation:

$\text{Regression Line: } Y = \text{SLOPE} \times X + \text{INTERCEPT}$

Step 5: Predictions

Once you have the regression line equation, you can use it to make predictions for new values of the independent variable.

Scenario: Sales Prediction

Let’s consider a scenario where we want to predict monthly sales (Y) based on advertising spending (X).

Month	Advertising Spending (X)	Monthly Sales (Y)
Jan	50	2000
Feb	75	2300
Mar	60	2100
Apr	90	2500
May	120	3000

Calculation in Excel

1. Scatter Plot: Create a scatter plot to visualize the data points.
2. Calculate Slope and Intercept:

=SLOPE(C2:C6, B2:B6)     
      =INTERCEPT(C2:C6, B2:B6)

1. Regression Line Equation:

If the slope is 20 and the intercept is 1800, the regression line equation is:

$Y = 20X + 1800$

1. Predictions:

Use the equation to predict sales for a new advertising spending value, e.g., $80:

=20 * 80 + 1800

The predicted sales would be 3400.

Other Approaches

Excel Data Analysis Tool

Excel provides a built-in Data Analysis Tool for regression. Go to the “Data” tab, click on “Data Analysis,” and choose “Regression.”

Excel Regression Functions

LINEST: This function returns statistics about a trendline fitted to known data points.
FORECAST: Predicts a future value based on existing values.