Inventory control using FIFO

Hello ALL,

i have a Order Entry and Invoice application with Inventory control i.e when they sell the inventory gets deducted.
Now my customer is asking for FIFO (first in first out)
Question;
how do i go about it?
How to tell the computer which one is first?
What if i have 10 from one purchase and 20 from another purchase with different Cost Price and somebody wants 15.
How to keep track of total for each Item?
and so on,
I also have in the application a Purchasing entry from Vendors and the inventory gets increased automatically.
Do you have an idea or a system how to make it work?
please let me know.
Thanks
LVL 1
frimyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dr. KlahnPrincipal Software EngineerCommented:
As the question stands, there's not enough information to provide a good answer.  Much of this depends on what kind of inventory is going to be handled and what facilities you have, or are willing to purchase, for checkin/checkout.

If it's case-quantity small items such as potato chips, read the manufacture date and the manufacturer's unique case ID off the barcode label on the case when it comes in and when it goes out.

If it's "larger" single units, and here you must define what is "larger" according to the customer's wishes, tell the suppliers you want RFID tags on every incoming item with machine-readable bills of lading including manufacture date.

If it's road graders, then FIFO should not be a problem.
0
Bill BachPresident and Btrieve GuruCommented:
I think you already know how to do it -- and it is REAL ugly.  This is why many systems simply track average cost.

