using excel for supply chain planningWe all love spreadsheets. But when do you know when you’ve pushed your Excel spreadsheet to the limits? In this week’s, Supply Chain Talk, Arkieva CEO Harpal Singh discusses the telltale signs that show when you are pushing the limits of Excel.

A Bulgarian truck driver once told me how he changed the bearings on his truck with a hammer and a chisel. I suppose if you are stuck in the middle of nowhere and that is the only way to get home, a creative use of what you know is the only option. In supply chain planning, I have seen spreadsheets used in every single function, from scheduling to strategic forecasting. Many of these spreadsheets are not for the faint-hearted. They are complex, convoluted, and a challenge. Every time I see a particularly complex masterpiece, it reminds me of the Bulgarian truck driver. Sure, you can change out the bearings with a hammer and a chisel, but, why would you?

[Featured Resource: What’s Wrong With Excel For Supply Chain Planning? ]

The Issue with Excel Spreadsheets for Supply Chain Planning

Don’t get me wrong. I love spreadsheets. They give me control and make the pesky details about formatting and presentation simple. I can relate different pieces of data by bringing in data from various sources, and quickly write some rules to compare options. The biggest benefit that I find is that, spreadsheets help me organize quantitative information so that I can evaluate different options quickly. My experience is that most supply chain planners use spreadsheets in one form or another, mainly because no single “corporate” system provides the speed, flexibility, control, and access to data that a spreadsheet provides.

[Related: 5 Spooky Excel Error Tales That Could Become Your Nightmare ]

But here’s the rub: spreadsheets are by nature a single person tool.

As soon as you try to use a spreadsheet as a central repository of data, it becomes difficult to manage the controls. Spreadsheet advocates will likely disagree, but all too often I have seen planners spend more time keeping spreadsheet data consistent instead of planning with the data. Spreadsheets also make it easy for the planner to make things complex. Usually, the first iteration of the spreadsheet tool is concise and simple. As requirements grow, the spreadsheet expands to incorporate more and more data and calculations. There are several things which are problematic. If the spreadsheet needs data, then it becomes a chore to keep it up-to-date. As the spreadsheet becomes more complex, there is always the possibility that the next person in the job will not understand what it does, and might start all over again.

A couple of years ago, we started working on a scheduling project. The transition costs and times were kept in a spreadsheet by the scheduler because the ERP calculations were seen to be meaningless. As we delved into the details, we found inconsistencies in the data. The stock answer was “yes I knew that, and I don’t use that.” The spreadsheet was designed by the scheduler, for the scheduler, and he knew what was right and what was wrong with it. It does not help if a spreadsheet is 90% accurate if you don’t know which 10% is wrong.

[Read Previous: Supply Chain Talk: 3 Challenges Supply Chain Executives Cannot Ignore ]

When should we use spreadsheets for supply chain planning and scheduling?

Supply chain planning systems are generally designed to handle routine tasks and routine problems. While they may not always provide flexibility to deal with surprises, any planner will tell you that no system can accommodate every weird situation that can come up. So, it is necessary for the planner to have a catchall tool (Excel) which can be used to aggregate data, manipulate data, and perform custom analysis to solve an issue. But using the tool as a substitute planning system all the time is probably not the wisest move.

More and more, companies are using quantitative tools to manage the supply chain. The idea is that, if you have a basic supply chain model that everyone in the business buys into, then it is easier to get everyone pulling in the same direction.

[Read Also: Supply Chain Talk: 4 Effective Strategies for Creating a Robust Scheduling Process]

Using Excel for Supply Chain Planning: 4 Telltale Signs That You’ve Pushed Excel to The Limits

So, when do you know if you have pushed Excel too far? It’s simple: “if it looks like a duck…”

Here are a few hints:

  1. If you spend more time updating the data in the spreadsheet instead of using it
  2. If you find that you cannot update the spreadsheet because someone else is using it, and if this happens on a Friday afternoon.
  3. If you look at a formula in a cell and the reference points to a spreadsheet on another computer.
  4. If part of the spreadsheet is password protected and the only person that knows the password has left the company.

A business succeeds by making “good decision ALL the time” and not by “making great decisions SOME of the time.” Good decisions require up-to-date and consistent data that everyone has confidence in. That data needs to be shared so that all folks involved in the decision-making process are on the same page.

[You Might Like: Supply Chain Talk: Are Best Practices Always Best? ]

Have a supply chain topic that you would like us to discuss? Join the Arkieva Supply Chain Talk with Arkieva CEO Harpal Singh. Add a comment below or send a tweet to @Arkieva with #ArkievaSCT, or email editor@arkieva.com.

Enjoyed this post? Subscribe or follow Arkieva on Linkedin, Twitter, and Facebook  for blog updates.