Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 06, 2023 03:38 PM
Hello! We operate in an industry dealing with perishable goods, where the timing of receiving products (INBOUND) and the customer's requested shipping date (OUTBOUND) are crucial factors. We are trying to create a base structure/formula so we can keep track of inventory.
Our primary challenge is to accurately determine the current inventory of raw materials on any given day. This inventory depends on the items we currently have on hand and any anticipated future deliveries of raw materials (INBOUND) for the upcoming week, provided they arrive before the customer's designated ship-out date (OUTBOUND Ship Date). Furthermore, our system must consider existing customer orders placed for the week and determine which incoming delivery of raw materials these orders will draw from.
For example, suppose we receive an INBOUND delivery of 200 units of raw material on Monday, with another 400 units arriving on Thursday. If a customer places an order on a Sunday evening and requests 200 units to be shipped on Friday, we'll allocate 200 units from Thursday's INBOUND delivery to the order scheduled for the Friday OUTBOUND ship date and we should still have 200 units available (from the Monday delivery) to fulfill any additional orders for that week plus then 200 left over from Thursday's inbound delivery to fill any shipping out on Thursday or Friday.
Now, if a customer selects a ship-out date for Friday and requests 600 units, we should be able to fulfill that order by using the 400 units from Thursday's delivery and the 200 units from Monday's delivery.
We need a solution that can confirm that when a customer chooses a specific ship-out date (e.g., Friday), our inventory for that date is accurately calculated. This calculation should consider future INBOUND product deliveries and account for any sales already scheduled for those future dates.
Current Base Structure:
Nov 22, 2023 01:16 AM
Hey @4pine,
Having already build inventory tracking systems and ERPs using Airtable I would recommend the followings:
- Build a process to submit each purchase in a table {Purchases}. This table has to be linked with Items
- The purchased quantity has to update the Available quantity in items table using automation
- Then build the similar process for the sales and using a second automation you will update the field of available quantity
On Items table you gonna need to have the following tracking fields:
- Available quantity
- Commitment quantity -> sold but not delivered yet, those items are still in your warehouse before your picker collects them. Ideally you don't want to sale those pics
- Ordered quantity (optional)-> quantity you purchased but not arrived
For any kind of question please do not hesitate to text back.
Yours sincerely,
Dimitris Goudis