Please help with the Excel Spreadsheet with the information below

Using the information in the scenario below, create an FTE budget for a one-year period, in Excel.

Your column and row headers should look like this example.

You are the manager of a nursing unit in a nursing home. It is budget season. Your organization has moved to zero-based budgeting, meaning you are not editing an existing budget. You are starting with a blank worksheet. The information below is what you know about your department and will have to be taken into account when creating your labor budget.

• The unit is 30 beds. A full-time equivalent (FTE) position is 40 hours and part-time is a minimum of 16 hours and a maximum of 29. Why 29 hours? Because the Affordable Care Act sets 30+ hours as full-time. Shifts are the traditional 7-3, 3-11, and 11-7 times.
• There is one manager and one assistant manager that work daylight. They are not replaced when they are off.
• A registered nurse (RN) is required for each shift.
• One certified nurse aide (CNA) is required for every 10 patients on each shift. One Licensed Practical Nurse (LPN) is required for every 15 patients on each shift.
• One unit clerk is assigned to the day shift Monday-Friday.
• Payroll taxes and employee benefits (PTEB) is assumed to be 30% of all wages. This includes employee health benefits, paid-time-off (PTO), Medicare, and Social Security taxes.
• Shift differential for 3-11 is $0.60 per hour and $1.00 per hour for 11-7.
• PTO coverage is factored at 10% for those positions requiring coverage (full time only). Direct care and administrative support staff earn PTO that must be covered.

Wage Rates For Each Position

Manager $43.2692

Assistant Manager $38.4615

RN $32.6923

LPN $20.1923

CNA $14.4230

UC $14.4230