Answer directly on the worksheet using the Confidence Interval Explanation file.

Direction and worksheet is attached!

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%

To prepare for the final project:

This part of the assignment is not graded, an

final Mission Trip project.

Your Mission Trip will occur in 5 years, so yo

able to do the 5 year projections of your Mis

You should also be filling in the details of yo

the Mission Trip assignment:

• An overview of your project: Where are g

the sole purpose of your trip.

• Cultural and social aspects of the country

• How your choice of project works toward

How does your personal worldview and t

trip?

• Information about your target population

population of the country as a whole?

• Aspects of the country that make it a suit

• The economic situation in your co

• Possible concerns that you need

safety from violence or from hea

inal project:

gnment is not graded, and you will not hand it in at this time. It is here to help you stay on track for your

ill occur in 5 years, so you will need to make budget projections into the future. By now, you should be

r projections of your Mission Trip budget.

illing in the details of your narrative. If asked, you should be able to answer the following questions from

our project: Where are going, and what service will you be providing? Remember, evangelism cannot be

of your trip.

al aspects of the country and its population that led you to choose this area of the world.

of project works toward fulfillment of the Judeo-Christian-Islamic ideal to love your neighbor as yourself:

ersonal worldview and the possible worldviews of your target population influence your plans for the

ut your target population: Who are you planning to serve? How does this population compare to the

e country as a whole?

ountry that make it a suitable location for your trip. In particular, include information about

nomic situation in your country and how your project will improve this.

concerns that you need to keep in mind to keep your team and your target population safe. This could be

