MAT 144 Major Assignment 2

Answer directly on the worksheet. 

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
  63	52	71	54	93	74	56	39
  23	0	68	54	1	75	53	39
  21	3	76	64	9	76	61	49
  64	35	72	55	17	77	57	40
  54	44	71	55	25	78	56	40
  72	49	86	77	33	79	71	62
  50	35	86	62	41	80	71	47
  52	38	88	67	49	81	73	52
  49	10	78	59	57	82	63	44
  55	32	73	45	65	83	58	30
  73	52	84	57	73	84	69	42
  55	17	78	53	81	85	63	38
  51	37	77	57	89	86	62	42
  38	26	88	68	97	87	73	53
  28	0	83	61	5	88	68	46
  60	44	70	32	13	89	55	17
  57	40	65	48	21	90	50	33
  59	30	74	52	29	91	59	37
  60	42	73	53	37	92	58	38
  61	34	79	58	45	93	64	43
  57	33	87	59	53	94	72	44
  71	50	79	57	61	95	64	42
  57	40	77	55	69	96	62	40
  63	38	66	52	77	97	51	37
  64	43	73	58	85	98	58	43
  63	52	85	63	93	99	70	48
  77	55	1	100	62	40

  Part 2 Questions 4-6

  Before wells were dug - Millions of E.Coli per ml	After wells were dug - Millions of E.Coli per ml	Improvement Data: Before - After	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.
  23	52	formula	IMPROVEMENTS
  21	3	formula	min =	formula	All computations use column D, the "Improvement" data.	4. Since you collected water from the same source twice it makes sense to analyze the amount by which each well’s water quality improved. Calculate a data set that would measure the improvement level of each well, and the descriptive statistics for that data set, including both the standard deviation and standard error (SE) for the data set. (see section 3.5 of the textbook). Make a frequency distribution and histogram for your data. (Calculate the improvement in the water quality of each well in column D. (Difference in Level of e. Coli.) Then, fill out the two tables to the left and make a histogram of the improvement levels. (NOTE: The Standard deviation of this data set is not the same as the standard error. Use the formulas from section 3.5 of the text to calculate the standard error of the means.))
  64	35	formula	max =	formula
  54	44	formula	mean=	formula
  72	49	formula	SD =	formula
  50	35	formula	SE =	formula
  52	38	formula
  49	10	formula
  55	32	formula	Frequency Distribution
  73	52	formula	Low	High	Bins	Cumulative Frequency	Frequency
  55	17	formula	Formula/Number	Formula/Number	words or formula	formula	formula
  51	37	formula	Formula/Number	Formula/Number	words or formula	formula	formula	5. You have calculated one sample of 100 wells and their improvement levels. If you could take all possible samples of 100 wells, the distribution of all of those sample means would be a normal distribution. (see section 3.5). Find the 95% confidence interval of that distribution, using your sample mean as the population mean and the standard error of your sample as the population standard deviation. (Calculate the 95% confidence interval of the sampling distribution in cells F24 and H24.)
  38	26	formula	Formula/Number	Formula/Number	words or formula	formula	formula
  28	0	formula	Formula/Number	Formula/Number	words or formula	formula	formula
  60	44	formula	Formula/Number	Formula/Number	words or formula	formula	formula
  57	40	formula	Formula/Number	Formula/Number	words or formula	formula	formula
  59	30	formula	Formula/Number	Formula/Number	words or formula	formula	formula
  60	42	formula	Formula/Number	Formula/Number	words or formula	formula	formula	6. Recall that a 95% confidence interval shows a range of values that is 95% likely to contain the true value of a parameter. For example, if your 95% confidence interval for the average improvement level is (14, 18), it would mean that the true average improvement level is 95% 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 outside your confidence interval? c) If the value 0 is outside your 95% confidence interval, can you conclude (with 95% confidence) that the water became cleaner? d) What if the value 0 were inside your confidence interval? Could you conclude in that case that the water became cleaner or not?
  61	34	formula	Formula/Number	Formula/Number	words or formula	formula	formula
  57	33	formula	Formula/Number	Formula/Number	words or formula	formula	formula
  71	50	formula	Formula/Number	Formula/Number	words or formula	formula	formula
  57	40	formula	(remember to create the Histogram, too).
  63	38	formula
  64	43	formula
  63	52	formula
  23	0	formula	95% Confidence Interval
  21	3	formula	Lower number	to	Higher number
  64	35	formula	formula	to	formula
  54	44	formula	Answer Question 6 (a):
  72	49	formula
  50	35	formula
  52	38	formula
  49	10	formula
  55	32	formula
  73	52	formula
  55	17	formula
  51	37	formula	Answer Quastion 6 (b):
  38	26	formula
  28	0	formula	Answer Question 6 (c):
  60	44	formula
  57	40	formula	Answer Question 6 (d):
  59	30	formula
  60	42	formula
  61	34	formula
  57	33	formula
  71	50	formula
  57	40	formula
  63	38	formula
  64	43	formula
  63	52	formula
  23	0	formula
  21	3	formula
  64	35	formula
  54	44	formula
  72	49	formula
  50	35	formula
  52	38	formula
  49	10	formula
  55	32	formula
  73	52	formula
  55	17	formula
  51	37	formula
  38	26	formula
  28	0	formula
  60	44	formula
  57	40	formula
  59	30	formula
  60	42	formula
  61	34	formula
  57	33	formula
  71	50	formula
  57	40	formula
  63	38	formula
  64	43	formula
  63	52	formula
  23	0	formula
  21	3	formula
  64	35	formula
  54	44	formula
  72	49	formula
  50	35	formula
  52	38	formula
  49	10	formula
  55	32	formula
  73	52	formula
  55	17	formula
  51	37	formula
  38	26	formula
  28	0	formula
  60	44	formula
  57	40	formula
  59	30	formula
  60	42	formula
  61	34	formula
  57	33	formula
  71	50	formula
  57	40	formula
  63	38	formula
  64	43	formula
  63	52	formula