• 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

  • stock_Allocation.xlsx
    8.5 KB · Views: 87
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

  • stock_Allocation.xlsx
    12.8 KB · Views: 95
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)
 
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!
 
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:
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
 
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.
 
Hi, I have something similar but in a horizontal template... i need to allocate stocks based on the rank and the limit stock. could you please help me here, TIA
 

Attachments

  • Book1-sample.xlsx
    11.4 KB · Views: 16
Doe
As You have just read Forum Rules from
You would reread especially from How to get the Best Results at Chandoo.org
that Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.
 
Back
Top