Hypothesis testing and scatter plots using Excel

*Note: For problems 1 - 4, complete the 5-step process:

Step 1: State the null hypothesis and the alternate hypothesis.
Step 2: Select the level of significance.
Step 3: Evaluate the test statistic.
Step 4: Formulate a decision rule with critical value of test statistic.
Step 5: Compare the test statistic to the critical value and make the decision about H0.

1. Hypothesis testing with 2 populations:

A college's Business Management department is concerned that dual degree students may be receiving lower grades than the regular management students. Two independent random samples have been selected. 175 observations from population 1 (dual degree students) and 150 from population 2 (management only degree.) It is known from past studies that the population variance for dual degree students is 4.8 and the population variance for management only students is 5.1. The current study found that the mean score for the dual degree students is 83 and the mean score for the management only students is 84.
At a significance level of .05 is there evidence that the dual degree students are receiving lower grades than regular management degree students?

2. F-Test Two-sample for Variances

Use Excel's 2-sample variances data analysis tool to solve the following problem. A well-known ice cream store wants to test two different methods for scooping ice cream so they can use the one that has the least variability in their training of new and current employees. It took a sample of the ice cream cones scooped and measured the weights.

Use the F-Test Two-Sample for Variances to test the hypothesis that the variances between the two methods are the same. Interpret the results.

Method 1 Method 2
4.7 3.6
3.9 4.3
3.2 3.9
3.8 5.4
3.9 4.1
4.8 4.7
3.6 4.8
5.1 3.5
4.8 3.9
5.3 3.9
3.4 4.8
4.2 5
5.3
4.4
4.6

3. Hypothesis testing using the p value:

The hypothesis is that the mean IQ of the population is 100. A random sample of six IQs in this population resulted in the following values: 118 98 113 110 101 111.
Using the .05 significance level, is it possible to conclude that the mean is different from 100?
a. State the decision rule.
b. Compute the test statistic
c. What is your conclusion?
d. Use Excel to find the p-value. What does it mean?

4. Hypothesis testing on a proportion:

The administration of a college is concerned that a significant number of their students are not graduating in 4 years. One factor that may contribute to the low graduation rate after 4 years could be the number of students that change their majors after the first year at Jones College. A nationwide study found that about 49% of first year students at any university change their major after the first year. Jones College sampled 100 students and found that 51 had changed their major after their first year. At the .05 level, is the proportion of students at Jones College that change their major higher than 49%?

5. Scatter Plots:

Graph each set of data using the xy chart feature in Excel. Add the chart name, Axes descriptions, r squared, and the linear equation to your chart. (Hint: Add a trendline to the chart, then review the options to format the trendline and find what you need).

a. Highest Grossing movies and the year they were released.
$2,386.80 1997
$2,068.20 2015
$1,670.40 2015
$1,519.60 2012
$1,516.00 2015
$1,405.40 2015
$1,341.50 2011
$1,276.50 2013
$1,255.40 2013
$1,159.40 2015
$1,151.10 2016
$1,123.80 2011
$1,119.90 2003
$1,118.60 2012
$1,104.10 2014
$1,084.90 2012
$1,076.20 2006

$1,063.20 2010
$1,045.70 2011
$1,029.20 1993
$1,027.00 1999
$1,024.50 2010
$1,022.60 2016
$1,021.10 2012
$1,004.60 2008
$974.80 2001
$970.80 2013
$968.50 1994
$963.40 2007
$960.30 2010
$958.40 2013
$956.00 2014
$949.90 2007
$937.90 2016
$936.70 2003
$934.40 2009
$926.00 2002
$919.80 2004
$896.90 2005
$890.90 2007
$886.70 2009
$880.70 2015
$879.00 2002
$877.20 2012
$872.70 2016
$871.50 2001
$865.00 2013
$857.40 2015
$848.80 2005
$836.30 2009
$829.70 2012
$825.50 2010
$821.70 2002
$817.40 1996
$799.00 2007
$796.70 2004
$792.90 1982
$788.70 2013
$786.60 2008
$783.80 2004
$782.40 2016
$775.40 1977
$773.30 2014
$769.70 2009
$758.50 2014
$758.20 2006
$757.90 2012
$755.40 2014
$752.60 2010
$747.90 2014
$746.90 2012
$745.00 2005
$743.60 2013
$742.10 2003
$731.30 2009
$727.60 2016
$723.20 2013
$714.40 2014
$712.20 2011
$710.60 2014
$709.70 2009
$709.70 2007
$709.00 2014
$698.50 2010
$694.70 2011
$694.40 2012
$682.30 2015
$677.90 1994
$675.10 2014
$672.80 1999
$668.00 2013
$665.70 2011
$660.90 2006
$657.80 2014
$654.30 2003
$653.40 2015
$649.40 2002
$644.60 2013
$631.70 2008

b. Hours studied and test grade

3 75
5 90
2 75
6 80
7 90
1 50
2 65
7 85
1 40
7 100
2 70
6 85
7 85
1 70
5 80
5 85
5 80
3 50
3 60
9 95
5 75
6 80
2 50
5 65
2 40
3 70
8 80
4 75
5 60
6 95

c. Humidity level and time spent outside
50% 6
40% 7
70% 0.5
20% 4.5
90% 0.25
30% 4
70% 5.5
30% 2
20% 2
30% 2.75
40% 2.75
10% 8.5
90% 1
70% 2.2
80% 1.5
50% 3
70% 1.25
40% 4
30% 3.75
20% 6
20% 5
70% 2.5
70% 2
80% 1
50% 4