Reason #4 Making key decisions by modelling the supply chain in Excel
Over the years, working for and with numerous manufacturing companies, I’ve seen many supply chain practices that cost companies money. Over the next several weeks, I’ll outline these issues and discuss some ideas around how to avoid these practices. You can find the previous posts here:
- Reason #1: Offshoring without getting the full picture
- Reason #2 Poorly executed or non-existent sales and operations planning
- Reason #3 Not having end-to-end supply chain visibility
In my career, I’ve had the pleasure of working with several top tier supply chain companies. Companies that are household names. Companies that have been in business for decades. Companies worth billions of dollars. Companies that are forced to use Excel to manage large swaths of their advanced supply chain planning. Companies that are starting to realize that while Excel is a powerful tool and can be used for lots of things, it isn’t the tool to use to run your supply chain.
Excel excels (if you’ll pardon the pun) at many things. But modelling complex supply chain relationships isn’t one of them. There are many issues with using excel that have been written about numerous times in this blog. A sampling are here, and here.
I can briefly summarize the main points;
Companies use Excel because their traditional planning systems don’t allow them to view and understand aggregate data and more importantly, don’t allow them to effectively react quickly to change. However, because people need this information and because people (especially those in supply chain) are very smart and come up with ingenious ways to solve problems, they extract data from their ERP systems and build complex models in Excel.
So we understand why companies turn to Excel; they can’t get what they need from ERP. Now let’s look at why Excel shouldn’t be used to run your supply chain.
Errors – Excel is a free form modelling tool – which means anyone can build a spreadsheet for just about anything. Many of these spreadsheets are not validated or tested, meaning that the model is only as good as the persons that create the model. Millions of dollars have been lost to Excel errors.
Everyone has their own version – While you can password protect and lockdown Excel spreadsheets it is difficult to do effectively and many companies simply don’t do it. This means that often there are multiple copies of the same spreadsheet, all slightly different. I’ve been in meetings where what appears to be the same spreadsheet tell different tales because someone made a data or formula change. Eventually everyone has their own version and are all going off in different directions.
Excel is not supply chain software – it doesn’t matter how good your Excel model is, you simply cannot model the complexity of the supply chain in Excel. This means that the best you can do is build an approximation of your supply chain in Excel. As we know, in supply chain, details do matter and the small detail that is approximated in your model might be the detail that costs you.
So if ERP can’t do it and Excel isn’t the tool, what tool can help you make supply chain decisions? This tool needs to have the following characteristics;
End to end visibility – To make supply chain decisions, you need to have visibility across your supply chain. You need to be able to see where inventory exists, what capacity is available and what the issues are.
Simulation – The ability to create a scenario, make a change and instantly see the impact of what that change means that you can try things out and know with confidence that it’s going to work.
Full supply chain analytic model – Supply chain planning is very complex and while most vendors have similar basic logic there are many differences between systems, even within implementations of a given system. To effectively model this logic, you need a tool that can simultaneously model the supply chain logic from all these different systems.
Collaboration – No one person has knowledge of the entire supply chain in their head. You need to be able to work with others to resolve complex issues. So an effective supply chain decision tool will need to allow you to quickly identify who you need to work with and then share your scenario with those people.
How do you make your major supply chain decisions? Comment back and let us know!