Is your team drowning in complex spreadsheets? These best practices help keep your data working for you, not against you.
Your team has raised your next fund, you have made capital calls for expenses and investments, and the deal teams have made acquisitions! This is a huge achievement and as you know, has been very time consuming. Great job, but what does the mean for operations and the ‘back office’?
Huge amounts of data – in different places, in different formats, from different owners and in different systems, with little interactivity of systems and inefficiencies.
The impact is also felt at the portfolio company level, with additional underlying reporting requirements of the portfolio company CFO. With ‘add-on’ acquisitions (bringing different ERPs), operating CFO’s are put into the position of needing to consolidate financial data, with the inherent requirements of currency translation, numerous ERPs, eliminations, and more. The normal process is to build out elaborate spreadsheets with the complex use of ‘SUMIF’ and ‘VLOOKUP’ references. Have you ever tried to take over such a spreadsheet, or try editing it? What about adding a new acquisition or making an updated forecast?
At the Fund level, many Private Equity firms maintain their accounting activity in unsophisticated accounting packages. This is a normal practice as core accounting requirements typically do not justify using more sophisticated and expensive packages; however, it does lead to challenges. Lack of automatic access to detailed underlying investor data results in vast amounts of manual data entry. Meeting investor level internal and external reporting obligations becomes extremely inefficient, or sometimes even impossible in the format being requested. The need for tracking detailed data at the LP level creates inefficiencies throughout the process, including:
Maintenance of numerous complex data tracking spreadsheets with manual updates to data each period.
Time consuming production of reports to support investor related requests.
Difficult to produce ILPA reporting templates and detailed investor capital account statements (institutional investors requests for access to more detail is becoming the ‘norm’ and can be expected to increase in the future).
Manual data entry into numerous process spreadsheets, including waterfalls, IRR, MOICs and other KPIs.
Numerous locations and forms of data.
A lack of data integrity and data inconsistencies.
Now add performance tracking and Portfolio Company reporting to the mix. Monthly financials are submitted via Excel, PDF’s, PowerPoint and others. Deal teams create their own spreadsheets that track summary information for the respective company – which are usually inconsistent between deal teams and include adjustments to EBITDA that are not reflected in the underlying company. Let’s now include the quarterly requirement to provide an ILPA format reporting template for the underlying investments. More data gathering, more inefficiency, and an ever-increasing drain on expensive resources.
Private Equity finance professionals live with this situation every day, using systems and processes that do not interact with each other and severely hamper their ability to perform efficiently.
But…
Spreadsheets are here to stay for the foreseeable future.
Organizations are swimming with spreadsheets, despite their limitations which include:
Inability to handle large data
High storage volume
High maintenance requirements
User specific
Data inconsistencies between various sources
Extreme complexity and difficulty auditing and editing
Using QuickBooks® or equivalent programs is likely to continue. Even if you choose to go with more specific (and expensive) software, the challenges highlighted above will largely remain. Deal teams will want to do things their own way – even if there are inconsistencies. Portfolio companies will need different formats of reports as each business is unique and requires different metrics to monitor performance.
So what should you do?
If you want to continue to use the tools you already have, then the following best practices may help:
Define templates for data collection and consistency of data. One template may not fit all requirements – by identifying common themes, data can be standardized for specific investments or requirements.
Centralize your file systems and locations, allowing access to only those with appropriate authorization. Limit the amount of data team members file locally on their desktops or laptops. An efficient file structure on your main server should be established and a policy implemented with the requirement to store all data on your server.
Where possible, define reports on a common basis in order to simply the review process. Reviewing numerous reporting packs that look and feel completely different can be demanding – and difficult to quickly understand performance as you will spend too much time trying to figure out where the data is in the report.
Implement reporting calendars – put in place a well-defined reporting calendar such that expectations are set well in advance. Reporting calendars should be issued prior to the start of the year, and include due dates for monthly financials, monthly review dates, quarterly board meeting dates, deadlines for updated forecasts, budget due date and review dates. The more you include, the easier the process. Reporting calendars ensure the whole team is on the same page with regards timing and reporting expectations.
Build spreadsheets that are designed for multiple users. Far too often, spreadsheets are designed for the creator and no thought is given to others who may need to use them. Design workbooks assuming your colleagues will need to use and edit – note sources of data, bring data into workbooks in tables, limit use of complex macros, format your data consistently and add notes. A good rule of thumb is to keep the following in mind while creating sheets: “If I were hit by a bus tomorrow, could this be easily used by someone else?”
Move data collection away from deal teams – This may not always possible and does largely depend on the team and company dynamic, but it can bring efficiencies by reducing the amount of inconsistency in the data.
Is there an alternative?
As technology continues to evolve so too must businesses, or else risk being rendered obsolete by their forward-thinking competitors. Gartner's Magic Quadrant for Business Intelligence and Analytics Platforms highlighted this in their February 2017 issue: "The business intelligence and analytics platform market's shift from IT-led reporting to modern business-led analytics is now mainstream." With business-led analytics taking the forefront, empowered business users can take charge of their data personally, rapidly identifying new opportunities to leap ahead of the competition and differentiate themselves in the marketplace. By taking data into their own hands, business users move past the old inefficiencies and pains, and move forward toward new opportunities in the industry.
Thankfully, technology is available to help alleviate much of these problems and there are now many software providers out there offering solutions. However, many vendors offer fixes for only one part of the problem. Financial Consolidation software doesn’t address Portfolio Company tracking and management of Fund data. Expensive Fund accounting software focuses on accounting and LP data – but not Portfolio Company tracking or Financial Consolidations. Choose software that provides flexibility of solutions and applications.
How CenDat Solutions can help you.
CenDat Solutions provides powerful and flexible applications for the office of the CFO. Fund Data Management for Private Equity, Financial Consolidations, Portfolio Company Tracking, Budgeting and Planning, and other applications that need fast, flexible, and dynamic access to data.
CenDat/Jedox embraces the continued use of spreadsheets as one of the principal user interfaces to its powerful and flexible database – allowing users to remain in a familiar environment. Combined with web and mobile options, CenDat/Jedox can provide applications to eliminate the pain caused by volumes of data and numerous complex spreadsheets. The flexible architecture allows development of numerous applications to support your needs, including Fund Data Management, Portfolio Company tracking, Financial Consolidations, Reporting and Budgeting – and more.
To get more information on how to eliminate your data and reporting frustrations, contact us at Info@CenDatSolutions.com to schedule a free 30 minute consultation.