Care Home Operations: Occupancy, Staffing Ratios & Cost per Resident Day

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Turn a month of resident days into bed days available, occupancy and average residents per wing, and count the resident days of people who moved in or out during the month.
• Turn weekly fees into daily rates and September fee income, find each group's share of income and compare the average weekly fee of self-funded and publicly funded residents.
• Turn the shift pattern of each wing into care hours per day and per resident, compare them with the home's target and convert the shortfall into extra full-time staff.
• Fill a week's rota gaps with agency shifts, measure how much of the rota your own and bank staff cover, price the agency premium and the saving from moving shifts to the bank.
• Turn September's cost lines into cost per resident day, group them by category with SUMIFS and compare the cost with the fee income per resident day.
• Split costs into fixed and variable, find the occupancy at which Willowmere breaks even, test six occupancy scenarios and chart fee income against total cost.
• Build a six-month forecast that links departures, free beds, the waiting list and admissions, and estimate how long a new referral waits for a place.
• Turn monthly falls and pressure ulcers into rates per 1,000 resident days, compute the six-month rate, flag months for review and highlight them in the grid.