FIFO approach stock movement

[sathishmadhu]

Hi All

Need help,
purpose of the creating excel workbook is to maintain stock movement with FIFO approach, if any old stock remains in the system should alert us.
Inflow sheet - stock inflow through lorries each arrival will have batch number i.e. same lorry bring in stocks with various batch number in a year.
Outflow sheet -While delivering stock to customers to record from which lorry and batch number stock is moving out.
Stock view sheet - remaining stock of lorry & batch number avoid all zero batch stock and column should appear only if any stock remains.

How do I go about it. Please suggest.

Thanks in advance
Sats

 

[Comfy]

What version of Excel are you using?

You can use either VBA or Power Query.

 

[sathishmadhu]

Thanks for your reply,

version 2003 - 2007

 

[Comfy]

Here's am example, written and tested in XL 2010, hopefully it works.

https://1drv.ms/x/s!AoASJ-g9hQ69g9JGjCbUd1Fjh_8VsQ

Download this file (from my OneDrive) as it won't work in Excel Online.

 

[sathishmadhu]

ADVERTISEMENT

Many thanks for workbook, please note that lorries and batch numbers to come in columns and items to come over rows, i.e. as and when lorry arrives i create a new column with batch number all stock quantities to feed depending on the stock arrives.

 

[Comfy]

For a more refined response your questions have to contain more detail.

Please post examples of your data in sheets "Inflows" and "Outflows"

Along with an example of what you expect the output to be.

 

[sathishmadhu]

ADVERTISEMENT

Pls find attached workbook for your reference, my sole purpose to design this books is to know that has Finst in stock has been issues or still remain in warehouse. some times old stock remains in some corner in warehouse to trace such stocks i need this system.

link to file: https://1drv.ms/f/s!Ap_Z6ytujGCYaooLmOrM8kZXRZk

many thanks

 

[Comfy]

It's going to take a lot of time to get this working, time that I can't commit I'm afraid.

The main problem is the way in which you are recording your data, this makes it very difficult to do any sort of Analysis. This wouldn't be too bad if you could use Power Query but as you're not you're stuck with a VBA solution.

Do you have any control over the format of the data collection sheets ("Inflow", "Outflow")

Ideally, the data would be recorded like this (no need for multiple sheets)

https://1drv.ms/x/s!AoASJ-g9hQ69g9JGG7yu506UhVNM7A

 

[sathishmadhu]

sorry i cannot store data in the above said format, i am also not able to conclude as in how the design of the sheet should be. please give a thought and share with me as and when you get any ideas on my preferred format sent earlier.

Thanks you