The Excel-ephant

The Excel-ephant in the room is the big problem that few people want to address directly in financial processes because it can be so big. We tend to want to focus on the strategic core systems, and forget how many business-critical processes are actually dependent on Excel.

Or in other words: The widespread business reliance on Excel raises multiple issues that often go unnoticed or unaddressed.

In effect, organisations often treat Excel as the main solution to anything data related. Have complex data? Put it in Excel. Need a quick report? Excel. Want a makeshift database even? Excel again.

This leads to enterprises stitching together colossal patchwork quilts of spreadsheets. One error, a single mis-entered figure, or a formula gone awry, and suddenly the business is losing money or making critical decisions based on incorrect data. That is the operational risk of Excel.

This article outlines how a modern solution, might both mitigate the operational risk of Excel and leverage its many capabilities. I will first outline the issue and the extent of its impact in financial services. I’ll then present a software called Coherent Spark, which offers a new approach to the problem, and finish on the opportunities that the Spark solution provides to organisations.

?

Excel is the most popular spreadsheet software in the world; In fact, it’s the single most used business software package in the world. It’s estimated that Excel is used by a billion people, with at least half a billion actively using Excel in the workplace today.

The key stat relevant to operational risk is that about 90% of spreadsheets contain at least 1% of errors in their formulas – a figure that is aggregated from various Excel auditing studies.

One famous event showing an impact of spreadsheet error was the London Whale incident in 2012, when errors in workbooks that were calculating Value at Risk played a part in $6 billion of corresponding trading losses.

There are numerous publicised examples of the consequences of spreadsheets going wrong. For more information on some of those, the European Spreadsheet Risk Interest Group offers numerous case studies and research on spreadsheet errors and their impacts.

In terms of the scale of the issue that we’re addressing, it’s not an exaggeration to say that Excel underpins the entire financial system. Financial processes that often excel-based are in the above list, but the list is by no means exhaustive.

Some of these processes are ubiquitous across all organisations. For example, all established organisations undertake Financial Planning & Analysis (FP&A) as part of their international management. FP&A is budgeting, forecasting, financial planning, and analysing actual performance against those figures. Other financial processes include:

·???????? Group Consolidated Reporting: Group consolidated reports that are aggregated and adjusted from sub-reports, for example from divisions or geographically dispersed locations.

·???????? Financial Reporting: The creation of custom financial reports, including income statements, balance sheets, and cash flow statements.

·???????? Cash Flow Management: Modelling and analysing cash inflows and outflows to ensure liquidity and optimise cash management.

·???????? Performance Metrics: Calculating Key Performance Indicators (KPIs) like Earnings Before Interest and Tax (EBIT), Return on Investment (ROI), and others to gauge the financial performance of a company or project.

·???????? Sensitivity Analysis: Examining how changes in different variables can affect a particular outcome, such as how changes in interest rates might impact NPV.

·???????? Scenario Planning: Utilising Excel’s Scenario Manager to compare different financial scenarios side-by-side.

·???????? Foreign Exchange Risk Management: Monitoring and managing foreign exchange risk, particularly for businesses operating in multiple currencies.

·???????? Capital Budgeting: Analysing the profitability and risk of various investment opportunities to assist in the capital allocation decision-making process.

·???????? Investment Analysis: Tracking and analysing investment portfolios, including stocks, bonds, and other securities, using real-time data links.

·???????? Risk Analysis: Tools like Excel’s Data Analysis add-in can be used to carry out risk analysis, such as Monte Carlo simulations.

·???????? Valuation Models: Building valuation models like Discounted Cash Flow (DCF), Comparable Company Analysis (CCA), and Precedent Transactions is a common practice in Excel.

·???????? Time Value of Money Calculations: Functions like NPV (Net Present Value), IRR (Internal Rate of Return), and PMT (Payment) are used to understand the value of money over time, critical for investment decision-making.

·???????? Debt Scheduling: Creating amortisation schedules to plan and track loan payments, including principal and interest over time.

·???????? Lease Analysis: Evaluating and comparing various leasing options for assets like equipment or real estate.

·???????? Tax Planning: Compiling and analysing data related to taxation, including planning and compliance.

The range of excel operational risk is represented above. In total, it can be highly material, and is often focused on in detail by internal and external auditors, as well as regulators.

For the first, and most important point: In regulated industries, such as Financial Services, there needs to be transparency and auditability in all business processes. Excel has historically been viewed as a control weakness because embedded business logic, such as in formulae, can often be opaque with little documentation around the method of calculation or how it was derived.

Key person dependency: As companies grow or requirements change, and processes become more intricate, reliance on the makeshift power of Excel means an increasing volume of essential functions are often managed through labyrinthine spreadsheets that sometimes only the original author fully understands.

Human error is, for example, typing mistakes, incorrect formulas, or wrong inputs that can lead to incorrect conclusions.

