We all recognize the strengths of spreadsheets, why we love them and persist in using them for managing the allocation and calculation of master/feeder, partnership, series and other complex structures. They are flexible, capable of handling gloriously intricate
calculations, wonderfully familiar. We understand how they work and yet each one can be customized to meet a specific need and adapted as that need changes.
There’s also a dark side to fund administrators' relationship with the rows, columns, formulae, functions, charts and graphs. There are inherent problems in the use of spreadsheets for complex calculations. It is these that should prompt you to find an alternative.
Let’s list them out.
Manual intervention and an accumulation of process steps are unavoidable if using spreadsheets for the calculation of complex processes. Each time data is extracted from or imported back into your core investment accounting/transfer agency solution there’s
a new risk of data corruption and inaccurate information being used as the basis for calculations. Additionally, you run the risk of formulae being changed and causing incorrect values which impacts your final valuation. If the creator of the spreadsheet leaves
there is a possibility that no-one else truly understands how it works
Auditors recommend that all calculations or allocations are done on a dedicated platform that has full security and audit controls. This is something that a spreadsheet cannot offer. They will look for a fully automated back-up of data through a platform
rather than relying on human intervention like a spreadsheet does.
It takes significant resources to create and map out the formulae required for complex relationship-related calculations, especially as they will need to be tested and re-tested to assure that they are correct. There is also the cost of error to be considered,
both financially and reputationally. If you could automatically, visually map the relationships and let the underlying system do the calculations, it would free up resources, saving money and reducing risk.
Even once spreadsheet calculations have been set up, tested and are ready to go, it still takes time to run the processes. That’s before you extract the data and put it back in the system to send on to your clients. Imagine a world where you could automatically
calculate the NAV’s for 60+ underlying series funds or multiple income gain/losses for 100 partners at the push of a button, posted in just seconds. Why would you ever use a spreadsheet again.
Your present and future investors will want to know how you handle master/feeder, partnership or series structures and whether your processes are robust and replicable. This is hard to achieve when using spreadsheets and their use may afford you a black
mark in the due diligence check list. Select a system where this functionality is embedded, add the fact that reports can be created to go direct to investors and you’re back in consideration.
Walking away is never easy. But in the context of managing the allocation and calculation of complex structures, there are solutions which can do everything that a spreadsheet can. What’s more is that they provide a robust environment and with the added
benefits of auditable history, automation and visual tools, supporting both offshore and onshore funds and unlimited fund structures. It’s time to say goodbye to spreadsheets.