23 November 2014

71410

Justin Hayes - Linedata

1 | posts 6,580 | views 0 | comments

It’s not me, it’s you: time to leave spreadsheets behind

09 July 2014  |  6581 views  |  3

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.

1.OPERATIONAL RISK

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

2. AUDITS

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.

3. COST

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.

4. TIME

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.

5. MARKETABILITY

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.

 

TagsRisk & regulationPost-trade & ops

Comments: (3)

Ralph Baxter - ClusterSeven - London | 10 July, 2014, 07:50

Justin

Interesting article but you are seriously out-of-date. Enterprise spreadsheet management technology can resolve many of your concerns.

 

Be the first to give this comment the thumbs up 0 thumb ups! (Log in to thumb up)
Ketharaman Swaminathan - GTM360 Marketing Solutions - Pune | 11 July, 2014, 18:29

I read somewhere that, while 70% of FORTUNE 500 companies have ERPs, over 90% of them submit their reports to their Boards in Excel. Spreadsheets are here to stay - I can bet that the investors are themselves very likely to use spreadsheets to evaluate the investee's proposal and to maintain the due diligence checklist. Tech vendors are increasingly realizing the futility of fighting Excel and positioning themselves as complementary to Excel - DOMO is an example I came across recently (https://twitter.com/GTM360/status/481444394747568128). 

Be the first to give this comment the thumbs up 0 thumb ups! (Log in to thumb up)
John Doyle - FSS Technologies UK - London | 14 July, 2014, 14:38

As usual with these kind of articles there's a mish mash of opinion and fact and a plethora of concepts which are misplaced.

Justin has a point in that he's saying there are tools out there that replace the spreadsheet and are more reliable and safer; bu that's not to say that spreadhseets don't have their place too.

It's really no more relevant than making sure you use the right tools for the job.  We'd all agree with that.  I just don't see the point in rubishing something for the sake of selling different tools to that that you are criticising.

The spreadsheet isn't dead; the analytical tools like SAP aren't dead, there's room for both.

I'd rather see a blog that told us what some of the analytical tools were and why we'd use them.

 

 

Be the first to give this comment the thumbs up 0 thumb ups! (Log in to thumb up)
Comment on this story (membership required)
Log in to receive notifications when someone posts a comment

Latest posts from Justin

It’s not me, it’s you: time to leave spreadsheets behind

09 July 2014  |  6581 views  |  3  |  Recommends 0 TagsRisk & regulationPost-trade & ops
name

Justin Hayes

job title

Product Manager

company name

Linedata

member since

2014

location

Dublin

Summary profile See full profile »

Justin's expertise

What Justin reads
Justin writes about
Justin's blog archive
July 2014 (1)

Who is commenting on Justin's posts

John Doyle
Ketharaman Swaminathan
Ralph Baxter