You need to calculate the average sick days based on high job satisfaction and/or high gym usage.

a. Open e08_exam_chap_data and save as e08_exam_chap_LastFirst.

b. 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.

c. 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.

d. Use the fill handle to fill H8:H11 for the rest of the quartile information.

e. 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.

f. Enter a function in G15 that determines the standard deviation of sick days for the entire population.

g. Enter a function in H15 that determines the variance of sick days for the entire population.

h. 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.

i. Create a Covariance analysis of job satisfaction and sick days. Be sure to include the column labels.

j. Set the analysis results to populate starting in G22.

k. 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.

l. 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.

m. Create a histogram that includes the frequency of each quartile (values) and the sick days for each quartile (bin).

n. Make sure that you display the data in a graph that includes a cumulative %.

o. Rename the new worksheet to Graph.

p. Save and close the file. Based on your instructors directions submit e08_exam_chap_LastFirst.