The dangers of spreadsheet managing your CRE debt portfolio

Turning bad data good

Effective management of commercial real estate (CRE) debt portfolio begins with good data management. All stakeholders must have easy access to centralized data for monitoring, reporting, and making informed decisions. The data must be accurate and reliable to avoid irrelevant conclusions and costly mistakes. However, having good data is just the beginning; it is equally important to have the ability to access, analyze, and measure the impact of changes to key assumptions and market conditions in a timely manner.

Though spreadsheets offer powerful customization, they also offer an exponential number of pitfalls as the data and analysis needs increase.

In today's data-driven business environment, the ability to keep up with the data stream and turn it into actionable information is vital. This is where technology comes in, empowering business teams to meet the demands of the modern environment.

For decades, spreadsheets have been the standard and go-to method for collecting and looking at data as they’re easy to use, fast, flexible, and do not require the involvement of IT. Though spreadsheets offer powerful customization, they also offer an exponential number of pitfalls as the data and analysis needs increase.

Spreadsheet vulnerabilities

The problems with spreadsheets start with the simplest form of data entry and grow through attempts at complex analytics processes. The common phrase “garbage in, garbage out” is interpreted to be around data quality - the better the data that goes in, the better the results that come out of it. Problems continue as integration issues present themselves. A lack of integration between source systems and spreadsheet models is typical, thus limiting the end results when paired with larger, more complex software platforms.

Overall, the shortcomings of spreadsheets for debt management can be broken down into four main areas:

1. Accuracy

2. Auditability

3. Efficiency and continuity

4. Visibility and security

Accuracy

CRE organizations update their debt portfolios which requires pulling in dynamic information such as interest rates, property appraisals, and cash flows from other systems and data sources. As more numbers are manually entered into a spreadsheet-based waterfall model, the likelihood of human error resulting in erroneous data getting into the model at the input or calculation level escalates. This is not a result of spreadsheet skills or experience but established statistical probability around unintended mistakes.

The report “Cognitive Science of Spreadsheet Errors: Why Thinking is Bad” studied human error rates for simple tasks. In short, humans are generally quite accurate creatures, but all humans have a baked-in affinity for messing up on a small percentage of tasks. The error rates for commercial spreadsheets on a cell-by-cell basis is between 1 and 6%, meaning for every 100 cells manually worked on, an anticipated 1-6 of those cells will have an error.

For every 100 cells manually worked on in a commercial spreadsheet, an anticipated 1-6 of those cells will have an error.

The risks of making mistakes in spreadsheets exponentially increase with regular use. Spreadsheets are quickly and easily shared both horizontally and vertically across teams, organizations and clients, resulting in multiple copies of the same datasheet and only best guesses about which one, if any, is truly accurate. One of the most commonly used processes in spreadsheet usage, copy and paste, can also be completely debilitating as formulas are not replicated as the user expects.

While the ease of spreadsheets lacks a rigid structure, this same structure often leads to re-keying and mis-keying important data. Sometimes these errors reveal themselves, but a simple decimal point can lead to huge errors, unwanted manual labor, and increased stress.

Using a modern debt management solution that has a centralized database and can integrate with third-party systems can significantly reduce the amount of manual input required with spreadsheets.

This, in turn, mitigates potential inconsistencies by minimizing data entry to a single manual input event.

Auditability

Spreadsheets are very inadequate for audit purposes. When the results of inputted data are unexpected or irrational, people must go back to double-check their entries, formulas, etc., resulting in an expensive time commitment. When looking at data in a spreadsheet, it is challenging to know whether changes were made yesterday or the month before and who made them, even in a shared access environment. If someone had filled in a number as a projected figure and forgot to remove it, it’s hard to know when this entry was made and, thus, what other figures are impacted by it.

With the majority of debt portfolios being built in spreadsheets, there are multiple hands in the process, beginning at the initial input and through the lifecycle of each entered and monitored loan. Formulas can be based on assumptions, and sometimes the only way to understand an assumption is through talking with the creator, which is frequently a challenge with spreadsheets when anonymous entries and modifications are the norm.

Comparing debt performance across different spreadsheets potentially made from different calculations and parameters is like comparing apples to oranges.

Spreadsheets are highly customizable, a great thing for ease of use but potentially very risky when it comes to extrapolating information. Comparing debt performance across different spreadsheets potentially made from different calculations and parameters is like comparing apples to oranges. What is the evidence that one loan is outperforming another when an organization can’t trust the methods used to get to the result? This lack of transparency influences decision-making all the way up to the boardroom.

With debt management technology, changes to a debt portfolio are dynamic, so when one person makes an edit, everyone else can see what they have done and the impact of their changes.

Any additions, deletions, or modifications are visible via version control, and data manipulation across multiple users can monitored through audit logs, resulting in improved integrity of calculations and overall output.