om violence or from health problems (for example, will your team need inoculations to travel to the

Input your name here

name

Unadjusted CPI, all items for 5 years ago

Unadjusted CPI, all items for last month

Inflation rate: something that cost $1.00 five

years ago would cost what now?

What percent increase is this?

Total budget from Worksheet 1

5 year projected budget total

Month

number

number

Year

number

number

CPI

number

number

formula

formula

number

formula

Part 1 – Budget Projection:

Your friends have decided to delay your dre

and you need to estimate what the cost of y

Step 1: Go to the Bureau of Labor Statistics w

bin/surveymost?cu.

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

estimate the price of your trip in five years a

dget Projection:

s have decided to delay your dream vacation from Worksheet 1 for five years,

ed to estimate what the cost of your trip will be by then.

to the Bureau of Labor Statistics website at https://data.bls.gov/cgimost?cu.

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 earlier to

he price of your trip in five years and the five year inflation rate.

Before wells

were dug Millions of

E.Coli per ml

23

21

64

54

72

50

52

49

55

73

55

51

38

28

60

57

59

60

61

57

71

57

63

64

63

23

21

64

54

72

50

52

49

55

73

55

51

38

28

After wells

were dug Millions of

E.Coli per ml

52

3

35

44

49

35

38

10

32

52

17

37

26

0

44

40

30

42

34

33

50

40

38

43

52

0

3

35

44

49

35

38

10

32

52

17

37

26

0

YOUR NAME:

Joe Lope

Before

min = formula

max = formula

mean= formula

SD = formula

sample size = formula/number

0 count = formula/number

Ratio

Percent Clean

min =

max =

mean=

SD =

sample size =

0 count =

Before

formula

Conversions

ml

29.5735

oz

1

In 24 ounces

E.coli before

formula

E. coli after

formula

After

formula

formula

formula

formula

formula/number

formula/number

After

formula

60

57

59

60

61

57

71

57

63

64

63

23

21

64

54

72

50

52

49

55

73

55

51

38

28

60

57

59

60

61

57

71

57

63

64

63

23

21

64

54

72

50

52

49

55

73

55

44

40

30

42

34

33

50

40

38

43

52

0

3

35

44

49

35

38

10

32

52

17

37

26

0

44

40

30

42

34

33

50

40

38

43

52

0

3

35

44

49

35

38

10

32

52

17

51

38

28

60

57

59

60

61

57

71

57

63

64

63

37

26

0

44

40

30

42

34

33

50

40

38

43

52

Part 2 – Data Analysis:

Enter your name in cell F1 to generate data.

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 and

after your mission. 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 improve.

1. Calculate descriptive statistics for your data in the table provided in the Excel spreadsheet. Use the

means and standard deviations of the data to decide if it appears that there has been improvement in

water quality? (Fill in the before (F3:F8) and after (H3:H8) tables to the left for the descriptive statistics.

The data has been named before and after for your convenience in creating formulas.)

Answer here:

2. The water quality is “good” if the count of E coli is 0; otherwise, the water quality is still bad. Calculate

the proportion of wells with “good” water to wells whose water is not good. From this measure does it

appear that the quality of water improved? Explain and use the proportions that you calculated. (In G11

and H11 calculate the percent Clean for before and after.)

Answer here:

.

3. Look at well #1 (B2 and C2) in your data. If you drank 24oz of water how many E.coli would you ingest if

you drank from the well before the mission? After the mission? (In E19 and G19 calculate how many E.coli

would you ingest if you drank 24 oz. of water from Well 1 before the mission and after the mission.)

ou ingest if

many E.coli

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

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

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

Random

seed

numbers

9

17

25

33

41

49

57

65

73

81

89

97

5

13

21

29

37

45

53

61

69

77

85

93

1

9

17

25

33

41

49

57

65

73

81

89

97

5

8

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

13

21

29

37

45

53

61

69

77

85

93

1

9

17

25

33

41

49

57

65

73

81

89

97

5

13

21

29

37

45

53

61

69

77

85

93

1

9

17

25

33

41

49

57

65

73

81

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

89

97

5

13

21

29

37

45

53

61

69

77

85

93

1

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 Level:

ml

Before – After

23

21

64

54

72

50

52

49

55

52

3

35

44

49

35

38

10

32

IMPROVEMENTS

min =

formula

max =

formula

mean=

formula

SD =

formula

SE =

formula

73

55

51

38

28

60

57

59

60

61

57

71

57

63

64

63

23

21

64

54

72

50

52

49

55

73

55

51

38

52

17

37

26

0

44

40

30

42

34

33

50

40

38

43

52

0

3

35

44

49

35

38

10

32

52

17

37

26

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

Frequency Distribution

95% Confidence Interval

Lower number

to

Higher number

formula

to

formula

28

60

57

59

60

61

57

71

57

63

64

63

23

21

64

54

72

50

52

49

55

73

55

51

38

28

60

57

59

60

61

57

71

57

63

64

63

23

21

64

54

72

50

52

49

55

73

0

44

40

30

42

34

33

50

40

38

43

52

0

3

35

44

49

35

38

10

32

52

17

37

26

0

44

40

30

42

34

33

50

40

38

43

52

0

3

35

44

49

35

38

10

32

52

55

51

38

28

60

57

59

60

61

57

71

57

63

64

63

17

37

26

0

44

40

30

42

34

33

50

40

38

43

52

ncy Distribution

Cumulative

Frequency

Formula

Formula

Formula

Formula

Formula

Formula

Formula

Formula

Formula

Formula

Formula

eate the Histogram, too).

Part 2 – Data Analysis:

You have just completed a mission to Sierra Leone. The goal of the mission was to impro

100 wells in a certain region. You collected data on the E. coli count from each well befo

(Q2). You need to write a report on the success of the mission and for that you need to p

analysis on the data. You will be looking at the data from different perspectives to deter

has improved.

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 analyze the a

well’s water quality improved. Calculate a data set that would measure the improveme

the descriptive statistics for that data set, including both the standard deviation and st

data set. (see section 3.5 of the textbook). Make a frequency distribution and histogram

the improvement in the water quality of each well in column D. (Difference in Level of e.

two tables to the left and make a histogram of the improvement levels. (NOTE: The Stan

data set is not the same as the standard error. Use the formulas from section 3.5 of the t

standard error of the means.))

5. You have calculated one sample of 100 wells and their improvement levels. If you cou

samples of 100 wells, the distribution of all of those sample means would be a normal di

3.5). Find the 95% confidence interval of that distribution, using your sample mean as t

