Sunday 4th Dec 2016 - Logistics Manager

The last days of the spreadsheet?

In a dynamic business environment where complexity rules, advanced planning systems are fast becoming “must have” tools of the supply chain. Are the days of the spreadsheet numbered?

What is supply chain management but good planning? In essence a well ordered supply chain ensures that goods are produced in the most efficient manner and are supplied in appropriate quantities to where they are in demand. Put this way it sounds quite straightforward enough. However, in reality, achieving the optimum results more often than not involves significant levels of complexity – a planner’s nightmare – and that challenge is set to grow considerably.

This article was published in the October 2015 issue of Logistics Manager.

This article was published in the October 2015 issue of Logistics Manager.

Complexity within supply chains is increasing rapidly. Businesses are seeking to adapt to a competitive landscape that is becoming ever more fragmented and where buyer’s demands are relentlessly becoming more sophisticated. Lines of supply are often global, multifaceted and prone to risk. So link these factors to tight financial constraints, finite resources and short timelines and it is easy to see why planners are under pressure.

The more resourceful organisations will need to harness complexity to ensure that machines and people are use in the most productive way, that raw materials are purchased and scheduled in a cost effective manner, and that finished goods are passed between various nodes in the supply network in a seamless and timely fashion.

But are the planning tools commonly used for the job up to the challenge at hand? Why do planners still depend so heavily on the humble yet ubiquitous spreadsheet?

According to Rod Schregardus, operations manager at Access Group for Orchestrate planning and scheduling software, around 90 per cent of managers tend to use spreadsheets for manufacturing and supply chain planning purposes.

But things may be changing. “We find most companies are using spreadsheets for planning and scheduling and when they fail, they tend to look for an alternative. At a certain level a spreadsheet is quite easy to use, but then once you start getting further into it, there are problems of complexity and using it can become very time consuming – especially when things change,” he says.

Schregardus points out that a spreadsheet can take a lot of effort to update and can contain hidden dangers. He says, “Often they are error prone – especially with large spreadsheets. You may drill down to get to a number, but you can’t necessarily tell if its correct and because it has links and formulas, you can be happily working away and not notice that something is broken in the middle – you may just not see it.”

He also cites issues that can arise around multiple copies and version control, as well as the dreaded scenario where the “expert” who built the spreadsheet is away or off sick – would anyone else understand the formulae used or know how to make changes?

 

Maturity

Jonathan Jackman, VP EMEA at supply chain planning software vendor Logility, refers to the analyst Gartner’s maturity scale, where companies relying on spreadsheets for supply chain planning are in the lowest quadrant of the scale. “It isn’t until companies mature and understand the value of supply chain planning, improved S&OP [Sales & Operations Planning] and Integrated Business Planning that they realise they have to move on from spreadsheets and support planning processes with technology,” he says.

Jackman observes that companies experiencing fast growth often have a heavy reliance on spreadsheets, simply focusing on “getting product out of the door”. However, he warns that spreadsheets can be an inhibitor of sustained growth, “they are not sophisticated, robust or collaborative enough to be able to support a company [going forward],” he says.

However, it is the planner’s need to tackle complexity that Jackman sees as the big challenge. “In most supply chains there is a level of complexity – probably on a global scale – to be supported and for this there needs to be a level of optimisation. But to balance demand and supply there has to be a number of trade offs and without the proper optimisation engine it is very difficult to optimise a supply chain properly,” he says, making it clear that spreadsheets are woefully inadequate for the task.

He goes on: “And because of the complex global nature of supply chains there is a fair amount of collaboration that goes on. But collaborating through spreadsheets, sharing them and updating them in isolation is invariably error prone. Compare that with a system like Logility where everything is tracked, it’s central, and you can see an order trail – there are clearly many advantages.”

Dawn Howarth, partner at consultancy Oliver Wight, believes strongly in the value of having a single source of data. She is of the opinion that companies should always be looking to have a fully integrated planning system and should move away from spreadsheets at the earliest opportunity.

“One of our key principles is having “one set of numbers”,” she says. “When you work on spreadsheets it’s all knowledge in an individual’s head rather than freely available to the rest of the organisation. And you find every one has their own version of the spreadsheet and nobody has the full picture.” She adds, “You normally find the amount of money it costs an organisation, just because of the time it costs for re-planning or lack of visibility, is huge.”

Howarth recommends that all companies should aim to have their key data available in a planning system. However, that said, she says most companies don’t need the sophistication of planning systems they often think they need.

 

