How to prepare your data for a supply chain planning software implementation

Almost every company that uses MRP for planning struggles with poor data quality. It’s a direct result of MRP’s inadequate planning support – and users resorting to offline spreadsheets.

A supply chain planning solution is much more powerful than MRP. However, automating the planning process requires accurate input data. Therefore, your data will need to be cleansed and normalized, and processes put in place to maintain the data over time.

The good news is that all this is standard procedure and a normal part of the implementation process. Your system vendor knows exactly what to expect and how to ensure your data gets to the quality level needed. This article provides practical advice about how to prepare your data for a supply chain planning implementation with best practices for sourcing, cleansing, normalising and maintaining your supply chain data.

The importance of high-quality data

Planners using a combination of MRP and Excel tend to rely less on data in the company’s ERP system and more on gut feel and external data in complex spreadsheets. As a result, the ERP data is often poorly maintained and generally not up to scratch.

Supply chain planning solutions operate differently than MRP. They use mathematical optimization and can automate planning tasks to a greater extent, which makes high-quality data essential for accurate predictions and recommendations.

Here are some of the primary data categories and examples of data that are needed for a supply chain planning solution:

• Demand data: Historical and current sales data are required to generate a demand forecast. If a separate system already provides a forecast, it must be shared with the planning solution. In addition to this, customer orders must also be obtained.
• Inventory data: Information about current inventory levels and replenishment lead times.
• Production data: Details on manufacturing capacity, lead times, and production constraints.
• Supplier data: Supplier lead times and pricing information are essential for planning and managing the procurement process.
• Cost data: Data on labor costs, material costs, transportation costs, and other expenses are necessary for developing plans that maximize company profits.

The system of record

Most of the data your supply chain planning software needs will already be available in your existing systems. We refer to these as systems of record (SORs). A system of record is where data is captured, stored, and managed to ensure accuracy and completeness across the organization. While other systems, such as your supply chain planning solution, can access and store SOR data, the SOR remains the authoritative source. In addition to reliable data, the SOR should provide strong validation and maintenance capabilities and should integrate easily with other systems.

A system of record (SOR) or source system of record (SSoR) is a data management term for an information storage system that is the authoritative data source for a given data element or piece of information.

The basics

Before we move on to some more detailed checklists and best practices for preparing your supply chain planning data, let’s look at some general recommendations that will ensure your data efforts stay on track:

Don’t be over-ambitious

While your SOR probably requires a complete clean-up, it is impossible to find all data issues by simply running through a data cleansing process. When you start using the new planning system, the application acts like a kind of ‘washing machine’, as any strange outputs are likely due to incorrect data. Embrace this run-in period. The system will uncover discrepancies and you can ensure your data is in good order prior to more thorough solution testing.

Put the right person/people on the job

Data issues need to be addressed quickly – otherwise, a 3-month project can easily become a 12-month project. We recommend appointing a team or an individual to promptly address any data quality issues. These people must have the time, authority, and know-how to perform the necessary tasks. Generally, it would be a data analyst within your business, supported by the supply chain manager.

Put a process in place

One of the biggest challenges is maintaining your system of record quality over time. Data can quickly become outdated if it is not managed correctly. For this reason, it’s essential to implement a data governance process that addresses data cleansing methods, data normalization, and data quality standards.

Checklist – preparing your data for a successful implementation

Let’s go through a checklist for preparing your supply chain planning data. By following these steps, you can ensure that your planning system works from reliable data.

1. Define data requirements

Identify the specific data elements needed to support your planning processes. The initial data categories listed above are an excellent starting point, however, your exact requirements will depend on your unique solution scope.

2. Identify the system(s) of record

Once you’ve documented the data requirements, you need to identify the corresponding system of record. If any required data doesn’t exist, you must decide how to source it and where it should be stored and maintained.

Most businesses rely on their ERP system for supply and demand input. A modern ERP ticks all the SOR boxes and is the originating system for most master and transactional data in the organization. In our experience, the average supply chain planning solution gets over 95% of its input from the existing ERP system. Other systems that may complement your ERP as a system of record include Master Data Management (MDM) systems, CRM systems, Excel, and cloud-based data management platforms.
NOTE: While ERP data is often replicated in a data warehouse, it doesn’t mean that the data warehouse is the SOR.

3. Cleanse and normalize data

Cleanse and normalize the data to ensure it’s accurate, consistent, and complete and that it meets the requirements of your planning processes. This involves running data quality checks, removing duplicates, correcting errors, and standardizing data formats and structures.

Best practice for data cleansing

Data cleansing is critical to succeeding with your supply chain planning implementation. The following takes you through a structured approach to cleaning up your SOR data:

1. Define the cleanup scope: Identify which data sets need to be cleaned and which are excluded. Determine the timeframe for the cleanup and the resources required.
2. Analyze the data: Assess the data quality and identify potential errors, inconsistencies, or duplicates. Typically, this is done with SQL interrogation scripts. If unavailable, create a data dictionary to define the data and ensure consistency. Determine which records are still relevant and delete any unnecessary ones. Identify patterns in the data that could indicate problems with the system.
3. Develop a data cleanup plan: Establish the order in which records should be cleaned up. Designate responsible individuals or teams for cleaning up each set of records. Create guidelines for resolving errors, inconsistencies, and duplicates. Decide how to handle records that cannot be fixed or are no longer relevant.
4. Execute the cleanup plan: Train your designated staff on the guidelines and procedures developed in step 3. While most data will require manual input, some data might be fixed using a query language. Execute the cleanup monitoring progress and adjusting as needed.
5. Verify the cleanup results: Ensure that the cleanup was executed according to the plan. Validate data accuracy, completeness, and consistency using SQL scripts or other appropriate methods. Verify that the system functions as expected post-cleanup.
6. Document the cleanup process: Document the cleanup plan, including any adjustments made during execution. Capture any issues encountered and their resolutions to ensure a comprehensive understanding of the cleanup process.

Best practice for maintaining your system of record data

Proper maintenance is necessary to ensure your system of record data remains accurate, reliable, and consistent over time. This must be treated as a continuous process, not a one-off exercise. These best practices will help you maintain your system of record data:

1. Establish data governance processes that ensure data accuracy, consistency, and completeness. Data governance involves establishing policies, standards, and procedures for data management, including data quality control, cleansing, and normalization.
2. Regularly audit your data to ensure it is accurate and current. Review the data to identify errors or inconsistencies and validate the accuracy of the data against external sources. This can be done with scripts that automatically test the data quality and present any quality issues in a dashboard. Reviewing these data validation dashboards should be part of your S&OP cycle.
3. Enforce data standards to ensure consistency across all data sources. This includes using standardized naming conventions, data formats, and data definitions.
4. Automate data entry processes to reduce the risk of human error. This can be done using software tools that automate data entry or through the implementation of standard data entry protocols.
5. Train your team on the importance of data quality and how to maintain it. This should include data entry best practices, data quality standards, and data governance processes.
6. Continuously improve data quality by identifying and addressing issues, tracking data quality metrics, and implementing necessary process improvements.
7. Perform regular data backups to ensure that data is not lost in the event of a system failure or data corruption.

By following these best practices, you can ensure that your system of record always contains high-quality data that supports effective supply planning and decision-making.