/
3.36 HPRD Mapping

3.36 HPRD Mapping

Owned by Miranda Stewart
Last updated: Aug 09, 2022 by Wagner Montes

@Wagner Montes

 

The report data is organized in three colored sections besides the facility information:

  • Green section:

    • PART 1 - 2021/22 - April 1st, 2021 Budget (Funded)

  • Purple section:

    • 2021/22- Funded Budget - Full Year Summary

    • 2021/22 - Q3 summary ~ forecast to period ending March 31, 2022

    • Variance - forecast vs funded budget

  • Orange section:

    • YTD for the period April 1 to December 31, 2021

In these 3 sections all Fields contents include data and calculations for each facility, identified by columns A and B, and organized in three groups:

  • A. HA Owned & Operated - (HA)

  • B. Private - for Profit - (PFP)

  • C. Private - Not-for-Profit - (PNP)

 

Index

Table

Field Name

Excel position or Column

Field Type

drop-down / text field / radio button / int/ float

Calculated Field

(specify formula/calculation)

Dependencies (Internal Sources)

Notes
(Bonnies’s comments and conclusions)

Index

Table

Field Name

Excel position or Column

Field Type

drop-down / text field / radio button / int/ float

Calculated Field

(specify formula/calculation)

Dependencies (Internal Sources)

Notes
(Bonnies’s comments and conclusions)

1

In-Scope Residential Care Facilities

Facility ID

A

Integer

No

 

 

2

In-Scope Residential Care Facilities

Facility Name

B

String

No

 

 

3

In-Scope Residential Care Facilities

Owner Type

C

String

No

 

 

4

In-Scope Residential Care Facilities

City

D

String

No

 

 

5

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded)

# of Beds

F

Integer

No

 

 

6

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded)

(1) Bed Days (100% occupancy)

G

Integer

=F11*365
Number of Beds x 365

# of Beds (Index 5)

 

7

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A)
Total Funded Direct Care Hours - Nursing & Allied / Nursing

(2) RN

H

Float (Rounded)

No

 

 

8

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A)
Total Funded Direct Care Hours - Nursing & Allied / Nursing

(3) LPN

I

Float (Rounded)

No

 

 

9

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A)
Total Funded Direct Care Hours - Nursing & Allied / Nursing

(4) Care Aide

J

Float (Rounded)

No

 

 

10

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A)
Total Funded Direct Care Hours - Nursing & Allied / Nursing

Subtotal - Nursing

K

Float (Rounded)

=SUM(H11:J11)
RN + LPN + Care Aide

RN (Index 7)
LPN (Index 8)
Care Aide (Index 9)

 

11

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A)
Total Funded Direct Care Hours - Nursing & Allied / Allied

(5) Professional

L

Float (Rounded)

No

 

 

12

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A)
Total Funded Direct Care Hours - Nursing & Allied / Allied

(6) Non-Professional

M

Float (Rounded)

No

 

 

13

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A)
Total Funded Direct Care Hours - Nursing & Allied / Allied

Subtotal - Allied

N

Float (Rounded)

=SUM(L11:M11)
Professional + Non-Professional

(5) Professional (Index 11)
(6) Non-Professional (Index 12)

 

14

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A)
Total Funded Direct Care Hours - Nursing & Allied

Total Funded Direct Care Hrs - Nursing & Allied

O

Float (Rounded)

=+K11+N11
Subtotal - Nursing + Subtotal - Allied

Subtotal - Nursing (Index 10)
Subtotal - Allied (Index 13)

 

15

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (B) Funded Hourly Rate ($) / Nursing

RN

P

Currency

=U11/H11
RN / (2) RN

RN (Index 20)
(2) RN (Index 7)

 

16

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (B) Funded Hourly Rate ($) / Nursing

LPN

Q

Currency

=V11/I11
LPN / (3) LPN

LPN (Index 21)
(3) LPN (Index 8)

 

17

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (B) Funded Hourly Rate ($) / Nursing

Care Aide

R

Currency

=W11/J11
Care Aide / (4) Care Aide

Care Aide (Index 22)
(4) Care Aide (Index 9)

 

18

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (B) Funded Hourly Rate ($) / Allied

Professional

S

Currency

=Y11/L11
Professional / (5) Professional

Professional (Index 24)
(5) Professional (Index 11)

 

19

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (B) Funded Hourly Rate ($) / Allied

Non-Professional

T

Currency

