Stock Allocation From Future Purchase Orders

Hello Everyone,

I am looking for a way to automatically assign inventory to sales orders with current on hand inventory and then incoming purchase orders. I would like to see the PO# and PO land date the inventory is being allocated from. Once all that QTY is assigned, it will move to next available. The priority will be based off "Confirmed Ship Date" and "Sales ID" as the secondary priority.

I have attached a basic spreadsheet with a few tabs. The "On Hand" tab is there to pull inventory from first, and then move to the "Future PO Dates" tab to start allocating from the incoming POs.



Please let me know if you have any ideas, or if I can provide more information.