Versions Compared

Old Version 40

changes.mady.by.user Wagner Montes

Saved on

compared with

New Version Current

changes.mady.by.user Wagner Montes

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Wagner Montes

Introduction

View file
name3.36 HPRD sample for CGI ~ Operator info removed - Only colored tables.xlsx

General Notes:

  • When bonnie reviews, she looks at HPRD, if there are variances, does there explanation make sense.

The original sample Excel workbook provided by Bonnie included five (5) sheets:

  • Notes

  • Q3 ~ reporting

  • VIHA - 3.36

  • 21-22 investment

  • VIHA - 3.36 investment

The copy available above contains only the Q3 ~ reporting, which is the 3.36 HPRD report received from the HA and had all provider identity information removed.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

...

  • A. HA Owned & Operated - (HA)

  • B. Private - for Profit - (PFP)

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

There are remaining tables excluded from this sample attached that are created by Bonnie, including calculations based on the facility related fields to generate her own reports (to be discussed).

Below there is a list of notable fields included in the three colored tables present in Q3 ~ reporting sheet:

  • - (PNP)

Float (rounded to .00

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 it’s will not reflected on BH (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

/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 HPRDthan 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)= 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

51

Variance - forecast vs funded budget

DCH ($)

BC

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

=+AV11-AO11

52

Variance - forecast vs funded budget

% od forecast to bgt for DCH

BD

Percentage

Percentage

=+AU11/AN11

53

Variance - forecast vs funded budget

HPRD ~ based on bed days

BE

Float (rounded to .00)
including negative values represented by (x)

=+AW11-AP11

54

Variance - forecast vs funded budget

Variance Explanation for differences with # beds, bed days, DCH, cost and HPRD

BF

No Reference
Probably string

No

55

YTD for the period April 1 to December 31, 2021

# Beds

BH

Integer

=+AL11

  • Each HA forecasts differently. Might be worth asking the HAs about what their reasoning behind their calculations is and if it would be useful to standardize it. The value in standardizing it could be in having a pre-populated field.

YTD for the period April 1 to December 31, 2021

YTD Planned Bed Days at 100% occupancy

BI

Integer

=F11*275

  • Multiplication value depends on how many days passed
    Total Year to Date

YTD for the period April 1 to December 31, 2021 / ii

YTD Actual Bed Days at 100% occupancy

BJ

Integer

=BI11

YTD for the period April 1 to December 31, 2021

Variance - YTD Actual vs Planned Bed Days

actual over or (under) plan

BK

Integer
including negative values represented by (x)

=+BJ11-BI11

YTD for the period April 1 to December 31, 2021

YTD Planned Direct Care Hours

BL

Float (Rounded)

=AN11/365*275

YTD for the period April 1 to December 31, 2021 / i

YTD Actual Direct Care Hours

BM

Float (Rounded)

No

  • Value comes from the Quarterly Report (collected from Payroll Report)

YTD for the period April 1 to December 31, 2021

Variance - Actual vs Plan Direct Care Hours

actual over or (under) plan

BN

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

=+BM11-BL11

YTD for the period April 1 to December 31, 2021

Planned YTD HPRD

BO

Float (rounded to .00)

=+BL11/BI11

YTD for the period April 1 to December 31, 2021 / i/ii

Actual YTD HPRD

BP

Float (rounded to .00)

=BM11/BJ11

YTD for the period April 1 to December 31, 2021

Variance explanation for bed days and DCH

BQ

String

No

YTD for the period April 1 to December 31, 2021 / iii

YTD Actual Resident Day, ie, based on actual bed occupancy rate

BS

Integer

No

  • Quarterly Report

YTD for the period April 1 to December 31, 2021 / i/iii

YTD actual HPRD = actual DCH divided by actual resident day, ie, based on actual bed occupancy rate

BT

Float (rounded to .00)

=BM11/BS11

YTD for the period April 1 to December 31, 2021

Variance explanation for occupancy

BU

Probably string

No