=Z11/M11
Non-Professional / (6) Non-Professional

Non-Professional (Index 25)
(6) Non-Professional (Index 12)

 

20

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A) * (B) = (C)
Total Funded Direct Care Costs - Nursing & Allied ($) / Nursing

RN

U

Currency

No

 

 

21

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A) * (B) = (C)
Total Funded Direct Care Costs - Nursing & Allied ($) / Nursing

LPN

V

Currency

No

 

 

22

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A) * (B) = (C)
Total Funded Direct Care Costs - Nursing & Allied ($) / Nursing

Care Aide

W

Currency

No

 

 

23

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A) * (B) = (C)
Total Funded Direct Care Costs - Nursing & Allied ($) / Nursing

Subtotal - Nursing

X

Currency

=SUM(U11:W11)
RN + LPN + Care Aide

RN (Index 20)
LPN (Index 21)
Care Aide (Index 22)

 

24

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A) * (B) = (C)
Total Funded Direct Care Costs - Nursing & Allied ($) / Allied

Professional

Y

Currency

No

 

 

25

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A) * (B) = (C)
Total Funded Direct Care Costs - Nursing & Allied ($) / Allied

Non-Professional

Z

Currency

No

 

 

26

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A) * (B) = (C)
Total Funded Direct Care Costs - Nursing & Allied ($) / Allied

Subtotal Allied

AA

Currency

=SUM(Y11:Z11)
Professional + Non-Professional

Professional (Index 24)
Non-Professional (Index 25)

 

27

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / (A) * (B) = (C)
Total Funded Direct Care Costs - Nursing & Allied ($)

Total Funded Direct Care Cost - Nursing & Allied

AB

Currency

=+X11+AA11
Subtotal - Nursing + Subtotal Allied

Subtotal - Nursing (Index 23)
Subtotal Allied (Index 26)

 

28

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / Total Funded HPRD / Nursing

(2)/(1) RN

AC

Float

=+H11/G11
(2) RN / (1) Bed Days (100% occupancy)

(2) RN (Index 7)
(1) Bed Days (100% occupancy) (Index 6)

 

29

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / Total Funded HPRD / Nursing

(3)/(1) LPN

AD

Float

=+I11/G11
(3) LPN / (1) Bed Days (100% occupancy)

(3) LPN (Index 8)
(1) Bed Days (100% occupancy) (Index 6)

 

30

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / Total Funded HPRD / Nursing

(4)/(1) Care Aide

AE

Float

=+J11/G11
(4) Care Aide / (1) Bed Days (100% occupancy)

(4) Care Aide (Index 9)
(1) Bed Days (100% occupancy) (Index 6)

 

31

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / Total Funded HPRD / Nursing

Subtotal - Nursing

AF

Float

=SUM(AC11:AE11)

(2)/(1) RN + (3)/(1) LPN + (4)/(1) Care Aide

(2)/(1) RN (Index 28)
(3)/(1) LPN (Index 29)
(4)/(1) Care Aide (Index 30)

 

32

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / Total Funded HPRD / Allied

(5)/(1) Professional

AG

Float

=+L11/G11
(5) Professional / (1) Bed Days (100% occupancy)

(5) Professional (Index 11)
(1) Bed Days (100% occupancy) (Index 6)

 

33

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / Total Funded HPRD / Allied

(6)/(1) Non-Professional

AH

Float

=+M11/G11
(6) Non-Professional / (1) Bed Days (100% occupancy)

(6) Non-Professional (Index 12)
(1) Bed Days (100% occupancy) (Index 6)

 

34

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / Total Funded HPRD / Allied

Subtotal - Allied

AI

Float

=SUM(AG11:AH11)
(5)/(1) Professional + (6)/(1) Non-Professional

(5)/(1) Professional (Index 32)
(6)/(1) Non-Professional (Index 33)

 

35

Part 1 - 2021/22 - April 1 st, 2021 Budget (Funded) / Total Funded HPRD

Total Funded HPRD - Nursing & Allied

AJ

Float

=+AF11+AI11
Subtotal - Nursing + Subtotal - Allied

Subtotal - Nursing (Index 31)
Subtotal - Allied (Index 34)

 

36

2021/22- Funded Budget - Full Year Summary / (A) - from "green" section

# Beds (Publicly Subsidized or Funded)

AL

Integer

=+F11
# of Beds

# of Beds (Index 5)

 

37

2021/22- Funded Budget - Full Year Summary / (A) - from "green" section

