Register now or log in to join your professional community.
It is indeed a good question but need a lot of work to answer.
Although an inventory in excel will be slow once data accumulates. My approach will be is to create one excel file with the following worksheets:
1. Item Master list with all the relevant information plus your beginning balance and summary of your item movemment via VLOOKUP from your other worksheet or vice versa
2. Worksheet for Receivings sorted by data ascending
3. Worksheet for stock transfers and/or deliveries sorted by data ascending
4. Worsheet for Stock Adjustment ( Positive or Negative ) )sorted by data ascending
5. Item Movement Worksheet VLOOKUP from the combined worksheets
To make it a FIFO ( First in First Out ) Stock Transfers and STock Adjustment should VLOOKUP from the receiving worksheet searching for the earliest date available. The receving worksheet should containt a column TRANSFER OUT which records if the item was taken out from that particular record
Please give me details about your sheet and I'll be glad to build formulas
u can prepare an excel file with colmns.
Date of arrival - Item code - item discription - Qty In - Unit of Measurement
or add some more cols for Qty Out - Date of Sale
later you can filter.
Must be sent to me via email in order to explain to you,where I cannot explain only through an Excel worksheet
Just apply Data Filter option on top of column you want to use as FIFO. You can get data as you require whether it is FIFO or LIFO.
Dear Shahbaz,
In sha Allah i will make that sheet today and will send u on first priority.
when i wil send u that sheet, i will also direct u with the formulas.
