Instructions:

Access the files for the assignment, including:

Job Data Exercise $($Excel file$)$

It includes several worksheets of data reflecting compensation data from your company.

Per Title VII of the CRA of $1964,$ the Equal Pay Act, and most recently the Lilly Ledbetter Act, employers are expected to conduct analyses of pay practices and identify potential sources of systemic bias against protected groups and institute reforms to remedy these biases. For the purpose of this study we will focus on gender so that we keep the complexity of the analysis to just two groups rather than multiple groups.

Steps include:

Using the VLOOKUP function, combine information contained on the DEMOGRAPHICS, JOB DATA, and SALARY INFO worksheets from the "Job Data Exercise" file, such that all data points per individual are included on a single worksheet.

Be careful to ensure that you convert data so that it is uniform for all incumbents $($i$.$e$.,$ salary$)...$ remember, there are $2080$ hours in a standard work year, so to calculate an hourly rate from salary, you would divide the salary by the number of hours in the year. To convert from hourly to salary, you would do the opposite and multiply the hourly rate by the number hours in a year.

Pay equity between men and women$$For the jobs listed, do the following:

Filter the data in the "Combined Data Worksheet" to retain the data that you need.

Compute the average salary for men and women

Compute the overall average salary

After completing computing the average salary for each position, create a bar chart for all positions $($in one chart$).$ Visually examine the data $($i$.$e$.,$ the bar chart$)$ and describe the pattern $($pay differences$/$similarities$)$ for each position.

Conduct a t$-$test $($using the T$.$TEST function$)$ for independent samples to determine if a statistically significant difference exists between the average compensation for men and women $($whether one group is paid significantly higher than the other$).$

Pay equity between Whites and Non$-$Whites$$For the jobs listed, do the following:

Filter the data in the "Combined Data Worksheet" to retain the data that you need.

Compute the average salary for Whites and Non$-$Whites

Compute the overall average salary

After completing computing the average salary for each position, create a bar chart for all positions $($in one chart$).$ Visually examine the data $($i$.$e$.,$ the bar chart$)$ and describe the pattern $($pay differences$/$similarities$)$ for each position.

Conduct a t$-$test $($using the T$.$TEST function$)$ for independent samples to determine if a statistically significant difference exists between the average compensation for Whites and Non$-$Whites $($whether one group is paid significantly higher than the other$).$