Federation University
STATS6900 Quantitative Methods for Business
Data Presentation Assignment
27/2014
Date Due:
Total Marks: 30 marks
Worth: 20% of final assessment
This assignment requires a substantial amount of computer work and written comment. You may need to seek guidance from your tutor along the way. Do not leave things until too late!!
The questions explain what is required and provide some information about the presentation of your answers. Follow the instructions carefully!
Some Hints & Tips about the questions and presentation are given below. Make sure you read and follow these carefully. In particular you should take note of sections 1 and 2 from the Hints & Tips below regarding presentation of answers.
Some Hints and Tips:
1. Make sure the front page of your assignment includes both your name and your student number
2. The questions in this assignment are numbered. Your answers should also be numbered. Please follow this numbering.
3. Note that since the assignment may need to be scanned and then reproduced in black and white avoid the use of colour wherever possible. Make sure that all graphs are readable in black and white. Excel can have a tendency to insert coloured backgrounds, particularly on scatterplots, which completely hide the graphs and data points when converted to B and W! Remove such coloured backgrounds.
4. Recall that just because data for a variable is numerical does not mean that it is quantitative!
5. In all your tasks only include information that has been requested: for example, the summary table in part 4 should be one table including the average, the standard deviation and the number in each group.
6. To examine the complaint raised in Task 6 focus upon whether the increases were lower or not. Resist the temptation to talk about “fairness” and “gender bias” as we have no information about these aspects.
7. To answer the question raised in Task 7 we really need data giving % increases in salary for each year, and for each employee. This means going back and re-examining a large amount of data from many years – a very large task. Since we have not got this data, we will do something less.
a. The horizontal axis is year of first employment.
b. The trend line you have been asked for is linear.
Background:
For this assignment 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. The data is contained in the
file Assignment Sample Generator-14.xls in the worksheet Salaries Data.
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 2 marks 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 2 marks 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 4 marks 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 1 mark 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? 2 marks
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 3 marks workers in each position.
3 marks
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 3 marks 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 2 marks 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, 2 marks 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 3 marks trend line to the data. Does it seem to be a good fit?
1 mark
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 2 marks 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?
Total = 30 Marks
Worth 20%