Essay Grade

Part 1
Requirements: Answer each question fully. Use Excel formulas with cell references. Answers must be recorded o
Possible points
Points earned
Five year inflation rate
10
Projection of expenses in Worksheet 1
10
Part 1 total
20
0
Part 2
Requirements: Answer each question fully. Use Excel formulas with cell references. Answers must be recorded o
Possible points
Points earned
Descriptive Statistics
16
Interpret Descriptive Statistics
14
Proportion calculations
10
Interpretation of proportions
10
Conversion of before well
5
Conversion of after well
5
Improvement level data set
5
Descriptive Statistics for improvement levels
10
Histogram
5
Standard error of the mean
5
Confidence interval
10
Discussion of the placement of 0
10
Part 2 total
105
0
Total of Worksheet 2
125
0
t1
references. Answers must be recorded on the worksheet.
Comments
t2
references. Answers must be recorded on the worksheet.
Comments
0%
Alicia Fitts
name
Unadjusted CPI, all items for 5 years ago
Unadjusted CPI, all items for last month
What is the 5 year inflation rate, that is
percent increase in your CPI values?
If something cost $1.00 five years ago, what
would it cost now?
Total budget from Worksheet 1
5 year projected budget total
Month
number
number
Year
number
number
CPI
number
number
formula
formula
number
formula
Like Topic 4 DQ 1
You should just use information from Major
retrieve fresh CPI data, here are the instrruc
Step 1: Go to the Bureau of Labor Statistics w
Step 2: Check U.S. All items, 1982-84=100
Step 3: Click “Retrieve Data”
Use the most recent CPI value and the CPI fo
earlier to estimate the price of your trip in fi
rate.
CPI Data Link
just use information from Major Assignment 2, but if you want to
esh CPI data, here are the instrructions again.
to the Bureau of Labor Statistics website at the link below.
eck U.S. All items, 1982-84=100
k “Retrieve Data”
ost recent CPI value and the CPI for the same month but five years
stimate the price of your trip in five years and the five year inflation
CPI Data Link
Before wells
were dug Millions of
E.Coli per ml
8
61
63
61
52
71
58
38
0
69
56
52
59
58
62
23
67
85
55
57
56
73
62
28
58
61
63
49
56
59
110
21
55
68
60
71
63
64
91
After wells
were dug Millions of
E.Coli per ml
52
39
60
34
33
62
38
26
6
39
41
38
38
30
40
0
48
54
23
33
39
53
40
0
51
45
44
10
40
37
95
3
38
52
43
50
44
42
64
YOUR NAME:
Alicia Fitts
Before
min = formula
max = formula
mean= formula
SD = formula
# of wells tested = formula
# of wells with 0 E coli = formula
Ratio
Percent Clean
Conversions
ml
29,5735
In 24 ounces
E.coli before
formula
60
70
48
70
55
62
70
2
64
48
61
57
57
50
17
34
57
56
62
49
73
72
0
43
54
61
71
66
63
58
0
55
59
68
44
61
55
4
17
57
72
53
64
71
64
53
21
44
37
24
52
32
42
48
0
35
24
33
40
40
33
8
13
40
26
36
18
52
44
0
10
44
43
43
40
38
43
0
37
30
44
28
49
17
0
0
27
49
32
40
47
43
27
1
65
55
50
58
53
68
63
8
61
63
61
52
71
58
45
42
35
50
39
46
52
0
39
60
34
33
62
38
After
min =
max =
mean=
SD =
# of wells tested =
# of wells with 0 E coli =
Before
formula
Part 2 – Data Analysis:
Enter your name in cell F1 to generate data
You have just completed a mission to Sierra
quality of water in 100 wells in a certain reg
well before and after your mission. You nee
for that you need to perform some statistic
from different perspectives to determine if
formula
formula
formula
formula
formula
formula
After
formula
Recall Topic 1 DQ 2
1. Calculate descriptive statistics for your da
the statistics including mean, max and sta
there has been improvement in water qual
statistics to obtain full marks. (Fill in the b
descriptive statistics. The data has been nam
formulas.)
Answer Question 1 here:
versions
oz
1
4 ounces
E. coli after
formula
2. The water quality is “good” if the count o
Calculate the proportion of wells with “goo
measure does it appear that the quality of
you calculated. (In G11 and H11 calculate th
Answer Question 2 here:
3. Look at well #1 (B2 and C2) in your data.
ingest if you drank from the well before the
how many E.coli would you ingest if you dra
after the mission.)
Nothing to answer here!
ata Analysis:
r name in cell F1 to generate data.
ust completed a mission to Sierra Leone. The goal of the mission was to improve the
water in 100 wells in a certain region. You collected data on the E. coli count from each
e and after your mission. You need to write a report on the success of the mission and
ou need to perform some statistical analysis on the data. You will be looking at the data
rent perspectives to determine if the water quality has improve.
e descriptive statistics for your data in the table provided in the Excel spreadsheet. Use
ics including mean, max and standard deviations of the data to decide if it appears if
been improvement in water quality? This requires a thorough discussion of these
o obtain full marks. (Fill in the before (F3:F8) and after (H3:H8) tables to the left for the
e statistics. The data has been named before and after for your convenience in creating
uestion 1 here:
er quality is “good” if the count of E coli is 0; otherwise, the water quality is still bad.
the proportion of wells with “good” water to wells whose water is not good. From this
does it appear that the quality of water improved? Explain and use the proportions that
ated. (In G11 and H11 calculate the percent Clean for before and after.)
uestion 2 here:
well #1 (B2 and C2) in your data. If you drank 24oz of water how many E.coli would you
ou drank from the well before the mission? After the mission? (In E19 and G19 calculate
E.coli would you ingest if you drank 24 oz. of water from Well 1 before the mission and
mission.)
Nothing to answer here!
Original
Before
Data
78
19
32
125
53
68
4
106
38
36
4
17
43
23
32
49
36
2
33
58
75
82
80
70
79
73
76
72
72
70
84
81
69
85
100
70
74
63
Original
After
Data
67
4
23
110
41
42
10
79
6
16
14
5
9
3
8
28
18
21
22
25
59
63
60
52
50
66
54
42
55
53
54
62
59
52
69
57
45
39
Random
seed
numbers
14
27
40
53
66
79
92
5
18
31
44
57
70
83
96
9
22
35
48
61
74
87
100
13
26
39
52
65
78
91
4
17
30
43
56
69
82
95
13
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
63
4
17
110
38
53
0
91
23
21
0
2
28
8
17
34
21
0
18
43
60
67
65
55
64
58
61
57
57
55
69
66
54
70
85
55
59
48
52
0
8
95
26
27
0
64
0
1
0
0
0
0
0
13
3
6
7
10
44
48
45
37
35
51
39
27
40
38
39
47
44
37
54
42
30
24
76
78
87
71
83
71
75
76
63
70
65
83
76
78
76
68
77
75
67
74
86
85
72
73
59
72
64
67
79
64
86
74
83
81
70
71
68
76
72
71
86
86
88
78
73
84
78
60
75
64
41
67
56
57
58
39
38
50
59
48
59
49
47
51
58
53
45
58
67
48
65
43
55
25
48
55
33
65
53
61
55
47
54
54
64
55
55
77
62
67
59
45
57
53
8
21
34
47
60
73
86
99
12
25
38
51
64
77
90
3
16
29
42
55
68
81
94
7
20
33
46
59
72
85
98
11
24
37
50
63
76
89
2
15
28
41
54
67
80
93
6
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
61
63
72
56
68
56
60
61
48
55
50
68
61
63
61
53
62
60
52
59
71
70
57
58
44
57
49
52
64
49
71
59
68
66
55
56
53
61
57
56
71
71
73
63
58
69
63
45
60
49
26
52
41
42
43
24
23
35
44
33
44
34
32
36
43
38
30
43
52
33
50
28
40
10
33
40
18
50
38
46
40
32
39
39
49
40
40
62
47
52
44
30
42
38
77
88
83
70
65
74
73
79
87
79
77
66
73
85
77
57
68
61
32
48
52
53
58
59
57
55
52
58
63
55
10
23
36
49
62
75
88
1
14
27
40
53
66
79
92
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
62
73
68
55
50
59
58
64
72
64
62
51
58
70
62
42
53
46
17
33
37
38
43
44
42
40
37
43
48
40
Before
wells
were dug Millions of
E.Coli per
ml
After
wells
were dug Millions of Improvement
E.Coli per Data:
ml
Before – After
8
61
63
61
52
71
58
38
0
52
39
60
34
33
62
38
26
6
formula
formula
formula
formula
formula
formula
formula
formula
formula
IMPROVEMENTS
min =
formula
max =
formula
mean=
formula
SD =
formula
SE =
formula
69
56
52
59
58
62
23
67
85
55
57
56
73
62
28
58
61
63
49
56
59
110
21
55
68
60
71
63
64
39
41
38
38
30
40
0
48
54
23
33
39
53
40
0
51
45
44
10
40
37
95
3
38
52
43
50
44
42
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
Low
High
Bins
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
(remember to create the Histogram, too).
All computations use column D
“Improvement” data.
Frequency Distribution
95% Confidence Interval
Lower number
to
Higher number
formula
to
formula
91
60
70
48
70
55
62
70
2
64
48
61
57
57
50
17
34
57
56
62
49
73
72
0
43
54
61
71
66
63
58
0
55
59
68
44
61
55
4
17
57
72
53
64
71
64
53
64
44
37
24
52
32
42
48
0
35
24
33
40
40
33
8
13
40
26
36
18
52
44
0
10
44
43
43
40
38
43
0
37
30
44
28
49
17
0
0
27
49
32
40
47
43
27
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
21
65
55
50
58
53
68
63
8
61
63
61
52
71
58
1
45
42
35
50
39
46
52
0
39
60
34
33
62
38
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
Part 2 – Data Analysis:
You have just completed a mission to Sierra Leone. The goal of the mission was
to improve the quality of water in 100 wells in a certain region. You collected
data on the E. coli count from each well before (Q1) after your mission (Q2).
You need to write a report on the success of the mission and for that you need
to perform some statistical analysis on the data. You will be looking at the data
from different perspectives to determine if the water quality has improved.
computations use column D, the
“Improvement” data.
ncy Distribution
Cumulative
Frequency
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
eate the Histogram, too).
Frequency
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
formula
4. Since you collected water from the same source twice it makes sense to ana
well’s water quality improved. Calculate a data set that would measure the im
the descriptive statistics for that data set, including both the standard deviati
data set. (see section 3.5 of the textbook). Make a frequency distribution and
(Calculate the improvement in the water quality of each well in column D. (D
fill out the two tables to the left and make a histogram of the improvement lev
of this data set is not the same as the standard error. Use the formulas from se
the standard error of the means.))
5. You have calculated one sample of 100 wells and their improvement levels.
samples of 100 wells, the distribution of all of those sample means would be a
3.5). Find the 95% confidence interval of that distribution, using your sample
the standard error of your sample as the population standard deviation. (Cal
of the sampling distribution in cells F24 and H24.)
6. Recall that a 95% confidence interval shows a range of values that is 95% like
your 95% confidence interval for the average improvement level is (14, 18), it w
likely to be in the range (14, 18).
Now answer each of these questions:
a) What would it mean for the average improvement in water quality to be 0?
b) Look at the confidence interval you calculated–is the value 0 inside or outsid
c) If the value 0 is outside your 95% confidence interval, can you conclude (with
d) What if the value 0 were inside your confidence interval? Could you conclud
Answer Question 6 (a):
Answer Quastion 6 (b):
Answer Question 6 (c):
Answer Question 6 (d):
eone. The goal of the mission was
n a certain region. You collected
e (Q1) after your mission (Q2).
he mission and for that you need
ta. You will be looking at the data
e water quality has improved.
ource twice it makes sense to analyze the amount by which each
ta set that would measure the improvement level of each well, and
cluding both the standard deviation and standard error (SE) for the
ake a frequency distribution and histogram for your data.
ality of each well in column D. (Difference in Level of e. Coli.) Then,
histogram of the improvement levels. (NOTE: The Standard deviation
d error. Use the formulas from section 3.5 of the text to calculate
ls and their improvement levels. If you could take all possible
those sample means would be a normal distribution. (see section
t distribution, using your sample mean as the population mean and
pulation standard deviation. (Calculate the 95% confidence interval
H24.)
s a range of values that is 95% likely to contain the true value of a parameter. For example, if
improvement level is (14, 18), it would mean that the true average improvement level is 95%
vement in water quality to be 0?
ted–is the value 0 inside or outside your confidence interval?
ce interval, can you conclude (with 95% confidence) that the water became cleaner?
ence interval? Could you conclude in that case that the water became cleaner or not?
sheet1
Dice 1
Dice 2
4
2
4
2
5
2
4
1
1
3
1
6
1
1
3
5
6
5
4
3
4
5
2
5
5
5
4
1
2
1
6
5
4
6
3
5
4
4
1
1
Total
1
5
2
6
4
1
6
6
5
6
2
1
1
4
5
6
6
1
4
5
6
3
1
2
5
6
5
4
4
3
5
5
5
3
3
2
5
3
5
4
5
7
6
8
9
3
10
7
6
9
3
7
2
5
8
11
12
6
8
8
10
8
3
7
10
11
9
5
6
4
11
10
9
9
6
7
9
7
6
5
Descriptive Statistics
MEAN
7.3
MEDIAN
7
MODE
7
STDEV
2.4724
MAX
12
MIN
2
RANGE
10
COUNT
40
Страница 1
Worksheet 2
INFLATION, STATS & RATIOS, AND STATS & CI
Inflation

Let’s get started.

1. Follow the link.
https://www.bls.gov/re
gions/southwest/data/
consumerpriceindexhis
torical_us198284_table.pdf
This is a safe file. ☺

2. Notice the latest
data for 2018.

3. See the picture on
the left for the data
column to use.
Inflation

Let’s continue.

1. The inflation rate is a
dollar value. Very similar
to sales tax.

2. For the percent
increase do not divide
by 100. See circled
formula for D10.

3. Do not use my
numbers for the CPI as
they are only an
example.

4. Cells D9, D10, and D12
must have formulas.
Statistics & Ratios
1.
Enter your name in Cell
F1.
2.
Use the statistic formulas
from worksheet 1 and
Topic 1 DQ 2.
3.
Use COUNT for sample
size and COUNTIF for 0
count.
4.
Ratio for Percent Clean =
0 Count/sample size.
5.
How would we calculate
the conversion ratios?
6.
Don’t forget to use Before
and After in your formulas
when applicable.
Stats Question
4 – 6.

Let’s get started.

1. Calculate the
Improvement Level in
column D.

2. Its okay to have a
negative value in
column D.
Descriptive
Statistics

Let’s get started.

1. Use the descriptive
statistics formulas you
used in Topic 1 DQ 2.
Descriptive Statistics

https://mediaplayer.pea
rsoncmg.com/assets/gt
q1e_1_4_1_24_Descriptiv
e_Stats

2. What is the standard
error?.

3. The SE = SD/sqrt(of the
number of data items).
Frequency
Table

Let’s get started.

1. Notice that we have
11 rows in our table.

2. These rows
represent different
groups that we are
organizing our data
into.

3. In our table we are
calling them Bins.
Frequency
Table Cont.

Let’s get started with
the Low value in cell
F12

1. The low value is the
minimum value from
our statistics, G3.

2. Note, some use G3 –
0.1 for that low value.
Frequency
Table Cont.

Let’s get started.

1. Notice there is
added a Bin Width
calculation in cell I3.

2. The Bin Width = the
range/11. The range =
MAX – MIN.

3. The High value is the
low value + Bin Width,
=F12+I$3.
Frequency
Table Cont.

Let’s keep going.

1. Column H is the title
or name of the Bins
Notice that I have
written it two different
way. You choose. ☺
NOTE this is text not
data.

2. The cumulative
frequency is the sum of
the frequency values.

I12 = J12, I13 = I12 +
J13, I14 = I13 + J14, etc.
Frequency
Table Cont.

Let’s keep going.

1. Column J is the
frequency ,or the
count, of how many
data are in each of the
bins.

2. Note that the sum of
the frequencies is the
total number of data in
column A.
Frequency
Table Cont.

To construct the frequency column J, do the following steps.

1. Enter the data in Column A. This is already done.

2. Complete column G.

3. In cell J12, type, but don’t press enter, the following formula.
This is already done.
=Frequency(D$2:D$101,G12:G22)

4. Hold the “Shift” key and click your mouse the last cell in column J, J22. This will
highlight all of column J.

5. Press the “F2” key on your computer (your formula will show) and then press
“Ctrl-Shift-Enter” to copy the formula as an array.

This will complete column J for you.
Making the
Histogram

Let’s conclude.

1. To make the bar
graph highlight all of
column J including
title.

2. Select Insert (from
top tabs.)

3. Select column
graphs ->top option

4. Select 3-D column
graph

You have the graph. ☺
Making the
Histogram

To make this bar graph
a histogram click on
the graph and then:

1. Select Design -> from
top tabs

2. Select Quick Layout
-> Top left under Home

3. Select histogram 3rd
row and 2nd column.

You have the
histogram. ☺
Making the
Histogram

To make histogram
have an outline,
different color, etc.:

1. Click on the graph to
select all bars.

2. Right mouse click for
options.

3. Select outline and
then color of choice.

You have the
histogram with outlines.
☺
Making the
Histogram

To change the titles of the
bins:

1. Click on the axis label
you want to change.

2. Choose the Design tab.

3. Choose Select Data

4. Choose Edit Horizontal
Axis Labels

5. Highlight H4 through
H14 and press enter and
your done. ☺
Hello Class,
Here are a few videos to help you with Major Assignment 2.
Major Assignment 2 explanation https://youtu.be/FxeuNfrixAM The process for finding the correct tables has
changed. If you are unable to find your two CPIs, just use the two I showed.
Frequency table and histogram https://youtu.be/e01iLYqkTao
Standard Error https://mediaplayer.pearsoncmg.com/assets/gtq1e_3_5_1_24_Std_Error
Countif function video https://www.youtube.com/watch?v=CLEYppjUWFI
The most challenging part of Major Assignment 2 for most students is the last question about confidence intervals.
Here are a few good resources if you need some assistance.
Video https://www.youtube.com/watch?v=siqx4PbqJ6s
Articlehttp://www.wikihow.com/Calculate-Confidence-Interval
You should also find an additional Excel document attached to the assignment description in LoudCloud about
confidence intervals. I also attached it to this post. It should help answer part 2 questions 5 and 6.
I often see students skip a calculation on the assignment. All of these can be found using Excel, so if you don’t
know how to do one of the calculation ask for help, look at the videos in MyMathLab, or search You Tube for a
video showing how to find the calculation.
If you have questions, make sure to include your Excel document so I can see what you are doing.

Purchase answer to see full
attachment