We meet EMS companies every day who tell us about the creative ways they’ve used Excel to organize their business processes. One of the most common ways that EMS companies currently do labor calculations is “through a spreadsheet that has evolved over many, many years.” At CalcuQuote, we believe Excel is one of the most powerful and flexible software ever created for businesses. But like anything, even if Excel can do anything, that doesn’t mean it should.
Here are five signs that will let you know you need to consider an alternative to your Excel spreadsheet.
It’s easy to develop tunnel vision about how Excel will solve all of your problems…until you see what else is out there. If you’ve got a spreadsheet reaching increasing levels of complexity, formulas breaking down and macros doing fairly complex actions, you are stretching Excel to its limits. At some point, even if you are the in-house excel expert that can always figure out how Excel can do what you need, you have to start considering long-term sustainability for your organization. Excel was designed for simple datasets, but if you’re trying to use it for more than that, there’s a good chance that it will error out and the local Excel guru won’t be immediately around to fix it.
Excel spreadsheets are great when you’re the only person who has to deal with the file. You know when you mess up a formula, you can access it whenever you want, you can make sure the file doesn’t get overwritten, etc. But what about when you have to collaborate with others? Especially for Excel files that are on shared drives, chances are you have been asked to open in “Read-Only” mode. With a web application like CalcuQuote, you can effectively collaborate on the same data set, enable version control, set validations, configure workflows, and more. QuoteCQ allows multiple people to simultaneously engage in your Request for Quote process with their own login and password. Multiple people could even simultaneously work on pricing a single bill of material so that rush quotes can actually be rushed. CalcuQuote even allows you to give limited access to customers and suppliers to update the BOM and get approvals on alternates, without sharing other confidential data.
A single excel spreadsheet equates to one table in a database. You can relate datasets to each other within excel using the ever-infamous VLOOKUP formulas. But does that mean you should? The answer to this, like most other things, is it depends. As your formulas become increasingly complex and tables increasingly interlinked, the chances of a tucked-away error blowing up the entire spreadsheet increase while the performance of your spreadsheet continues to decrease. This type of data linking can also make it impossibly difficult to manage consistent data formatting and make updates. By comparison, QuoteCQ has over 50 different linked tables and thousands of columns with millions of rows for even our smallest customers. However, the database can perform lookups, calculations, and compile graphs much faster and with greater consistency than a formula-driven spreadsheet. From a user’s perspective, they just see well-organized data at their finger as and when they need it.
Security within a spreadsheet, especially when multiple people are accessing the same file is very difficult to manage. You can “protect” cells and limit functionality, you can require passwords to unlock cells, and you can set basic validation rules. But all of that is easy to override in most cases with a simple copy and paste. If you want true controls without limiting usability, a web application is the way to go. Most enterprise-level web applications, including QuoteCQ, come with a suite of security features including secure login, field-level security, and complex logic for validating entries. The front-end web page controls what kind of data is allowed to go into the database, giving you clean predictable datasets that you can then analyze for decision making.
If you’ve tried to organize your numerous excel spreadsheets with a naming convention, you already know how painful that process can be as more people get involved. A well-organized, database-driven application can help you organize your spreadsheet data and files in a consistent, easily searchable way. The biggest benefit of using a database to organize individual spreadsheets is that you can analyze across the datasets. For example, if you have a labor model that begins with an Excel template, and you create a new Excel file for each quote that you process, how do you spot trends across quotes? With a relational database approach, you can view each quote individually, or you can examine a single slice of data within every quote you’ve ever done. This can be very valuable information for answering questions like “What is the correlation between customer X, winning a quote, and the price of my SMT labor time?”