This paper circulates around the core theme of a) Download the Excel file and copy the spreadsheet for your group to a new Excel file. (2 marks) together with its essential aspects. It has been reviewed and purchased by the majority of students thus, this paper is rated 4.8 out of 5 points by the students. In addition to this, the price of this paper commences from £ 99. To get this paper written from the scratch, order this assignment now. 100% confidential, 100% plagiarism-free.
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)
Page 3 of 5
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)
Page 4 of 5
QUESTION 2 (27 marks)
An airline researcher studied reservation records for a random sample of 100 days to estimate μ, the mean number of no-shows (persons who fail to keep their reservations) on the daily 4 pm commuter flight to Melbourne. A summary of the sampled records is contained in the airline spreadsheet.
Required: a) Select data from the airline spreadsheet based on your group number and copy the data to a new spreadsheet. Use your group data to answer the questions that follow. (2 marks)
b) Use the Excel AVERAGE function to find the sample mean number of no-shows. (1 mark)
c) Use the Excel STDEV.S function to find the sample standard deviation of the number of no-shows. (1 mark)
d) Should you use the standard normal distribution, the t-distribution or neither to construct a 97% confidence interval for the population mean number of no-shows? Explain your reasoning. (2 marks)
e) What is the point estimate of the population mean? (1 mark)
f) Find the margin of error for the 97% confidence interval. Show all your workings (3 marks)
g) Construct a 97% confidence interval for the population mean. Interpret the results. (4 marks)
h) Would a different random sample of 100 days have provided the same interval estimate as the one you just constructed? Give reasons for your answer (2 marks)
i) Find the minimum sample size needed to estimate the population mean to within 1/5 of a standard deviation with 99 percent confidence. Show all your workings. (4 marks)
