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% 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.2 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: 95% 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: 65.87 Standard Deviation of Sampling Dist: 2.277169168 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
Purchase answer to see full attachment