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