ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Allocate ventilators and respiratory therapists across hospitals
2
John F. Raffensperger, Marygail Brauner, and R. J. Briggs
3
Based on "Planning Hospital Needs for Ventilators and Respiratory Therapists in the COVID-19 Crisis," April 2020, RAND Corp.
4
We expect the user knows how to use Microsoft Excel, and uses their own judgement and experience on the input values.
5
6
References
7
AHRQ Publication No. 16(17)-0018-1-EF, Johns Hopkins Medicine/Armstrong Institute for Patient Safety and Quality,“Mechanically Ventilated Patients: Final Report”, January 2017 
8
CSRC, “California Society for Respiratory Care Safe Staffing Standards Position Statement and White Paper,” California Society for Respiratory Care, October 2016, calculated from page 18.
9
Xiang Li, Yue Hu, Shunye Zhu, Yueping Li, Ling Huang, Yubin Li, Lingli Long, “Epidemiological Feature and Outcome of 292 Hospitalized Patients with COVID-19 Under Adequate Medical Resource Condition,” The Lancet, March 13, 2020. https://papers.ssrn.com/sol3/papers.cfm?abstract_id=3550016
10
Except for these references, data in this spreadsheet is hypothetical.
11
12
Target wait time for a new patient to get on a ventilator, hours7.0KEY
13
14
Hospital 1
Hospital 2
Hospital 3
Hospital 4
15
Patients
16
Average length of stay on a ventilator, each non-COVID-19 patient (AHRQ 2017), days/patient4.54.54.54.5
17
Arrivals per day, new non-COVID-19 patients on ventilators (Xiang Li 2020), patients/day2.02.05.02.0
18
Average census of non-COVID-19 patients9.09.022.59.0=E16*E17
19
20
Average length of stay (days) on a ventilator, each COVID-19 patient, days/patient11.011.011.011.0
21
Arrivals per day, new COVID-19 patients on ventilators, patients/day5.07.012.01.0
22
Average census of COVID-19 patients55.077.0132.011.0=E20*E21
23
24
Average length of stay on ventilator, average patient, days/patient9.19.69.16.7
=(E16*E17+E20*E21)/(E17+E21)
25
Arrivals per day, all new patients on ventilators, patients/day7.09.017.03.0=E17+E21
26
Average census, all patients64.086.0154.520.0=E24*E25
27
28
Ventilators
29
Number of ventilators currently on hand9010016018
30
Erlang C formula, probability of wait, same as % time all servers are busy0%9%56%100%
=MIN(1,POISSON.DIST(E29,E26,FALSE)/(POISSON.DIST(E29,E26,FALSE)+(1-E26/E29)*POISSON.DIST(E29-1,E26,TRUE)))
31
Expected wait for a new patient to get a ventilator, hours0.321.7121.6160.0
=E24*E30*24
32
33
Target probability of wait, given target wait time3.2%3.1%3.2%4.4%
=$B$12/E24/24
34
Number of ventilators needed to reach target maximum wait time8110517929
=MATCH($B$12/E24/24,INDEX('Erlang lookup'!$C$5:$OM$405,E26+1,0),-1)
35
Probability of wait, given target number of ventilators2.6%3.0%3.4%4.0%
=MIN(1,POISSON.DIST(E34,E26,FALSE)/(POISSON.DIST(E34,E26,FALSE)+(1-E26/E34)*POISSON.DIST(E34-1,E26,TRUE)))
36
Additional ventilators needed (or surplus) (9) 5 19 11 =E34-E29
37
With the target number of ventilators, expected wait for a new patient to get a ventilator, hours5.86.97.46.4
=24*E24*E35
38
39
Respiratory therapists
40
Number of ventilators per respiratory therapist (CSRC 2016)4.94.94.94.9
41
Number of respiratory therapists currently available10203011
42
Respiratory therapists needed to support this number of patients on ventilators1722376=E34/E40
43
Additional respiratory therapists needed (or surplus) 7 2 7 (5)=E42-E41
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
94
95
96
97
98
99
100