Bed Days (100% occupancy)

AM

Float (Rounded)

=+G11
(1) Bed Days (100% occupancy)

(1) Bed Days (100% occupancy) (Index 6)

 

38

2021/22- Funded Budget - Full Year Summary / (A) - from "green" section

Total Funded Direct Care Hours (Nursing & Allied)

AN

Float (Rounded)

=+O11
Total Funded Direct Care Hrs - Nursing & Allied

Total Funded Direct Care Hrs - Nursing & Allied (Index 14)

 

39

2021/22- Funded Budget - Full Year Summary / (A) - from "green" section

Total Funded Cost of Direct Care Hours ($)

AO

Currency

=+AB11
Total Funded Direct Care Cost - Nursing & Allied

Total Funded Direct Care Cost - Nursing & Allied (Index 27)

 

40

2021/22- Funded Budget - Full Year Summary / (A) - from "green" section

Total Funded HPRD

AP

Float (rounded to .00)

=+AJ11
Total Funded HPRD - Nursing & Allied

Total Funded HPRD - Nursing & Allied (Index 35)

  • It’s needed to check if they are greater of smaller than 3.36, 2.9, and other thresholds.

41

2021/22 - Q3 summary ~ forecast to period ending March 31, 2022

# Beds (Publicly Subsidized or Funded)

AR

Integer

No

 

  • Sometimes a bed can be added during the year - The change will be made by the HA and will not be reflected on Number of Beds in the Orange table (BH - Index 55)

42

2021/22 - Q3 summary ~ forecast to period ending March 31, 2022 / B

Bed Days (100% occupancy)

AS

Integer

=+AR*365
# Beds (Publicly Subsidized or Funded) * 365

# Beds (Publicly Subsidized or Funded)

 

43

2021/22 - Q3 summary ~ forecast to period ending March 31, 2022 / C

Resident Days (ie, forecasted actual bed occupancy)

AT

Float (Rounded)

Calculated by HA - may need standardization

To be defined

 

44

2021/22 - Q3 summary ~ forecast to period ending March 31, 2022 / A

Total Actual Direct Care Hours (forecasted Nursing & Allied)

AU

Float (Rounded)

Calculated by HA - may need standardization

To be defined

  • VIHA calculates using quarters values separately while the other HA’s use the YTD value.

    • VIHA uses the last quarter as reference while other HA’s use the whole YTD value to calculate the next quarter

  • Bonnie has no final conclusion about the best methodology

  • It may need standardization

45

2021/22 - Q3 summary ~ forecast to period ending March 31, 2022

Total Funded Cost of Direct Care Hours ($) ~ ie, HA anticipated cost

AV

Currency

Calculated by HA - may need standardization

To be defined

  • Cost divided by number of funded hours and multiplied by total actual direct care hours

  • If the forecasted total is larger than the funds, it may need to be adjusted to the total funds or the cost per hour

46

2021/22 - Q3 summary ~ forecast to period ending March 31, 2022 / A/B

HPRD = forecasted actual DCH divided by bed days at 100% occupancy

AW

Float (rounded to .00)

=+AU11/AS11
Total Actual DCH (Forecasted Nursing & Allied) / Bed Days (100% occupancy)

Total Actual DCH (Forecasted Nursing & Allied) - (Index 44)
Bed Days (100% occupancy) (Index 42)

  • This number is usually higher than Total Funded HPRD

  • It’s needed to check if the value is greater or smaller than 3.36

47

2021/22 - Q3 summary ~ forecast to period ending March 31, 2022 / A/C

HPRD = forecasted actual DCH divided by forecasted resident days

AX

Float (rounded to .00)

=+AU11/AT11
Total Actual DCH (Forecasted Nursing & Allied) / Resident Days (Forecasted)

Total Actual DCH (Forecasted Nursing & Allied) (Index 44)
Resident Days (ie, forecasted actual bed occupancy) (Index 43)

  • It’s needed to check if the value is greater or smaller than 3.36

48

Variance - forecast vs funded budget

# Beds

AZ

Integer - including negative values represented by (x)

=+AR11-AL11

 

 

49

Variance - forecast vs funded budget

Bed Days

BA

Float (Rounded)
including negative values represented by (x)

=+AS11-AM11

 

 

50

Variance - forecast vs funded budget

DCH (Hrs)

BB

Float (Rounded)
including negative values represented by (x)

=+AU11-AN11