• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Stock allocation of an item as per FIFO

sunderlalrwr

New Member
I need an excel formula to allocate stock of an item based on column"FIFO_Rank" in Table-1 from stock Table-2.

TABLE-1
ITEM SO Qty FIFO_Rank Stock_Allocation
Chair 11 72
Table 50 14
Chair 14 99
Chair 20 36
Table 25 20
Table 20 49

TABLE-2

Item Stock
Chair 30
Tables 50

Please help.....
 

Attachments

sunderlalrwr

New Member
Dear, need some more clarity
I want to allocate qty based on FIFO_Rank column "I" (order how much old) from stock table

In case of Item Chair : 1st stock should allocate to row no. 8 then row no. 7 & then row no. 9.
In case of Item Tables : 1st stock should allocate to row no. 12 then row no. 10 & then row no. 11

Check attached sheet
 

Attachments

sunderlalrwr

New Member
sunderlalrwr
Did You explain clear, how would rank effect to allocation?
This would be one sample ...
I wants to stock allocation to Sales Orders

I have some open sales orders "Item" , "SO Qty" & "FIFO Rank/Aging in Days"

1st I want to allocate quantity to such orders who are old (in term of days)
 

vletm

Excel Ninja
sunderlalrwr ... I want to allocate old orders first nothing else ... hmm?
Didn't that #2 Reply so?
with original values...
Chair: rank order is 99 - 72 - 36
Tables: rank order is 49 - 20 - 14
BUT
You haven't name which part of Chairs
would give to the oldest (biggest or smallest).
And
How would You see which order would allocate in which order?
So far, You could see only results!
 

sunderlalrwr

New Member
Chair: rank order is 99 - 72 - 36
Tables: rank order is 49 - 20 - 14

Order should allocate same as you wrote
 
Last edited by a moderator:

SUNCIOUS

New Member
Hi,

I was wondering how to modify above formula (=MIN(H7,MAX(0,LOOKUP(G7,$L$7:$L$8,$M$7:$M$8)-SUMIFS($H$7:$H$12,$G$7:$G$12,G7,$I$7:$I$12,">"&I7)))) if I have one more criteria - which is to limit the returning value to return once only
if there is duplicate under FIFO_Rank 's column?

For example using above question :
There is duplicate under "Chair" - with "72" appearing twice.

TABLE-1
ITEM SO Qty FIFO_Rank Stock_Allocation
Chair 11 72
Table 50 14
Chair 14 99
Chair 20 72
Table 25 20
Table 20 49

TABLE-2
Item Stock
Chair 30
Tables 50

Thanks in advance for any help received!

Regards,
Suncious
 

rjrd1004

New Member
Hi.. I have same question as SUNCIOUS. I impremented the formula from HAZ into my open requirements allocation and all works fine until I show duplicate ranks, in my case, same days old for same item.
 
Top