If your company uses spreadsheets for financial planning, analytics, and reporting, how are you managing the risk of errors with a technology that’s nearly 40 years old? 

There were many major events in 1985: Live Aid concert in London, Mikhail Gorbachev became the premier of USSR, Michael Jordan was named NBA rookie of the year, and Microsoft Excel was launched. 

Spreadsheets have certainly been a transformative force over the last 35+ years and are ubiquitous in the financial and banking industry. But it’s becoming an increasingly risky technology. The growing frequency of errors in complex calculations or transactions in spreadsheets is causing billions of dollars in fines and losses.

For example:

• A U.S. bank mistakenly wired US$894 million to payoff a loan, instead of the U$7.8 million interest payment, due to a spreadsheet error
• A U.K. bank was fined over £5 million because of errors in their risk reporting spreadsheets, dropping their share price by 30% and forcing top executives to resign
• Another U.S. bank lost US$6 billion and was fined more than US$900 million for inadequate controls and unsafe practices with their spreadsheets

Regulators are increasingly using fines to compel companies to address the inherent risks of complex spreadsheets, including lack of version controls, inconsistent data references, hidden and poorly understood formulas, and human error. And more stringent regulations targeting spreadsheet risk are coming soon.

What’s the alternative?

Spreadsheets are exceptional tools for data manipulation and analysis. Some of the biggest advantages of spreadsheets are the ability to build formulas with little or no coding expertise, the ease of getting data in and results out, and the debugging capability of showing the dependencies of each cell. 

Any alternative needs to provide at least as much functionality. Start with an easy-to-use and widely popular programming language. Add the governance, centralized database, functional consistency, and quality assurance processes of modern software systems. And why not throw in the power and flexibility of cloud processing and distributed computing as well?

Migrating spreadsheets to a software repository

Spreadsheets are spread so far throughout most organizations that shifting suddenly to a new platform is not feasible. A successful migration plan must include an automated conversion tool that can read existing spreadsheets, convert the formulas into an appropriate programming language without changing the underlying behavior, and store them in a modern code repository with access and version controls. This approach enables an effective migration to a new system, preserving existing functionality without requiring a complete rewrite of every single formula.

Once the spreadsheets have been uploaded and converted, governance and access controls make it much easier to share code and ensure that everyone is using a consistent set of formulas. You will be able to quickly identify what pricing models are being used, track every code change, run automated test scripts, and rollback changes to reproduce earlier results.

Leveraging the power of Python and the Cloud

The growing popularity of the Python programming language, with its readable, English-like syntax and broad range of available software libraries, makes it a natural choice for representing spreadsheet functions and formulas. Python is considered to be one of the easiest programming languages to learn. It is open source and readily accessible to people who are writing spreadsheet models or using data science notebooks yet powerful enough to code sophisticated, enterprise-scale applications. Automating the conversion of spreadsheets to Python accelerates the migration process while maintaining structure and functionality.

Since you are already migrating the spreadsheets to something new, why not make them cloud-powered? Many of these types of spreadsheets take a long time to process when run on a desktop computer, delaying results or limiting the scope of the analysis. Leveraging a cloud platform significantly raises the performance and scalability potential, enabling you to get results when you need them and run a much wider range of scenarios. Wouldn’t you like to be better prepared for edge conditions or have updated risk assessments in minutes instead of just end of day?

Depending on dependency graphs

Finally, a useful feature of spreadsheets is their ability to show the user how cells relate to each other, using a structure called a dependency graph. But these are not exclusive to spreadsheets. Migrating to a platform with advanced dependency graph and database functions expands the potential of these tools beyond simply debugging formulas. 

When a model is stored as a dependency graph you can trace a calculation through all of the values and sub-formulas that it relies on. This makes it easier to identify an error in the formula or understand the sources of an unexpected result. But dependency tracking combined with the cloud also makes processing updates much faster. When the platform keeps track of the complex web of interconnected formulas it can determine the optimal caching and recalculation of results. Add in the elastic compute functions of the cloud and you can run independent calculations in parallel to get results in seconds instead of minutes or hours.

Another valuable feature for finance is the ability to have multiple layers of dependency data that keep track of data revisions, corrections, and updates. Instead of just having the current entry for any value, all values are kept, enabling unlimited time travel through the data. Rerun reports as of any time to effortlessly reproduce earlier reports, compare results, and understand the effect the changes had on downstream calculations.

Following the leaders

Financial industry leaders and innovators such as PIMCO and Blackstone have already begun their migration away from spreadsheets and are reaping the benefits of this cloud-powered approach. The results? Greater consistency and governance of their financial models. Scalability needed for more exhaustive analytics and broader risk scenarios. And the ability to ask new questions and make rapid decisions about their business, in ways that weren’t easy before. What could you do with these capabilities?