Reference no: EM13669885
Background:
You will examine data obtained from a small private company that currently employs 171 people. Each year this company completes a review of its employees' salaries, as well as other information relating to their employment. Information on several variables is collected including gender, department worked in, the year they started working for the company, beginning salary, current salary and the type of position they hold.
Assignment Tasks:
1. Before you begin your analysis you are required to take a random sample of size 70 from the 171 cases in the file. Use the file Assignment Sample Generator-14.xls to do this. Open this file and go to the first worksheet labelled Instructions & Macro. Read the enclosed instructions carefully and proceed as directed. If you have repeated cases your final sample size may be less than 70. This will not be a problem. Answers to the questions below are to be based on this random sample of up to 70 cases. Make sure to keep a safe copy of your sample since you cannot use Random Sample Generator-14 to reproduce the first sample.
2. Data List: Provide a printout of the data in your sample, with ID numbers in ascending order.
3. Variable List: Provide a summary table listing each variable with information under the following column headings: Variable Name, Variable Description (see the information above) and Variable Type (qualitative or quantitative). If the variable is quantitative you need to state whether it is discrete or continuous.
4. Summary table:
a) Prepare a single summary table that shows average and standard deviation of current salary for males and females according to their Position within the company. (You have 6 groups of people, 3 groups of males and 3 groups of females) Think carefully about the layout of rows and columns of your table.
As well as averages and standard deviations you should also include the number of employees in each group. So each cell in your final table should contain 3 descriptive statistics - mean, standard deviation and n the number of cases.
b) Indicate any combinations of gender and position (i.e. cells in the table) where there are too few cases for meaningful statistics to be calculated.
c) Consider any differences between Males and Females with respect to the following:
• Type of employee position.
• Starting salaries with respect to their positions in the company?
In each case explain your reasoning.
5. Comparing Departments: The head of Human Resources wants to compare the structure of the four departments within the Company. In particular she wants to answer the following question: Does the proportion of workers in each position (positions 1, 2 & 3) remain the same as we move from one department to another?
a) Produce suitable charts for each department that show the proportion of workers in each position.
b) Use the charts to answer the question stated above, and give evidence, in bullet point form, to support your answer.
6. Average salary increase per year: There have been complaints in this company that female employees have been given lower salary increases than their male colleagues. Examine the issue: Have the salary increases for females been lower?
a) Create a new column, called Length Empl which shows the length of time a person has been employed in the company, as of the end of December last year. Assume that if an employee started in 2008 then they have been employed for 6 years; if they started in 1997 then they have been employed for 17 years, and so on. Create a second new column, called Avg Incr which shows the average increase in salary
per year for each employee since the year they started work for the company.
- State the formulae you have used to produce these new columns. Print out the ID column and the new data - 3 columns. This should be in ascending order of the ID numbers.
b) Use Avg Incr to create a histogram showing the distribution of the average salary increase per year (Note: the horizontal axis represents length of employment). This histogram has been created to get some idea of the distribution of the data. Comment upon the shape of this distribution: Is it symmetric? Is it skewed? If so which way?
c) Now provide side by side boxplots for Avg Incr, split on gender. Again, comment on what you see. Most importantly, provide your answer to the question: "Have the salary increases for females been lower than those for males?"
7. Changes in salary increases over time: We have been asked to find out if there is any evidence that yearly increases in salary have been changing over time. We will graph the average salary increase (vertical axis), against the year of first employment with the company.
a) Produce a scatterplot for Avg Incr against year of first employment. Fit a linear trend line to the data. Does it seem to be a good fit?
b) Fit a quadratic trend line to the data. Does this fit the data better? Does it seem to be a good fit? Provide appropriate reasons for your answers.
c) Recall that we have held together 3 distinct groups of employees, in positions 1, 2 and 3. Here we attempt to repeat part (a) while treating the three groups separately.
Produce a stratified scatterplot, distinguishing between employees in different positions. (Hint: Sort the data by position first, then after selecting the Scatterplot option in XL choose Series to define the groups of data points corresponding to the position levels). Fit linear trend lines to each of the three sets of data.
Comment on this new plot: - Do the trend lines seem to fit the data well? - What conclusions can you draw about salary increases within each of the groups?