Balance

“There is definitely a balance. In any organisation there are always people who like technology and a tool is always the answer – even if they don’t really know the question, or the problem they are trying to solve,” she says.

“We see many companies that have bought fairly sophisticated planning tools and they either revert back to spreadsheets because they don’t know how to use them, or they use a very small fraction of their capability. They rush into implementation before they know how they want to run their businesses and therefore they don’t set it up for the long-term future.

“We suggest you need a tool that is going to grow with you. But you need the vision and the business processes you want for the future before you start to implement a tool,” she says.

Howarth goes on to explain Oliver Wight’s approach relating to People, Processes and Tools. She says: “While they are equally important, we prescribe that in an early point of maturity in an organisation it’s very much about people and behaviour, and then creating formal processes that everyone adopts and tools take a slight back step until you have established this. Then as you grow in maturity the tools become more important – allowing you to do things faster and better.”

Rod Schregardus from Access Group highlights some of the benefits to moving to an Advanced Planning System. He believes companies moving from spreadsheets tend   to be looking for one tool for           data base scheduling, capacity planning and “what if” analysis. “Data base scheduling is obvious. You want to have a common production plan that is data base driven, so that anyone can view their individual area and see what they are doing on a Monday morning,” he says. “Then there is capacity planning. You want to be able to plan out all of your sales orders, works orders, purchase orders and be able to see if you’re able to hit on-time delivery. If you’re missing a deadline you want to be able to do some “what if” analysis to see if it works to take on some contract labour or subcontract something out, or even buy another piece of equipment.

“Once you’ve made those decisions you can feed that information back into your data base schedule, so that if you’re planning overtime, you can use Orchestrate to identify what jobs to do over the weekend and ensure that the people coming in are exactly clear on what jobs to do and in what sequence,” says Schregardus.

 

Visibility

To achieve visibility, tight integration is required. Orchestrate will, obviously, import and export data to and from an ERP system, something that Schregardus says “would be difficult with a spreadsheet”. He emphasises the benefits of an intelligent, integrated system. “That means you have all your works orders, purchase orders and sales orders contained within Orchestrate and as you create new sales orders etc. in your ERP then it imports and flows into the APS system. You can view if your purchase orders are early or late too.” He illustrates his last point with a story on how a customer improved its cash flow through being able to see that the company was ordering ten tons of titanium unnecessarily early – “a clear benefit of complete visibility and a proper understanding of the situation,” he adds.

However, one of the biggest issues for companies trying to gain a greater understanding of true demand and the dynamics of the supply chain, is managing the vast mountain of data that is now available.

Dawn Howarth of Oliver Wight says, “There is just too much data to know what to do with. And we don’t always recognise the level of accuracy that we have within the data. Therefore should we be making multi-million pound decisions on data without understanding how accurate it is? If you really knew how badly some companies manage their data on a day-to-day basis you would never use that data for anything significant.”

Referring to the trend for big data, she says companies can be overwhelmed and can suffer “analysis paralysis”. The issue is too much data and not enough information.

The problem for many businesses is that “they haven’t been able to transform data into information or knowledge,” she says. “Big data is often seen as the “Holy Grail”, but people haven’t really got the processes and understanding to use it properly at the moment – but the potential if phenomenal.”

Howarth believes it is early days for big data. “A lot of companies are learning the hard way when they invest in these large data warehouses – they realise it’s just not good enough at the moment,” she says; pointing out that there are two elements to address. “They have got to ensure the accuracy of the data they are collecting but then, it’s the ability to weed out exactly what they are trying to get from the data… and being able to quickly draw on it to take good decisions – rather than just being swamped in numbers.”

So it seems good planning is dependent upon ensuring accuracy of data and the use of sound analytical tools.

According to Jonathan Jackman of Logility, Advanced analytics is a hot topic within mature organisations at the moment. He says, “There’s loads of data swimming around the place now, lots of data sources and different elements of data and that finds its way into a supply chain system. So you need an analytics engine to cut through that and understand what the trends are and what’s going on in the marketplace. And then that needs to be fed back into the supply chain to get it to react in time to meet the service requirements.”

For the “front-runners” this dashboard approach to planning brings significant advantages. Both Schregardus and Jackman agree that businesses are now better able to run “what if” scenarios and simulations to see what the impact would be on the supply chain to changes in circumstances. In a dynamic business environment such tools are fast becoming invaluable.