Regression Analysis Used in Compensation Administration

t Statistic

The F statistic allows you to determine the fitness of the overall regression model, while t tests allow you to assess the relative importance of the individual predictors. The critical t value differs depending on the hypothesis and sample size, but to keep things simple for this course, we will set it at 2.5. (Note: similar to the significance value of F you can look at the P-value for t when assessing significance, again looking for values less than .05).

In this course, if the absolute value of the t statistic is greater than 2.5, then we reject the null hypothesis.

Looking at the Summary Output, the t statistic is not higher than 2.5 (excluding the intercept). That means none of the variables are significant in predicting Compensation. That explains the low R², as well as the low F statistic.

Standard error and 95% confidence interval

The standard error can also tell you if the variable is significant. Compare the standard error of the variable to its coefficient. If the standard error is large relative to the coefficient, that means the variable is probably not significant.

Looking at the Summary Output, all the standard errors of the variables are rather large. In most cases, they are larger than the coefficients themselves. Remember that a 95% confidence interval is +/- 2 standard errors. If the standard error is large, +/- 2 standard errors will give you a wide range. Tip: If the 95% confidence interval contains the number 0, that is, the interval is from less than zero to more than zero, you cannot reject the null hypothesis and that variable is not significant. Except for the Intercept, the 95% confidence intervals in this example contain the number 0, therefore, none of them are significant.

Regression Analysis Using Excel

Regression and multiple regression analysis can be done with Microsoft Excel’s Analysis ToolPak Add-in found by selecting File, then Options, and in the Excel Options pop-up, Add-ins. In the Add-ins box, select Analysis ToolPak and select OK.

Once the Analysis ToolPak has been added, select Data and then in the menu items select Data Analysis. In the Data Analysis pop-up, select Regression and OK.

In the Input section, the Y range is the column of dependent data. The X Range is the column of independent data. If there are multiple columns of independent data (multiple regression), the X range is the first cell in the first column to the last cell in the last column.

A 95% Confidence Level is the default.

To try this, you can make your own spreadsheet or use the data provided on the Regression Analysis Data page that has columns for Salary, Company Revenue, and Years of Experience. Output includes regression statistics and an ANOVA table like the one that has been used in this course.