Your supply chain is costing you money - Reason #4 Making key decisions by modelling the supply chain in Excel

addtoany linkedin

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:

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!  


David Hardman
- September 24, 2014 at 1:02pm
"it doesn’t matter how good your Excel model is, you simply cannot model the complexity of the supply chain in Excel."

That's the bottom line. At the end of the day Excel is a spreadsheet, albeit a very fancy one. But it can only do what you know how to do...there is very little that doesn't require manual effort and input. And people make plenty of mistakes. Even on misentry can throw off the whole spreadsheet.
John Westerveld
- September 24, 2014 at 2:55pm
Thanks David - I couldn't agree more!

One thought on the manual input issue you raised. Excel can be a problem in two ways - 1) Errors in the logic of the spreadsheet (this is particularly nasty) and 2) Errors in the data that drives the spreadsheet. While this is absolutely an issue for Excel, it is also an issue in dedicated systems as well.

There will be a post coming out in a couple weeks talking about the costs and risks associated with erroneous supply chain data. Stay tuned!
Mark Aird
- September 26, 2014 at 2:29am
If you're unable to build an optimisation model in Excel, if you can't do LP/MIP/NLP in Excel, then don't use it to inform specifically business problems. But if you can't do those things, I'd question the wisdom of you trying to use more complex/capable software. The software is only the tool. Building the model and extracting the inputs from the ERP are the important tasks. Of you can't get those right, tool selection is the least of your concerns.
Bram Bongaerts
- September 26, 2014 at 9:25am
Good point by Mark. What's missing in the characteristics, and what's typically missing in many SCM advanced tools, is the ability to create a feasible plan (i.e. considering capacity or supply constraints) either by optimization or by heuristics (if the problem allows it).

Often improvements aim at gaining visibility. This should not be the aim. Better decision making should be the aim, with visibility the enabler.

In any case, this 4 series blog is a great read!
John Westerveld
- September 26, 2014 at 10:18am
Thanks Bram and Mark. Mark, I love your comments about the software being the tool. I totally agree. Even with the best tools you need a craftsman to be able to make something of value. That being said, one of the reasons a craftsman is a craftsman is that he knows the right tool for the job, where as the layman may not. While you CAN cut a board in half with a hammer and chisel, you might not get the best results. You can build an optimization model in Excel and get a result, however, it will suffer from the same potential issues highlighted in the original post.

Bram, I couldn't agree more. Better decisions IS the goal and visibility helps you get there. However visibility on it's own without analytics, scenarios, alerting and other supporting tools will not drive the best decisions.

Leave a Reply