Automating Inventory Depletion for Complex Medical Device BOMs Using Airtable
The Client
The client, a medical device manufacturing company, was using Airtable for inventory management but faced challenges in efficiently managing their production process, particularly in decrementing inventory for complex bill of materials (BOMs) with up to 300 items per product.
The Challenge
Key Challenges:
- Manual, time-consuming process for decrementing inventory
- Need to handle multiple lots and locations for each part
- Lack of an efficient work order system integrated with inventory management
- Requirement to follow specific inventory rules (e.g., using lowest lot numbers first, prioritizing certain locations)
The Solution
We designed and implemented a custom Airtable solution that included:
- Creation of a Work Order system integrated with existing inventory data
- Development of a Work Order Line Items table to manage multiple systems per order
- Implementation of lookup and rollup fields to connect work orders with parts and quantities
- Design of an automation system to handle inventory decrementing based on work orders
The solution was implemented over multiple consulting sessions, with the framework established in the first session and further refinement and automation planned for subsequent sessions.
The Results
- Streamlined work order creation process
- Automated inventory decrementing, reducing manual effort and potential errors
- Improved traceability of parts used in each product
- Enhanced ability to manage complex BOMs efficiently
- Potential for better inventory forecasting and management
Return on Investment
Although specific numbers are not provided, the potential ROI can be estimated based on:
- Time saved in manual inventory management processes
- Reduced errors in inventory tracking
- Improved accuracy in production planning
- Better utilization of inventory, potentially reducing carrying costs
Tech Used
- 100% Airtable
