All family offices make significant use of spreadsheets, most typically Microsoft Excel. In some offices spreadsheets are the foundation for the key accounting and reporting activities, often paired with a basic general ledger such as Quickbooks. In other organizations, spreadsheets supplement the capabilities of robust and specialized systems that provide the core processing.
Spreadsheets are the Spackle of the technology infrastructure: They work because they're easy and customizable.
But their flexibility is often their liability.
Spreadsheet applications are not developed with the same rigor as more sophisticated applications, which leaves them open to significant flaws in the quality of the generated information. Some of the most significant issues are:
Errors are easy to make and hard to detect.
Inconsistency in the way information is entered and organized can be a problem, especially when multiple users are involved. This leads to discrepancies and unmanageable data.
Lack of training. Many handy tools and controls are available to accelerate development, but people are not properly trained to use them.
Lack of standards. Spreadsheets are often considered independent works of art. Developers are not encouraged to conform to standard and consistent methodologies and formats.
Complexity. Spreadsheets can become enormously complex
It's Fixable And Manageable
Spreadsheets do what we ask of them and they do it very well. Yet beneath the covers, there are various issues that may compromise their value. Sophisticated integrated applications incorporate many quality attributes based upon a rigorous development process; most often, spreadsheet applications do not. Through our experience in analyzing the utilization of spreadsheets in family offices and other financial organizations, we have found recurring problems. Fortunately, they are all fixable and avoidable.
Below are some of the most common issues that arise when using Excel and other spreadsheets, and the tools and techniques that you can use to fix them.
1) Develop standards:
A standards document provides the basis for guaranteeing that all applications are developed in accordance with agreed upon rules. The standards will help coordinate all Excel development as well as define the environment for the future. These standards will define specific rules that cover the topics that are generic within the spreadsheet community and relevant to your organization. Standards should cover the following topics:
Spreadsheet and worksheet naming conventions.
Guidelines for defining multi-tab workbooks.
Guidelines for organizing workbooks into manageable sections.
Guidelines for developing workbook logic including:
-Standards for data entry, including data validation.
-Formatting conventions.
-Techniques for accessing data in other worksheets and workbooks.
-Standards for developing reports.
-Protecting worksheets from unintended changes.
-Spreadsheet controls.
Guidelines for validating and testing spreadsheets.
Guidelines for storing and archiving spreadsheet versions.
2) Have a spreadsheet guru:
Each organization should have an individual or group that defines and administers all activities related to spreadsheet development and usage. At a minimum, this will encourage people to discuss common issues and techniques-solving old problems and preventing new ones. This guru should have the following responsibilities:
Development and updates of the spreadsheet standards.
Oversight of spreadsheet development and implementation.
Training and technical support.
3) Invest in education:
Developing robust spreadsheets requires significant skill and knowledge. Excel provides a host of very powerful features that could provide significant improvements in productivity. Unless you know where to look, however, many of these features are hidden within a complex web of menus and arcane terminology. Key employees should get formal training in advanced Excel topics such as pivot tables, array formulas, data tables and Visual Basic programming. These and other features will provide incredible insight into how previously intractable problems can be addressed.
Leverage The Tools
Leverage the tools that are currently in place (and probably underutilized), and invest in some new tools. This will impact your organization's productivity and the effectiveness of your spreadsheet development.
1) Spreadsheet organization tools:
Many spreadsheets are difficult to understand. User data, formulas and output reports are intermingled on one or more worksheets with little thought given to the difficulty in using and maintaining the spreadsheet. Excel provides the following organization tools:
Data Validation: This allows you to define the type of data and values allowed to be put into each cell. It can go a long way towards eliminating user error.
Conditional Formatting: While most spreadsheets use standard formatting capabilities, conditional formatting allows a single cell to have different formats based on the cell's value.
Named Ranges: This allows names to be assigned to cells and cell ranges. Rather than having a complex formula that references cells in the native lingo, named ranges can be used to create formulas like market value = units * price. Also remember to name each sheet in your workbook.
Cell Comments: Putting comments directly within a cell can be very useful for defining a complicated formula or describing the meaning of an input cell.
Worksheet Protection: All workbooks should be protected so that formulas cannot be inadvertently modified.
Views: Views allow a single sheet to take on multiple appearances. A worksheet having many columns can use views to provide snapshots of information without duplicating the data.
2) Data tools:
Most of the power of Excel lies in the wide array of data manipulation features inherent in the product. These tools include spreadsheet functions-formulas that you put in cells and spreadsheet features, which are the tools provided to manipulate and analyze information. As a starting point, the following should be considered essential components of your tool kit:
Data Lookup Functions: Lookup, Vlookup, match and index provide the functionality essential to linking separate data tables within your spreadsheet.
Conditional Functions: Functions such as SUMIF and COUNTiIF provide the most direct way to summarize data within a spreadsheet range based on specific criteria.
Data Base Functions: These functions allow you to treat a spreadsheet range as a mini-database and provide a more sophisticated means to slice and dice your data.
Pivot Tables: One of Excel's most powerful tools, pivot tables allow you to reorganize, summarize and analyze your data.
Data Modeling: Data tables and scenarios can be used to create sophisticated data models and perform "what if" analyses.
3) New tools:
While Excel, depending on your version, has some basic auditing tools, there are various third-party Excel add-ons that allow the developer or tester to peer more deeply into the inner workings of the spreadsheet. These tools can spot inconsistencies in formulas, unused cells and other signs of errors and inefficiencies. Many of these products integrate with Excel so that they appear to be part of the application. They are an excellent way to protect your spreadsheet investment.
There are also third-party developers that have built tools to easily import data from accounting packages to Excel spreadsheets. These products, if used properly, can provide a tight integration with the office's general ledger and eliminate time-consuming duplicate data entry. Many family offices use these tools to provide enhanced reporting not available through the general ledger software.
Summing Up
The goal of this article was to sensitize you to spreadsheet related issues that probably, at least to some degree, are present within your office. While undertaking these recommendations takes time and money, you will recognize immediate and tangible operational improvements and a very significant reduction in risk at a fraction of the cost involved with implementing a new accounting or portfolio management system.
Hugh Bagatelle ([email protected]) is a partner at Windward Advisory Group, a family office technology consultant in Princeton, N.J.