Calculate average daily census from patient days

Staffing Assigment

Sheet1
Assignment 1 Available Actual
Student Answer Sheet: Complete each section as indicated. Points Points
When creating formulae, feel free to place "constant" numbers in separate cells (e.g. in the first section, you may wish to put 8, 40, and 80 in H7, H8, H9 to use the cell identifier in your formulae.)
Calculating Hours and Shifts: 10 points
Insert formulae to calculate appropriate hours and shifts
FTEs ONE WEEK HOURS ONE WEEK SHIFTS ONE PAY PERIOD HOURS ONE PAY PERIOD SHIFTS
0.2 8 1 16 2
0.4 2
0.6 2
0.8 2
1 2
2 2
Calculating Worked FTEs: 5 points
Insert formulae to calculate missing shifts and FTEs. Format FTE to 1 decimal
STAFF S M T W T F S SHIFTS FTEs
NM 0 1 1 1 1 1 0 5 1.0
RN 5 6 6 6 6 6 5 1
LVN 4 3 3 3 3 3 4 1
NA 4 4 4 4 4 4 4 1
US 1 1 1 1 1 1 1 1
TOTAL 14 15 15 15 15 15 14 1
Calculating NHPPD: 2.5 points:
Insert formula in C29 to calculate NHPPD; format to 2 decimal places
Unit 3A
# 8 hr. shifts 98
# Pt. Days 61
NHPPD: 2.5
Calculating Caregiver (Variable) Hours: 2.5 points
Insert formula in C38 to calculate caregiver hours; format to 2 decimal places
Unit 3A Caregiver hours
RN Shifts 54
LVN Shifts 14
NA Shifts 13
Pt. Days 61
Caregiver Hours: 2.5
Calculating Fixed Hours: 2.5 points
Insert formula in C47 to calculate fixed hours; format to 2 decimal places
3A: # of shifts for Nurse Manager, Clin. Spec., & Unit Secretaries
NM 5
CNS 5
US 7
Pt. Days 61
Fixed Hours: 2.5
Calculating Non-worked Hours: 5 points
Insert formula in B58 to calculate the sum of non-worked shifts;
Then, insert a formula in B59 to calculate the number of non-worked hours; format with 0 decimals
Number of non-worked 8-hour shifts per year:
Sick leave = 12 shifts per year 12
Vacation 10
Holiday 6
Training 3
Misc. 3
Total Shifts 2.5
Total Non-worked Hours 2.5
Calculating Paid FTEs: 7.5 points
Insert formulae to calculate Paid FTEs using PWR of : 1.18 where appropriate; format for 1 decimal
STAFF S M T W T F S SHIFTS WORKED FTEs PAID FTEs*
NM 0 1 1 1 1 1 0 5 1.0 0 2
RN 5 6 6 6 6 6 5 40 8.0 0 0.75
LVN 4 3 3 3 3 3 4 23 4.6 0 0.75
NA 4 4 4 4 4 4 4 28 5.6 0 0.75
US 1 1 1 1 1 1 1 7 1.4 0 0.75
TOTAL 14 15 15 15 15 15 14 103 20.6 0 2.5
Calculating the number of required positions: 4 points
Insert formulae to calculate the number of required positions (whole numbers)
STAFF S M T W T F S SHIFTS WORKED FTEs PAID FTEs POSITIONS (given: NM, CNS, RN)
NM 0 1 1 1 1 1 0 5 1 1 1
CNS 0 1 1 1 1 1 0 5 1 1 1
RN 2 3 3 3 3 3 2 19 3.8 4.48 4
LVN 3 3 3 3 3 3 3 21 4.2 4.96 1
NA 2 4 4 4 4 4 2 24 4.8 5.66 1
US 3 3 3 3 3 3 3 21 4.2 4.96 1
TOTAL 10 15 15 15 15 15 10 95 19 22.06 1
Putting it all Together
Staffing Pattern: 21 points
Complete the information for the following staffing pattern, by inserting formulae in appropriate cells. Assume:
Hours/shift: 8
PWR: 1.15
Pt. Days: 220 for one-week period
STAFF S M T W T F S SHIFTS WORKED PAID FTEs POSITIONS
FTEs*
NM 0 1 1 1 1 1 0 1.5
CNS 0 1 1 1 1 1 0 1.5
RN 6 6 6 6 6 6 6 3.5
LVN 2 3 3 3 3 3 2 3.5
NA 6 8 8 8 8 8 6 3.5
US 2 2 2 2 2 2 2 3.5
TOTAL 4
Calculation of NHPPD, Variable, and Fixed hours for staffing pattern above: 12 points
Insert formulae in D105, D107, and D109 to:
Calculate NHPPD: 4
Calculate Variable Hours: 4
Calculate Fixed Hours: 4
Determining Paid NHPPD: 12 points
Given the following data, insert formulae in cells I114, I115, and I116
PWR= 1.12
What is the Paid NHPPD with a Worked NHPPD of: 15 4
What are the Paid Caregiver Hours with Worked Caregiver Hours of: 7.25 4
What are the Paid Fixed Hours* with Worked Fixed Hours of: 1.6 4
*These fixed hours are for staff who are replaced
Effect of Acuity: Part A=5 points; Part B=11 points
Insert formulae into cells J125 and J126 to answer the questions below:
Standard Adj. Acuity
Patient Volume 12500 12500
NHPPD 6.62 6.62
Acuity Level (in RVU) 0 3
A. How many FTEs are needed to care for this patient volume (in column labeled Standard)? 5
B. What is the difference between the number of FTE needed for the actual volume, when acuity is taken into consideration? 11
Total Points 100 0
&P

Sheet2
Sheet3