Rethinking Spreadsheets and Performance Management
While a host of different budgeting, forecasting and reporting technologies have faded in and out of popularity over the years, the simple spreadsheet has continued to be a mainstay of finance teams at credit unions for decades.
According to CFO Magazine, “Microsoft gauges the number of Excel users worldwide at more than 400 million, and Forrester Research estimates 50 to 80% of enterprises still use stand-alone spreadsheets for critical applications like financial reporting.”
The ubiquitous Excel spreadsheet is simple enough for any beginner to understand yet powerful enough to support high-level applications and modeling. However, are spreadsheets being utilized properly to best support the institution’s business objectives?
Many industry experts deride the use of spreadsheets as part of a closed-loop performance management process because they tend to be error-prone, they lack the level of control required by larger institutions, and waste valuable resources on mundane activities such as uploading, downloading, reconciling, emailing, cutting and pasting.
Despite these well-documented deficiencies, the spreadsheet is not going away anytime soon and continues to be favored by finance professionals due to its modeling capabilities and ease of use.
Within a credit union’s performance management domain – including budgeting and forecasting, reporting and analytics, strategy management, profitability and cost management – spreadsheets remain the de facto management tool, but there are ways to leverage performance management technology and embrace Excel in a more innovative, impactful way.
Enhancing the Familiar
It is critical for performance management software to produce useful, accurate calculations, but the software should also complement and enhance Excel’s capabilities to satisfy the credit union’s need for complete flexibility and control. For example:
Data Storage and Access
The credit union’s performance management data source must be clearly defined and easily accessible, as an audit can arise at any moment. Data should be stored in a secure repository that is accessed and manipulated through an easy-to-use Excel interface. Budgets, models and reports – including logic and formatting – are managed and stored in one platform to be used in a consistent manner across the organization. This results in trusted data that can be used confidently in decision making.
Instances may arise when a credit union needs to create a unique grouping of data for reporting purposes. By using a consolidated performance management platform vs. stand-alone spreadsheets, it becomes possible to easily aggregate and present any data across the credit union’s multi-dimensional landscape, eliminating the credit union’s need to use formulas to consolidate data housed in stand-alone spreadsheets.
Scalability The use of stand-alone spreadsheets is manageable in low user count environments, but completely out of the question in medium to large user populations. The goal in serving large user populations should be the delivery of robust business models that are unique to the individual without requiring manual intervention from the system administrator. Well-designed performance management software allows credit unions to design budget templates and calculation methods that align with their different business segments – such as back office and front office – and at the touch of a button, will format data into unique planning workbooks with no manual intervention required.
Security and Workflow
Stand-alone Excel spreadsheets are nearly impossible to manage from a security and workflow perspective. Passwords can be applied to spreadsheets, but the design was never intended for a broad audience with various rights and authorization levels. Performance management software with dynamic read/write access provides greater flexibility to meet the credit union’s desired workflow and specified approval chain.
Credit unions should consider leveraging performance management software that includes these capabilities, as it will satisfy competing needs of flexibility and control in performance management reporting and analysis.
Next Page: Excel as Foundation
Excel as Foundation, Not Afterthought
The spreadsheet is a great baseline tool because it is both powerful and more importantly, familiar. As a result, nearly all performance management technology providers offer an Excel add-on.
However, maximizing the value of your performance management solution and eliminating Excel’s problematic qualities requires software that moves beyond a simple add-on in which Excel acts as a lens, simply looking into the complex database where the credit union’s core logic is created and managed.
Relegating the calculation logic to database script logic typically results in either a system too unruly to maintain, or one that’s too simple to derive much value from. The lack of user friendliness inherent with script-based systems causes users to rely heavily on IT and to give up attempting any level of sophistication with their models – often keeping separate spreadsheet models outside of the core system.
There is a better way. Performance management software solutions should embrace and maintain the financial modeling capabilities and ease of use that financial professionals know and appreciate, while ensuring the data integrity, security, workflow and enterprise performance that the organization requires. The result is actionable information that the credit union can trust and understand to enable better decision making, for example:
- Calculation methods help to ensure the integrity of corporate models without sacrificing robustness or ease of use. Calculation methods can be simple – such as spreading an annual total across a 12-month period based on the value of another variable – or quite complex – such as computing the average yield of an adjustable rate mortgage portfolio across time given a particular interest rate forecast and prepayment model. Calculation methods embrace Excel syntax but add to it numerous capabilities such as the ability to read/write from and to a controlled database, the ability to point to centrally managed driver tables in order to consistently use common calculations across the entire organization.
- Variable extraction is the ability to extract and manage common variables (interest rates, sales volume projections etc.) separately from the calculations they influence. This concept is tremendously powerful, enabling power users to perform scenario testing and sensitivity analysis to key drivers. Variables can be enterprise-wide or limited to a portion of the organization, such as a region. The many-to-one variable model is enabled without linking spreadsheets – a vital distinction from stand-alone Excel. This approach greatly adds to the power and integrity of any collaborative financial model.
- Dynamic referencing eliminates a standard pitfall of Excel fixed-cell references. Calculations that point to other locations in a spreadsheet can be compromised when the spreadsheet geometry changes, typically in the form of adding or deleting rows and columns. Organizations should embrace the concept of dynamic referencing, which alleviates the fixed reference issue when spreadsheet geometry changes, while giving users the ability to add or delete columns and rows based on their user profile.
- Error trapping directs users to the exact location where a calculation error occurs, so credit unions can easily solve those all-too-common Excel error messages and ensure that workbooks, reports, and models retain their integrity.
Overall, the ability to manage performance management calculations will reduce spreadsheet errors without sacrificing robustness and ease of use.
The Ongoing Debate Within the realm of financial budgeting and planning, there will always be arguments for and against Excel’s capabilities, but despite its faults and deficiencies, any attempts to replace or eliminate the spreadsheet have fallen short.
To overcome Excel’s shortcomings, credit unions should leverage a performance management solution that expands upon the finance-friendly nature of Excel while eliminating the error-prone functionalities of stand-alone spreadsheets to satisfy both the credit union and its IT department’s needs.
Alleviating repetitive, labor-intensive tasks and streamlining performance management operations enables credit unions to better focus on strengthening operational processes, resulting in more profitable growth and an overall greater user experience.