Analyzing supply chain data is at the heart of any growth-oriented company. By scrutinizing raw operational data and transforming it into usable business insight, best performing companies save thousands of euros, improve operational efficiency, and achieve overall business growth.
Surprisingly, many companies still use Excel as their primary analysis tool. Data is extracted from multiple systems and then manipulated and analyzed using multiple Excel sheet formulas and sometimes other analytical tools. These Excel-based analytical processes have so many drawbacks that they essentially hold back the ability to optimize your supply chain, spot problems and respond quickly to events.
Let’s review some of the top weaknesses of using Excel for analytics.
Multiple spreadsheets = multiple versions of the truth
In order to trust your data and be able to take decisions, you must have a single version of the truth. With Excel, this is not always the case.
Data is exported from numerous data sources and then combined in a spreadsheet. This is not just time consuming, but also error prone. Specific queries need to be prepared and run and this often means losing the granularity of the information. In more complex scenarios, applying the desired analysis requires a series of Excel spreadsheets where data is manually copied and pasted between them. Consequently, there is no way to trace back data, there’s no audit trail, and it weakens confidence in the analysis.
Impractical maintainability and collaboration
In Excel, formulae are prone to be deleted or overtyped. Most of the time, it’s only the author of the spreadsheets who understands the logic and the complex business rules embedded in the formulas. As a result, spreadsheets are often not replicable, since calculations and formulas often need to be re-designed when the analysis and/or business rules change or when an employee leaves your company.
No KPI consistency across the organization
With each business function downloading data to create their specific analysis, you end up with a myriad of terms and standards that are used for the same calculation, or the same indicator. It is practically impossible to reach a consistent set of KPIs across the business that can be compared. Instead of focusing on optimizing and improving KPIs, the discussion of who’s data is right always comes up. Furthermore, too often formulas are designed and iterated to optimize a single silo at a time, instead of keeping a view on the end-to-end process.
Timeliness of results
The process of downloading data from multiple data sources is time consuming. Coupled with the difficulty of versioning the spreadsheets, it is difficult to ensure that the analysis is made on the freshest data that is available. Put differently, decisions can be made based on outdated information.
The alternative – an integrated data approach
Instead of the massive use of spreadsheet, you should adopt an integrated approach to data. The multiple data sources, such as operational (WMS, TMS, etc) and planning systems can be linked with other business data sources, such as ERP, sales / POS systems and procurement data. With this integrated system in place, the impact of supply chain performance on sales and customer retention can be directly modelled.
Here are three key areas you could focus on:
Using an integrated technology platform will allow you to create end-to-end visibility by instantly bringing together data at the most granular level from across the entire supply chain. This single point of truth, enriched with your own business rules, will allow supply chain operators to gain real-time visibility into data such as sales forecasts, bookings, transport, status, backlog, product reliability and inventory.
Allowing operators to create the views/analysis pertinent to their operational area, but at the same time adhering to company-wide business rules and strategy, ensures that optimal decisions are reached. Because all stakeholders use the same indicators, and calculations are consistent, you can rely on the information.
The result is that your team will be able to monitor its targets in real-time and make better informed decisions by ensuring all potential impacts of that decision before it has been executed.
Industrialize & speed up the processes
Automating daily recurring processes will allow for greater efficiency and better informed decisions along the entire supply chain. In case of an event impacting the supply chain, a unified platform should be able to push the user a first solution (based on your rules) and allow for collaboration with other supply chain partners to find the best option with regards to service level, costs and delivery times.
Enhancing this with the ability to simulate and create what-if scenarios on the fly will allow users to test the impact of a change in capacity, transport, stock holding, customer service levels, etc. on the most important indicators such as costs, service level and efficiency.
This enables responding quickly to unforeseen events in the supply chain, since decisions are based on the most up-to-date information, and on impact / cost-benefit analysis.
Execute and continuously improve processes
Once a decision is made based on analysis, you also need to execute it. Within spreadsheets, decisions could be made but there’s no way to execute them directly or communicate them to the source systems. In order to improve and speed up these processes, you’d want to execute these decisions from the analysis platform straight into the source systems.
Being able to execute decisions straight from within the analytical software saves time and reduces further the potential for error. It makes it easier to monitor and audit your decisions-making process, as you can take a look back at any point in time and see how the data supported (or challenged) a decision.
A single version of the truth. Instead of the possibility of numerous file versions, scattered around the business, have a single central database which can be seen by everyone.
Embedded logic. Instead of formulae being located in spreadsheet cells where they are prone to being deleted or overtyped unless protected, the business logic should be safely built into the system where it cannot be overwritten.
Consistent calculations and terminology. All departments and business units use the same system, so they all have to use the same terminology and calculations. Challenge all requests by different parts of the business to have their own features – is their business really so different from the rest of the company? Should it be? Clearly different markets require different marketing, but do not underestimate the value of keeping business processes simple.
Immediacy of results. Because all parts of the business are using the same system, as soon as changes are committed by one part, they are instantly available to the rest of the business. This is particularly important once the planning process is over and the actual results start flowing in. Any updates to sales forecasts should propagate to other users of the data as soon as possible.
Versioning. Excel is not great at comparing one version of a business plan to another – particularly if the constituents have changed. A properly designed database should handle this with no problem – enabling comparison between different drafts of the budget and between actuals and plan. This also implies that ideally the same system that was used for planning should be used to report on actuals to allow these comparisons.