Mat 144 major assignment 2

This assignment uses a scoring guide. Review the scoring guide on the last tab of the spreadsheet prior to beginning the assignment to become familiar with the expectations for successful completion.

Please note that the Confidence Interval Explanation document is provided for reference and assistance with Major Assignment 2.

Grading Sheet
Part 1
Requirements: Answer each question fully. Use Excel formulas with cell references. Answers must be recorded on the worksheet.
Possible points Points earned Comments
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 on the worksheet.
Possible points Points earned Comments
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 0%
Part 1 Inflation
Input your name here
name
You should just use information from Major Assignment 2, but if you want to retrieve fresh CPI data, here are the instrructions again. Step 1: Go to the Bureau of Labor Statistics website at the link below. Step 2: Check U.S. All items, 1982-84=100 Step 3: Click "Retrieve Data" Use the most recent CPI value and the CPI for the same month but five years earlier to estimate the price of your trip in five years and the five year inflation rate.
Month Year CPI
Unadjusted CPI, all items for 5 years ago number number number
Unadjusted CPI, all items for last month number number number
What is the 5 year inflation rate, that is percent increase in your CPI values? formula
If something cost $1.00 five years ago, what would it cost now? formula
Total budget from Worksheet 1 number
5 year projected budget total formula CPI Data Link
Like Topic 4 DQ 1
https://data.bls.gov/cgi-bin/surveymost?cu
Part 2 Questions 1-3
Before wells were dug - Millions of E.Coli per ml After wells were dug - Millions of E.Coli per ml YOUR NAME: Joe Lope 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. Original Before Data Original After Data Random numbers seed
23 52 Before After 8
21 3 min = formula min = formula Recall Topic 1 DQ 2 78 67 9 1 63 52
64 35 max = formula max = formula 1. Calculate descriptive statistics for your data in the table provided in the Excel spreadsheet. Use the statistics including mean, max and standard deviations of the data to decide if it appears if there has been improvement in water quality? This requires a thorough discussion of these statistics to obtain full marks. (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.) 19 4 17 2 4 0
54 44 mean= formula mean= formula 32 23 25 3 17 8
72 49 SD = formula SD = formula 125 110 33 4 110 95
50 35 # of wells tested = formula
Richard Ketchersid: Richard Ketchersid: Use =count() # of wells tested = formula 53 41 41 5 38 26
52 38 # of wells with 0 E coli = formula
Richard Ketchersid: Richard Ketchersid: Use =Countif() # of wells with 0 E coli = formula 68 42 49 6 53 27
49 10 4 10 57 7 0 0
55 32 Ratio Before After 106 79 65 8 91 64
73 52 Percent Clean formula formula Answer Question 1 here: 38 6 73 9 23 0
55 17 36 16 81 10 21 1
51 37 Conversions 4 14 89 11 0 0
38 26 ml oz 17 5 97 12 2 0
28 0 29.5735 1 43 9 5 13 28 0
60 44 23 3 13 14 8 0
57 40 In 24 ounces 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.) 32 8 21 15 17 0
59 30 E.coli before E. coli after 49 28 29 16 34 13
60 42 formula formula 36 18 37 17 21 3
61 34 2 21 45 18 0 6
57 33 33 22 53 19 18 7
71 50 58 25 61 20 43 10
57 40 Answer Question 2 here: 75 59 69 21 60 44
63 38 82 63 77 22 67 48
64 43 80 60 85 23 65 45
63 52 70 52 93 24 55 37
23 0 79 50 1 25 64 35
21 3 73 66 9 26 58 51
64 35 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.) 76 54 17 27 61 39
54 44 72 42 25 28 57 27
72 49 72 55 33 29 57 40
50 35 70 53 41 30 55 38
52 38 84 54 49 31 69 39
49 10 Nothing to answer here! 81 62 57 32 66 47
55 32 69 59 65 33 54 44
73 52 85 52 73 34 70 37
55 17 100 69 81 35 85 54
51 37 70 57 89 36 55 42
38 26 74 45 97 37 59 30
28 0 63 39 5 38 48 24
60 44 76 60 13 39 61 45
57 40 78 75 21 40 63 60
59 30 87 64 29 41 72 49
60 42 71 41 37 42 56 26
61 34 83 67 45 43 68 52
57 33 71 56 53 44 56 41
71 50 75 57 61 45 60 42
57 40 76 58 69 46 61 43
63 38 63 39 77 47 48 24
64 43 70 38 85 48 55 23
63 52 65 50 93 49 50 35
23 0 83 59 1 50 68 44
21 3 76 48 9 51 61 33
64 35 78 59 17 52 63 44
54 44 76 49 25 53 61 34
72 49 68 47 33 54 53 32
50 35 77 51 41 55 62 36
52 38 75 58 49 56 60 43
49 10 67 53 57 57 52 38
55 32 74 45 65 58 59 30
73 52 86 58 73 59 71 43
55 17 85 67 81 60 70 52
51 37 72 48 89 61 57 33
38 26 73 65 97 62 58 50
28 0 59 43 5 63 44 28
60 44 72 55 13 64 57 40
57 40 64 25 21 65 49 10
59 30 67 48 29 66 52 33
60 42 79 55 37 67 64 40
61 34 64 33 45 68 49 18
57 33 86 65 53 69 71 50
71 50 74 53 61 70 59 38
57 40 83 61 69 71 68 46
63 38 81 55 77 72 66 40
64 43 70 47 85 73 55 32