Excel Quiz 2004. 6. 2

1. Given below are seven observations collected in a regression study on two variables, x (independent variable) and y (dependent variable). Use Excel’s Regression Tool to answer the following questions.

xy

212

39

68

77

86

75

92

a. What is the estimated regression equation?

b.Perform a t-test and determine whether or not x and y are related. Use  = 0.05.

c.Perform an F-test and determine whether or not x and y are related. Use  = 0.05.

  1. Find and interpret the coefficient of determination.

2. A study of wage discrimination at a local store compared employees’ gender and their status. Partial results of the study follow. Use Excel and test at  = .05 to determine if gender is independent of status.

Employee / Gender / Status /

Employee

/ Race / Status
1 / M / manager / 26 / F / associate
2 / F / associate / 27 / M / district manager
3 / M / district manager / 28 / F / manager
4 / M / manager / 29 / M / associate
5 / M / manager / 30 / F / district manager
6 / F / associate / 31 / F / district manager
7 / F / associate / 32 / M / district manager
8 / M / associate / 33 / M / district manager
9 / F / associate / 34 / F / associate
10 / M / manager / 35 / M / district manager
11 / F / manager / 36 / F / associate
12 / F / associate / 37 / F / manager
13 / M / associate / 38 / F / associate
14 / F / associate / 39 / M / district manager
15 / M / district manager / 40 / F / associate
16 / M / district manager / 41 / F / manager
17 / F / associate / 42 / F / district manager
18 / F / associate / 43 / M / manager
19 / M / associate / 44 / M / district manager
20 / F / manager / 45 / F / associate
21 / M / district manager / 46 / F / associate
22 / F / district manager / 47 / F / district manager
23 / F / manager / 48 / M / manager
24 / F / associate / 49 / F / manager
25 / F / associate / 50 / F / associate
  1. During “sweeps week” last year, the viewing audience was distributed as follows: 36% NBC, 22% ABC, 24% CBS, and 18% FOX. This year during “sweeps week” a sample of 50 homes yielded the following data. Use Excel to test at  = .05 to determine if the audience proportions have changed.

ABC / FOX / ABC / FOX / ABC / ABC / CBS / NBC / FOX / FOX
NBC / ABC / CBS / ABC / NBC / NBC / NBC / CBS / FOX / ABC
ABC / FOX / NBC / CBS / CBS / NBC / NBC / ABC / FOX / FOX
NBC / NBC / NBC / NBC / FOX / ABC / FOX / NBC / FOX / CBS
CBS / CBS / FOX / FOX / NBC / CBS / FOX / CBS / FOX / NBC

4.The manager of the Tammy Corporation, wants to determine whether or not the type of work schedule for her employees has any effect on their productivity. She has selected 15 production employees at random and then randomly assigned 5 employees to each of the 3 proposed work schedules. The following table shows the units of production (per week) under each of the work schedules.

Work Schedule (Treatments)

Work Schedule 1Work Schedule 2Work Schedule 3

506070

606575

706655

405440

455755

At a .05 level of significance, use Excel to determine if there is a significant difference in the mean weekly units of production for the three types of work schedules.

5. A local university administers a comprehensive examination to the recipients of a B.S. degree in Business Administration. A sample of 5 examinations is selected at random and scored. The scores are shown below.

Grade

56

85

65

86

93

Use Excel to determine an interval estimate for the mean of the population at a 98% confidence level. Interpret your results.

6. You are given the following information obtained from a random sample of 5 observations.

2018172218

At a 10% level of significance, use Excel to determine whether or not the mean of the population from which this sample was taken is significantly less than 21. (Assume the population is normally distributed.)

1