It is no secret that most CFOs and other investment professionals are addicted to using Excel. Spreadsheets are the most available tool for many accounting and number-crunching tasks. But they are not without limitation, and when financial institutions become reliant on spreadsheets for more complex tasks, problems arise. A spreadsheet was for a long time the tool of choice to digitise something that was previously paper-based, but when it comes to today’s requirements of digitalization – can it cope?
Distribution waterfalls in the private equity world define the returns investors receive and are dictated by the provisions of Limited Partnership Agreements (LPAs). They form a critical step in the partnership between fund managers (GPs) and investors (LPs) and are often unique and complex in nature.
Before we delve into some of the reasons not to use Spreadsheets for calculating waterfalls, here’s some stats which underline the problem.
- 90% of spreadsheets contain errors
- Half of spreadsheet models used in large businesses have ‘material defects’
- CFOs spend an average of 2.25 hours in spreadsheets every day and almost 50% of their time in creating reports out of those spreadsheets
1. Inaccuracies
Bad calculations and errors in spreadsheets often go undetected and are then distributed and shared with partners and investors. One small mistake in a formula can have significant repercussions and lead to incorrect results which can get compounded over time. Troubleshooting errors in complex waterfall calculations can be time-consuming and challenging, not to mention the reputational damage and potential legal costs.
2. Key person risk
Manual spreadsheets are created by individuals and not easily understood by everybody, which in turn prevents collaboration and consistency. They also increase the risk of dependency and problems when staff leave an organization. “Legacy” models can be a nightmare to understand if the creator leaves without sharing the information. This often leads to spreadsheets with multiple layers of logic, each built by different owners and making it even more complex to understand and review.
3. Security
Excel sheets can contain proprietary and sensitive data that should not be shared or fall into the wrong hands. Spreadsheets do not fall under the same scrutiny as IT projects, with versioning and other controls, and thus can become a security risk. Also, since Excel has long been a target for hackers the files themselves become a threat if shared.
4. Scale
As your business grows, using manual spreadsheets will limit your ability to scale and accurately track investment returns, catch-up, hurdle, fees, and other complex calculations. Even simple formulas need to be re-entered every single time a model is created and can be discovered to be wrong later on. The manual repetitiveness is not only boring and painful, but also a cause for undetected mistakes. Across a range of investments, each with its own terms and agreements, in different jurisdictions, can you realistically trust a spreadsheet to handle that complexity at scale?
5. Auditability
With the complexity of differing LPAs across a range of investments, can you trust Excel to handle the complexity and auditability, especially if the exposure to fees is considerable. Spreadsheets lack robust auditing features, making it difficult to trace and validate changes made to formulas and data. This lack of transparency can hinder the ability to ensure accuracy and accountability in complex calculations. Auditing spreadsheets is particularly challenging compared to an automated solution which can generate reports.
6. Lack of collaboration
Spreadsheets are fine for individuals, but as with key person risk, they are not designed for robust team collaboration and bring additional problems with version control, corrupted files, and ownership. They also help to perpetuate the problem of data silos within organizations, with data trapped in different worksheets which is not shared in one central repository.
Automation for waterfall distribution calculations
Clearly the days of using Spreadsheets for waterfall calculations is coming to an end. Whilst spreadsheets can still play their part in the process, usually through data loading or reporting, they should not be used as the waterfall calculation engine at the heart of your process.
qashqade provides private market organizations an enterprise product suite for streamlining their calculation, allocation, tracking and reporting processes. Waterfall and carried interest calculations can be automated, replacing manual, error-prone spreadsheets and improving communications between GPs and LPs through enhanced reporting. Built by private markets experts, for private markets experts, the qashqade platform offers a flexible, modular based solution for fund managers, fund administrators and investors alike, and is asset-class agnostic.
