Every CFO seems to wonder why they spend millions on ERP upgrades yet critical decisions seem to boil down to spreadsheets. In fact, many times, even large companies can be highly dependent on a single resource with a complex series of spreadsheets for a sales or budget forecast, cost reduction project details, and backlog status and follow-up. So, why does this happen? And how do we resolve the issue to better leverage our ERP and related technologies investments?
Why Do Companies Get Stuck in Spreadsheets?
Although it is often stated that people don’t like change, and so they hang on to spreadsheets, it is not one of the most frequent reasons we find when working with clients. Most people hanging on to their spreadsheets aren’t opposed to using other tools. As our consulting mentor says, “people don’t fear change; they fear the ambiguity of the journey”. The same is true with spreadsheets. If they cannot figure out how to serve customers and/or solve issues with the ERP system, they will go to something they are confident in – their spreadsheet!
In some situations, the company does not have a modern ERP system, and they cannot get the information directly from the ERP system. Thus, spreadsheets become vital to success. Leadership might get the information required from the team and so he/she becomes reluctant to take the plunge with an ERP upgrade as it is not a small or minor investment. However, we would be remiss if we didn’t mention that companies will not be successful long-term without a modern ERP system because old ERP systems are no longer are sufficient to support advanced customer requirements, scalability, and sustainability for a growing and evolving company.
Sometimes, I.T. and/or your ERP resources will not prioritize the report and/or functionality required to make the job easier to deliver the outcome expected. Similar to one of the key reasons ERP fails to deliver results, the theoretical “best practice” on how to use ERP will not deliver whatever the team member knows is required to serve customers, manage cost, and mitigate risk on a day-to-day basis. Unfortunately, they don’t know what to do to upgrade their use of the system, what data to define, and/or what setups to complete. It is simply easier to use the trusted spreadsheet. Once the company finally prioritizes and develops functionality that “works” and could replace the spreadsheet, the team can become resistant to rocking the boat with what is working since they know they are responsible to deliver results and are afraid of the kinks in the process as issues get ironed out.
Another reason resources jump to spreadsheets is when there are multiple systems (such as CRM, ERP, advanced planning, e-commerce, etc.) that are not connected. If you are dependent on understanding and/or connecting the data to make decisions, you are likely to rely on spreadsheets to connect and analyze the information. In the worst case scenario, folks are re-keying information into multiple places to perform the job. Unfortunately, this is not uncommon.
Lastly for the items that pop to mind, people might use a spreadsheet because it is a superior tool to perform the task. Spreadsheets have a purpose. In fact, we have worked with multiple companies that make a big deal of getting out of spreadsheets. They had folks who hid their spreadsheets on the side so that they could do their job. How crazy is that! As tools evolve, business intelligence systems such as Microsoft Power BI take over several of the powerful uses of spreadsheets; however, Power BI won’t always do the calculations required for a one-time analysis to make a quick decision. In fact, spreadsheets can be a great way to develop analysis and pilot results. Once the formulas are ironed out, they can be developed in Power Queries or another tool. Thus, don’t fall into the trap of too many spreadsheets or too few spreadsheets; instead, use common sense.
Case Study: Spreadsheets Holding the Bag
In an industrial equipment manufacturer, spreadsheets were left holding the bag with vital forecast information to support private equity expectations. In fact, one financial resource was overloaded with data yet solely responsible for figuring out the forecast as the process was so complex and spreadsheet laden that it was impossible to repeat. Thus, the leadership team brought on consultants to upgrade processes, dig through the data and make the process sustainable.
In this custom, engineer-to-order environment, the quotes and CRM information were in one system and the orders were in another ERP system. Both systems were robust, tier 1 ERP systems, but the information did not connect. Thus, Sales had insight into what was coming down the pike, but Operations had no visibility. In fact, even once the quote turned into an order and was in the operational system, it still had to be engineered (which could take from a few weeks to several months) before Operations had visibility to confirmed demand. Thus, they were running largely blind! From a detailed point-of-view, it required someone with intimate knowledge of products and configurations to plan, backed by someone with a good strategic and business sense to help navigate. Although they did an awesome job with execution and jumping through hoops to provide service to customers and manage costs, the process was not scalable and sustainable.
The team started by working on several parallel paths simultaneously. To achieve short-term relief, the team looked for a way to connect the quote data with the sales order data. There were no fields to easily connect the data, and so they added a field to connect the data. Of course, it is never that easy when dealing with real life situations. Thus, they developed solutions for order revisions, quote statuses, and other basic requirements. Beyond data basics, the two systems had to be connected. That sounds far simpler than it is to accomplish with most clients depending on business decisions, setups, I.T. protocols, and other factors.
Thus, we decided to connect the information in a simplified data warehouse so that quick wins could be achieved while the longer term, robust solution was put in place. We also added a vehicle for key input into the model so that the results would continue to be directionally-correct. This allowed us to replace the spreadsheets dependent solely on one person with a more robust and predictable process. With that said, if it was dictated to get rid of spreadsheets, they simply would not have a forecast for several months while a process was developed, and the process could not be calibrated and tested to ensure directionally-correct results.
Beyond the basics, the team had to develop business process upgrades and new protocols for data to align the datasets for critical information. This work was a prerequisite for combining and/or upgrading systems down-the-line. For example, they designed configuration strings to identify a base “model” or grouping of information so that they could provide key information about orders and quotes upfront to the MRP planning engine, capacity analyses, and for scheduling optimization. They stored these configuration strings in both systems. The team rolled out quick wins to gain visibility while continuing to develop a full toolset to support the order fulfillment process and work through data integrity issues. Thus, after the 80/20 was achieved, they went back to reevaluate, standardize and simplify the design so that it would be more scalable and sustainable. Thus, it is set up to work with Microsoft Power Queries fueling Microsoft Power BI and also prepared for the next ERP upgrade or for the two systems to be connected directly.
Path Forward
Before jumping to conclusions that your team members are fearful of change or dictating that all spreadsheets must be gone by x date, find out what’s going on. Why are your teams using spreadsheets for certain tasks that seems like they could be automated quite easily? Also, don’t become overzealous and automation happy. We have seen clients that spend double the money to automate tasks to deliver the same results when the old process was sufficient, scalable, and sustainable. On the other hand, don’t find it acceptable to be resistant to automate because your best resources are worried the results might not be perfect on the first try. Push for automating repeatable tasks, ones that can become a good candidate for predictive analytics, and ones that will free up time for critical resources. You are likely to have to push back even with your best people. What is the bottom line? Use common sense.
Did you like this article? Continue reading on this topic:
The Value of Data in Fueling Profitable Growth