Basic Programming for Data Acquisition off of Purchase Orders, and Work Orders

Question

I am an engineer at an aluminum extrusion factory. I am not well-versed in coding but have a little bit of a background in python (a few college classes). I am 3 weeks in and have noticed the lack of automation here, it is extremely old school which leads to lots of human errors. Which leads to my project I need help with.

Basically, we have a customer with us who will be holding a type of stock we will set aside in mill finish at max capacity until they send a PO with a set amount of custom color to be pulled. Once stock has been pulled, in order to restock the mill finish stock, the amount of custom color they pulled will be added to any new PO's they send in in order to restock.

Consequently, it will be imperative to keep track of the current inventory of mill finish, the stock that will be pulled at any given time, the stock in the process of being extruded, and future stock that may or may not need to be added to purchase orders all at once.

I have a simple formula I believe will work:

Final stock = Initial stock - Stock leaving + Stock being extruded + Purchase order stock

I also want to be able to keep a running max total that should stay at the max amount for each part based off this information.

Is there a way to automate this to where the data can be retrieved from the PO's, work orders, extrusion schedule, and daily kanban count to always have an accurate count at any given time?

Answer 1

You can absolutely automate this process using programming and data manipulation in Python. Since you already have experience in Python and are familiar with programming concepts, I can guide you through the general steps to automate this inventory tracking. However, keep in mind that the precise implementation will depend on the structure of your data and existing systems. Here's a general guide to get you started:

Collect Data and Understand the Structure:
Before automating, you need to understand where the necessary data comes from. This could include purchase orders, work orders, the extrusion schedule, and daily kanban counts. Make sure you know the sources of this data and their format.

Import Libraries:
In Python, you can use libraries like pandas to manipulate and analyze data. If you're not familiar with pandas, it will be useful to learn more about how to use it for working with data tables.

Model the Data:
Convert your data into manageable structures in pandas, such as DataFrames. Each data table (purchase orders, work orders, etc.) can be represented as a DataFrame, making manipulation and analysis easier.

Process Data and Calculate Inventory:
Use the formulas you've mentioned to calculate inventory at different stages: final stock, stock that goes out, stock being extruded, stock for purchase orders. You can use pandas' aggregation functions to calculate relevant totals and quantities.

Update Inventory:
After calculating the values, update the inventory based on the operations carried out in that time period. This might involve subtracting materials going out, adding materials being extruded, and adding materials to future orders.

Continuous Tracking:
Set up the script to run automatically at regular intervals (it could be daily) and update the inventory accordingly. This will ensure you always have an accurate view of the inventory at any given time.

Generate Alerts and Reports:
If necessary, you can configure your script to generate alerts or reports when certain thresholds are reached or when there's a need to restock.

Exception Handling:
Make sure to consider possible issues and exceptions, such as missing data or errors in calculations. Adding error handling and validations will enhance the robustness of your solution.

Testing and Adjustments:
Before fully implementing the automation, perform thorough testing with sample data to ensure calculations and updates are being done correctly.

Implementation and Monitoring:
Once you're confident with the automation, deploy the solution in your production environment and monitor its performance to ensure it's generating accurate and useful results.

Remember that this is a general guide, and the exact details will vary based on your data and specific needs. You might need to collaborate with experts in your factory to better understand data structures and processes involved, ensuring that your automated solution is accurate and reliable.