This is the third post in a series about your resident census.
In this series, we have covered why a resident census is an essential tool. We also covered what goes into most resident census reports.
In this post, we’ll talk about how to create a simple resident census system, keep up with it, and use it to manage better.
Software
A resident census can come from a software program or a do-it-yourself spreadsheet.
Yardi is one of the more popular software programs for assisted living. With this program, you can track resident census and much, much more. Yardi may be a great value but it can be pricey for many some assisted living owners.
Many assisted living owners use their own spreadsheet system for tracking resident census. And that is the focus of this article: the do-it-yourself resident census system using spreadsheets.
Set-Up
The following list is an overview of the basic steps to set up a resident census system using a spreadsheet.
Setting Up The First Month
- Start a new blank spreadsheet. In this example, I’m using Google Sheets but you can use Excel or other spreadsheet programs. You can customize your census system to include anything you like but I’d suggest starting with these column headings: Unit, Resident Name, Date of Admission, Date of Discharge, Payor Source, and Monthly Rate. For more discussion on report data, go back to the previous article here.
- Each unit and each resident will go on rows under the column headings. Yes, I would enter a unit number even if that unit is currently vacant. It’s important to see both vacant and occupied units when using a resident census report.
- At the bottom of the monthly rate column, enter a forumla to sum the monthly rates of your residents. (Questions about Excel or Sheets formulas? Google the question for quick answers.)
- At the bottom of the date of admission column, enter a formula to count the number of rows with an admission date. This should give you the number of current residents.
When you’re done setting up your first resident census spreadsheet, it may look something like this.
For the most basic resident census, that’s it. That’s all you need to track the occupancy and monthly revenue for your facility.
The resident census can be much more complicated and involved. In this article, we’re sticking to the basics but you might want to add other columns to track other data.
Setting Up a Summary Worksheet
Your spreadsheet can have several worksheets. As you’ll read below, you can copy one month to a new worksheet to track the census for the next month. You can also set up a worksheet as a Summary of key indicators you track month over month and year over year.
When you have multiple worksheets in a spreadsheet, you will have tabs at the bottom of the spreadsheet that you click to move from one to the next. In the illustration below, you’ll see tabs for the Nov 2021 census and the Summary.
When you click on the Summary tab, you’ll go to a sheet you may set up to look like this.
At the end of each month, you’ll update the Summary sheet with your key indicators.
Workflow and Schedule
In the section above, we talked about how to set up the spreadsheet for your first resident census. You should only need to do that one time because you will make a copy from one month to the next in the spreadsheet.
In this section, we will cover the steps you take to keep your resident census up to date and put it to good use.
Start of Month
At the start of each month, make a copy of the worksheet for the current month and paste it into a new tab in the spreadsheet. If you haven’t done that before, a quick Google search will give you directions step by step.
Change the name of each tab to the month of your new report, such as ‘Nov 2021’. This will provide a historical record of your resident census for your analysis or a future lender or buyer.
Update the heading in the new month’s resident census with the current month and anything else you track each month.
Weekly Updates
Each of us works with a different style. I like to set a weekly reminder in my calendar for things like paying bills, payroll, or updating the resident census. You may choose to keep up with the census each day but a weekly update should help you stay on track.
As you have admissions, update your spreadsheet with the information for new residents. Likewise, update the spreadsheet with the discharge date for any resident discharged.
So how do you handle a discharge from and admission to the same resident unit mid-month? There are a few ways to handle it.
If you want a census that is a snapshot of a single day, update the unit row with the information for the new resident. This approach will lead to an accurate report of your census as of a certain date (usually the last day of the month). But you may want to have a better look at your census and revenue over the full month.
If you want a census that shows all residents during the month, you can insert a row and show the same unit twice. Add the new resident, their admission date, and other information in the inserted row beneath the row for the resident who was discharged. The report may need some massaging or explanation for an accurate occupancy rate or total revenue but it will show how you did over the full month and not just the last day of the month.
Weekly updates might include other things, such as changes in resident rates. Include a Comments column to add notes when changes happen.
End of Month
As you wrap up your month, you might want to take a few extra steps. These steps will probably be done at the same time that you close your financial books for the month.
Update the Summary Sheet. We covered setting up your Summary sheet earlier. It’s easy to transfer a few numbers from the monthly report to the summary each month. Over time, the Summary sheet will be very useful in spotting trends. It may give you a warning of potential trouble or it may give you encouragement that your work is paying off! The Summary sheet will be especially useful when you need to show others how your assisted living facility is performing, and we all need to do that from time to time.
Match census to financials. You can add a column to your spreadsheet to track just about anything. For smaller facilities, it’s simple to take information from your accounting system and enter the amount you billed each resident in the census. Why bother with this? Because your scheduled rents listed in the census might not always be the same as the amounts you billed. This can happen a) when a resident is admitted or discharged in the middle of the month, b) when there is a discount or an extra charge, or c) many other reasons why the census and income statement might not match. Later, when you need to show detailed support for your financials to a lender or a buyer, you will have the reports reconciled and explain any differences. Trust me – this can be the difference between making a sale or losing one.
I hope this Resident Census series has been helpful. If you’d like to learn more, please leave a comment or submit your question at SeniorCareMike.com.