Your company has asked you to look into company sick leave. The company has randomly selected 100 employees to survey to see if there is a correlation between job satisfaction, how many days a week they attend a gym, salary, and how many sick days they took last year. Average Sick Days You need to calculate the average sick days based on high job satisfaction and/or high gym usage. Open e08_exam_chap_data and save as e08_exam_chap_LastFirst. Enter functions into the Average Sick Leave chart area that calculate the following: · G3 = average sick leave of all employees in survey · H3 = average sick leave of all employees that have a job satisfaction higher than six · I3 = average sick leave of all employees that go to the gym more than three days a week · J3 = average sick leave of all employees that have a job satisfaction higher than six AND go to the gym more than three days a week Calculate Quartile Data You need to determine where the quartile ranges fall and the frequency of each range. Enter a quartile inclusive function in H7 to calculate the number of sick days for the 0 quartile (minimum value) using the appropriate mix of absolute and mixed cell references. Use the fill handle to fill H8:H11 for the rest of the quartile information. Enter a function in I7:I11 to calculate the frequency of each range of sick days (H7:H11). Determine Strength of Correlation You need to determine the strength of correlation between each of the factors and the number of sick days. Enter a function in G15 that determines the standard deviation of sick days for the entire population. Enter a function in H15 that determines the variance of sick days for the entire population. Enter functions that calculate the following: · G19 = the strength of correlation between days at the gym and sick days · H19 = the strength of correlation between job satisfaction and sick days · I19 = the strength of correlation between salary and sick days Using the Analysis ToolPak You need to load the Analysis ToolPak if you have not already done so. You will then use the Analysis ToolPak to run a Covariance report on Job Satisfaction versus Sick Days. You will also use the Analysis ToolPak to generate a forecast that shows which salary range is the most vulnerable given a higher number of sick days. Create a Covariance analysis of job satisfaction and sick days. Be sure to include the column labels. Set the analysis results to populate starting in G22. On a new worksheet, create a Forecast analysis that will forecast which salary ranges are most likely to be included if the company were to start seeing more than 21 sick days - the current maximum. Rename the new worksheet to Forecast. Create Histogram You need to create a histogram that displays the frequency of sick days based on quartiles. The data should be displayed in a graph that includes cumulative data. Create a histogram that includes the frequency of each quartile (values) and the sick days for each quartile (bin). Make sure that you display the data in a graph that includes a cumulative %. Rename the new worksheet to Graph. Save and close the file. Based on your instructor’s directions submit e08_exam_chap_LastFirst.
Your company has asked you to look into company sick leave. The company has randomly selected 100 employees to survey to see if there is a correlation between job satisfaction, how many days a week they attend a gym, salary, and how many sick days they took last year.
Average Sick Days
You need to calculate the average sick days based on high job satisfaction and/or high gym usage.
Open e08_exam_chap_data and save as e08_exam_chap_LastFirst.
Enter functions into the Average Sick Leave chart area that calculate the following:
· G3 = average sick leave of all employees in survey
· H3 = average sick leave of all employees that have a job satisfaction higher than six
· I3 = average sick leave of all employees that go to the gym more than three days a week
· J3 = average sick leave of all employees that have a job satisfaction higher than six AND go to the gym more than three days a week
Calculate Quartile Data
You need to determine where the quartile ranges fall and the frequency of each range.
Enter a quartile inclusive function in H7 to calculate the number of sick days for the 0 quartile (minimum value) using the appropriate mix of absolute and mixed cell references.
Use the fill handle to fill H8:H11 for the rest of the quartile information.
Enter a function in I7:I11 to calculate the frequency of each range of sick days (H7:H11).
Determine Strength of Correlation
You need to determine the strength of correlation between each of the factors and the number of sick days.
Enter a function in G15 that determines the standard deviation of sick days for the entire population.
Enter a function in H15 that determines the variance of sick days for the entire population.
Enter functions that calculate the following:
· G19 = the strength of correlation between days at the gym and sick days
· H19 = the strength of correlation between job satisfaction and sick days
· I19 = the strength of correlation between salary and sick days
Using the Analysis ToolPak
You need to load the Analysis ToolPak if you have not already done so. You will then use the Analysis ToolPak to run a Covariance report on Job Satisfaction versus Sick Days. You will also use the Analysis ToolPak to generate a forecast that shows which salary range is the most vulnerable given a higher number of sick days.
Create a Covariance analysis of job satisfaction and sick days. Be sure to include the column labels.
Set the analysis results to populate starting in G22.
On a new worksheet, create a Forecast analysis that will forecast which salary ranges are most likely to be included if the company were to start seeing more than 21 sick days - the current maximum.
Rename the new worksheet to Forecast.
Create Histogram
You need to create a histogram that displays the frequency of sick days based on quartiles. The data should be displayed in a graph that includes cumulative data.
Create a histogram that includes the frequency of each quartile (values) and the sick days for each quartile (bin).
Make sure that you display the data in a graph that includes a cumulative %.
Rename the new worksheet to Graph.
Save and close the file. Based on your instructor’s directions submit e08_exam_chap_LastFirst.
AI-generated content may present inaccurate or offensive content that does not represent bartleby’s views.
Unlock instant AI solutions
Tap the button
to generate a solution