You must track every item coming inbound, along with its cost and date.  You will also need an in-stock count for each individual purchase.  (While you could still have a single line item for the SKU as a whole, this can lead to data issues, so you probably will set up an InStock table with SKU and Order Number as a primary key, and SKU/Date as another key.  To find your current in-stock amounts, you have to sum up the InStock values for each SKU.  This is extra workload behind the scenes, of course, and usually slows a system down, too.  However, if you break with 3NF and store an inventory quantity in the SKU table, you always have a chance to have the SKU record show 34 in stock, but the sum of the InStock records show 33.  Now, you need to write a fix program to fix up the bad number, and so on....

When a sale is made, you'll read the InStock table by SKU and date.  If customer orders 15 and the first item shows 10 in stock, decrement by 10, and add 10*cost to your total inventory value for this sale.  Then, read the next InStock item, decrement by 5, and add 5*cost to your total inventory value.  Repeat until all items are accounted for.   Now, decrease inventory valuation GL account by the sum you've just calculated, because you don't want to miss that, either. And so on....
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnBusiness Consultant (Owner)Commented:
What is your inventory?  If retail kind of items, FIFO is a somewhat complicated way to value inventory. You must count it at regular (even if only annually) and value it to the current standard cost of each item. If standard cost goes down (competition) you could have a write down of inventory. If standard cost goes up (pricing), you could have a write up of inventory value. These changes affect Income.

Keeping inventory at an Average Cost (Average Cost method of retail inventory) is much easier to do because inventory kept at the average cost of inputs and outputs. I have use this in Retail Inventory and it works well. I used FIFO (and LIFO) in Manufacturing inventories some years back.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

David Johnson, CD, MVPOwnerCommented:
in addition to above your order pickers have to be in sync. This also depends upon how you store items. Newest always go to the bottom or back of the pile.
0
Bill BachPresident and Btrieve GuruCommented:
That would be righe for anything with a shelf life. Identical items (i.e. widgets) might not matter. Items with serial numbers are a whole different ballgame as well, as you have to track each serial number separately, too.
0
JohnBusiness Consultant (Owner)Commented:
That is why I am suggesting Average Inventory  -- to ease the workload.
0
PatHartmanCommented:
Lots of questions - no input from frimy.  You're wasting your time until he comes back.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Bill hit it and all you need is a lot table:

tblItemLots
ItemID
LocID
LotNumber
InitialDate
Cost
Qty

  You create one record every time you received something.    When you relieve inventory, you relieve the oldest qty first.   If that lot doesn't have enough qty for what you need to relieve, then you go to the next oldest record.   As mentioned, items can also have a shelf life value.    If the lot is older than the shelf life, then it is not usable.  Here's an example:

Region-Capture.jpg
 all three lines are for the same item / location (which in this system is a warehouse, not a location in a warehouse).    Lot # is ###'s because this system does not have lot tracking turned on.   All they care about is FIFO.

 So if I needed to relieve 15 units, it would be 6 @ $5.65, and then 9 @ $5.787.   First qty would become 0, second 195.

Jim.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
And please understand this can be a monumental undertaking. There's an entire software industry built around inventory control, and some of the commercial solutions run into the hundreds of thousands of dollars. Inventory control can be very tricky, depending on what you need (and it sounds like your client wants a system with all the bells and whistles). It can be done, and it can be done in Access, but it's a bit undertaking.
0
JohnBusiness Consultant (Owner)Commented:
FIFO is NOT a table thing, it is a process to revalue inventory to current standards with the difference going to income.

Think HARD. Average Cost inventory valuation is much simpler.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< Average Cost inventory valuation is much simpler.>>

 Simpler?  Maybe, but you still need to track your costs.  Also, it's not a trivial matter to switch and I believe that some types of businesses are required to use FIFO. NOTE: I am not an accountant.

 But beyond the systems considerations (which are all not that significant in my mind for either), there are many business considerations with the method of costing you choose.   Switching methods is not something that should be done lightly.

Jim.
0
JohnBusiness Consultant (Owner)Commented:
First, following my friend Jim's comment, I am an accounting person and I am versed in Average Cost, FIFO and LIFO (base and layers ).  Average Cost is the simplest to mechanize but of course it requires detailed tracking. It is easier to value within an inventory database.
0
PatHartmanCommented:
FIFO is NOT a table thing, it is a process to revalue inventory to current standards with the difference going to income.
That is not an accurate definition although you are correct in it being a process.  

It is also used for:
  • Controlling tax liability in a portfolio management application.  
  • To ensure selling/using oldest inventory first when the inventory has an expiration date.
You are all just GUESSING and wasting your time.
0
JohnBusiness Consultant (Owner)Commented:
I have done these revaluations so I am not just guessing.
0
Bill BachPresident and Btrieve GuruCommented:
Honestly, folks.... we can debate the advantages and disadvantages all day.  However, the O.P. is a software developer, and he has been asked by his customer for a FIFO solution.  From a coding perspective, this will be a challenge to someone who DOES understand accounting principles, and even more so for someone who doesn't.  If they have a strong desire and the budget to pay for the development effort, who are we to say whether they should do it or not?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
It never hurts to offer alternatives, even to a developer and I don't think we got too far off the beaten path with this one.

 I know many a time a different approach to a problem has been suggested, developer has gone back to the client, and they get a "thanks I didn't think of that" in response.

 As you pointed out, we don't know all the details.

 Many of us are on our own and it doesn't hurt to explore different ideas here.

Jim.
0
frimyAuthor Commented:
I see basically every body is on the same page, that "it's not so simple"
Bill has is more to point saying  << I think you already know how to do it  >>
I have an idea, I just wanted to see what the experts have to say. I wanted see if there is a easier way.

I think I have to the following;
we have to keep each receiving as a unique ItemNo (sub ItemNo for the Main ItemNo)
when entering a Order, the user will enter the Main ItemNo and will be able to select all the sub Items by fifo order, in a sub form.
This Sub form is based on at table i.e. tblOrderSubItems each detail in the [tblOrder detail] will have this [tblOrderSubItem] with all Items selected.
 The [tblOrder detail] will only have the Total Qty from the  [tblOrderSubItem]

All the subItems Qty's has to update while entering into the [tblOrderSubItem].

This is only the beginning, then we need if any in the subitems was changed.
and to be able to see Qty on hand by items and the details.
Same has to be done with purchasing and on and on.

And at the end, it also has to work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.