1 / 10

Excel Regression Analysis: Understanding Multiple Regression Model Output

Learn how to interpret regression statistics and coefficients tables in Excel for a multiple regression model. Understand standard errors, R2 values, t-statistics, p-values, and confidence intervals.

jeffreyt
Download Presentation

Excel Regression Analysis: Understanding Multiple Regression Model Output

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. EXCEL: Multiple Regression

  2. Regression Model • A multiple regression model is: y = β1+ β2 x2+ β3 x3+ u Such that: • y is dependent variable • x2and x3are independent variables • β1 is constant • β2and β3are regression coefficients • It is assumed that the error u is independent with constant variance. • We wish to estimate the regression line: y = b1 + b2 x2 + b3 x3

  3. Regression Analysis in Excel • We do this using the Data analysis Add-in and Regression. • Example:

  4. Regression Analysis in Excel

  5. Regression Analysis in Excel • The regression output has three components: • Regression statistics table • ANOVA table • Regression coefficients table.

  6. Interpreting Regression Statistics TableRegression Statistics • The standard error here refers to the estimated standard deviation of the error term u. • It is sometimes called the standard error of the regression. It equals sqrt(SSE/(n-k)). • It is not to be confused with the standard error of y itself (from descriptive statistics) or with the standard errors of the regression coefficients given below. • R2 = 0.8025 means that 80.25% of the variation of yi around its mean is explained by the regressors x2i and x3i.

  7. Interpreting Regression Statistics TableRegression coefficients table • The regression output of most interest is the following table of coefficients and associated output:

  8. Interpreting Regression Statistics TableRegression coefficients table • Let βjdenote the population coefficient of the jth regressor (intercept, HH SIZE and CUBED HH SIZE). Then • Column "Coefficient" gives the least squares estimates of βj. • Column "Standard error" gives the standard errors (i.e.the estimated standard deviation) of the least squares estimates bj of βj. • Column "t Stat" gives the computed t-statistic for H0: βj = 0 against Ha: βj ≠ 0.This is the coefficient divided by the standard error. It is compared to a t with (n-k) degrees of freedom where here n = 5 and k = 3. • Column "P-value" gives the p-value for test of H0: βj = 0 against Ha: βj ≠ 0..This equals the Pr{|t| > t-Stat}where t is a t-distributed random variable with n-k degrees of freedom and t-Stat is the computed value of the t-statistic given in the previous column. Note that this p-value is for a two-sided test. For a one-sided test divide this p-value by 2 (also checking the sign of the t-Stat). • Columns "Lower 95%”and "Upper 95%”values define a 95% confidence interval for βj.

  9. Interpreting Regression Statistics TableRegression coefficients table • A simple summary of the previous output is that the fitted line is: y = 0.8966 + 0.3365x + 0.0021z

  10. Exercise

More Related