Data cleaning for production planning

Main advantage of being in a big company is having business intelligence tools (BI) to extract information about your system. Typically, data is uploaded to the ERP system, and then is automatically imported to the BI tool on a daily basis.

Unfortunately, vast majority of companies don't use these data for more than getting some sales figures. In fact, you can do much more than that, especially in operations side. A couple of spreadsheets and some code could do wonders for factory managers.

Sadly, not all the data comes as we want. We need to do some cleaning and processing. For example, sales data is not really suitable for production. Yes, it has all the orders and prices and so on, but it lacks information about manufacturing orders, purchase data, etc. This could be solved easily by merging it with the data we need, but unfortunately the lookup function won't be enough in this case.

One of the most critical points in production planning is the ability to track and spot changes quickly. Humans are very visual, so having a color-coding system in the spreadsheets is crucial. Information about the change in the order date, the date when the components arrive or the amount of hours needed for a certain product is key for operations. This is very simple to implement using VBA code.

We would also need to track when orders are uploaded to the system and visually check them line by line. The result is a clean and well-organized table with all the data, where all the changes and new orders have been highlighted.

Production planning is much more difficult than merging few tables together and there is no one-rule-fits-all but having some code to start from would definitely ease the entire process. You can find a complete example in my GitHub repository.