In this document I want to take you through a quick and simple method I have used for checking order and demand alignments after a refresh of data or a processing period.
This will help check if bookings are correct, deliveries have been processed as expected or new demands have been added requiring planning. This also helps to check if there may be any data errors which may be as a result of incorrectly entered or not maintained data in material masters or of MRP data requirements within the company ERP system.
Each of the following Slides will walk you through the steps and simple formulas to compare the original and new data sets and find the differences for further investigation if needed.
First gather your necessary data in this instance I have generated a random demand list.
You want to pull in a report or set of data where each line has at least one unique piece of data to that demand line. Once combined with the rest of the line we can make a unique ID. This will become obvious why in the following steps.
If this really is not possible with the data, you have there are other ways to reference them but for this instance we will assume there is a unique data cell in each row.
We add a column for Unique ID as row A and in cell A2 add the =CONCAT() formula referencing the whole line.
This will combine each cell together to create a unique string of data for that row.
Drag the cell down to copy the formula to all the other rows.
Second Data Set
To ensure each ID is unique in the column we will also do a check by inserting another column as column B and calling this Check.
In cell B2 we will enter a =CountIf() formula referencing the whole A column against the cell next to this formula in this instance cell A2.
This will check the whole column and count how many times the value in this cell appears which should be 1. If you get 2 or more this means the ID created is not unique to that line and you may need to include more data in your report.
If all is well then drag the formula cell down the column to apply to your whole data.
To do the alignment check we are looking to compare against a refreshed set of data. This may be from a running database you have or through obtaining a regular report exported from your company ERP system you can pull this in to a second sheet for us to compare.
This check can be done as often as necessary but usually requires a refresh function to have taken place, such as with most ERP systems an overnight processing is done. It will also depend on the reasons as this outlines a lack of confidence in the data or requiring a means to check certain changes.
Once again add a unique ID column and the =Concat() formula in the same way as we did on sheet one.
Alignment Check 1
On your second data set we will now add a column for checking the data to the original in sheet 1.
In this instance we will add it in a column to the right but could be placed anywhere you prefer.
We want to check the unique ID of this row against the Unique ID Column in Sheet 1 hence using another =CountIf() formula. Again, drag the formula down against all your rows of data.
Alignment Check 2
The same check can be done for sheet 1 against the new data in sheet 2. this will show where deliveries have been made or demand amendments. As before where there is a zero these require investigation.
This check can be automated, and further checks expanded but for simplicity and anyone looking for a easy solution this is one of my methods.
I am Gavin Kent working with a vision of improving manufacturing businesses through demand management and operations planning with data driven tools and process improvement techniques.
For help and more information or to just connect you can reach me below.