please use excel and provide the sheet

Flint residents were told that by running (flushing) their tap water for about 45 seconds, the lead levels in the water will drop significantly.

The file titled Flint_Water_2.xlsx contains data on the lead levels in tap water for the homes for the initial draw and then for the draw from the same source after 45 seconds of flushing the water.

Did the average lead level reduce significantly after 45 seconds? Test this at 5% and 1% level of significance.

1. State the null and alternate Hypotheses. Make sure that your hypotheses are clear and unambiguous.
2. What is the p-value?
3. Did the average lead level reduce significantly at 5% level of significance? Why or why not?
4. Did the average lead level reduce significantly at 1% level of significance? Why or why not?
5. Please explain your findings in a clear language intended for a non-technical audience. Should the Flint residents be running their tap water for 45 seconds prior to drinking it?

----------------------

Continue analyzing the data set from Question 3.

Now, consider the issue of the proportion of homes that have more than 15 ppb (parts per billion) lead in their water. (Recall from the assignment in the previous module that the Environmental Protection Agency considers the proportion of homes which have more than 15 ppb lead concentration in their water when deciding whether to issue the safe water alert).Does the proportion of homes with more than 15 ppb lead significantly decrease after flushing the water for 45 seconds? Test this at 5% and 1% level of significance.

1. State the null and alternate hypotheses. Make sure that your hypotheses are clear and unambiguous.
2. What is the p-value?
3. Did the proportion of homes with more than 15 ppb of lead decrease at 5% level of significance after flushing the water for 45 seconds? Why or why not?
4. Did the proportion of homes with more than 15 ppb of lead decrease at 1% level of significance after flushing the water for 45 seconds? Why or why not?
5. Please explain your findings in a clear language intended for a non-technical audience. Should the Flint residents be running their tap water for 45 seconds prior to drinking it?

