Home Care Agency Economics: Client Hours, Caregiver Scheduling, Travel Time, Pay vs Bill Rates & Overtime

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.

• From one week of visits you apply a minimum billable visit length with MAX, count visits and scheduled hours per weekday with COUNTIFS and SUMIFS, and total the hours the agency can bill.
• Load the caregiver pay rate with employer on-costs, find the margin per hour and margin % of every service, price weekend shifts with separate bill and pay uplifts, and work out the bill rate a target margin requires.
• Compare the client hours demanded each day with the caregiver hours available, flag gaps and tight days with IFS, size the team per day with a buffer, and add up only the truly uncovered hours.
• For one caregiver's day you convert visit times into hours, price the mileage, measure utilisation (billable ÷ paid hours) and find what travel adds to every billable hour.
• Find each caregiver's overtime hours above a fictional weekly threshold, price the premium those hours cost, total the week's gross pay and highlight the rota lines that should be rebalanced.
• Give every invoice its due date from the payer's terms, total what is still open and what is already overdue, and calculate how many days an average unit of billing takes to come in.
• From a year of headcount, hires and leavers you calculate the turnover rate, the full cost of one hire, the unbilled onboarding cost, and what turnover costs the agency in a year.
• Roll a month of client revenue and direct care cost up into gross margin and margin % per service with SUMIFS, find the client hours that cover the overhead and chart the margin by service.