Scalability issues: Models that are not designed with scalability in mind can become unwieldy and prone to errors as they grow.

Overly complex models with extensive formulae and links can be difficult to understand, manage, and update, increasing the likelihood of errors.

Without proper version control, multiple versions of a model can exist simultaneously, leading to confusion and potential use of outdated or incorrect data. In Excel, one user might have one version of the spreadsheet, while their colleague has another. Which is the most up-to-date? Whose has the errors? That’s a risk when there isn’t robust version control.

If a model lacks proper documentation, it can be challenging for others to understand how it works, leading to misuse or misinterpretation.

Incorrect or oversimplified assumptions in Excel can lead to models that do not accurately reflect the business position. In a strategic system there might be more control over the release and changes to the calculation models.

Incorrectly constructed or copied formulas can result in wrong calculations. There can be hardcoded values in formulas, that haven’t been updated, and are hard to find.

There can be linking errors in cells between different sheets or different workbooks that can lead to incorrect data being pulled through.

Circular references, where an output is recursively included as its input, can cause incorrect calculations and may be challenging to identify and resolve.

There are data integrity risks where errors in the underlying data, whether from manual entry or from external sources, can propagate through calculations.

Without proper backups and recovery processes, there's a risk of losing critical data and workflow within Excel files.

If sensitive information is handled within Excel without sufficient security controls, there may be risks associated with unauthorised access or data leakage.

There can be compatibility issues with different Excel versions, or from using Excel on different operating systems, that may lead to inconsistencies and errors in how the workbooks function.

Poorly written macros or custom VBA code can introduce errors or security risks.

Some models can be highly sensitive to small changes in input variables, leading to significant variations in output and potential misinterpretations. Where the importance of precision is material, the operational risks of Excel may be amplified.

Without sufficient built-in error checks and validations, incorrect data or calculations may go unnoticed in Excel.

It is important to appreciate from this comprehensive list that, as a whole, the risk of embedded business processes in Excel can be significant, as currently stands.

Coherent Spark is a proposed solution to mitigate Excel operational risk.?

It is a tool that decreases Excel operational risk, and also enables data and business logic from Excel to be published to the cloud, automatically creating APIs, that can feed into data pipelines, such as feeds to databases, machine learning, data analytics, or interactive dashboards.

I will outline the solution, then I’ll show you what is it and explain why the functionality is important. I’ll finish by summarising the Spark benefits and opportunities.

The traditional approach to mitigate Excel operational risk has been to map the business logic existing in a spreadsheet to the most appropriate existing IT core system and build out the equivalent capability within that system in a programming language such as Java, Python, C++ etc. The business logic and process flows then have to be supportable and modifiable within the core system, in response to rapidly changing business needs.

When a model is in Excel, a business user can make a change and see the impact of the modification almost immediately; however, if the business process is running in a core IT system, any change is subject to IT prioritisation, software development and testing times, and then alignment to controlled release schedules; in some organisations that can take weeks or months to complete – way longer than the business requires.

The traditional approach then does bring the control, testing, and auditability required, but at the expense of speed. However, it’s not uncommon after deployment to see a new version of the original spreadsheet appear somewhere within the business again – because when a requirement doesn’t quite fit into the core system, new spreadsheets and offline processes start popping up to deal with the immediate business needs.

The new approach outlined here is to use Coherent Spark with Excel. It gives Excel the needed version control, transparency, and auditability because each version is published to the cloud and auditable as at the date of upload. Business users are able to control their processes and publish to the cloud via an Excel add-in, which then feeds into data pipelines via automatically generated APIs. There’s no learning curve. Operational risk is reduced. Development timelines and resources aren’t required, potentially saving the business a significant amount of money. And changes can be made instantly by the business, as needed.

As far as most business users are concerned, Coherent Spark is an Excel add-in, called Spark Assistant. The Excel workbook is uploaded to the cloud via the add-in.

In this model we can see in Excel that the grey inputs result in the Premium as an output. All the data values are uploaded, as well as the business logic, comprised of the calculations and reference data that creates the output.

This is a very simple example, but potentially the data outputs could be entire data ranges or datasets that are uploaded to the cloud and available via API to feed into data pipelines. Coherent Spark can therefore be a way of uploading Excel data to the cloud, as well as publishing and version-controlling the business logic that was applied.

When uploading the workbook, a form shows the lines of codes automatically generated in the conversion and the equivalent development person months saved.?

The version, in this case 0.3.0, becomes effective by default as at the date of upload.

Above is the Coherent Spark cloud service, available in a browser. It represents the Excel workbook that has been published to the cloud, and automatically converted into code with an API, that other services can plug into.?

On the left-hand side of the screen are the inputs within the Excel model, which have been automatically converted to parameters of the newly created API. When clicking Submit for these inputs, on the right-hand side is the API response, the generated outputs (in this case just one) as per those input values.

