End user computing: Grabbing the tiger by the tail

Finance organizations find themselves grappling with a dilemma around the use of Excel. On one hand Excel with its simplicity and advanced functionality has allowed even the most unsophisticated end user to build complex and powerful financial models. On the other hand Excel has proliferated so widely that organizations are unable to track where it is used and are often at the mercy of relying on figures for decision making or reporting, which are derived in ungoverned and unaudited Excel models. Some organizations have spent years trying to come up with a solution to address the use of Excel with very little success.

In 1846 Augustus De Morgan, the first Chair of Mathematics at the University College of London, created an array of rows and columns to tabulate mathematical computation. This framework became the foundation upon which modern spreadsheets are based.

Modern spreadsheets as we know them evolved from mainframe based systems to the widely used Excel tool incorporated in Microsoft Office today. In the late 1960s a mainframe system known as LANPAR, were used by corporations like General Motors and AT & T for budgeting. As the personal computing revolution took off, a number of different spreadsheet solutions were offered. VisiCalc, which was introduced in the late 1970s for the Apple 2 computer, contained 20 rows and 4 columns. Bill Gates then countered it with Multiplan for personal computers in the early 1980s. Multiplan did not gain much market share but it would serve as the basis on which Excel is built. Following closely, and post introduction of Multiplan, Lotus 1-2-3 was launched. Closely following the introduction of Multiplan, Lotus 1-2-3 was compatible with IBM and DOS operating systems and it eclipsed VisiCalc thanks to its graph capabilities, use of pre-defined functions and menu at the top of the screen. These are some capabilities that we actually take for granted today. Excel was launched in 1985 and with Excel 2.0 for Windows 2.0 Office Suite; Microsoft Excel began to displace Lotus 1-2-3 from its leader position in the spreadsheet market.

When I began working in banking the industry relied primarily on the Lotus 1-2-3 spreadsheet solution. Lotus 1-2-3 was displaced, in 1993, by Microsoft Excel 5.0, when that version of Excel was widely adopted by the Financial Services Industry. Excel 5.0 introduced the use of the workbook with a limited number of worksheets and VBA support. Excel 7.0, which was 32 Bit, more stable and faster, followed in 1995. In 1997, Excel 8.0 introduced the paperclip, now mercifully gone, and a VBA interface for developers. From there users and Excel models proliferated throughout organizations, to an extent that today no group within Financial Services organizations does not use or rely on Excel data in some way.

The ease of use combined with the power of Excel makes it a popular tool among Financial Services end users. Excel contains more than enough computational capability for even very complex financial models and has the benefit of flexibility needed to adopt assumptions and models as assumptions, products, data and other inputs or components can evolve. Organizations investing in robust and governable platforms to replace Excel solutions often find Excel tends to proliferate again as users seek ways to utilize the data from the enterprise platforms in ways not originally envisioned. This can happen in response to market evolutions, product evolutions, new regulations or new operational processes. In response to changes, which enterprise platforms are not nimble enough to address, at least not quickly, users will extract data out of the platforms in order to manipulate it to meet the need. This tradeoff between processing power and governance offered by enterprise platforms and flexibility often required in the industry is why Excel continues to be the end user tool of choice. Excel has enough processing capability to meet the bespoke needs faced by users and offers superior flexibility often required in an ever changing environment.

So Excel use is a double edged sword. The same features which make Excel desirable, the other attributes of computing power, ease of use and flexibility, also create issues for organizations. Organizations face pressure from regulators, auditors and senior management to have data in governable systems, minimizing operational risk and compliance risk. Excel models are often created by SMEs without much oversight, leading to the risk of errors being made without detection. Excel models are often copied, shared, amended without protected cells or worksheets, which means organizations can lack an audit trail and lose integrity. And when the users who created spreadsheet models leave, organizations often find that they also lose the knowledge of how to run and control the models that were created. There is also the risk of users taking these models with them and deleting what was in place at a former employer.

So what can an organization do to best address the issues associated with Excel usage without sacrificing flexibility and processing power for the end user? Organizations have been facing this issue for many years. In fact, many organizations have lived for years with outstanding audit points associated with reliance on Excel for reporting and monitoring sensitive data because they could find no better alternatives. Perhaps the answer is not to attempt to eliminate Excel. The better alternative is to find solutions to govern the use of Excel. Organizations need a solution that governs the use of Excel as whole not just individual workbooks. The ideal solution would govern the access to Excel models up front at “login” and assign roles and workflow governance to the use of the models and subsequent use of the data produced. The solution would incorporate business process management to ensure data changed is agreed before being incorporated in the model and the relevant users and managers are informed of changes and next steps. The solution would then provide an audit trail of what had been changed. The mapping of the processes associated with the Excel models within the solution would show the full data flow of the functions relying on Excel models. The mapping of data flows would allow organizations to make informed decisions based on cost benefit analysis to determine which areas are candidates for implementing robust platforms to replace Excel models and which models are best left in place.

A few solutions are already in use today, but they fall short in certain areas like security. Excel security functions can be circumvented by skilled users and passwords can be forgotten or lost if users leave your organization. Then you need IT to intervene. SharePoint meets the needs around sharing and governance but does not offer business process management capability. However a new generation of solutions is rising to meet all needs; governance, business process management, and audit trail capabilities. We are collaborating with one such organization, providing domain expertise to help implement such a solution effectively. Businesses will benefit from increased Excel governance, and will not suffer from loss of flexibility and or power provided by Excel to end users. Or to put it in a better manner, organizations can still benefit from Excel flexibility and power without the associated governance shortfalls.

John Trainor

Director of European Capital Markets Consulting, Virtusa. John received his MBA from London Business School and has worked in and with international banks for over 23 years. John helps clients implement solutions to address challenges facing the banking sector, including meeting regulatory challenges, assessing and implementing target operating models and implementing programs for continual process improvement.

More Posts