RECENT ASSIGNMENT

Master of Professional Accounting/ Graduate Diploma of Accounting
MA508/GA508 Business Statistics
Trimester One 2016
Assessment 3-Assignment: USING TECHNOLOGY IN STATISTICS
Question 1 (49 marks)
Some employees of Western Fields Limited were asked to fill in a questionnaire for health insurance purposes. The sample data from the employees’ questionnaires is provided in the Employee Questionnaire Excel file. The Excel file contains information about:
• the employee’s identification number
• the employee’s age,
• the number of children that an employee has
• the employee’s salaries in thousands of Australian dollars, and • the gender of the employee
Required:
a) Download the Excel file and copy the spreadsheet for your group to a new Excel file. (2 marks)
The spreadsheets are labelled as G1 to G12. Select the relevant spreadsheet based on your group number and use it to answer the following questions. For example G1 contains the data for group 1 and members of group 1 should copy the material from G1 spreadsheet to a new Excel file.
b) Indicate the level of measurement of the four variables contained in the spreadsheet and give reasons for your answers. (8 marks)
c) Use the Excel COUNT function to find the number of employees who filled in questionnaires. (2 marks)
d) Suppose that the Human Resource Manager tells you that there are currently 150 employees working at Western Fields Limited. Explain how you could use the information provided to make a statistical inference. (2 marks)
e) Suppose you want to investigate the relationship between gender and children. One way to do this is using Excel’s PivotTable feature that can be accessed via the insert menu.
• Download and read the Microsoft Excel help document entitled “HOW TO CREATE A PIVOT TABLE”.
• Prepare a Pivot Table using Gender as your row labels, Children as your column labels and Count of Gender as your values.
• Copy and paste the your final Pivot Table to a word document (2 marks)
f) Use the Pivot Table you prepared in part (e) of this question to answer the following questions.
Show all your workings:
i. How many female employees have two or more children? (2 marks)
ii. How many male employees have at least three children? (2 marks)
iii. Calculate the probability that an employee has no children. Comment on the calculated probability. (2 marks)
iv. Suppose a female employee is selected at random. What is the probability that the chosen employee has at least two children? Comment on the calculated probability. (2 marks)
g) Suppose you want to examine the relationship between the gender of employees and the number of children an employee has.
• Prepare a new Pivot table with Gender as your row labels, Children as your column labels and Sum of Children as your values.
• Copy and paste the your second Pivot Table to a word document (1 mark)
h) Use the pivot table prepared in part (g) of this question to answer the following questions:
i. What is the total number of children for all the employees surveyed? (1 mark)
ii. Which gender of employees has the most children? (1 mark)
iii. Which gender has the least number of children? (1 mark)
i) Use Excel’s COUNTIFS function to determine the number of :
i. women that are over 40 years with no children (1 mark)
ii. men that are over 40 years with no children (1 mark)
iii. women that are over 40 years with more than 2 children (1 mark)
iv. men that are over 40 years with more than 2 children (1 mark)
j) Prepare a descriptive statistics table for the Age variable.
• Use the Data Analysis button in Excel to do this. You should see the Data Analysis button on the furthest right hand side once you click on the Data tab in Excel.
• If you do not see the Data Analysis button, refer to the descriptive_statistics_in_excel PDF file for step-by-step instructions on how to prepare the descriptive statistics table.
• Copy and paste the prepared table to a word document (1 mark)
k) Use the information from the descriptive statistics table in part (j) of this question to answer the following question: Comment on the measures of central tendency and measures of variation for the age variable. (4 marks)
l) Use Excels percentile function to find the 65th and 90th percentile for the age variable. Interpret the calculated percentiles. (4 marks)
m) Use Excel’s Quartile function to find and interpret the first, second and third quartiles for the employees’ salaries (6 marks)
n) Find and interpret the interquartile range for employees’ salaries. (2 marks)