SampleID	Zip Code	Ward	Pb Bottle 1 (ppb) - First Draw	Pb Bottle 2 (ppb) - 45 secs flushing
1	48504	6	0.344	0.226
2	48507	9	8.133	10.77
4	48504	1	1.111	0.11
5	48507	8	8.007	7.446
6	48505	3	1.951	0.048
7	48507	9	7.2	1.4
8	48507	9	40.63	9.726
9	48503	5	1.1	2.5
12	48507	9	10.6	1.038
13	48505	3	6.2	4.2
15	48507	9	4.358	0.822
16	48505	5	24.37	8.796
17	48505	2	6.609	5.752
18	48503	7	4.062	1.099
19	48507	9	2.484	0.72
20	48507	9	0.438	1.046
21	48503	5	1.29	0.243
22	48504	6	0.548	0.622
23	48504	2	3.131	0.674
24	48504	6	120	239.7
25	48505	1	2.911	0.406
26	48505	5	16.52	10.26
27	48505	1	1.984	1.13
28	48503	7	5.367	2.474
29	48504	2	5.5	8.4
30	48506	4	0.639	0.223
31	48503	7	6.087	28.87
31	48503	7	10.32	13.47
33	48503	6	66.88	2.662
34	48505	1	20.41	3.543
35	48504	6	109.6	80.47
36	48503	8	5.06	3.406
37	48504	2	2.774	0.21
38	48505	3	4.453	3.679
39	48505	1	0.4	0.3
40	48529	9	0.974	0.142
41	48505	5	3.228	2.534
42	48505	2	12.55	4.132
43	48505	3	0.501	0.156
44	48504	2	2.448	0.373
45	48505	3	5.508	5.157
46	48504	6	1.293	0.441
47	48505	1	4.699	1.395
48	48504	6	6.093	2.682
49	48504	2	0.8	0.8
50	48503	5	1.626	1.332
51	48507	8	2.576	2.852
52	48504	1	2.362	0.467
53	48503	5	1.585	0.494
54	48506	4	3.058	1.808
55	48506	4	2.423	0.393
56	48503	8	30.91	42.58
57	48503	8	4.47	3.649
58	48505	1	2.172	1.76
59	48505	3	1.8	0.5
63	48503	5	0.965	0.166
65	48505	1	7.636	5.206
66	48506	3	3.158	1.948
67	48503	7	105.3	12.84
68	48506	3	4.476	0.355
69	48504	1	2.828	6.694
71	48503	5	2.481	3.86
72	48507	5	11.52	0.288
73	48507	8	3.784	0.292
74	48503	5	1.344	0.729
75	48504	6	11.93	9.645
76	48507	9	10.96	7.744
77	48504	6	3.341	0.555
78	48503	5	1.229	1.192
79	48503	6	6.3	1.1
79	48503	6	5.153	0.385
80	48504	6	6.054	0.927
82	48504	2	31.14	4.73
83	48504	6	102.7	9.894
84	48504	2	1.38	3.734
85	48505	3	1.132	2.17
87	48507	9	3.232	2.989
88	48532	8	0.507	2.315
90	48506	4	8.561	5.141
91	48505	3	9.997	0.983
92	48504	1	4.152	0.758
93	48504	2	75.82	11.65
95	48506	4	138.8	2.745
96	48507	9	0.8	0.2
97	48504	2	7.244	1051
98	48506	3	1.621	0.3
99	48505	1	1.032	0.363
100	48504	2	0.866	0.292
101	48504	2	2.525	0.59
102	48505	5	9.408	4.444
103	48505	0	0.739	4.883
104	48505	1	0.9	0.2
105	48504	2	1.403	0.142
106	48503	8	5.655	5.882
107	48505	2	31.06	8.578
108	48506	4	1.469	0.291
109	48505	1	23.85	2.301
110	48505	2	9.766	11.13
111	48505	1	4.69	0.953
112	48505	3	4.066	5.894
113	48505	1	0.846	0.455
114	48505	1	2.054	3.978
115	48506	7	3.744	5.592
116	48507	9	12.9	2.202
117	48505	1	0.543	0.183
118	48503	7	6.877	2.984
119	48505	1	0.552	0.19
121	48506	3	59	2.9
122	48506	4	0.349	0.13
123	48506	4	4.764	1.388
124	48506	4	0.832	0.284
125	48506	4	1.224	0.568
126	48507	9	15.9	3.7
127	48506	4	5.667	1.405
128	48506	4	3.564	2.767
129	48506	4	0.475	0.2
130	48507	9	5.3	0.5
131	48506	4	1.166	0.736
132	48506	4	0.684	0.306
133	48503	8	6.347	1.724
134	48507	9	10.56	5.672
135	48502	5	2.273	2.808
136	48505	1	1.571	1.265
137	48503	8	5.402	4.196
138	48504	6	43.19	7.688
139	48503	8	1.492	1.409
140	48503	5	66.24	17.75
141	48505	1	1.799	0.032
142	48503	8	1.861	1.355
143	48503	8	2.672	2.001
144	48505	1	3.741	1.211
145	48506	4	1.934	0.374
146	48506	4	27.05	0.902
147	48506	4	1.174	0.291
148	48507	9	2.325	1.099
149	48506	4	1.966	0.253
150	48506	4	1.959	0.438
151	48506	4	0.823	1.881
152	48506	4	11.2	7.553
153	48504	2	5.668	3.341
154	48506	4	6.261	1.316
155	48506	4	4.797	1.594
156	48506	4	0.64	0.905
158	48507	9	8.713	2.799
159	48507	9	2.544	1.099
161	48507	9	0.41	0.096
162	48503	7	32.85	35.76
163	48503	7	12.87	14.87
164	48503	8	38.02	38.7
165	48507	9	2.435	8.183
166	48507	9	2.997	1.867
167	48507	9	11	10.53
168	48507	9	6.219	12.33
169	48503	8	8.8	3.1
170	48504	6	8.071	0.947
171	48507	9	3.262	0.453
172	48503	8	2.267	0.541
173	48505	3	0.922	0.878
174	48504	6	27.02	31.25
176	48503	8	0.906	0.961
177	48507	9	2.85	6.862
178	48504	2	1.852	0.472
179	48506	4	5.35	1.328
180	48503	7	25.21	4.337
182	48504	6	15.55	3.962
183	48506	4	0.793	0.533
184	48506	4	5.068	0.683
185	48507	8	26.64	8.878
186	48506	4	1.867	0.165
189	48506	4	19.16	12.54
191	48503	5	28.7	12.7
192	48507	9	20.22	8.908
193	48507	8	2.9	0.6
194	48503	7	18.86	5.051
195	48507	9	2.816	0.324
196	48506	3	118.4	40.78
197	48506	3	27.45	0.939
198	48503	7	1.2	0.1
200	48506	4	4.681	0.755
201	48506	3	11.57	6.08
202	48532	8	6.557	0.289
203	48505	3	3.4	9.6
204	48504	6	0.7	0.2
205	48507	8	158	90.83
206	48506	4	0.977	0.47
207	48503	8	8.471	4.692
208	48507	9	11.47	23.15
209	48503	7	5.228	2.477
210	48507	9	0.956	0.196
211	48504	6	1.671	0.405
212	48507	9	1.152	0.708
213	48507	9	0.5	0.1
214	48503	5	10.74	2.331
215	48507	9	3.9	0.4
216	48507	9	2.149	0.368
217	48506	4	1.1	0.4
218	48507	9	7.087	9.467
219	48505	1	1.329	0.609
220	48507	9	6.2	0.7
221	48505	3	0.8	0.26
222	48503	8	9.3	9.7
223	48504	2	2.1	1.2
224	48505	1	4.563	3.106
225	48504	2	4.808	6.196
226	48504	2	0.753	2.526
227	48505	1	1.862	1.213
228	48503	7	1.183	0.366
229	48503	7	8.2	3.2
230	48504	6	3.679	0.498
231	48505	1	2.37	7.333
234	48504	2	0.828	1.318
235	48503	7	0.719	0.254
236	48504	1	2.822	1.221
237	48504	8	2.867	0.723
238	48503	7	2.332	3.588
240	48503	8	4.401	2.111
241	48504	6	2.708	2.238
242	48504	6	34.13	6.002
243	48503	7	5.218	2.614
244	48503	7	15.73	13.95
245	48504	6	3.045	2.744
246	48503	7	1.1	0.5
247	48503	7	1.386	0.288
248	48504	6	0.915	0.354
249	48504	6	2.145	0.345
250	48507	8	4.056	0.547
251	48503	7	1.668	1.508
252	48503	7	7.575	1.362
253	48507	8	5.59	4.306
254	48503	5	0.708	0.326
255	48504	6	1.701	4.397
256	48504	6	1.467	0.149
258	48504	2	2.582	259.8
259	48505	2	22.08	15.86
260	48507	8	16.51	2.024
262	48507	8	56.26	4.692
263	48504	6	2.433	1.334
264	48503	7	0.5	0.2
265	48504	6	29.13	11.57
266	48505	1	12.3	0.5
267	48504	6	3.445	0.29
268	48507	9	16.49	12.83
269	48503	7	3.365	2.45
270	48503	7	1.154	0.176
271	48507	9	13.53	21.91
272	48504	6	2.229	1.573
273	48503	7	28.91	5.471
274	48503	7	6.601	1.929
275	48507	9	0.948	0.27
276	48505	3	3.484	0.434
278	48503	7	1.888	0.359
279	48503	7	13.95	12.2
280	48504	6	6.27	4.036
281	48506	7	19.12	22.02
282	48507	9	1.633	0.465
283	48503	7	1.114	0.605
284	48503	7	3.9	0.558
285	48504	1	3.521	0.45
286	48507	9	3.832	0.794
287	48505	3	3.243	0.738
289	48503	7	0.99	0.25
290	48507	9	1.203	19.26
291	48506	3	2.261	0.102
292	48503	4	16.99	6.32
293	48503	7	3.322	2.559
294	48506	4	14.33	1.284
295	48503	7	18.11	20.21
296	48503	7	12.81	7.874
298	48503	7	1.083	0.322
299	48503	7	29.59	3.258
300	48505	1	4.287	4.345