Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

How to make an excel spreadsheet for managing inventory by using FIFO method?

user-image
Question added by Shahbaz Gul , Manager Accounts , Claris Medical
Date Posted: 2013/09/18
Zafar Iqbal
by Zafar Iqbal , Teacher (Pak Studies) Subject Specialist , Home Tutor

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

emad awad
by emad awad , internal audit manager & cost control manager , nile sugar

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.

 

taha amer
by taha amer , Finance Manager , Arabia ‎Holding

Must be sent to me via email in order to explain to you,where I cannot explain only through an Excel worksheet

Hamed Iftikhar
by Hamed Iftikhar , Assistan Manager Accounts/ Finance , The Crescent Textile Mills Ltd

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.

Aftab Khan
by Aftab Khan , Finance Officer , Analytical Solutions PVT Ltd.

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.

More Questions Like This

Do you need help in adding the right keywords to your CV? Let our CV writing experts help you.