this is not required. Case Write-up 1: COVID Screening Due: 10:00 p.m. Eastern Time the day...

Transcribed Image Text:

this is not required. Case Write-up 1: COVID Screening Due: 10:00 p.m. Eastern Time the day the before the Week 2 Live Session Goal: Understanding how to model complex relationships in Excel is an important skill for any model-building exercise. This assignment gives you the opportunity to practice transforming a qualitative description of a business scenario into a quantitative model that can be used to assess the impact of decisions on key performance measures. Instructions: Turn in a one-page report that summarizes the conclusions you have reached based on the analysis you have conducted with your model. Your report should include answers to the study questions below. Please also turn in your final Excel model. Please organize the Excel model so that a third party could clearly locate data, calculations, and any relevant supporting analysis. You have been tasked with developing a COVID response plan for the Piedmont University health system. The university has developed a COVID screening protocol that requires all students and all employees (faculty/staff) to be tested for COVID on a regular basis. Specifically, anyone who is symptomatic will be tested immediately, asymptomatic students will be tested every other week, and asymptomatic faculty/staff will be tested every fourth week. Piedmont University has 31,000 students and 15,000 faculty/staff. In any given week, the university expects 15% of the students and 5% of the faculty/staff to be symptomatic. For this exercise, you can assume that the testing of symptomatic individuals occurs in addition to the testing that would regularly occur for asymptomatic individuals (i.e., you don't have to remove the number of symptomatic individuals when determining how many tests of asymptomatic individuals will occur in any given week). The results of a COVID test can fall into one of three categories: positive, negative, or inconclusive. Symptomatic individuals have a 40% chance of a positive test, 40% chance of a negative test, and 20% chance of an inconclusive test. Asymptomatic individuals have a 5% chance of a positive test, 75% chance of a negative test, and a 20% chance of an inconclusive test. Any patient who receives an inconclusive result will immediately be retested, and the second test will have a 10% chance of being positive and a 90% chance of being negative. The demands on the health-care system will depend on the results of testing. Anyone who tests negative will obviously not need any further care. Of those who test positive, 2.5% are expected to require hospitalization. Furthermore, 10% of the hospitalized COVID patients will need a ventilator. Hospitalized COVID patients who don't need a ventilator will remain in the hospital for two weeks on average, whereas those patients requiring a ventilator will have an average hospital stay of three weeks. Study Questions 1. How many COVID tests can you expect to administer in any given week? 2. How many hospital beds do you expect to be occupied by COVID patients at any given time? 3. What additional features would you like to add to your model to make it more realistic/useful? How would you expect your model (and the parameters used in it) to evolve over time? this is not required. Case Write-up 1: COVID Screening Due: 10:00 p.m. Eastern Time the day the before the Week 2 Live Session Goal: Understanding how to model complex relationships in Excel is an important skill for any model-building exercise. This assignment gives you the opportunity to practice transforming a qualitative description of a business scenario into a quantitative model that can be used to assess the impact of decisions on key performance measures. Instructions: Turn in a one-page report that summarizes the conclusions you have reached based on the analysis you have conducted with your model. Your report should include answers to the study questions below. Please also turn in your final Excel model. Please organize the Excel model so that a third party could clearly locate data, calculations, and any relevant supporting analysis. You have been tasked with developing a COVID response plan for the Piedmont University health system. The university has developed a COVID screening protocol that requires all students and all employees (faculty/staff) to be tested for COVID on a regular basis. Specifically, anyone who is symptomatic will be tested immediately, asymptomatic students will be tested every other week, and asymptomatic faculty/staff will be tested every fourth week. Piedmont University has 31,000 students and 15,000 faculty/staff. In any given week, the university expects 15% of the students and 5% of the faculty/staff to be symptomatic. For this exercise, you can assume that the testing of symptomatic individuals occurs in addition to the testing that would regularly occur for asymptomatic individuals (i.e., you don't have to remove the number of symptomatic individuals when determining how many tests of asymptomatic individuals will occur in any given week). The results of a COVID test can fall into one of three categories: positive, negative, or inconclusive. Symptomatic individuals have a 40% chance of a positive test, 40% chance of a negative test, and 20% chance of an inconclusive test. Asymptomatic individuals have a 5% chance of a positive test, 75% chance of a negative test, and a 20% chance of an inconclusive test. Any patient who receives an inconclusive result will immediately be retested, and the second test will have a 10% chance of being positive and a 90% chance of being negative. The demands on the health-care system will depend on the results of testing. Anyone who tests negative will obviously not need any further care. Of those who test positive, 2.5% are expected to require hospitalization. Furthermore, 10% of the hospitalized COVID patients will need a ventilator. Hospitalized COVID patients who don't need a ventilator will remain in the hospital for two weeks on average, whereas those patients requiring a ventilator will have an average hospital stay of three weeks. Study Questions 1. How many COVID tests can you expect to administer in any given week? 2. How many hospital beds do you expect to be occupied by COVID patients at any given time? 3. What additional features would you like to add to your model to make it more realistic/useful? How would you expect your model (and the parameters used in it) to evolve over time?