• 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.

FIFO Based Stock Allocation

dipti

New Member
Hi,

Kindly help to create formula in excel which will allocate stock in column E based on Manufacturing date (Based on Manufacturing Rank) SKU wise (Table : Stock In Hand) to Orders SKU wise (Table : Order In Hand) based on each order booked date (Based on Booked Rank).
Quantity required to be subtracted from On hand Quantity, column L, each time stock is allocate to respective order.
For example :
For Chair with Booked Rank =1 (Row No. 7 - Order In Hand Table), Ordered quantity = 60
From Stock In Hand Table, stock of Chair with Manufacturing Rank = 1, Stock Quantity = 50,
needs to allocated, but 10 units are less to fulfilled the order,
so remaining 10 units can be taken from stock of Chair with Manufacturing Rank = 2 with 60 units,
and remaining 50 units of hair with Manufacturing Rank = 2 can be allocated to Chair with Booked Rank =2.
 

Attachments

  • Stock Allocation Based on Manufacturing Date.xlsx
    11.1 KB · Views: 19
dipti
Two questions:
You are writing FIFO and in Your example based on Manufacturing date, but after those ... You'll miss those ... hmm?
Which one is FIFO: 02-Aug-19 or 04-Aug-19 ?
 
Hi,

In case of Item Chair :

1st Stock allocation
(In Order Table) 1st stock should allocate to row no. 10 , (From Stock table)1st stock should allocate from row no. 7
Stock required on (In Order Table) row no. 10 = 20, but stock available in (From Stock table) in row no. 7 = 5
(In Order Table) so 15 yet to fulfilled, then it can be taken (From Stock table) in row no. 8 = 10
(In Order Table) now 5 yet to fulfilled, then it can be taken (From Stock table) in row no. 9 = 10
Now stock required in (In Order Table) row no. 10 is fulfilled, and (From Stock table) in row no. 9 = 5, balance
It kind of cycle, which keeps on allocating stock based on Stock rank.

2nd Stock Allocation
(In Order Table) 2nd stock should allocate to row no. 7 = 11, (From Stock table) balance stock in row no. 9, balance can be allocated
(In Order Table) so 6 yet to fulfilled, then it can be taken (From Stock table) in row no. 10 = 5
(In Order Table) so 1 yet to fulfilled, now there is no stock left for Chair (From Stock table)

Same function is required for table, which will allocate stock, based on stock rank.

Have taken reference from below link

https://chandoo.org/forum/threads/stock-allocation-of-an-item-as-per-fifo.39674/

Need urgent help for above query, thanks a lot for your reply.
 

Attachments

  • Stock Allocation.xlsx
    10.1 KB · Views: 17
Last edited:
dipti
If You're writing something like Need urgent help for above query then You should answer to questions?

I tried to ask two basic and even clear questions
... and ...
Could You verify, that You answered those my questions?
 
Hi,

1. You are writing FIFO and in Your example based on Manufacturing date, but after those ... You'll miss those ... hmm?Yes
Answer - Yes, logic should based on rank of manufacturing date (smallest to largest) also booked date (smallest to largest), have explained in my second post.

2. Which one is FIFO: 02-Aug-19 or 04-Aug-19 ?
Answer - FIFO is 1st - 2 Aug 2019, 2nd - 4 Aug 2019. (Smallest to Largest)

Please use my second post example attached.
Thanks a lot.
 
dipti
1) Yes ... but after those terms, You explain something else - why?
2) Yes ... 02-Aug-19 is before 04-Aug-19 ... but as written above

Why Your files change?
If You're still something 'urgent', then You should keep basic things same
... that means, You should have had very clear idea!
Now, I have to wait for Your next version of this ...
 
Hi,

Please refer the second version, I currently working on that.
1) Yes ... but after those terms, You explain something else - why?,
In the 1st version, I was not so clear on output, When I started working on second version, I am now have clear understand of what output is required.
2nd version is final, no changes after that.


2) Yes ... 02-Aug-19 is before 04-Aug-19 ... but as written above - Unable to understand "but as written above"
 
dipti
You should reread Forum Rules:

1) Windy ... and before Your previous reply, You did above - without needed actions
2) Your Unable to understand "but as written above"
You should try to reread my written #1 reply.
 
dipti
You should reread Forum Rules:

1) Windy ... and before Your previous reply, You did above - without needed actions
2) Your Unable to understand "but as written above"
You should try to reread my written #1 reply.

Hi,

Will follow the forum rules.
My Apologies.
 
dipti
As there were some challenges and
You would like to see all steps too
press [ Allocate Stock ]-button
and
You could see something
 

Attachments

  • Stock Allocation.xlsb
    20.8 KB · Views: 47
Back
Top