BUS-352-RS-CLC-COVID_in_Arizona-Student-Jazmin Lopez

CLC – Exploring COVID-19 in Arizo Overview Tasks (All students must complete this section individually) Instructions for Data Analysis Portion Questions Binomial Distribution Columns F and G in the "Analysis" tab are there if you wish to calculate individual probabilities to answer the questions rather 2. How many in the "Total on Ventilators" were in the selected month from the pivot table (count for Response CFR)? 4. What is the probability that exactly 5 of them are ventilated? P(x = 5)? 5. What is the probability that more than 5 are ventilated? P(x > 5)? 6. What is the probability that between 4 and 7, inclusive, are ventilated? P(4 <= x <= 7)? During the early stages of the COVID-19 pandemic, many hospitals worldwide became insufficiently stocked with necessities t beds. To investigate the hospital circumstances surrounding the peak stages of COVID-19 in Arizona, you will analyze ventilato Tracking Project by The Atlantic. The dataset was compiled for June 2020 through February 2021 and is attached in the Excel a You will use the DCOVAS framework. Reference readings in The Statistician In You: Simple Everyday Life-Hacks (TSIY). ·          Define the problem. ·          Collect data from the appropriate source(s). ·          Organize data using tables. ·          Visualize data using a chart or graph. ·          Analyze data using a tool and/or calculation(s). ·          Solution: Provide a conclusion and solution. Review the provided pivot table and bar chart on the “RawData” tab. What are your initial observations of the pivot table and bar chart information that is provided? (Your opinion will be documen Select randomized sample for "Current Hospitalized." 1.        On the "RawData" tab, click the drop down arrow in the "Record Shuffler" column and select "Sort Smallest to Largest" (a 2.        Copy the first randomized 51 rows of "Current Hospitalized" (Column D) to include the label. 3.        Paste the 51 rows into the tab “Analysis” cell A1 as indicated. You should have 50 records. Organization has been provided for you in the pivot table in the "RawData" sheet. Visualization has been provided for you in the "RawData" sheet. ·          Each team member should complete the binomial distribution and Poisson distribution sections using a different month o ·          Each team member will use their individual sample of 50 "Current Hospitalized" to get a descriptive statistics table from E ·          The "Comparison of Samples to Population Analysis" and the "Putting It All Together" sections can be distributed across g ·          Each student's "Analysis" results should be copied and pasted into the appropriate area of the "SummaryResults" tab, wh 1. How many in the "Total in ICU" column were in the selected month from the pivot table (sample size n)? 3. If we assume being on a ventilator as a success, what is the rate of success in your sample (sample proportion p)? This refle Using the success rate p from above, for any given day and a random sample of 10 ICU patients across Arizona, use the binom

Poisson Distribution Columns F and G in the Analysis tab are there if you wish to calculate individual probabilities to answer the questions, rather th 7. What is the Average Deaths per day in the selected month from pivot table (μ)? Assuming the number of deaths per day are relatively consistent within any month, use the Poisson distribution to calculate th 8. What is the probability of exactly 20 deaths in one day of the selected month? P(x = 20)? 9. What is the probability of less than 20 deaths in one day of the selected month? P(x < 20)? 10. What is the probability of 40 or more deaths in one day of the selected month? P(x >= 40) 11. What is the probability of 120 or more deaths in one day of the selected month? P(x >= 120) 12. What is the probability of deaths on one day of the selected month being between 10 and 40? P(10 <= x <= 40) Probabilities of Sampling Distributions 13. What is the probability of the sample mean from 50 random days being fewer than 2,000 patients hospitalized? 14. What is the probability of the sample mean from 50 random days being more than 2,000 patients hospitalized? 15. What is the probability of the sample mean from 50 random days being fewer than 3,000 patients hospitalized? 16. What is the probability of the sample mean from 50 random days being between 1,500 and 2,500 patients hospitalized? Comparison of Samples to Population 18. What is the range of the sample's means? Does the population mean fit within this range? Putting It All Together: As a group, prepare and submit ONE group PowerPoint presentation (8-12 slides). The presentation should include s Deliverables Using your random sample of 50 records from the variable "Current Hospitalized" that you pasted into Column A in the "Analy into the "Analysis" tab where indicated (Column C below Poisson calculations). Copy the entire "Current Hospitalized" column in the "RawData" tab and paste into Cell T1 of the "SummaryResults" tab. Run descriptive statistics from Excel's Data Analysis Tool on the entire "Current Hospitalized" column and paste the summary t Copy each team member's "Sample" tables Into the "SummaryResults" tab cells as indicated in the worksheet. Change "Stude 17. What is the sampling error each team member experienced compared to the population mean? Recall from Chapter 7 in E Sample Mean - Population Mean · Create a roles and responsibility slide. · Explanation of all DCOVA elements. · Include a screenshot of the pivot table and bar chart located in the “RawData” tab. Include your group’s initial viewpoint · Create a slide to comment on the binomial and Poisson result differences that you noticed in in each team member's cal ventilators? How can the data regarding deaths per day during a peak month of the pandemic help officials to understand the capacity for ICU beds, ventilators, or other hospital-related needs? Are there any patterns or trends that you can validate with addressed in this study? · Create a summary slide to compare the population descriptive statistics of current hospitalized to the individual sample incurred? What was the range of the sampling means and how did the population mean compare to them? Would the samplin unavailable? Be sure to include your reasoning. Research two or more sources that validate your summary slide. · Add a reference slide and include two or more references. Note : Each student should use their own sample to answer their assigned section(s). Each team member's Excel file ("Analysis submitted.

Date State 2/22/2021 AZ 0 1590 478 299 0.4676 12/23/2020 AZ 54 4163 972 673 0.9623 12/17/2020 AZ 147 3884 899 618 0.4050 8/24/2020 AZ 0 908 345 176 0.9563 1/9/2021 AZ 98 4918 1121 791 0.1008 9/10/2020 AZ 22 628 204 120 0.6692 1/29/2021 AZ 203 3970 1002 678 0.9644 2/8/2021 AZ 7 2853 828 536 0.7083 2/4/2021 AZ 176 3303 946 618 0.3101 8/28/2020 AZ 49 809 272 160 0.5870 2/17/2021 AZ 82 1941 593 354 0.8644 6/27/2020 AZ 44 2577 657 433 0.1201 10/10/2020 AZ 13 685 145 71 0.6725 6/1/2020 AZ 11 968 377 238 0.2820 2/7/2021 AZ 37 2910 838 561 0.1448 1/2/2021 AZ 46 4484 1074 761 0.0947 7/11/2020 AZ 69 3485 899 620 0.9883 7/23/2020 AZ 89 2966 851 617 0.4021 8/26/2020 AZ 104 967 305 180 0.6347 10/28/2020 AZ 14 871 188 94 0.7849 10/29/2020 AZ 13 874 186 95 0.5901 7/30/2020 AZ 172 2348 758 531 0.3072 9/21/2020 AZ 2 472 119 57 0.3357 1/14/2021 AZ 182 4930 1167 821 0.6395 1/28/2021 AZ 176 4087 1023 692 0.9768 8/16/2020 AZ 14 1208 417 267 0.2414 12/11/2020 AZ 91 3482 809 508 0.2012 8/12/2020 AZ 148 1469 519 328 0.0636 10/5/2020 AZ 1 567 134 57 0.8732 6/8/2020 AZ 3 1266 390 246 0.8897 10/8/2020 AZ 10 728 156 63 0.3873 10/18/2020 AZ 3 714 176 85 0.0360 11/21/2020 AZ 30 1916 435 253 0.6595 10/4/2020 AZ 1 545 133 59 0.6980 9/29/2020 AZ 9 540 119 64 0.9614 7/19/2020 AZ 31 3136 894 622 0.1343 8/18/2020 AZ 23 1167 427 260 0.1113 11/18/2020 AZ 53 1700 396 218 0.7779 8/21/2020 AZ 4 1068 365 234 0.3005 12/7/2020 AZ 0 3059 736 459 0.7228 11/22/2020 AZ 7 1932 438 256 0.1699 New Deaths Reported Current Hospitalized Current in ICU Current on Ventilator Record Shuffler <-- Sort

6/3/2020 AZ 40 1092 379 239 0.2965 7/14/2020 AZ 92 3517 970 674 0.0452 8/19/2020 AZ 105 1160 414 250 0.1189 1/10/2021 AZ 105 4988 1122 791 0.9754 12/29/2020 AZ 171 4475 1053 720 0.9404 2/3/2021 AZ 214 3456 955 646 0.6963 7/10/2020 AZ 44 3432 876 615 0.1286 6/23/2020 AZ 42 2136 614 386 0.6347 7/4/2020 AZ 17 3113 796 413 0.8179 1/6/2021 AZ 127 4877 1084 747 0.9229 6/18/2020 AZ 32 1667 540 341 0.6377 8/11/2020 AZ 45 1574 510 346 0.4987 9/22/2020 AZ 20 527 122 47 0.0683 2/23/2021 AZ 148 1515 447 266 0.9133 10/22/2020 AZ 5 848 164 92 0.2684 2/25/2021 AZ 121 1385 415 241 0.9042 10/3/2020 AZ 12 605 127 52 0.0710 6/4/2020 AZ 15 1079 375 223 0.6971 8/8/2020 AZ 56 1659 539 400 0.9121 6/19/2020 AZ 41 1832 519 362 0.5223 11/27/2020 AZ 20 2301 532 325 0.9989 2/9/2021 AZ 231 2744 797 524 0.3932 9/30/2020 AZ 18 560 115 55 0.1985 1/30/2021 AZ 76 3828 984 645 0.7364 6/30/2020 AZ 44 2793 683 455 0.5404 10/20/2020 AZ 7 777 170 91 0.5038 10/2/2020 AZ 19 586 125 48 0.9481 12/1/2020 AZ 48 2594 597 369 0.0680 9/26/2020 AZ 35 509 115 56 0.9987 2/28/2021 AZ 13 1251 402 210 0.4824 9/5/2020 AZ 36 702 225 131 0.6799 8/13/2020 AZ 36 1411 497 334 0.4190 6/6/2020 AZ 30 1278 391 292 0.8534 11/7/2020 AZ 38 1139 249 137 0.9362 7/16/2020 AZ 58 3454 918 657 0.6911 11/20/2020 AZ 43 1835 431 244 0.7843 12/28/2020 AZ 42 4390 1007 715 0.4806 10/21/2020 AZ 17 832 171 91 0.5537 2/24/2021 AZ 43 1449 430 253 0.9019 1/22/2021 AZ 229 4495 1054 739 0.7044 9/9/2020 AZ 30 658 203 111 0.1389 12/3/2020 AZ 82 2743 642 386 0.6679 11/25/2020 AZ 9 2217 531 335 0.4301 11/15/2020 AZ 2 1506 368 183 0.1651

10/11/2020 AZ 0 630 144 79 0.4507 7/18/2020 AZ 147 3238 894 657 0.5299 2/13/2021 AZ 114 2300 701 412 0.3957 10/30/2020 AZ 16 900 188 99 0.2516 6/26/2020 AZ 45 2110 581 312 0.0590 9/7/2020 AZ 0 658 221 119 0.3062 6/21/2020 AZ 1 1942 556 361 0.9814 11/8/2020 AZ 17 1185 272 148 0.8662 11/26/2020 AZ 44 2289 523 335 0.2802 1/3/2021 AZ 0 4557 1081 762 0.5377 9/2/2020 AZ 21 752 248 146 0.2674 8/4/2020 AZ 66 2024 638 474 0.3809 10/14/2020 AZ 5 711 163 85 0.7393 12/19/2020 AZ 118 4014 939 612 0.6487 12/30/2020 AZ 78 4526 1076 750 0.5943 8/22/2020 AZ 68 1046 363 209 0.4257 10/25/2020 AZ 5 828 181 91 0.5109 1/11/2021 AZ 6 4997 1158 783 0.9465 11/29/2020 AZ 10 2458 573 356 0.3541 10/26/2020 AZ 1 837 179 97 0.9372 6/14/2020 AZ 3 1457 452 317 0.8741 1/25/2021 AZ 1 4229 1027 702 0.4283 12/21/2020 AZ 1 3925 904 628 0.4631 6/9/2020 AZ 23 1243 438 264 0.2196 9/4/2020 AZ 41 742 236 145 0.6598 10/19/2020 AZ 3 721 177 94 0.0168 1/26/2021 AZ 209 4221 1028 687 0.0275 7/21/2020 AZ 134 3041 865 608 0.8710 10/1/2020 AZ 24 620 122 54 0.8857 10/12/2020 AZ 0 667 155 80 0.7109 8/23/2020 AZ 15 969 329 191 0.6397 7/20/2020 AZ 23 3084 886 622 0.9387 1/21/2021 AZ 244 4580 1058 731 0.6594 7/1/2020 AZ 88 2876 675 466 0.2747 1/12/2021 AZ 335 5082 1183 786 0.8402 2/20/2021 AZ 59 1650 517 294 0.7402 12/6/2020 AZ 25 2977 714 462 0.4387 9/19/2020 AZ 16 528 120 70 0.4224 7/12/2020 AZ 86 3432 922 631 0.9758 7/13/2020 AZ 8 3373 936 671 0.4632 2/16/2021 AZ 3 2047 601 383 0.5434 12/27/2020 AZ 3 4190 988 667 0.7495 1/8/2021 AZ 197 4907 1122 799 0.5571 12/31/2020 AZ 146 4564 1028 746 0.2506

8/6/2020 AZ 70 1879 593 427 0.9543 2/19/2021 AZ 145 1738 563 334 0.7194 8/1/2020 AZ 53 2226 710 490 0.8896 11/13/2020 AZ 17 1381 335 176 0.7819 12/2/2020 AZ 52 2699 642 386 0.0956 7/26/2020 AZ 18 2650 837 581 0.2443 1/17/2021 AZ 17 4773 1118 800 0.1848 11/9/2020 AZ 0 1232 292 142 0.5857 9/13/2020 AZ 7 525 171 87 0.7992 12/20/2020 AZ 34 3899 885 595 0.1578 1/24/2021 AZ 68 4320 1054 720 0.6018 12/13/2020 AZ 35 3622 831 537 0.7305 7/15/2020 AZ 97 3493 929 671 0.9053 11/23/2020 AZ 0 2008 469 294 0.8475 9/12/2020 AZ 27 574 185 93 0.9818 12/10/2020 AZ 73 3408 799 496 0.5788 11/3/2020 AZ 38 956 227 112 0.8685 1/13/2021 AZ 191 5055 1158 805 0.0178 8/17/2020 AZ 0 1182 430 266 0.9715 9/11/2020 AZ 15 577 171 100 0.4506 9/3/2020 AZ 65 745 241 135 0.3758 1/15/2021 AZ 185 4866 1138 753 0.7155 10/24/2020 AZ 4 819 191 87 0.9420 12/8/2020 AZ 23 3157 744 487 0.6565 2/6/2021 AZ 63 3060 849 561 0.6214 11/6/2020 AZ 22 1082 250 138 0.0530 7/17/2020 AZ 91 3466 944 687 0.0718 9/8/2020 AZ 2 657 212 112 0.3293 11/19/2020 AZ 19 1796 433 227 0.7030 6/12/2020 AZ 17 1336 429 278 0.2927 11/4/2020 AZ 39 1065 241 127 0.7011 2/2/2021 AZ 238 3513 944 671 0.8303 6/5/2020 AZ 16 1234 375 241 0.6633 9/27/2020 AZ 1 483 116 59 0.6771 8/31/2020 AZ 0 768 256 152 0.4533 2/21/2021 AZ 22 1598 501 286 0.5329 10/6/2020 AZ 6 665 138 65 0.9023 2/1/2021 AZ 4 3654 984 662 0.8416 10/17/2020 AZ 18 757 174 81 0.8148 1/23/2021 AZ 169 4442 1049 728 0.7071 10/13/2020 AZ 8 706 146 88 0.1458 6/29/2020 AZ 0 2721 679 465 0.2108 9/18/2020 AZ 42 516 130 72 0.1331 10/27/2020 AZ 16 861 187 100 0.3838

10/9/2020 AZ 3 706 155 70 0.3373 9/23/2020 AZ 27 583 114 62 0.6616 2/18/2021 AZ 213 1823 566 330 0.2710 8/25/2020 AZ 21 999 319 168 0.8910 8/10/2020 AZ 4 1575 506 366 0.9199 12/16/2020 AZ 108 3809 882 602 0.2046 6/7/2020 AZ 2 1252 392 248 0.7655 9/25/2020 AZ 28 521 119 65 0.9888 7/28/2020 AZ 104 2564 814 574 0.0679 1/4/2021 AZ 3 4647 1082 782 0.5338 6/15/2020 AZ 8 1449 464 307 0.3198 9/28/2020 AZ 0 468 115 57 0.3776

Pivot Table Data Date Total Current HTotal Current i Total Current oTotal New Deaths Jun 50850 14980 9833 726 Jul 95275 26277 18058 2062 Aug 41750 13903 9195 1335 Sep 17528 4905 2658 621 Oct 22918 5031 2477 329 Nov 48539 11363 6429 660 Dec 114172 26553 17540 2225 Jan 142421 33555 23341 4256 Feb 63442 18590 11710 2860 Visualization: Date Jun Jul Aug Sep Oct Nov Dec Ja 0 20000 40000 60000 80000 100000 120000 140000 160000 Data Column M Column N Column O Column P

A. Binomial Distribution 1590 Month Selected 4163 Sample Size (n) 3884 Response of Interest (ROI) 908 Count for Response (CFR) 4918 628 3970 2853 3303 809 1941 2577 685 B. Poisson Distribution 968 Month Selected 2910 4484 3485 2966 967 871 874 2348 472 4930 4087 Descriptive Statistics for Hospitalized Sample 1208 Insert Descriptive Statistics Summary Table Here 3482 1469 Current Hospitlized 567 1266 Mean Current Hospitalized Sample Proportion (p) =CFR/n For n=10, P(x = 5) =BINOM.DIST(x,trials(n),success_rate(p),FALSE) For n=10, P(x > 5) =1-BINOM.DIST(x,trials(n),success_rate(p),TRUE) For n=10, P(4 <= x <= 7) =BINOM.DIST(x,trials(n),success_rate(p),TRUE)-BINOM.DIST(x-1, trials(n),success_rate(p),TRUE) Average deaths per day (μ) Exactly 20 in one day P(x = 20) =POISSON.DIST(x,μ,FALSE) Less than 20 in one day P(x < 20) =POISSON.DIST(x-1,μ,TRUE) 40 or more deaths in one day P(x >= 40) =1-POISSON.DIST(x-1,μ,TRUE) 120 or more deaths in one day P(x >= 120) =1-POISSON.DIST(x-1,μ,TRUE) Between 10 and 40 deaths in one day ? P(10 <= x <= 40) =POISSON.DIST(upper x,μ,TRUE) - POISSON.DIST(lower x-1,μ,TRUE)

728 Standard Error 714 Median 1916 Mode 545 Standard Deviation 540 Sample Variance 3136 Kurtosis 1167 Skewness 1700 Range 1068 Minimum 3059 Maximum 1932 Sum 1092 Count 3517 1160 4988 C. Probabilities of Sampling Distributions 4475 3456 3432 2136 3113 xbar = Mean =AVERAGE(range) Standard Deviation (s) =STDEV.S(range) Sample size (n) =COUNT(range) Standard error =s/sqrt(n) P(xbar<2000) =NORM.DIST(x,mean,stdError,TRUE) P(xbar>2000) =1-NORM.DIST(x,mean,stdError,TRUE) P(xbar<3000) =NORM.DIST(x,mean,stdError,TRUE) P(1500<xbar<2500) =NORM.DIST(upper x,mean,stdError,TRUE)-NORM.DIST(lower x, mean,stdError,TRUE)

203.601407567004 17 1941 18 #N/A 19 1425.20985296903 20 2031223.125 21 -1.22610537800296 22 0.360270793222293 23 4516 24 472 25 4988 26 111867 27 49 28 29 30 31 2269.1 32 1414.0 33 50.0 34 200.0 35 0.0892 36 0.5755 37 0.9999 38 0.5826 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 86 87 88 89 90 91 92 93

138 139 140 141 142 143 144 145 146 147 148 149 150

Population Table Jazmin Lopez Sample Table StudentName2 Sample Table StudentName3 Sample Table StudentName4 Sample Table StudentName5 Sample Table Current Hospitalized Current Hospitlized Sampling Sampling Sampling Sampling Sampling Error Error Error Error Error Mean 2188.62 Mean 2283 94.378676 Standard Error 83.1941 Standard Error 203.601 Median 1897.5 Median 1941 Mode 472 Mode #N/A Standard Deviation 1372.07 Standard Deviation 1425.21 Sample Variance 1882581 Sample Variance 2031223 Kurtosis -0.98543 Kurtosis -1.22611 Skewness 0.52125 Skewness 0.36027 Range 4614 Range 4516 Minimum 468 Minimum 472 Maximum 5082 Maximum 4988 Sum 595305 Sum 111867 Count 272 Count 49 Max Min Sample Mean Range Currently Hospitalized StudentName1 StudentName2 StudentName3 StudentName4 StudentName5 C. Probabilities of Sampling Distributions C. Probabilities of Sampling Distributions C. Probabilities of Sampling Distributions C. Probabilities of Sampling Distributions C. Probabilities of Sampling Distributions xbar = Mean =AVERAGE(range) Standard Deviation (s) =STDEV.S(range) Sample size (n) =COUNT(range) Standard error =s/sqrt(n) P(xbar<2000) =NORM.DIST(x,mean,stdError,TRUE) P(xbar>2000) =1-NORM.DIST(x,mean,stdError,TRUE) P(xbar<3000) =NORM.DIST(x,mean,stdError,TRUE) =NORM.DIST(upper x,mean,stdError,TRUE)-NORM.DIST(lower