I have my inventory system set up using average cost as the basis for costing. I am now looking at ways to approach costing as either LIFO (last in first out) or FIFO (First in first out). The only way I can think of doing this is to create an inventory table that has a child table linked by product code to the parent inventory table. The records of this child would essentially hold the purchase price of the stock, the quantity of the purchase, the current quantity of the stock and a calculated field that yields the difference of the previous two. It would also contain a purchase date. What I envision is that whenever a customer makes an order the program will find the last batch purchased ( that is if we choose the LIFO method of inventory valuation) and subtract the quantity from it the costing will be at the actual cost at the time of purchase. the only other way I can think of is to actually generate an actual stream of records for each inventory item purchased so that whenever a sale occurs the inventory will be reduced by that amount. Any more ideas?
-
-
RE: Lifo Fifo or Average value
The best way to accurately keep track of that information is to set-up a database that tracks inventory levels to keep accurate pricing. Relieving and receiving into levels as appropriate.
For example:
A record for each receipt at that specific price is created as inventory is received. Then as invty is relieved, qty is pulled from the most recent level until either that level until that level is used up. When a new receipt is recorded, if it is the same price, it can be received into the current level, however if the price is diff, a new level is created.
RECEIPTS:
10/10/2000 + 20,000 12.50/ea L_1
10/12/2000 + 5,000 13.25/ea L_2
10/14/2000 + 7,500 11.80/ea L_3
10/15/2000 + 2,000 11.80/ea L_3
10/20/2000 + 15,000 10.25/ea L_4
ISSUES:
10/13/2000 - 13,000 LVL_2 -5,000 (l_2 depleted)
LVL_1 -8,000 (l_1 reduced)
10/17/2000 - 12,000 LVL_3 -9,500 (l_3 depleted)
LVL_1 -2,500 (l_1 reduced)
10/21/2000 - 9,500 LVL_4 -9,500 (l_4 reduced)
EOM Levels
Level_1 9,500 @ 12.50/ea = $118,750.00
Level_4 5,500 @ 10.25/ea = 56,375.00
------------
$175,125.00
============
I probably went into way more detail and info than you need. But just wanted you to be aware of what is involved. Also, their are tax implications switching to LIFO or FIFO, which I'm sure you are probably aware of.
-
RE: Lifo Fifo or Average value
Mark I am haing trouble understanding you levels system. Can you enlighten me on how you tables are structured?Comment
-
RE: Lifo Fifo or Average value
Greg - You may want to take the time to go to the "Business Owners Toolkit" - topic, "Identification of Inventory Items". This is a very complete analysis of the FIFO and LIFO systems, how to evaluate your inventory, how to arrive at cost of goods sold, etc.
goto www.toolkit.cch.com/text/PO7_2334.asp
LowellComment
-
RE: Lifo Fifo or Average value
Greg,
You probably should have a separate price table and quantity table for inventory in order to do this data chore. Your inventory objects would be uniquely linked to the child historical price associated with each order quantity. Also review the LIFO and FIFO references previously mentioned.
For FIFO cost you would total the value from the start of the quantity and prices list for the number of items currently sold. For a LIFO cost you total the value from the end of the quantity and prices list and work toward the start until you've costed the number of items sold.
I like to add a start date and end date to my child price history records. If there is a question about which price was in effect, you can compare this with the date of the order to check for entry errors and other linking problems.
DaveComment
Comment