Linear Regression in Excel

Introduction

• Learn how to perform linear regression using Excel.
• Focus on two variables:
  • X variable (explanatory variable): Interest rates
  • Y variable (response variable): Median home prices

Creating a Scatter Plot

• Requirements: Two variables
• Highlight both variables (excluding averages) for the scatter plot.
• Insert a scatter plot in Excel.

Customizing the Scatter Plot

• Adding Labels and Titles:
  • Use chart elements to add a title and axis labels.
  • Optionally, use quick chart layouts for a pre-fabricated design.
• Adding a Trend Line:
  • Insert a linear trend line.
  • Customize using chart layouts.

Adjusting Axis Ranges

• Adjust the x-axis and y-axis to reduce empty space:
  • Set x-axis minimum to 5.
  • Set y-axis minimum to 100,000.

Enhancing the Trend Line

• Change the color to red for visibility.
• Increase the thickness of the line.
• Change the line style to solid instead of dashed.

Analyzing the Graph

• Display slope and intercept on the graph.
• Identify the negative slope of the trend line.

Calculating Correlation

• Correlation measures the strength of the relationship:
  • Use the formula CORREL(X range, Y range).
  • Example correlation coefficient: -0.62
• Use two decimal points for clarity.

Finding Slope and Intercept

• Slope Calculation:
  • Use SLOPE(Y Range, X Range) function.
  • Slope indicates that for each unit increase in interest rate, home prices decrease by $23,000.
• Intercept Calculation:
  • Use INTERCEPT(Y Range, X Range) function.
  • Intercept indicates home price at zero interest rate is $393,000.

Conclusion

• The linear regression provides insights into the relationship between interest rates and home prices.
• Negative correlation suggests that as interest rates increase, median home prices tend to decrease.