Exp22_Excel_Ch08_HOE_Assessment

Step

Instructions

Points Possible

1

Start Excel. Download and open the file named Exp22_Excel_Ch08_HOE_Assessment.xlsx. Grader has automatically added your last name to the beginning of the filename.

0

2

You will calculate the standard deviation and variance of the test scores within the sample.

Enter a function in cell H9 to calculate the standard deviation of the sample of text scores in the range C4:C53.

Enter a function in cell I9 that calculates the variance of the sample data in the range C4:C53.

Format the results as numbers to display no decimal points.

9

3

After calculating the standard deviation and variance to help determine the data points’ distance from the mean, you theorize that there is a direct relationship between attendance and test scores. You will use the CORREL function to test the strength of the relationship.

Enter a function in cell J9 to determine the correlation of the values in the range C4:C53 and F4:F53. Format the result as a number with two decimal points.

7

4

You want to determine the frequency of student absences based on the criteria of perfect attendance. Attendance is divided into the following bins: 0 days absent, 1 to 5 days absent, and 6 to 10 days absent. To do this, you will use the FREQUENCY function.

Use the Frequency function in the range I12:I14 to determine the frequency of absences located in the range F4:F53 based on the bins located in the range H12:H14. Divide the results by the sample size in I5 in order to return the results as a percentage. Then format the results in the range I12:I14 as Percentage number format with 0 decimal places.

5

5

You will analyze SAT data across multiple high schools in the district. As part of the analysis, you use the Analysis ToolPak.

Make the High School Samples worksheet active. Ensure the Analysis ToolPak is enabled. Use the Analysis ToolPak to create a Anova: Single Factor report starting the Output Range in cell F7. Use the input range B3:D53 and ensure Labels in first row check box is selected. AutoFit the width for columns F:L.

6

6

You hypothesize that there is a negative relationship between attendance and test scores. Your next assessment is an analysis of trends between SAT scores and attendance.

Make the Combined Score Samples worksheet active. Create a Covariance report using the input range C3:D153 and the output range H3. Be sure to include the labels in the first row. Set the column width of columns I:J to 13.

6

7

Use the Analysis ToolPak to create a histogram starting in cell H8 using the input range D4:D153 and the Bin range F4:F6. Include Cumulative Percentage in the chart output. Position the chart so the upper left corner begins in cell H13.

6

8

Your data only contains data from students with up to 10 absences; however, you want to predict the impact on test scores of students with more than 10 absences. Your last task is creating a forecast sheet to predict the impact of up to 15 absences on SAT scores.

On the Combined Score Samples worksheet, select B3:D153 and sort by column D smallest to largest. Next, create a Forecast Sheet  based on the range C3:D153. Use 15 as the Forecast End value. Format the newly created chart with chart Style 12. Position and resize the chart so it spans the range F3:L15. Rename the worksheet Forecast.

7

9

You have collected a sample set of data that includes the years of service and salaries of 50 teachers from the Banton School System. You want to use this data to create a trendline that can be used for forecasting. You will create a scatter plot chart of the data and add a linear trendline with equation and R-squared.

Make the Educator Assessment worksheet active. Insert a Scatter (X,Y) chart using the range C3:C52 as the X axis values and D3:D52 as the Y axis values. Add a linear trendline that displays the equation on chart and R-squared value. Position the equation in the upper-left corner of the chart and add the chart title Salary Analysis. Position and resize the chart so it spans the range F6:I19.

5

10

Excel automatically created the linear equation y = 3014.5x + 9028.2. However, you can calculate this equation manually using functions in Excel. You will use this method to verify the accuracy of the chart. Next, you will use the INTERCEPT and SLOPE functions to verify the results.

Use the Intercept function to Calculate the Intercept of the equation in cell H21. Use the range D3:D52 as the known_ys and C3:C52 as known_xs.

Use the Slope function to Calculate the slope of the equation in cell H22. Use the range D3:D52 as the known_ys and C3:C52 as known_xs.

12

11

Your next step is to test the accuracy of your trendline by using the RSQ and STEYX functions.

Type RSQ in cell G23 and Standard Error in cell G24.

Use the RSQ function to Calculate the R-squared value of the equation in cell H23. Use the range D3:D52 as the known_ys and C3:C52 as known_xs.

Use the STEYX function to Calculate the standard error value of the equation in cell H24. Use the range D3:D52 as the known_ys and C3:C52 as known_xs.

11

12

For your final task, you will use the FORECAST.LINEAR function to predict the salary of a teacher who has worked for 30 years. You will then manually check the results using the linear equation y = mx + b in which m and b represent intercept and slope, respectively.

Type Predicted Salary in cell G3. Type High in cell G4, Low in cell G5, and Check in cell J2.

Use the FORECAST.LINEAR function in cell H3 to calculate the predicted salary of an educator based on 30 years of service. Use the range D3:D52 as the known ys and C3:C52 as the known-xs.

In cell H4, calculate the upper threshold of the forecast value by adding the predicted value (H3) with the standard error (H24).

In cell H5, calculate the lower threshold of the forecast value by subtracting the predicted value (H3) with the standard error (H24).

Apply Accounting Number format to the range H3:H5 and cell K2.

In cell K2, use the linear equation to verify the value determined using the FORECAST.LINEAR function. The linear equation is y = mx + b. Use the predicted value in cell H2 multiplied by the slope in cell H22 plus the intercept in cell H21.

26

13

Save and close Exp22_Excel_Ch08_HOE_Assessment.xlsx. Exit Excel. Submit the file as directed.

0

Total Points

100