Keeping 186 Properties From Becoming a Spreadsheet Problem

Managing more than 180 properties in Excel

Managing one property means keeping track of a surprising number of things.

Heating and air conditioning. Fire alarms and extinguishers. Water heaters. Generators. Elevators. Septic systems. Landscaping. Snow removal. Inspections. Vendors. Utilities.

Now imagine doing that for more than 180 properties.

That was the situation for the facilities team at a large nonprofit human services organization in Massachusetts. The organization operates residential properties where individuals receiving services live and receive care.

Each property is a little different. The equipment may be different. The vendors may be different. Even the service schedules can vary. One item might need attention every year while another is on a three-year schedule.

For years, the team managed all of this in Excel.

And, to be fair, it was a pretty impressive spreadsheet.

There were separate worksheets for the different things the team needed to track. Property information was repeated across those worksheets so inspections, equipment, vendors and services could all be associated with the right house. Formulas were used to identify overdue items and roll that information up into a summary.

Over time, the workbook became harder to maintain.

Some of the formulas weren’t working reliably anymore. Adding or changing a property could create problems elsewhere in the workbook. Information that should have appeared on the summary didn’t always make it there.

Even answering a fairly simple question could involve more work than it should.

If someone wanted to know which water heaters were overdue for inspection, for example, they might have to work through more than 180 rows and count them manually.

That’s not really an Excel problem.

It’s a scale problem.

Making the information easier to manage

ThreeWill worked with the organization to move the property information into a solution built on Microsoft 365.

The team can now maintain information about each property in one place, including equipment, inspections, maintenance dates, vendors, utilities and service providers.

Adding a property is simpler, too.

Before, information such as the property address might need to be entered again on multiple worksheet tabs. Now the property is entered once and that basic information can be used throughout the related parts of the system.

The team can track things like fire alarms, generators, heating systems, septic systems and water heaters along with the inspection or maintenance schedules associated with them. Vendor and service-provider information can also be tied back to the property it belongs to.

Because the system is built on SharePoint, changes to the underlying information are also available through SharePoint version history. That gives the team a way to look back at previous versions when needed without building a separate history-tracking process into every part of the application.

Starting with what needs attention

The dashboard is where the difference is probably easiest to see.

Before, figuring out what needed attention meant reviewing the worksheets and relying on the workbook’s summary formulas.

Now the team can start with the dashboard.

If three water heaters are overdue, they can see that there are three and identify the properties involved. They can also see what is coming due and what is current without reviewing every property individually.

That is particularly useful because the schedules aren’t all the same. Different equipment and services can have different inspection or maintenance intervals, so keeping up with the dates gets more complicated as the number of properties grows.

The team still has to do the work. The dashboard just gives them a much quicker way to see where that work is needed.

More than inspection dates

The original spreadsheet contained much more than a list of inspections.

The team needs to know which vendor services a particular property. They may need specifications for a generator or another piece of equipment. They need utility and contact information. They need to know who handles landscaping, plowing and sanding.

And the answer can be different at every house.

That information now has a central place to live.

The system can also calculate information across the portfolio. One example is total square footage, which the organization needs to report and which is now calculated automatically.

There is also the potential to make the information easier to share with other employees.

With the old workbook, access had to be tightly controlled because someone editing the wrong cell or formula could affect the file for everyone. The new approach can allow people to view the information they need without necessarily giving them permission to change it.

Being used every day

One of the people using the system is Katie, an Administrative Assistant who supports Facilities Operations.

She helps keep the property information organized and coordinates service activity with the homes. Since the system went live, she has been using it daily to update information and add new items as they come in.

She is also looking at adding properties from other affiliates, along with making the normal changes that come with maintaining a portfolio over time.

Her reaction to the dashboard was pretty straightforward:

“I love the Dashboard!”

That daily use is probably the clearest indication of what has changed.

The team still has more than 180 properties to manage, and all of the equipment, vendors, inspections and maintenance schedules that come with them.

They just have a much easier way to keep track of it all.