How to Save Time and Money on Waterfall Calculations

Excel and other spreadsheet tools have historically been used within the commercial real estate industry for waterfall calculations since they are easily accessible, affordable, and most people have a general idea of how to use them. As a result, onboarding and training costs are able to be kept to a minimum.

While spreadsheets are indeed useful, they also have their limits. For example, you can almost always produce results in a spreadsheet, but the numbers generated are not always accurate. Mistakes like these are where spreadsheets can have costly repercussions.

Read ahead to about the problems with spreadsheets and how RealPage IMS can be leveraged to eradicate spreadsheet errors.

 

The Problems with Spreadsheets

Distribution waterfalls, in general, are very complex, but the math behind them is not overly complicated. The problems typically arise within the confines of Excel. Take the example of Barclays, who accidentally made almost 200 contract purchases from Lehman Brothers in a bankruptcy buy-out deal. A pair of junior lawyers tried to reformat an Excel spreadsheet with over 24,000 individual cells of data and hid, rather than deleted, key cells. This mistake left Barclays financially on the hook for unwanted Lehman assets.

As illustrated in this example, the problems with spreadsheets primarily manifest as errors that are easily overlooked and risks that cannot be resolved. Let’s take a closer look at what this could mean for your organization.

Error

Studies repeatedly show that 9 out of 10 spreadsheets contain at least one error, and further research shows that attempts to correct those problems often introduce additional errors. These mistakes are not typically software defects or bugs. Instead, they are usually the result of human error. These problems have become so widespread that a consortium of professors, researchers, and professionals who study and develop processes and methods for spreadsheet risk management was formed.

Common spreadsheet errors include mistyped numbers, invalid formulas or logic, rounding mistakes, incorrect cell formatting, and copy-and-paste inaccuracies. In rare cases, somebody who knows what to expect may be able to identify that there are one or more errors on the spreadsheet. Even then, locating the actual placement of the error and what is causing it presents another challenge. In most cases, the mistake is not obvious and the data is accepted as the truth. That brings us to the next problem…

Risk

Most organizations have a single person who is responsible for all of the data entry in waterfall distributions. This individual is usually the only team member that understands it, making it hard for a colleague to help them. While other software programs typically have backstops and are always testing and double-checking calculations, this is not the case with Excel spreadsheets. Yet, this information is still used in making important decisions and distributions. Should one or more of the aforementioned errors occur, as is statistically likely, the cost to the organization can be to the tune of millions or even billions of dollars in time and money. That’s not to mention the potential for damaged reputations and business relationships, poor decision making, and lost future revenue.

The Benefits of RealPage IMS

Compared to spreadsheets, IMS automates formulas and calculations, uses customizable templates to maintain compliance, and seamlessly connects to distribution processes. After transitioning from spreadsheets to the AIM platform, firms can experience the following benefits:

Speed

Creating the rules for a waterfall distribution can be very manual and time-consuming. Automating this complex process means distributions can be calculated much more efficiently and effectively (literally in minutes!). This results in hours of time savings. And by reducing some of the admin work, employees can focus on more key aspects of their jobs.

Accuracy

Calculating waterfall distributions can be very complex, and the number of variables can make the task very cumbersome. One small typo could result in incorrect payouts, which can have major implications for your bottom line. Further, in this industry, accuracy is not optional. The format of the paperwork is standardized, meaning it has to be correct and it has to follow the proper format. With templating, companies can be sure that they are compliant with those mandated guidelines, serving to also mitigate risk. The Waterfall Audit feature can help troubleshoot any discrepancies, while the Distribution Workflow allows each distribution to be reviewed by other team members.

Scalability

With only a few projects, a firm may be able to continue using Excel to calculate waterfall distributions. As the number and complexity of projects grows, however, using spreadsheets becomes too much of a hassle and creates opportunities for many more errors. With IMS, you can establish repeatable processes with higher degrees of accuracy, enabling you to manage larger portfolios.

At the end of the day, the increased speed, accuracy, and scalability offered by RealPage IMS equals huge cost savings for any organization – and the benefits of the platform definitely outweigh the costs of continuing to use spreadsheets.

 

You may also be interested in Leveraging CRE Tech to Manage Waterfall Distributions.