return to home page

 

MODELING POSSIBILITIES

This report is composed by Eleanor Fulton based on

Problem 33 in Chapter 11 of our text on page 615.

 

To browse two other modeling possibilities, please access

Arun Upadhyay‘s report

Kelly Hart’s report

 

Situation

The human resources manager of DataCom, Inc. wants to predict the annual salaries of given employees.  Data have been collected for a sample of employees and are given in the file P11_5.xls.  This report will use multiple regression modeling to help the human resources manager to analyze and to predict annual salaries of given employees.

 

 

Variables

The variables used in this modeling possibility are as follows:

 

  1. The number of years prior relevant work experience
  2. The number of years employments at DataCom
  3. The number of years of education beyond high school
  4. The employee’s gender
  5. The employee’s department
  6. The number of individuals supervised by the given employee

 

 

Multiple Regression Model

 

The six VARIABLES are inputs for the REGRESSION FORMULA and result in the output SALARY!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


Mathematical representation

The mathematical representation of the typical multiple regression equation has the form:

 

Y = B0 + B1X1 + B2X2 + B3X3 + …

 

In this modeling possibility:

 

Y is salary;

B0 is the constant regression coefficient;

B1 is the regression coefficient for how X1, or Years Previous Experience, contributes to salary;

B2 is the regression coefficient for how X2, or Years Employed, contributes to salary;

B3 is the regression coefficient for how X3, or Years Education, contributes to salary;

B4 is the regression coefficient for how X4, or Gender, contributes to salary;

B5 is the regression coefficient for how X5, or Department, contributes to salary;

and B6 is the regression coefficient for how X6, or Number Supervised, contributes to salary.

 

Based on the data and the nifty regression tool in Microsoft Excel, PREDICTED SALARY equals:

 

19589.4707 - (106.5479 * Years Previous Experience) + (621.0566 * Years Employed) + (1631.8308 * Years Education) – (1654.0746 * Gender) + (2134.2893 * Department) + (134.0143 * Number Supervised)

 

The R2 value is near 82%, so we can probably rely on the variables as good predictors of salary.

 

Implementation and Use

How can the multiple regression formula assist the human resources manager?  Below are four possibilities for use…

 

 

Ø       According to the estimated regression model, is there a difference between the mean salaries earned by male and female employees at DataCom?  If so, how large is the difference?

 

Using the simple method of calculating means,

The mean salary for females is $42,418.

The mean salary for males is $37,002.

 

According to the regression formula, it is possible to calculate the female salary relative to the male salary.  Holding all other variables constant besides the employee’s gender in the mathematical formula from above, the formula may be shown as:

 

PREDICTED SALARY = 19589.4707 – (1654.0746 * Gender)

 


Using Gender = 0 for females and Gender = 1 for males,

 


Insert the value for females (Gender = 0) into the formula:

PREDICTED SALARY = 19589.4707 – (1654.0746 * 0) = $19,589.47

 

Insert the value for males (Gender = 1) into the formula:

PREDICTED SALARY = 19589.4707 – (1654.0746 * 1) = $17,935.40

 

Based on these predictions, there is a clearly a difference between the mean salaries earned by male and female employees at DataCom:  Females get paid $1,654.07 more on the average than males.

 

Ø       According to the estimated regression model, is there a difference between the mean salaries earned by employees in the sales department and those in the advertising department at DataCom?  If so, how large is the difference?

 

Using the simple method of calculating means,

The mean salary for employees in the sales department is $34,001.

The mean salary for employees in the advertising department is $40,665.

 

According to the regression formula, it is possible to calculate the sales department salary relative to the advertising department salary.  Holding all other variables constant besides the employee’s department in the mathematical formula from above, the formula may be shown as:

 

PREDICTED SALARY = 19589.4707 + (2134.2893 * Department)

 

Using the values 1 for Sales and 3 for Advertising,

 

Insert the value for Sales (Department = 1) into the formula:

PREDICTED SALARY = 19589.4707 + (2134.2893 * 1) = $21,723.76

 

Insert the value for Advertising (Department = 3) into the formula:

PREDICTED SALARY = 19589.4707 + (2134.2893 * 3) = $25,992.34

 

Based on these predictions, there is a clearly a difference between the mean salaries earned by sales department employees and advertising department employees.  Sales department employees get paid $4,268.58 less on average than advertising department employees.

 

Ø       According to the estimated regression model, in which department are DataCom employees paid the highest mean salary?  In which department are DataCom employees paid the lowest mean salary?

 

Using the simple method of calculating means, employees in the engineering department at DataCom are paid the highest mean salary and employees in the sales department are paid the lowest mean salary.

 

According to the regression formula, in the previous problem,

we calculated the PREDICTED SALARY for the sales and advertising

departments.  Now calculate PREDICTED SALARY for the purchasing

and engineering departments using the values 2 and 4, respectively.

 

Insert the value for Purchasing (Department = 2) into the formula:

PREDICTED SALARY = 19589.4707 + (2134.2893 * 2) = $23,858.05

 

Insert the value for Engineering (Department = 4) into the formula:

PREDICTED SALARY = 19589.4707 + (2134.2893 * 4) = $28,126.63

 

Based on these predictions and the ones in the previous problem, Sales department employees get paid the less on average, and Engineering department employees get paid the most on average.

 

Ø       Predict the salary of a female employee who served in a similar department at another company for 10 years prior to coming to work at DataCom.  This woman, a graduate of a 4-year collegiate business program has been supervising 12 subordinates in the purchasing department since joining the organization 5 years ago.

 

By using the multiple regression formula (as shown above in Mathematical Representation), this woman’s salary is predicted to be  $34,033.35.

 

 

 

 

 

 

 

 

 

 


end of report

(return to home page)