How to reduce inventory from the same SKU

[Arthur23]

Hi, I've been having problems trying to find the correct formulas on an excel file of inventory and sale order data.

I want to be able to fullfill orders FIFO, by ship date. Inventory needs to be allocated to sales orders with the earliest ship date first.
In case there are sales orders on the same date, I want to allocate inventory to the largest orders first

The problem I have is that if there are orders on the same date with the same SKU I want to be able to reduce the inventory quantity from the highest sales order to the lowest automatically using formulas so I have the current inventory stock after substracting all the orders starting from the highest to the lowest.

Not sure which formula to use

Many thanks in advance

 

[bobsan42]

Inventory management is not a trivial task and formulas are not the tool for the job.
Reserved quantities must be removed from the inventory, delivered must be added., A running sum is not the way to go in the long term. Especially if you want to implement FIFO.
And for this you will need something more than just an SKU like a Lot or a Batch Number with delivery date. FIFO should not care about order size - just order of appearance.
For things to be complete - you will need delivery cost and sales price ...
Basically a major project on its own. And a certain amount of code will have to be written to do the job.
Maybe think it carefully through a bit more and rephrase what you're trying to achieve.