I saw this post over at the Sourcing Innovation blog. It links to an excellent article with the catchy title of “Don’t let this happen to you!” on Supply and Demand Chain Executive. It brings to the forefront thoughts I’ve been having over the past little while. It amazes me that every once in a while we’re approached by a company that has built staggeringly complex spreadsheets to do basic planning. I’m glad that they’ve come to realize that a spreadsheet is no way to run a multi-million dollar company, but at the same time…wow. The more surprising thing is how many mid-size to large companies are out there that still do large portions of their planning on spreadsheets and haven’t realized the peril they are in. So…what’s wrong with using a spreadsheet? I’ve approached this issue in an earlier blog post where I linked to a study that shows that 80-90 percent of spreadsheets contain serious errors. The article on SDC Exec gives some real life examples;
- A high-volume software company unwittingly threw $20 million annually into the scrap bin. The culprit was a hidden, and devastating, spreadsheet logical error that systematically triggered over-ordering seasonal printed materials.
- A financial services firm underestimated support center demand by over 250 agents, roughly 25 percent of total demand. Only very fast action was able to turn around customer comments like, “I can’t get tech support and I hate your company!” Formula errors and inappropriate assumptions had negated demand associated with deploying a new service platform.'
- The national build-out of a digital service network was beset with months of delays and millions of dollars in lost revenue. Spreadsheet-based material planning and execution tools were overwhelmed with the size and complexity of the job.
I’m not anti-spreadsheet. In fact, I consider myself to be an Excel wonk. I’ve built some honkin’ big spreadsheets in my day, including pages of VB script. In retrospect, many of these would have been much better suited for a proper enterprise application. So how do companies get into a position where decisions worth millions of dollars are based on some guy’s error-prone spreadsheet? It certainly doesn’t start out this way (I can’t imagine an IT organization when asked to source a planning system would propose a spreadsheet written by the guy down the hall…) No, what typically happens is that some creative individual, tasked with a small but challenging problem that their ERP system can’t handle, creates a simple spreadsheet that solves the problem. Then, as the company grows, and challenges change, the simple spreadsheet expands, evolves, becomes more complex until eventually, you have a multi-headed Hydra lurking in your business processes. The SDC Exec article has some tips to mitigate some of the risk associated with using spreadsheets…but I think the most important advice is to review your processes. For each key process that is spreadsheet based, plot the business process and the complexity of the spreadsheet (Execution Risk) on the matrix shown.
Discussions
The dilemma is the change management and administrative challenges of what are essentially centralised (and therefore relatively inflexible) IT solutions, vs. the self-managed (but possibly inefficient) spreadsheet approaches.
But, trying to pin the blame on spreadsheets when things go wrong instead of admitting that it perhaps wasn't such a good idea to entrust "the guy down the hall" with a task that was obviously beyond his training, capabilities and/or understanding is behaving like a plumber who blames his wrench for what is essentially shoddy workmanship on his part. There is no excuse for any business "unwittingly (throwing) $20 million annually into the scrap bin", spreadsheets or no spreadsheets. If this really happens in any organisation, then it is a sign of a more deep-seated problem arising from deficiencies that go far beyond a few formulas on a spreadsheet. That, in my opinion, would be appallingly inexcusable practice.
I have worked in Supply Chain Management for many years, in various organisations ranging from small companies to enormous ones, in just about every aspect of Operations you care to mention. The one invaluable tool I found again and again to most effectively bridge gaps in ERP systems, as well as providing rapid deployment solutions to situations that came flying at us from left-field (tell me you don't know what that feels like) were spreadsheets. I have built numerous complex systems to manage a wide range of situations that are still being used from nothing more than the humble spreadsheet. In fact, I actually created an entire end-to-end ERP system that managed the total manufacturing requirements of an offshore plant. This was only possible because I have also gained a lot of indepth knowledge and experience in VBA programming in my time with spreadsheets, which has helped me create complete applications that look and function more like standalone programs than that familiar grid appearance.
The thing about spreadsheets most people don't realise is that they are so much more than just very large grids into which you can enter fancy formulas that give you fancy results. They are actually an incredibly robust platform on which it is possible to construct immensely sophisticated and consistently accurate systems, not just models.
The only trick to all of this is that you have to know what you're doing, and possess a deep understanding not only of what you're trying to achieve, but also of what can be achieved. If you are just a programmer with no Supply Chain knowledge and/or experience, then i'd want to make sure that there was a fully qualified Supply Chain professional looking over your shoulder for every line of code you wrote, because SCM is not a walk in the park. On the other hand, if you are just an SCM professional with no programming knowledge and/or experience, and your capabilities with spreadsheets were limited to just entering formulas in cells, then you need to get a programmer over whose shoulders you could stand and direct.
With a bit of luck, you might also be able to find someone who is both a qualified and experienced SCM professtional AS WELL AS a capable and proven VBA developer.
When spreadsheets go bad, it's usually because the person using them has a very limited idea of what they're doing or are meant to be doing, or because there are already some shockingly poor practices enshrined in the organisation that need to be looked at and fixed first.
Leave a Reply