Risks Investment Managers Take Using Spreadsheets | Gresham
It’s natural to choose a familiar tool to make tasks easier, and turning to spreadsheet applications for calculations is no exception. In the investment management world, financial services teams, CFOs and operations teams often use spreadsheets for client fee calculation and billing. While the endless formulas, worksheet linking, and cross-tab capabilities may make spreadsheets a good choice at first, firms will quickly realize how much complexity and risk they end up creating.
Investment managers must be able to prove the accuracy of their calculations and how access to the data is controlled. When selecting the method for calculating fees and billing clients, care should be taken to ensure the platform provides flexible and consistent fee calculation, fee collection capabilities, audit tracking and data security, external system interfaces and analytical reporting. Hands down, spreadsheets offer limited ability to meet these requirements.
Here are six substantial risks investment managers take when they use spreadsheets for client fee calculation, billing and cash flow management.
1. Delayed Cash Flow
Calculating and ensuring timely collection of fees are vital to revenue flow. The ability to consistently and accurately handle a variety of account-level, date-based fee calculations and billing scenarios driven by investment management agreements (IMAs) is also critical. Firms need the flexibility to support IMAs and fee rate changes, but spreadsheets fall short every time since they require manual effort to create separate calculations for different time frames. Before long, a firm will not only have a myriad of sheets to manage within a single workbook, which delays the collection of fees. It will also have no way to monitor how an account’s fees contribute to the revenue of the firm. Being able to make focused fee schedule changes based on effective dates is a true benefit of an effective fee billing process.
2. Fee Collection Errors
When it comes to collecting the fees, firms generally rely on two methods: directly debiting the client’s account via a feed to the custodian, or invoicing the client directly. Using spreadsheets to build the various file layouts for each custodian is a manual process that increases the chances of errors in terms of layout and data accuracy.
A fee billing system should provide functionality to automatically create consistently-formatted custodian fee files. Furthermore, firms should be able to set, at the account-level, which accounts are direct debit and which are not beyond just custodian affiliation.
3. Inadequate Invoicing
Invoices are client facing and directly represent the firm. Spreadsheets do not allow for both summary and detail invoice information to be displayed. They also lack the ability to track and differentiate between the party paying the invoice, and interested parties receiving a copy of the invoice.
4. Inadequate Audit and Security
Any firm who delivers invoices via email needs invoice storage capability and security protocols to protect sensitive information during transmission. While spreadsheets can generate pdf file formats, the steps are manual at best and provide no storage of processed documents.
Firms must limit and control access to client data and fee agreement properties and remain accountable for changes to fee billing rules. Spreadsheets may provide brute-force sheet protection, but they provide no audit trails, ability to trace history of fee billing structures, or user permissions.
5. Inefficient, Fragmented Processes
Fee data is relevant to many functions within an investment management firm, making the ability to send fee data to down-stream systems another key benefit. But too often, billing or operations staff must re-key data into accounts receivable systems, general ledger systems, and even portfolio accounting systems for use in calculating net of fee performance numbers. Using spreadsheets to create pivot tables on different worksheets only increases the number of worksheets to maintain, which quickly creates overwhelming numbers of worksheets to search and review.
6. Lack of Business Intelligence
Firms are looking to benefit from the wealth of information they have. Analytical reporting is beneficial to the firm as a whole, whether to simply report on fees calculated for the period or to compare multiple periods as a means to spot trends. However, extracting meaningful data from spreadsheets proves difficult as there is no consistency between sheets. Rolling up or grouping the data becomes increasingly difficult as the number of worksheets grows.
Improving the Billing Process
An efficient method of calculating and collecting fees means faster revenue flow. As investment management firms consider their options for periodic fee billing, consideration should be given to their desired business outcomes and what tool would best enhance the process. Firms should consider their needs for flexible and consistent fee calculation, fee collection, audit tracking and data security, connectivity, and analytical reporting − and how well their current tools address them. In the end, it will quickly become evident that using a spreadsheet is not the most accurate, secure and profitable choice.
