Building an Asset Management and Compliance System with the City of San Jose

In the fall of 2018, the City of San Jose selected our proposed solution for a challenge they submitted to ‘Startup in Residence’ (also known as STIR). STIR is organized by City Innovate and is a challenge-based procurement process that makes it easier for startups to connect with government.

The following details the problems the City of San Jose dealt with and the technology we’ve built to help.

Background

Our challenge was submitted by the City’s housing department, and is more specifically associated with the asset management team within that department. In order to understand the problems this team faced, we need to provide some context for some of the affordable housing processes in San Jose.

Like many cities, San Jose provides loans to developers to help them build more housing. In exchange, the developer is required to designate a certain number of the units within the property as ‘affordable’. These affordable units are rented at a price point that is considered affordable to different levels of median family income (or MFI levels). The number of units that need to be set aside for each MFI level is outlined in a contract associated with the loan. This means that each property has different requirements for the number of affordable units.

In addition to setting aside affordable units in their property, the developers are also required to submit a ‘rent roll’ to the city on a yearly basis. The rent roll contains information about all of the affordable units in the property along with basic demographic information of the tenants that occupy them. The main purpose of these rent rolls is so that the City can ensure the properties are providing the number of affordable units required by their contract (aka compliance). However, the data submitted within these rent rolls is also incredibly useful for the housing department when it comes to understanding the supply and use of the affordable housing in the city.

The Problem

Before we came into the picture, rent rolls were submitted and tracked using fairly complex Excel spreadsheets. They had multiple tabs filled with information — some were needed for calculation purposes, some were only relevant to the City, and some were meant for the developers to fill out.

Every year, a property had to download the most up to date Excel template for the rent roll from the City’s website, fill it out, then submit it via e-mail.

On the City’s side, three asset management specialists received the spreadsheets, saved them to a shared drive, manually verified that they were filled out correctly and that the property was in compliance, and then manually entered some of the compliance information into Salesforce (where much of the housing-related data is stored). This was a very manual process that took weeks of time and was prone to errors.

These are just some of the issues we discovered while learning about the process:

• If the rent roll was incorrect, the asset management specialists had to deal with multiple versions of the file as the developer e-mailed in updated copies.
• Since compliance requirements differ for each property, the city relied on the developers to enter the information in the spreadsheet. Once the rent roll was submitted, the specialists had to double-check this information against what they had stored in Salesforce.
• Verifying compliance involves checking multiple pieces of information against each other. This process can be difficult, confusing, and is prone to error when done manually.
• Data that doesn’t change from year to year (such as unit numbers) had to be re-entered each year on the new spreadsheet template.

Aside from the difficulty of verifying compliance, one of the biggest issues the City had with the spreadsheet process was the amount of work needed to gather useful data from these documents.

If city council asked for a piece of simple information such as “How many 1 bedroom units are designated as affordable in San Jose?”, it would take days if not weeks for the asset management specialists to come up with that number. Why? Because they had to open each of the hundreds of rent roll spreadsheets individually, get the number, record it somewhere, and then aggregate the numbers. Getting data for multiple years was completely out of the question with this setup.

We concluded that a technological solution could not only help streamline and shorten the compliance process, but also drastically improve the access to quality affordable housing data.

Our Solution

Working with the team at San Jose, we built the following Asset Management and Compliance portal. This application will be used by both the developers submitting the rent rolls and the asset management specialists verifying them.

Submitting a rent roll

Each year, a developer will log in to the system to begin the rent roll process. This is what their experience looks like.

Upon login, the developer will see a list of their properties, along with their current compliance status for this year.

When the developer starts the rent roll process, a wizard walks them through all the information needed for the rent roll, reducing the number of questions received by the asset managers.

Upon submitting the rent roll, the system automatically checks for errors. Data with issues are highlighted and an error message helps further explain the problem. If there are errors that must be corrected, the developers are expected to fix these errors before the asset managers get involved.

Verifying compliance

On the city’s side, asset management specialists also have a dashboard view of all the properties they manage. This allows them to quickly and easily see which properties they need to spend time with.

The details of each property can be viewed, along with the compliance requirements for that property. In this system, the compliance requirements are imported from the city’s Salesforce data and used to verify the rent rolls when they are submitted.

Compliance requirements are highly configurable for each property. This means the system can continue to be used even if the city moves to new compliance restrictions in the future.

Once a rent roll is updated, City employees can see the same view of the rent roll and as the developers (including any errors). Once the rent rolls are completed and approved, data from this application is uploaded into Salesforce to keep everything in sync.

The reference data pages contain data and requirements that are used for compliance across all properties and changes on a yearly basis. This data is updated each year and a history of previous data is saved for reference.

The utility schedule is an example of reference data.

Reporting

A reporting interface gives the city a whole new way to get data about their affordable housing and see how it changes over time. The reporting homepage shows a snapshot of the portfolio data. From here, the city can either create a new report or load a saved one.

There is a lot of flexibility when creating a report, including the ability to select the type of visualization, which the fields and format to use, and the time frame. Sample reports based off common questions help teach users how they can build their own reports.

If a report is used frequently, it can be saved for quick and easy access in the future.

Line graph visualizations can help the city understand how the data changes over time.

Conclusion

This project is currently in beta testing by the city’s asset management specialists, with plans for a full release in Spring 2020. So far, it is showing promise that it will not only streamline and accelerate the yearly compliance process, but serve as a useful tool for gathering and analyzing data.