10 Things You Need to Know About Spreadsheets

managing construction loans manually is riskier than you think.

If you have a history of construction lending, you most likely started out using a spreadsheet to organize a construction loan or two. It’s not a bad idea, since they’re accessible, easy enough to use, and inexpensive to implement.

When your monthly volume increased—along with your construction loan administration team—you found yourself stuck managing draws between spreadsheet tabs, reconciling missing data, and fixing broken formulas while the stack of paper files piled up and started threatening to make your desk buckle under the weight.

Don’t feel bad.

Spreadsheet applications like Microsoft Excel are a user-friendly option for individuals or small groups with a need for crunching modest datasets. Most operating systems come with a ready-to-use spreadsheet application pre-installed, so it makes sense that they are a popular choice among banks, mortgage lenders, and credit unions.

But spreadsheets are not the right tool for the job. Consider using a butter knife to cut through a piece of lumber. It’s an easy tool to grab from the drawer, and it may work eventually with a lot of effort and wasted energy, but there’s a faster, more efficient way to approach the problem.

To help you break free from managing construction loans manually, we have compiled a list of 10 reasons why spreadsheets are the absolute worst tool for managing your construction loans:

1. Common Mistakes Will Cost You

The familiarity and relative ease of spreadsheets can be misleading. They are not a foolproof tool—especially when you factor in human error.

In fact, a 2008 analysis of multiple studies suggests that 88 percent of spreadsheet documents contain one or more errors.

Over the past several years, several cases of spreadsheet errors that cost financial institutions billions of dollars were made public. One in particular is the JP Morgan Chase $6 billion trading loss—a result of a user copying-and-pasting the wrong data from one spreadsheet into another.

88-percent-construction-loan-spreadsheets-errors

2. Manual Entry is Tedious and Leads to Risky Shortcuts

Let’s be honest. Entering loan information manually is a pain in the tuchus. The natural tendency for anyone, including the best administrators, is to attempt to speed up the process by taking shortcuts. This can result in a lot of small data entry mistakes to occur.

Allow me to explain.

The same 2008 study referenced above found that spreadsheet users have a 1.79 percent probability of making an error per cell. How many cells are in a single loan file? You can imagine how the chance for error is compounded when trying to manage a full pipeline of the highest risk loans in your portfolio: construction loans.

When tracing the JP Morgan Chase debacle further, the error was found to be caused by an analyst who was manually copying and pasting data from one Excel sheet to another.

3. Excel is not a cloud-based database

Though Excel looks like a database, it doesn’t function in the same way and can’t handle large amounts of information as effectively as a cloud-based database. In fact, it tends to slow down drastically when it’s overburdened with data.

Because spreadsheets are not databases, they can’t be updated by more than one person at a time, the data they contain can’t be audited, they don’t allow for a consistent workflow, and they offer little security or control to catch mistakes.

4. Spreadsheets Violate Construction Lending Best Practices

Because it isn’t designed like a database, there’s no ability for users to set draw warnings, key document and permit expiration reminders, along with other important tracking features.

Construction Lending Best Practices call for making sure the budget is always in balance, tracking expiration of permits and key documents, setting draw warnings when documentation is not in place, and never over-dispersing funds relative to the work that has been completed.

5. Managing Something You Can’t Track is Impossible.

It’s almost impossible to manage something that you cannot track. With spreadsheets information is usually scattered across multiple files and folders. There is no easy way to get a complete view of your loan pipeline - let alone the task in progress and/or their status.

A spreadsheet does not offer automated tracking features to ensure work is being completed in a timely manner. This can delay decision making and increase time to disburse funds.

6. Spreadsheets Increase Fixed Costs

On average, construction administrators can manage 35 loans at any given time. Pushing your admins to go above that can put an undue strain on their productivity. The only way to scale your production is to hire more admins.

All expenses in this case stay in house, because there is no way to outsource any part of the process on a spreadsheet!

On the other hand, cloud-based construction loan software can empower your administrators to manage 350% more loans than manual applications.

 

7. File Sharing ≠ Secure Collaboration

Even with Google Drive and Microsoft Sharepoint, sharing spreadsheets is limited to teammates—and vulnerable to risk when sharing files outside your organization.

File sharing is not true collaboration made across construction loan administration teams. Without an ability to track changes in Excel, loan admins may accidentally insert duplicate or incorrect loan file information without other users knowing. This can lead to team members having alternate, conflicting versions of the same spreadsheet.

Multiple iterations of spreadsheet files and linked spreadsheets practically ensure somebody will be referencing the wrong data. An inaccurately linked spreadsheet at a Tennessee government organization brought on a $6 million loss and a $12,500 audit charge.

8. Turn on the Fog Lights, Transparency is Limited.

Since spreadsheets do not offer much in the way of collaboration, it makes it much harder to control access permissions; leaving your borrower and builder with a foggy user experience.

Granting your warehouse bank access to your loan pipeline or allowing your builder to upload change orders and draw requests in real-time is virtually impossible with a spreadsheet.

Many of our warehouse partners refuse to offer lines of credit to lenders utilizing spreadsheets because they lack transparency and do not follow construction lending best practices.

9. Compliance Constraints

Government regulations require all loan documents be stored safely with secure file backups to guard against information loss. More and more investors and warehouse banks, as well, are requiring secure docs that are tracked regularly and easily accessible.

Since spreadsheets do not offer built-in compliance tools to maintain a clear audit trail, they will put you in an awkward position when the compliance officer comes to visit and a competitive disadvantage when requesting lines of credit.

10. Reporting is Vital.

How easy is it to pull a report on a single loan file let alone a pipeline of loans being managed with a spreadsheet? Not easy, right?

That’s because Excel is a spreadsheet tool not a reporting tool. That means you have to spend time manually gathering and verifying input data, manually building formulas, charts and graphs, and finally manually distributing the final report to your team.

Depending on the complexity of your reports, they could take 1-2 days or months to implement. Either way you slice it, creating manual reports takes a ton of effort and time that you could be spending on building your construction program.

What’s the alternative to spreadsheets?

With all the complexities involved with construction lending, you need an out-of-the-box solution like the Construction Loan Manager that will improve your risk management, lower your fixed expenses while increasing your team’s productivity with a configurable workflow.

Did we Miss Anything?

Let us know the biggest hassle you’ve encountered with spreadsheets in the comments below.