An Application Development Team Case Study:
Accounts Control Spreadsheet Tool for a Hotel Chain: Microsoft Excel and Sage Financials
Technology: Microsoft Excel
Client: A large hotel chain with a wide variety of properties and amenities
Year: 2020
Our client was putting together a management pack of tools and processes that they were rolling out across all their hotels in their Irish operation. As part of this suite of tools was the envisaged Account Control Spreadsheet Tool.
Their central accounts team had put together the core of a very powerful custom made accounting tool, constructed within a MS Excel spreadsheet file. This file was pulling data from a huge number of different resources, such as account extracts from Sage, Bank Statements, Trial Balances and Credit Card statements. They were then using this Control Spreadsheet Tool to help both report on and to balance their accounts on both a daily and monthly bases. However, they faced a huge problem with the file! Although it was operational from a functional perspective it was not usable from a user’s point of view!
The construction of the file was so complex that there were left with thousands of external links to other files. These links were both pulling in data directly into cells but also used as part of formula and look-ups. The result of all this was a MS Excel file that was unmanageable from a user’s perspective as it would take an age just to load. Every time Excel then needed to perform a calculation (which is very often) it would hang. This waiting time could also end up being multiplied further in instances where other MS Excel files may have been open at the same time.
In delivering a solution Olas not only addressed these key performance issues but also brought some great extra benefits to the project. Transforming it from a spreadsheet based file to that of a fully-fledged MS Excel based application.
The following is a summary of tasks Olas sought to complete as part of this project:
- Remove the need for links to external resources
- Remove the need for links to external resources
- Remove the need for links to external resources
- Reduce and improve formula where possible
- Improve overall performance and load times
- Improve overall performance and load times
- Automatically creating new journals, to be posted back up to Sage
- Add Worksheet and Workbook Protection
- Create an admin section for managing settings and drop-downs
- Introduce a user interface and menu bars to make the navigating the many worksheets in the file a much more manageable task.
- Add a clean, unified colour scheme and look and feel
To achieve these goals, Olas sought to take an inventive approach which made use of a daily import process through VBA Coding, which in only a matter of seconds would bulk import data from 64 external files in a single go. Day sheets would then be created from hidden template sheets contained in the file for each day in the month. When rolling over to the next day, all calculations from the previous day would be stored as values and the 64 source data import sheets would be cleared out and replaced with the new day’s data. This meant that at any one time the file would only contain raw source data and formula relating to just the current day; thus massively reducing the amount of formula required in the file at any one time. This approach proved extremely successful with the performance of the file going from frustratingly unusable to incredibly quick.
