A number of our engagements originated with a spreadsheet that had become a challenge to use and maintain in our clients’ day-to-day operations. When originally faced with a new process or program, it’s typical to introduce a spreadsheet to assist in its management. In fact, if you were to reach out to us at the very beginning stages of a new process or a new set of data, then we would typically recommend starting with a spreadsheet: they are a powerful tool to get a solution off the ground quickly and can be managed by those who will actually use it (often a key exercise for those involved to understand and refine the process itself). Spreadsheets are easy to understand and manipulate by technical and non-technical staff alike, they are easy to modify making them adaptable to changes in policy and procedure, and they are trusted by users since they have full visibility into how the spreadsheet works.
However, as the amount of data they manage increase as well as the complexity of the process they handle, they can quickly become a challenge to maintain and tedious to work with. As a platform, their weaknesses start to show when you need to provide centralized access to their data, to extract reports from the data, or to enforce more stringent controls to avoid data loss.
Once a spreadsheet starts to show deficiencies, it may be time to migrate it to a platform that better suits the needs of the domain. In this article we will overview your options.
Enhance the Spreadsheet with Macros
This option builds on the powerful scripting capabilities that exist both in Excel (Visual Basic for Applications) and Google Sheets (Apps script) in order to provide automation or capture advanced business logic. This option requires less analysis, planning, and development, plus it allows you to preserve all the benefits of having a spreadsheet. However, it has a strong drawback too: you’re still using a spreadsheet. The limitations of the medium remain, they are only being patched. If there are a number of features to add, then you may be facing even more effort in the long-run and the solution can become an even bigger headache. If the problem you are facing is small in scope (e.g., you would like to add basic error checks, synchronize with a web service/database, or perform basic reporting), then enhancing the spreadsheet with macros is a viable option to consider. For anything more complicated you risk that the time invested in adding macros will become a sunk cost when you may need to replace the spreadsheet down the road.
Utilize a Rapid Application Development Platform
A Rapid Application Development (RAD) platform such as Microsoft Access, FileMaker and Caspio can save you development effort particularly if you have someone on staff experienced in one of these platforms. These solutions make assumptions about the environment in which they are used to reduce the amount of development required. This has a noticeable benefit when your application fits neatly within the assumptions made by the platform, typically record management.
However, this benefit becomes less pronounced as complexity is added to the solution: access control, auditing, process controls, triggers and automation, complex business rules, heavy load, integration with other systems, and mobile and web access will likely require more extensive development work. As these extra features are required, these platforms become less of a rapid development environment, but more of a platform for custom software development (see below). Once you reach this point, more flexible development platforms (e.g., .NET, PHP, Java, etc.) may yield a lower total cost of ownership depending on the project. Therefore, it will be important to understand the future complexity of the project to indeed make sure that you will be saving with this option.
Deploy a Boxed Solution
If the domain or process that the spreadsheet is handling is common within your industry, it is likely that a boxed solution will exist. If your process is relatively consistent with others in your industry (or you’re looking to make it more standardized), then this option is often the most cost effective. However, where the domain is specific, or where the boxed solution contains more features than you need, there is often a business case to evaluate this option against a custom solution. Also beware that the provider of a boxed solution may be unable or unwilling to make the customizations you require for your operations; or conversely, in certain domains a boxed solution may require a significant amount of extra customization before it is able to work for your business. Therefore, when comparing options it will be important to ensure that the total cost of the project is considered.
Develop a Custom Solution
A custom solution allows you to build something that will fit your organization’s needs precisely. The software will be developed based on feedback from stakeholders which will increase trust in and efficiency of the final solution. This option works especially well when you have staff members invested in the day-to-day operation of the spreadsheet who would like to be actively involved in the construction of a better solution.
There are two major downsides to this approach, however. The first is the cost: with boxed software, the company that has built the software is splitting their investment across their customer base; with custom software, you will be the sole investor in the project. The second downside is the risk: if your needs or the underlying technology evolve, you will be responsible for any over-runs or changes that may be required. For example, if you invested in a BlackBerry app five years ago, you may now need to invest in an Android, iPhone or web-based app to replace it. Consequently, it will be important to understand these risks, and the means by which they can be mitigated, so you can effectively compare custom software against the other options.
How Dattivo can Help
Our approach is to take time upfront to understand the entire problem domain: the processes, nuances, and how the data are currently structured. This allows us to model the pros and cons of the four options mentioned above so that you can make an informed decision on your investment. For example, after analyzing the needs of a project management consulting firm, it was determined that based on the deficiencies they were facing with Excel, adding macros to their current spreadsheet would suffice to solve their problems. However, in our commodity value reporting project, there were multiple needs that made it clear that custom software was required: multiple reports had to be generated on a regular basis, staff needed to remotely access the data, and data had to be regularly shared with other systems.
Our recommendation is to always start from the problem, model the domain and weigh the pros and cons of the options going forward. We view software as infrastructure for your business that needs to be considered over its entire lifespan in order to arrive at the best solution to satisfy the needs of your organization.
Kevin is a software engineer and partner with Dattivo Software in London, Ontario. With a formal background in software engineering, he designs, develops and implements software solutions. His interests include how software supports business operations, model-driven architectures and design patterns. He can be reached at firstname.lastname@example.org.