Part 1 Requirements: Answer each question fully. Use Excel formulas with cell references. Answers must be recor Possible points Points earned Five year inflation rate 10 Projection of expenses in Major Assignment 1 10 Part 1 total 20 0 Part 2 Requirements: Answer each question fully. Use Excel formulas with cell references. Answers must be recor Possible points Points earned Descriptive Statistics 16 Interpret Descriptive Statistics 14 Proportion calculations 10 Interpretation of proportions 10 Conversions 10 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 1 h cell references. Answers must be recorded on the worksheet. Comments 2 h cell references. Answers must be recorded on the worksheet. Comments 0% Input your name below: Allissa Soloninka Color Key: formula textumber 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 Major Assignment 1 5 year projected budget total Month April April Year CPI 2015 2020 236.599 You should just use the budget information f CPI values, here are the instructions: 256.389 Step 1: Go to the Bureau of Labor Statistics w Step 2: Check the box next to "U.S. city avera Step 3: Click "Retrieve Data" 8% Use the most recent CPI value and the CPI fo earlier to compute the 5 year inflation rate. U the price of your trip five years from now. $1.08 $15,833.68 $17,158.07 Like Topic 4 DQ 1 CPI Data Link just use the budget information from Major Assignment 1. For the here are the instructions: to the Bureau of Labor Statistics website at the link below. eck the box next to "U.S. city average, All items - CUUR0000SA0" k "Retrieve Data" ost recent CPI value and the CPI for the same month but five years ompute the 5 year inflation rate. Use this inflation rate to estimate f your trip five years from now. CPI Data Link Enter your name in cell A2 of "Part 1 - Inflation" to generate data. Before wells After wells were dug were dug E.Coli per ml E.Coli per ml 16 28 25 29 34 6 9 21 22 13 13 10 27 13 20 22 34 9 30 10 12 6 7 17 26 11 20 9 24 6 26 23 39 7 7 9 29 36 34 45 53 15 29 0 13 0 15 15 23 7 21 21 27 4 22 31 26 11 20 7 36 20 36 1 18 23 31 19 27 2 30 12 YOUR NAME: From Part 1 A1 Allissa Soloninka 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 31 31 23 18 24 17 32 35 29 42 24 15 17 21 27 15 15 7 19 32 6 18 31 22 41 32 13 28 20 23 6 11 19 36 32 15 15 15 16 34 14 13 21 30 14 29 14 13 10 0 19 20 10 33 25 0 7 16 23 9 1 0 4 16 0 0 13 9 48 33 11 22 15 24 0 0 6 27 32 0 16 0 3 30 7 0 10 19 9 25 33 22 29 35 0 19 23 14 31 30 9 11 14 27 8 5 27 36 20 19 30 12 24 20 39 29 32 22 43 22 23 27 27 32 23 31 20 28 25 0 16 5 2 35 29 10 0 0 20 7 6 0 37 18 11 26 10 6 8 32 10 24 16 27 10 14 5 11 10 5 18 0 A2 of "Part 1 - Inflation" to ate data. Part 2 - Data Analysis: Enter your name in cell A2 of "Part 1 - Inflatio You have just completed a mission to Sierra Le quality of water in the wells in a certain regio well before and after your mission. You need for that you need to perform some statistical from different perspectives to determine if th After min = formula max = formula mean= formula SD = formula # of wells tested = formula # of wells with 0 E coli = formula Before formula versions Recall Topic 1 DQ 2 1. Calculate descriptive statistics for your data the statistics including mean, max and standa there has been improvement in water quality statistics to obtain full marks. (Fill in the befo descriptive statistics. The data has been name formulas.) Answer Question 1 below: After formula Color Key: oz 1 4 ounces E. coli after formula formula textumber 2. The water quality is \"good\" if the count of E Calculate the proportion of wells with \"good\" measure does it appear that the quality of wa you calculated. (In G11 and H11 calculate the Answer Question 2 below: 3. Look at well #1 (B2 and C2) in your data. If ingest if you drank from the well before the m how many E.coli would you ingest if you dran after the mission.) Nothing to answer here! s: ell A2 of "Part 1 - Inflation" to generate data. ted a mission to Sierra Leone. The goal of the mission was to improve the e wells in a certain region. You collected data on the E. coli count from each your mission. You need to write a report on the success of the mission and perform some statistical analysis on the data. You will be looking at the data ctives to determine if the water quality has improve. ve statistics for your data in the table provided in the Excel spreadsheet. Use g mean, max and standard deviations of the data to decide if it appears if ovement in water quality? This requires a thorough discussion of these ll marks. (Fill in the before (F3:F8) and after (H3:H8) tables to the left for the The data has been named before and after for your convenience in creating stion 1 below: s \"good\" if the count of E coli is 0; otherwise, the water quality is still bad. tion of wells with \"good\" water to wells whose water is not good. From this ear that the quality of water improved? Explain and use the proportions that 11 and H11 calculate the percent Clean for before and after.) stion 2 below: and C2) in your data. If you drank 24oz of water how many E.coli would you om the well before the mission? After the mission? (In E20 and G20 calculate ld you ingest if you drank 24 oz. of water from Well 1 before the mission and ng 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 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 70 74 63 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 57 45 39 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 88 78 73 84 78 77 88 83 70 65 74 73 79 87 79 77 66 73 85 77 67 59 45 57 53 57 68 61 32 48 52 53 58 59 57 55 52 58 63 55 Enter your name in cell A2 of "Part 1 - Inflation" to generate data. Before wells were dug E.Coli per ml After wells were dug E.Coli per ml Improvement Data: Before - After 16 25 34 9 22 13 27 20 34 30 12 28 29 6 21 13 10 13 22 9 10 6 7 26 20 24 26 39 7 29 34 53 29 13 15 23 21 27 22 26 20 36 36 18 31 27 30 17 11 9 6 23 7 9 36 45 15 0 0 15 7 21 4 31 11 7 20 1 23 19 2 12 All computations use column D, the "Improvement" data. min = formula max = formula mean= formula SD = formula count = formula SE = formula Histogram - Width Bin Width formula Frequency Distribution Low formula formula formula formula formula formula formula formula formula formula formula High Bins formula auto formula auto formula auto formula auto formula auto formula auto formula auto formula auto formula auto formula auto formula auto Remember to create the Histogram. 95% Confidence Interval Lower number to Higher number formula to formula 31 31 23 18 24 17 32 35 29 42 24 15 17 21 27 15 15 7 19 32 6 18 31 22 41 32 13 28 20 23 6 11 19 36 32 15 15 15 16 34 14 13 21 30 14 29 14 13 10 0 19 20 10 33 25 0 7 16 23 9 1 0 4 16 0 0 13 9 48 33 11 22 15 24 0 0 6 27 32 0 16 0 3 30 7 0 10 19 9 25 33 22 29 35 0 19 23 14 31 30 9 11 14 27 8 5 27 36 20 19 30 12 24 20 39 29 32 22 43 22 23 27 27 32 23 31 20 28 25 0 16 5 2 35 29 10 0 0 20 7 6 0 37 18 11 26 10 6 8 32 10 24 16 27 10 14 5 11 10 5 18 0 Part 2 - Data Analysis: You have just completed a mission to Sierra Leone. The goal of the mission was t wells in a certain region. You collected data on the E. coli count from a sample of mission. You need to write a report on the success of the mission and for that yo statistical analysis on the data. You will be looking at the data from different pers water quality has improved. Color Key: formula textumber 4. Since you collected water from the same source twice it makes sense to analy well's water quality improved. Calculate a data set that would measure the chan each well, and the descriptive statistics for that data set, including both the sta error (SE) for the data set. (see section 3.5 of the textbook and the additional Ex with this major assignment). Make a frequency distribution and histogram for y improvement in the water quality of each well in column D. (Difference in Leve tables to the left and make a histogram of the improvement levels. (NOTE: The S set is not the same as the standard error. Use the formulas from section 3.5 of th error of the means.)) ncy Distribution Cumulative Frequency formula formula formula formula formula formula formula formula formula formula formula create the Histogram. Frequency formula formula formula formula formula formula formula formula formula formula formula 5. You have calculated one sample of wells and their improvement levels. If you wells of the, the distribution of all of those sample means would be a normal dis the 95% confidence interval of that distribution, using your sample mean as the standard error of your sample as the population standard deviation. (Calculate the sampling distribution in cells F30 and H30.) 6. Recall that a 95% confidence interval shows a range of values that is 95% likely your 95% confidence interval for the average improvement level is (14, 18), it wo likely to be in the range (14, 18). (See the additional Excel document provided al Now answer each of these questions: a) Look at the mean for your improvement data, based on this what would you s wells. b) Look at the confidence interval you calculated--is the value 0 inside or outside c) If the value 0 is outside and less than your 95% confidence interval, can you co cleaner? d) What if the value 0 were inside your confidence interval? Could you conclude Histo gra m Answer Question 6 (a): Answer Quastion 6 (b): Answer Question 6 (c): Answer Question 6 (d): oal of the mission was to improve the quality of water in count from a sample of wells before and after your mission and for that you need to perform some data from different perspectives to determine if the it makes sense to analyze the amount by which each would measure the change in the number of E. coli in , including both the standard deviation and standard ok and the additional Excel document provided along tion and histogram for your data. (Calculate the n D. (Difference in Level of e. Coli.) Then, fill out the two ent levels. (NOTE: The Standard deviation of this data as from section 3.5 of the text to calculate the standard rovement levels. If you could take all possible samples of s would be a normal distribution. (see section 3.5). Find our sample mean as the population mean and the rd deviation. (Calculate the 95% confidence interval of values that is 95% likely to contain the true value of a parameter. For example, if ent level is (14, 18), it would mean that the true average improvement level is 95% l document provided along with this major assignment for more details.) n this what would you say about the change in the quality of water in the tested value 0 inside or outside your confidence interval? ence interval, can you conclude (with 95% confidence) that the water became al? Could you conclude (with 95% confidence) that the water became cleaner