Using Spreadsheets for Supply Chain Management?
Updated: Dec 2, 2021
In business (and software) circles, there is a decades-long love-hate relationship with spreadsheets. It’s no surprise considering their widespread use - globally there are approximately over 1 billion spreadsheets users (MS Excel and Google Sheets combined), with MS Excel accounting for around 800 million.
The program was released in 1985. and since then it grew into possibly the most important software used in business. It was created as a personal productivity app, but it was made flexible enough and robust you could say it holds the global finances together.
During the course of almost 4 decades, there have been numerous reported (and even more unreported) cases when spreadsheets caused serious problems and financial losses. The biggest reported financial loss attributed to spreadsheet error was JPM’s London Whale which cost the company $6 billion. It’s no surprise considering almost 90% of all spreadsheets contain errors.
Spreadsheets in SCM?
Supply Chain Management is an interesting domain for several reasons: it has an absolutely crucial role in everyday operations and it’s key for revenue growth - 79% of companies with high-performing supply chains achieve revenue growth superior to the average within their industries.
There are many people and departments involved in the process, they are usually in different locations, they need to make numerous assumptions during forecasting and they need to be agile and synchronized with each other and the market.
Two-thirds of companies consider Excel a supply chain system, but can spreadsheets support such an important and complex process? Below is a breakdown of the positive and negative aspects of spreadsheets in the context of SCM.
It’s widespread and easy to use
1.2 billion MS Office users in the world have access to Excel. It’s taught in schools and almost everyone has a basic understanding of how it works. If you ever participated in a software implementation project you’ll know how important this is. With some basic math and business knowledge, you can create a planning spreadsheet for your company, share it with your colleagues, and establish a basic planning process.
Flexibility and Expressiveness
As your company grows you can easily add new products, production facilities, or markets to the process. Add a new row, column, or sheet, connect it with the existing model and you’ve got a functional planning tool. Everyone involved can use the data for visualization, reports and support basic decision-making.
Feeling of Control
With spreadsheets, you have full control and ownership of the model. You can do any ad-hoc analysis or quick calculations using different data sources. It might be manual and error-prone, but you can get some basic results without lengthy meetings with IT and consultants. With enterprise solutions, almost any adjustment outside the agreed scope will cost you time and money, and the end result is always more rigid than your spreadsheet model.
(Perceived) Low cost
For a new company, setting up a planning process needs to be fast and cheap. Spreadsheets cost is practically non-existent since you probably already have a business package that includes Excel or Google Sheets. Both enterprise solutions and modern SaaS applications will have a significantly higher price point, making spreadsheets the first planning tool for nearly all companies today.
The Bad and the Ugly
The trouble with data
As time goes on (and your business grows) you will accumulate more data. This will slow down spreadsheet performance significantly and increase the chance of data corruption. Don’t forget there is a limit of approximately 1 million rows in MS Excel. You could potentially store data in separate sheets but this option will complicate data management even further.
A spreadsheet is not a database, and with the accumulation of data, it becomes more obvious. It’s hard to navigate and filter data, redundancies increase, and current access and error control features jeopardize data integrity.
Collaboration and Process Management
Apart from the data issues, growth adds another layer of complexity to your supply chain management - more team members that need to collaborate and give their input. In the beginning usually, the CEO and a few team members create top-down plans and forecasts, mostly relying on experience, and for a while, they can do it pretty well.
With each new product, sales channel, or market the process becomes more complex and requires a combination of top-down and bottom-up inputs with continuous updates throughout the organization. With spreadsheets, SCM is highly manual and error-prone and the process itself is managed, well, through dozens of emails.
Security and Auditing
Again, with team growth, security requirements become more strict and access to data needs to be limited. You can bypass these issues by making a copy of your forecasting file and sending it to every account or category manager separately (and this is what a lot of businesses do). When everybody sends their input you need to go through a tedious process of stitching everything together in one file. Even if you don’t make any mistakes in this process further iterations and changes in numbers are not automatically visible and require more manual work.
The alternative is to have everyone working on the same file, which can lead to security issues and more complicated input control. There are some basic auditing functionalities but it’s far from the user-friendly and secure environment you need. Ideally, you should be able to assign rights according to your team (or any other) structure, have team leaders verify the inputs which can then be automatically rolled up once they are approved.
Real-time Decision Making
Combining the three points described above and you get siloed information with very limited potential for real-time decision making. Everyone from demand planners to inventory managers has their own spreadsheet and it’s very complicated to consolidate everything in one place and on time. If not detected on time, swift market changes, like demand increase can cause understock of products or components resulting in lost sales, overpaying for inventory, higher shipping costs, or decrease in customer loyalty.
Scenario planning is a great example of an important planning process that is extremely clunky to perform in spreadsheets. If you’re using a bottom-up approach every department should add at least two versions to the plan (best and worst case). This means two times more data and all related risks of manual entry. Comparing the version across products or departments is, again, a manual and time-consuming task.
Applying a top-down approach is even harder, it can take weeks just to get a baseline plan and even longer to create additional versions. This beats the purpose of scenario planning which should be fast and connected with the entire model to support decision making.
In reality, every business decision you make has consequences in some other areas - introducing a new product will probably lead to cannibalization, but the detailed impact remains unknown usually until you get the actuals. In a siloed spreadsheet model the best you can do is guess and conclude from experience what might happen.
Take demand planning for example; during the process, a country manager often needs to view data from different perspectives (i.e. sales by regions, by product category or sales reps, etc.) and use that perspective to adjust the forecast. Increasing numbers on the higher level, or adding parameters like discounts, should be automatically calculated and distributed on lower levels of the hierarchy. Combining this with automated forecasts based on historical data, statistical methods, and user input is an impossible task for spreadsheets with multiple users and data sources.
Farseer tree view interface for multi-dimensional planning
With all this in mind, how do you know when it’s time to quit spreadsheets in SCM? Here are a few general recommendations:
You have multiple data sources like ERP, production planning software, CRM, etc.
You have multiple departments and team members participating in the process
You’re not sure if you can rely on data in your spreadsheets
It takes a lot of time to gather the information, often longer than you spend analyzing the impact of data and making decisions
You’re planning to introduce new products, expand to new markets or hire more staff
You want to automate your forecasting using historical data, statistical methods, and user inputs
Farseer dramatically shortens planning cycles, improves planning accuracy and eliminates manual work. We combine the power of AI with human knowledge, for the most accurate operations and financial plan.
If you would like to maximize revenue, optimize supply and improve your Cash-Flow, sign up for Farseer Demand Planning now.