the standard error of your sample as the population standard deviation. (Calculate the

of the sampling distribution in cells F24 and H24.)

6. Suppose that 0 was inside of the 95% confidence interval. From that measure,

water became cleaner? Why or why not? Suppose that 0 was outside the 95% confidenc

measure, could you conclude that the water became cleaner? Why or why not?

Answer here:

he mission was to improve the quality of water in

unt from each well before (Q1) after your mission

d for that you need to perform some statistical

nt perspectives to determine if the water quality

s sense to analyze the amount by which each

measure the improvement level of each well, and

andard deviation and standard error (SE) for the

stribution and histogram for your data. (Calculate

(Difference in Level of e. Coli.) Then, fill out the

levels. (NOTE: The Standard deviation of this

from section 3.5 of the text to calculate the

ement levels. If you could take all possible

ns would be a normal distribution. (see section

g your sample mean as the population mean and

(Calculate the 95% confidence interval

m that measure, could you conclude that the

tside the 95% confidence interval. From that

Why or why not?

Here is one sample from the before data. Hit to get a new

Samples from the after data

sample.

mean is found. This is repeat

distribution. The normal dist

Before

the mean of the after data a

25

standard deviation of the aft

20

root of the number of sampl

background. The 95% confide

15

red coloring on the normal di

10

mean + 1.96*SE). The meanin

5

the sampling distribution sho

>100

100

95

90

85

80

75

70

65

60

55

50

45

40

35

30

25

20

15

10

5

0

0

Here is one sample from the after data. Hit to get a new sample.

After

25

20

1000 mean counts

0.18

0.16

0.14

0.12

0.1

0.08

0.06

0.04

0.02

0

15

10

Mean of samplin

Standard Deviation of

5

100

>100

95

90

85

80

75

70

65

60

55

50

45

40

35

30

25

20

15

10

5

0

0

Here is another perspective. 100 samples were drawn from the After Data. A 95% CI

expect 95% of these CI’s to contain the true population mean. Hit to regenerate.

100 CI’s computed from Samples of size 100 from the After D

72

67

62

57

52

1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 37 39 41 43 45 47 49 51 53 55 57 59

52

1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 37 39 41 43 45 47 49 51 53 55 57 59

Population Mean

%CI’s that contain the mean:

94%

Each CI is formed by finding the me

standard deviation of the sample (S

of samples). The CI is computed as (

A good intuition for the CI: The mean is a point estimate. You take a sample of the population,

as an estimate for the population mean. Why should this estimate be any good, after all, you just

is an interval estimate, a 95% CI is an interval obtained from a sample and you interpret this as:

population mean is in the interval.” You are not predicting a specific mean for the population, inst

possible values for the population mean and you are able to quantify how certain you are that the

that interval.

Samples from the after data of size 100 are taken and the

mean is found. This is repeated 1000 times to get a sampling

distribution. The normal distribution which has mean equal to

the mean of the after data and standard deviation equal to the

standard deviation of the after data divided by the square

root of the number of samples, this is the SE, is shown in the

background. The 95% confidence interval is indicated by the

red coloring on the normal distribution, this is (mean – 1.96*SE,

mean + 1.96*SE). The meaning should be clear, about 95% of

the sampling distribution should occur in this interval.

1000 mean counts for samples of size 100

Mean of sampling distribution: 64.95

Standard Deviation of Sampling Dist: 2.331179678

m the After Data. A 95% CI was created for each. We should

mean. Hit to regenerate.

f size 100 from the After Data

59 61 63 65 67 69 71 73 75 77 79 81 83 85 87 89 91 93 95 97 99

59 61 63 65 67 69 71 73 75 77 79 81 83 85 87 89 91 93 95 97 99

I is formed by finding the mean (M) of the sample and then the

rd deviation of the sample (SD). SE is computed as SD/sqrt(#

ples). The CI is computed as (M – 1.96*SE, M + 1.96*SE)

e a sample of the population, take the sample mean and use this

be any good, after all, you just have one random sample. The CI

ple and you interpret this as: “I am 95% certain that the actual

c mean for the population, instead you are finding an interval of

y how certain you are that the true population mean is inside