Efficiency and continuity

Unexpected circumstances can be difficult to deal with, but they can happen at any time. For instance, if a key person leaves your company, and they hold the domain knowledge that is critical to using a set of spreadsheets to manage your debt portfolio. It can take a lot of time to regain that knowledge or train their replacement. What is Plan B in such situations? If you don't know where to start or who to contact, it can be challenging to pick up where the previous person left off, or onboard additional staff without having to pull someone away from work to share the ins and outs of your customer spreadsheet solution.

For a small-scale debt portfolio, spreadsheet management is a cost-effective solution, however for any debt portfolio of significant size or rate of growth, the following efficiency issues can stack up quickly:

• Depending on the diligence of a debt team, data entry can be a time-consuming and error-prone process

• Managing spreadsheets often requires manual intervention, limiting automation and increasing repetitive tasks

• Collaboration is limited when multiple users edit the spreadsheet simultaneously, causing version control concerns

• It is common for integration options to be limited, which frequently results in the need for manual import and export of data.

When it comes to debt management, it is vital to have models that are both connected and fast.

These models should be able to integrate with markets, other data sources, other systems, and third-party vendors while maintaining accuracy and speed. In modern debt management, connectivity, user-friendliness, and fast calculations and reporting are crucial.

As mentioned in the previous section on accuracy, a debt management solution with a centralized solution opens the doors to considerable efficiencies over a custom spreadsheet solution:

• Data entry is often carried out using automated or semi-automated systems, which helps to reduce the likelihood of errors

• Provides automation features for tasks such as reporting and updating values based on predefined rules

• Multiple users can collaborate simultaneously with role-based access control

• Financial systems can be integrated such as property management software, valuation software, or third-party data sources, to reduce manual data entry.

Additionally, these debt management solutions typically come with comprehensive documentation on how they operate and training services to onboard new users without disrupting the existing staff. In case of any questions or concerns regarding how a certain result was achieved or what action needs to be taken next, the solution's support team can assist.

Visibility and security

Complying with regulations and keeping debt stakeholders informed and updated is a requirement left unfulfilled by spreadsheets. As more people need access to data, sharing a password-protected spreadsheet becomes worrisome for security reasons and version control. With financial and confidentiality risks, managers and IT teams are simply unable to keep up with the movement of spreadsheets and the revolving doors on spreadsheet access.

Checking the numbers and cells used in formulas could take hours or days to do, and a simple “delete row” could nullify the accuracy of all formulas in the spreadsheet.

A common misconception is that spreadsheets are transparent since all of the data used in cell formulas is available immediately within the file. However, checking the numbers and cells used in formulas could take hours or days to do, and a simple delete row could nullify the accuracy of all formulas in the spreadsheet. This false sense of transparency and visibility results in trusting documents that have not rightfully earned it.

Before making capital commitments, investors are showing a growing interest in technologies and systems CRE organizations are using to manage funds/portfolios. This interest goes beyond strategy; it reveals information about the manager’s sophistication and ability to meet the investors’ reporting requirements while scaling their IT to support growth.

The demonstrated use of spreadsheets is widespread in the real estate industry, but so is the view that their deficiencies are a threat. As we realize where we are currently and how we get to where we want to go, the shortcomings of spreadsheets can be dealt with appropriately.

Organizations now have access to purpose-built software solutions that provide robust options for centralized and rights-managed systems.

These systems help in enforcing and preserving a single source of truth. With such solutions, assessing the impact of loan-level changes on the entire debt portfolio has become much easier.

It's time to evolve beyond spreadsheets

Spreadsheets have been depended on for many years and have worked well enough, but modern needs require more than their basic capabilities. With high-risk scenarios from poor data entry to lost domain knowledge on calculations and security issues, today’s CRE organizations need more than “good enough,” they require a platform that is accurate, simple to audit, plans for efficiency and continuity, eases the complications of access-based visibility, and addresses accountability concerns for stakeholders.

A modern debt management solution can bring many benefits. It provides a centralized system for monitoring, analyzing, and reporting debt. It programmatically feeds on data from multiple systems to incorporate forecast and historical data, reducing manual data entry and errors. With intuitive and near real-time representation of each loan's state, it makes understanding a portfolio of debt easier. It also has the functional depth to identify loan opportunities and expose risks. With just a few clicks, it maximizes transparency with drill-downs on any calculation, data dependencies, and audit changes throughout the entire lifespan of a loan. Finally, it can quickly simulate different assumptions such as interest or cash flow to see the effect on a debt portfolio.

As a CRE organization's debt challenges evolve and the quantity, type, and frequency of loan data continue to grow, the software used to support debt management decisions needs to be more robust than what solely spreadsheets can offer.