# Staffing Assigment

shimere123StaffingModuleExcelAssignment_1_Student_Answer_Sheet_125.xlsx

## 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