View file | ||
---|---|---|
|
Background
The original sample Excel workbook provided by Bonnie included 12 sheets that require data entry.
...
The report data is organized in three colored sections besides the facility information:
Detail each section
...
Index
...
Table
...
Session
...
Field Name
...
Excel position or Column
...
Field Type
int/ float / string
...
Calculated Field
(specify formula/calculation)
...
Dependencies (Internal Sources)
...
Notes
...
1
...
Facility Information
...
Facility Information
...
Facility Name
...
2
...
Facility Information
...
Facility Information
...
Address
...
3
...
Facility Information
...
Facility Information
...
City
...
4
...
Facility Information
...
Facility Information
...
Postal Code
...
5
...
Facility Information
...
Facility Information
...
Telephone
...
6
...
Facility Information
...
Facility Information
...
FAX
...
7
...
Facility Information
...
Facility Information
...
Facility's website Address
...
8
...
Facility Information
...
Facility Information
...
Site ID #
...
9
...
Facility Information
...
Facility Information
...
Program Type
...
10
...
Facility Information
...
Facility Information
...
Ownership Type
...
11
...
Facility Information
...
Facility Information
...
Legislation
...
12
...
Facility Information
...
Facility Information
...
Accreditation Body
...
13
...
Facility Information
...
Facility Information
...
Accreditation Date
...
14
...
Facility Information
...
Facility Information
...
Accreditation Expiry Date
...
15
...
Facility Information
...
Facility Information
...
Health Authority
...
16
...
Facility Information
...
Facility Information
...
Local Health Authoriy
...
17
...
Facility Information
...
Owner Information
...
Site ID #
...
18
...
Facility Information
...
Owner Information
...
Program Type
...
19
...
Facility Information
...
Owner Information
...
Ownership Type
...
20
...
Facility Information
...
Owner Information
...
Legislation
...
21
...
Facility Information
...
Owner Information
...
Accreditation Body
...
22
...
Facility Information
...
Owner Information
...
Accreditation Date
...
23
...
Facility Information
...
Owner Information
...
Accreditation Expiry Date
...
24
...
Facility Information
...
Owner Information
...
Health Authority
...
25
...
Facility Information
...
Owner Information
...
Local Health Authority
...
26
...
Facility Information
...
Operator Information
...
Operator Name
...
27
...
Facility Information
...
Operator Information
...
Address
...
28
...
Facility Information
...
Operator Information
...
City
...
29
...
Facility Information
...
Operator Information
...
Postal Code
...
30
...
Facility Information
...
Operator Information
...
Telephone
...
31
...
Facility Information
...
Operator Information
...
FAX
...
32
...
Facility Information
...
Operator Information
...
(Contact) Name
...
33
...
Facility Information
...
Operator Information
...
(Contact) Position
...
34
...
Facility Information
...
Operator Information
...
(Contact) Telephone
...
35
...
Facility Information
...
Operator Information
...
(Contact) E-mail Address
...
36
...
Facility Information
...
Preparer Contact Information
...
Name
...
37
...
Facility Information
...
Preparer Contact Information
...
Position
...
38
...
Facility Information
...
Preparer Contact Information
...
Telephone
...
39
...
Facility Information
...
Preparer Contact Information
...
E-mail Address
...
40
...
Facility Information
...
Preparer Contact Information
...
Fax
...
41
...
Facility Information
...
CFO or VP Finance Contact Information
...
Name
...
42
...
Facility Information
...
CFO or VP Finance Contact Information
...
Position
...
43
...
Facility Information
...
CFO or VP Finance Contact Information
...
Telephone
...
44
...
Facility Information
...
CFO or VP Finance Contact Information
...
E-mail Address
...
45
...
Facility Information
...
CFO or VP Finance Contact Information
...
Fax
...
46
...
Facility Information
...
Auditor's Contact Information
...
Audit Firm
...
47
...
Facility Information
...
Auditor's Contact Information
...
(Contact) Name
...
48
...
Facility Information
...
Auditor's Contact Information
...
(Contact) Position
...
49
...
Facility Information
...
Auditor's Contact Information
...
(Contact) Telephone
...
50
...
Facility Information
...
Auditor's Contact Information
...
(Contact) E-mail Address
...
51
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
In-Scope 3.36 HPRD Publicly Funded Beds
...
52
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
LTC Regular
...
53
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
LTC Temp
...
54
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
Other ~ specify
...
55
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
Subtotal ~ in-scope 3.36 HPRD publicly funded beds
...
56
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
Out of Scope Publicly Funded Beds
...
57
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
Short Stay Beds
...
58
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
respite
...
59
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
end of life
...
60
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
convalescent
...
61
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
flex
...
62
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
other
...
63
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
Subtotal ~ out-of-scope publicly funded beds
...
64
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
Private Beds
...
65
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
Private beds co-located with in-scope publicly funded beds
...
66
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
Private beds located in separate wing/unit
...
67
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
Subtotal ~ private beds
...
68
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / At April 1
...
Total Beds
...
69
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
In-Scope 3.36 HPRD Publicly Funded Beds
...
70
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
LTC Regular
...
71
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
LTC Temp
...
72
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
Other ~ specify
...
73
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
Subtotal ~ in-scope 3.36 HPRD publicly funded beds
...
74
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
Out of Scope Publicly Funded Beds
...
75
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
Short Stay Beds
...
76
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
respite
...
77
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
end of life
...
78
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
convalescent
...
79
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
flex
...
80
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
other
...
81
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
Subtotal ~ out-of-scope publicly funded beds
...
82
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
Private Beds
...
83
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
Private beds co-located with in-scope publicly funded beds
...
84
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
Private beds located in separate wing/unit
...
85
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
Subtotal ~ private beds
...
86
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Adjustment
...
Total Beds
...
87
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
In-Scope 3.36 HPRD Publicly Funded Beds
...
88
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
LTC Regular
...
89
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
LTC Temp
...
90
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
Other ~ specify
...
91
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
Subtotal ~ in-scope 3.36 HPRD publicly funded beds
...
92
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
Out of Scope Publicly Funded Beds
...
93
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
Short Stay Beds
...
94
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
respite
...
95
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
end of life
...
96
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
convalescent
...
97
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
flex
...
98
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
other
...
99
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
Subtotal ~ out-of-scope publicly funded beds
...
100
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
Private Beds
...
101
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
Private beds co-located with in-scope publicly funded beds
...
102
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
Private beds located in separate wing/unit
...
103
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
Subtotal ~ private beds
...
104
...
# of beds & occupancy rate
...
Data Collection ~ Bed Inventory / # of beds in the facility / Budget / Revised
...
Total Beds
...
105
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / Approved Beds
...
In- scope 3.36 HPRD publicly funded beds
...
106
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / Approved Beds
...
Out of scope publicly funded bed
...
107
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / Approved Beds
...
Private Beds
...
108
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / Approved Beds
...
Total beds and YTD occupied days
...
109
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / YTD Maximum Days
...
In- scope 3.36 HPRD publicly funded beds
...
110
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / YTD Maximum Days
...
Out of scope publicly funded bed
...
111
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / YTD Maximum Days
...
Private Beds
...
112
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / YTD Maximum Days
...
Total beds and YTD occupied days
...
113
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / YTD Occupied Days
...
In- scope 3.36 HPRD publicly funded beds
...
114
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / YTD Occupied Days
...
Out of scope publicly funded bed
...
115
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / YTD Occupied Days
...
Private Beds
...
116
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / YTD Occupied Days
...
Total beds and YTD occupied days
...
117
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / % Occupied
...
In- scope 3.36 HPRD publicly funded beds
...
118
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / % Occupied
...
Out of scope publicly funded bed
...
119
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / % Occupied
...
Private Beds
...
120
...
Data Collection - Year-to-Date (YTD) Resident Bed Days
...
Bed Types / % Occupied
...
Total beds and YTD occupied days
...
121
...
VIHA - staffing plan
...
122
...
123
...
124
...
125
...
126
...
127
...
128
...
129
...
130
...
131
...
132
...
133
...
134
...
135
...
136
...
137
...
138
...
139
...
140
...
141
...
142
...
143
...
144
...
145
...
146
...
147
...
148
...
149
...
150
...
151
...
152
...
153
...
154
...
155
...
156
...
157
...
158
...
159
...
160
...
161
...
162
...
163
...
164
...
165
...
166
...
167
...
168
...
169
...
170
...
171
...
172
...
173
...
174
...
175
...
176
...
177
...
178
...
179
...
180
...
181
...
182
...
183
...
184
...
185
...
186
...
187
...
188
...
189
...
190
...
191
...
192
...
193
...
194
...
195
...
196
...
197
...
198
...
199
...
200
...
201
...
202
...
203
...
204
...
205
...
206
...
207
...
208
...
209
...
210
...
211
...
212
...
213
...
214
...
215
...
216
...
217
...
218
...
219
...
220
...
221
...
222
...
223
...
224
...
225
...
226
...
227
...
228
...
229
...
230
...
231
...
232
...
233
...
234
...
235
...
Bonnie’s notes:
Quarterly Reporting
There is a calculator tool on bw - notes sheet. Below is a fixed example:
Reporting Period: | |
Start | 01-Apr |
End | 31-Mar |
# of days | 365 |
YTD | |
Start | 01-Apr |
End | 30-Sep |
# of days | 183 |
Quarter Ending
Q1 | ~ ie, April 1 to June 30 |
Q2 | ~ ie, April 1 ro Sept. 30 |
Q3 | ~ ie, April 1 to Dec. 31 |
Q4 | ~ ie, April 1/x0 to March 31/x1 |
Worked Hours Definition
Worked Hours - hours actually worked by an employee and/or contract service provider (productive time) including:
regular worked hours
statutory holiday hours worked
overtime hours worked
call back hours worked
relief hours worked
"coffee" break
meal break - 30 minute break included where RN is working without professional back-up, ie, "on-call"
Worked hours do not include:
non-paid hours
unworked hours earned, taken or paid for or in-lieu of:
-statutory holidaysannual vacation
sick time
any other purpose
staff development/education/orientation
"premium" (the list below is not intended to be exhaustive), such as payments based on hours for working:
an overtime hour
a named or statutory holiday
shift differential premium for working an evening, night or weekend shift
on-call premium/stand-by pay
Direct Care Worked Hours
Direct Care Worked Hours are a subset of worked hours (as defined above) and include only the hours worked by staff or contracted service providers (including RN, RPN, LPN, Care Aides and Allied Care) in the direct delivery of services to residents.
Direct care consists of one-to-one/first level of care interactions with residents (ie, hands-on patient care) and other clinical related interactions (eg, with family members and physicians) and includes care coordination, assessment, planning and delivery/treatment.
Allied care includes both professional and non-professional direct care providers. Pastoral care and volunteer coordinators are not considered to be direct allied care providers. The criteria to apply to distinguish between what is categorized as professional vs non-professional is whether or not the profession has a regulatory college in BC (ie, those with a regulatory college in BC are categorized as professional).
Examples of allied professional and non-professional direct care providers are as follows:
Allied Care - Professional Care:
occupational therapist
physio therapist
dietician
social worker
speech language therapist
Allied Care - Non-Professional Care:
recreation therapist and recreation co-ordinator (if performing similar function to a recreation therapist)
rehab assistant
activity worker
respiratory therapist
music/art therapist (if engaged in recreation activities with residents as an activity worker would be)
Direct care worked hours do not include hours worked by nurses who are administrators or provide clinical support, volunteers and positions paid for by donations or grants. In addition, tasks performed by care aides such as plating food, setting up the dining room for a meal or cleaning up after a meal, light housekeeping, and other laundry duties are also excluded.
Non-Direct Care Worked Hours - Out of Scope
Non-direct care worked hours consists of support or secondary resident care in which interactions with residents are not primarily focused on the care or care plan for the resident (eg, laundering the linens or preparation, cooking and plating of meals).
Cost of Direct Care Worked Hours - cost for both productive and non-productive direct care hours and include:
wage costs
benefits including:
employer paid benefits
shift differentials and premiums (ie, remote site)
vacation, statutory holidays
sick time
overtime
severance
retiring allowances
other paid leave (ie, orientation, education, etc)
purchased labour cost and meal allowances