Is Your Enterprise Suffering from “Excel Hell”?Posted by Itay Herskovits on Jan 29, 2015
First International Bank of Israel, FIBI, built out an internal reporting spreadsheet that quickly became a powerful and necessary tracking tool for the organization. However, as the importance of the data it contained grew, so did the complexity of the processes and procedures built around the core spreadsheet. Before long, the Excel workbook contained over a dozen spreadsheets, each with custom rules for validation and workflows that triggered manually based on the data it contained. To quote an analyst, it was “Excel Hell.”
Backand was able to help the bank find their way back to the light. By porting the spreadsheet into a normalized database and building business rules into an AngularJS application, Backand turned what had been a major pain point for the bank into a great asset for reporting on their internal data. Through agile, iterative development Backand took the organization from an Excel workbook to a fully-featured web application, complete with reporting dashboards and SAP/Oracle integration. Read the case study below and find out how Backand can help you escape “Excel Hell.”
Escaping Excel Hell
Microsoft Excel is a common tool used in place of custom applications in many organizations. However, as organization processes grow, so does the complexity of the spreadsheets in the workbook. Eventually what began as a simple data tracking tool can balloon into a process-laden monstrosity, complete with validation that rivals the most complex banking software. In this case study we’ll explore how Backand helped First International Bank of Israel, FIBI, escape from their private “Excel Hell” with a purpose-built enterprise back-office application driven by Backand’s open-source AngularJS framework.
FIBI had an issue with one of their internal data reporting tools. They had developed an internal spreadsheet that was used for basic data tracking and reporting of branch information. As the data it contained grew in importance, new features were continually added to the spreadsheet. First, simple reporting was added to aid in quick assessment of the underlying data. This gave way to additional controls on the underlying data. Then validations were added. “Before long,” one of the analysts noted, “we had five or six different versions of this spreadsheet floating around the organization. We spent a lot of time making changes, then remaking them in the latest version of the spreadsheet, only to have a different version come along that someone had made changes to that they forgot to publicize. It was like being in Excel Hell.”
The bank quickly realized things were getting out of hand, but they didn’t have the internal capabilities to create a purpose-built application to replace the spreadsheet. As an alternative, they built policies and procedures around the spreadsheet. Changes needed to be approved before they could be made, and the changes had to be thoroughly tested before being released. The spreadsheet was stored in a central location, and was tracked like a book at a library. Before long, the process around the spreadsheet had built up so much that simply accessing it became a chore. The spreadsheet, in other words, had evolved into an enterprise application – complete with access restriction and change control.
Backand Leads the Way
The bank, recognizing that they were out of their depth, brought in Backand to address their concerns. By analyzing their situation Backand was able to quickly outline a solution to FIBI’s Excel woes. Backand began by utilizing their framework to parse the Excel spreadsheet into a normalized database – a storage medium that was able to reduce redundancies in the underlying spreadsheet, resulting in a smaller storage footprint. Backand then worked to implement all of the validations that had previously been built into the spreadsheet, securing the data and maintaining data integrity.
Once the basic data and the required validations were in place, Backand was able to go to work configuring the application. By implementing more complex validations, Backand emulated the processes that had arisen in the bank as the importance of the spreadsheet had grown, and added styling and configuration to streamline the look and feel of the resulting application. Before long, Backand was able to present an application driven by Backand’s back-office that covered all of the data stored in the spreadsheet, ensuring that data quality was maintained through both server and client-side validation. “We had handed Backand a bloated, overly-complex Excel workbook,” one of the internal auditors noted, “and Backand was able to turn it into this flexible, secure web application that included all of the data and processes that had been coded into this spreadsheet.”
Implementing a workflow
The next task arose from the process around the original spreadsheet. Many of the entries in the data-tracking spreadsheet would spawn workflows – a series of steps and verifications that were required to be performed based on the information in the spreadsheet. Where manual processes had been previously built over the spreadsheet’s data, Backand was able to fully automate the workflow and reporting functionality. For example, one sheet in the Excel spreadsheet contained a status column that required additional research by internal auditors. As this research progressed, the value in the status column would change to reflect the ongoing process, triggering activities in other departments based on the point in the process being encountered.
“Keeping tabs on this process was a major chore,” a lead auditor noted. “We had to spend a lot of time emailing back and forth for status, finding out who was tasked with a given step and what its status was.” Backand saw this pain point, and worked with the bank to encode their workflows into the application. Before long Backand was able to present a new version of the application that handled the entire process automatically. The application would send off emails to the appropriate parties when a status column held a certain value, was able to route tasks among various individuals responsible for the resulting actions, and the application integrated with SAP and Oracle to keep other internal processes up-to-date. What had once been an Excel workbook and a series of guidelines had become a secured internal web application, with interactions governed by Backend’s software. Users could now edit the data concurrently, removing the waiting-on-updates that had resulted in a number of delays when using the prior spreadsheet, and automated a lot of the processes that had been built around this vital internal data.
Once the basic spreadsheet port was completed, the bank was able to begin working with the tools that Backand provides in earnest. They eaasily created several custom dashboarding pages – summary pages with graphs and charts summarizing the underlying data. The bank then created a form generator that could be used to greatly reduce the time required to enter data into the application. In addition, FIBI focused on styling, changing the layout and coloring to match that of the subsidiary brand bank using the application.
Using Backand’s Angular back office, the bank was also able to make more subtle changes, such as coloring of individual records based on record status. Through the open-source nature of Angular, the administrators of the application are able to make a number of changes by simply writing their own modifications, or even just using any of the many Bootstrap templates that interact well with AngularJS.
While FIBI had backed themselves into a corner with their internal spreadsheet usage, they still had several business needs that were being met by the unnecessarily obtuse process. Backand was able to reproduce all of the requirements built up around the tracking spreadsheet, converting a closely-guarded piece of vital business reporting into a full-featured database application with none of the arbitrary restrictions that had kept the earlier spreadsheet isolated and carefully curated. By utilizing the robust Angular back office back end, Backand was able to guide the bank out of “Excel Hell” and into the Angular-driven world, providing a web application that was more than the sum of its spreadsheet origins.
Learn how Backand can help your enterprise build Angular apps, both new and ones that are migrating. – Get started now.