What this simple model shows is that the business logic that was in the Excel workbook has been loaded to the cloud and converted to code. The business logic that was in Excel is the transformation from the inputs on the left to the outputs on the right in this cloud service.

The data and business logic within the Excel workbook is version-controlled within the cloud. A reviewer can therefore see what business model was applied to generate reported figures as an effective date, such as year-end or month-end.

If the calculations were changed and the workbook re-uploaded, there would be a new version uploaded with updated business logic. Prior versions can be auto-compared to the live model and restored where appropriate.

Above is a process overview of the solution.?

Individual Excel workbooks are no longer siloed. Business logic and data is published from any workbook to the cloud, and any other workbook with relevant permissions can download the data and business logic using the Coherent Spark add-in.

In terms of use cases, for example, the functionality could be very useful when consolidating workbooks, such as by Group Finance when consolidating reports from various locations. A group function could also centrally control the business logic applied by local teams, as it can be downloaded from the cloud to those teams.

Once the data is in cloud it can be connected to via the automatically generated API and feed into other applications, such as Power BI to create interactive dashboards.

This is a simplified diagram of the relative need for Coherent Spark. What it shows is that as business logic complexity increases, so does the need for Spark. So for example, a Monte Carlo simulation or a complicated risk model has more of a pressing need to be compiled and processed in the cloud than a relatively simple, straight-forward spreadsheet. Depending on the specific requirements, Excel remediation might include a data prep tool, such as Dataiku for heavy data processing.

Above is a list of Coherent Spark benefits.?

The main benefit is the reduction in Excel operational risk. Spark enables control of spreadsheet usage in the organisation with version control, and a searchable audit trail of every user interaction and underlying calculation. It also has additional functionality, such as data consistency analysis, large scale testbeds, and regression analysis for models that have been uploaded. In total, it provides enterprise controls for managing spreadsheets across the organisation and improves the quality, security, and compliance of a business process implementation.

Spark can serve as an interface for Excel to Cloud services, and thereby a conduit between business and IT. It can be an important intermediary between business and IT teams, swiftly transforming Excel workbooks into easily accessible, enterprise-grade APIs and functions. Those APIs, for example, can be wrapped as functions and callable from the cloud-data application Snowflake.

There are minimal technology resources required to implement a solution: business logic and data is seamlessly published to the cloud without requiring technology development resources.

There can be accelerated development cycles because solutions that once took months can now be completed in hours by users.

There’s also optimized model calculation speeds because calculation models converted to code run many times faster than Excel workbooks, leading? to potential performance gains of 100-1000 times at runtime. If the Excel model is complex and takes a long time to process, it very much makes sense for that model to be converted to code by Coherent Spark and compiled.

As outlined previously, business users can control and update their business logic, thereby empowering users to control their processes and use Excel more efficiently.

Business logic can be controlled and downloaded centrally. Often Excel workbooks proliferate with replicated formulae, worksheets, and copied versions. Spark, however, enables Excel formulae to be modularised and controlled as functions. These centrally controlled functions are called from Excel with the Spark Assistant add-in, ensuring business process consistency and reducing risks from spreadsheet replication.

With the solution, there is in effect Excel end-to-end process automation, because If the Excel inputs are mapped to Excel outputs by formulae, then the Excel process is entirely automated by the refresh of the formulae calculation.

And, of course, there is version control because Spark maintains a version history of the Excel workbook in the cloud, making version comparison simple and enabling rollbacks to prior versions if necessary.

The solution is robust to workbook structural changes because the API offers access to the defined Excel outputs (“Xoutput_” named ranges within Excel), independent of any modifications to the workbook’s structure, such as insertion of columns or changing the order of columns.

Spark offers stability and continuity because business users continue using the application in which they are skilled. Excel as an application is evolving, and Coherent Spark is a next evolutionary step in that process.

There is auditability and documentation. With each change automatically tracked and dated, it provides continuous documentation, simplifying the auditing process.

And lastly, there is enterprise-grade security and scalability. There is cloud service security in place; and cloud data and compute can be easily scaled up or down with demand.

Above are the key takeaways.?

The main point is: Coherent Spark is a solution that is relevant to most organisations because most organisations will be exposed to Excel operational risk, which the Coherent Spark software reduces.

Coherent is a world leader in the “Excel to code” market. The company is currently ahead of the curve in the key functionality that enables Excel workbooks to be converted into APIs.

The solution is particularly relevant to the management of EUC portfolios; it should improve business processing times, which can be very relevant in Finance at month-end; it can have big cost and time savings, as technology development is not required; and it can provide a data feed into the cloud where there can a be consolidation of the data for cloud-based services. This, in turn, can be a stepping-stone to further cloud-based end-to-end solutions.

Almost every organisation uses Excel. And this solution is, in effect, a next evolutionary step for the application.