FIFO, LIFO and AVERAGE method

aarontham
aarontham used Ask the Experts™
on
I designing a inventory system.
can i have some sample table which can do FIFO, LIFO and AVERAGE method.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Any well designed transaction journal can be used for LIFO or FIFO; however, handling the Average requires a bit more information/handling.
Let's say you have the following received goods and sold goods entries:
Direction       QTY       Price       Current Invetory Value    Avg Cost
 Nothing          0             0.00                     0.00                         0.00
   RCVD         50             1.00                  50.00                         1.00
   SOLD          25            2.00                   25.00                         1.00
   RCVD          50            1.50                 100.00                        1.33
   SOLD          25             3.00                   66.67                        1.33
Whereas you can handle the LIFO/FIFO strictly off of the Journal, the Average has to be maintained somewhere (usually in the InventoryItem's row).
So, those are 2 separate tables.
You may want to do a bit more reading on the three costing methods. ;-)
Kevin CrossChief Technology Officer
Most Valuable Expert 2011
Commented:
Agree with 8080_Diver.  Just remember you will want to have a date column, otherwise, you will have difficulty seeing the chronology of transactions which is especially important for FIFO / LIFO, since you need to know the first or last RCVD cost.

As pointed out, the ledger or table should be able to handle whatever as it is just storing what your application (business logic) code is calculating.  But for what it is worth, remember to take into consideration what you want to do with negative inventory.  We all say this should *never* happen, but in the real world folks drive inventory negative all the time so often it is unconsidered in most systems and result in a flawed ledger.

For example, consider this scenario:

Item 123 has current cost of $1, when 2 are shipped resulting in ($2) inventory value.
Item 123 is then received in quantity of 4 from work/purchase order for cost of $1.50 each.

If you let the default mathematical equation calculate, it will do the following:
4 * 1.50 + (-2.00) = $4.00 new inventory value
4 + (-2) = 2 items on hand
4.00 / 2 = $2.00 new cost

Notice you now have an average that is higher than any previous actual.  Is this ok?

If not, then you can consider some options like treating negative as 0 making the new cost $1.50 when the 4 items are received.  Your negative quantity will still net the on hand balance to 2 making the total new inventory value $3.00.  Alternatively, you can use absolute value for calculation of cost independently.  
abs(-2.00) + abs(6.00) = $8.00
abs(-2) + abs(4) = 6
8.00/6 = $1.33 new average cost
(-2 + 4) * 1.33 = $2.66 current inventory value

Hope that helps!

Kevin
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Here is a previous question that may have some additional information of interest:
http://www.experts-exchange.com/Database/MySQL/Q_24844550.html
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
aarontham, *laughing* I guess I didn't look to see that it was you.  Figuring you already know about the other question, so never mind my previous link -- though, it might serve as a nice reference here for other readers.  

To that point, though, it would be nice to hear how much you have progressed in the last year.  Hopefully this is going well for you.
I have to question how the accepted "answer" is an answer at all.  Accepting a social comment as an answer out of friendship will tend to discourage experts from resonding to questions by the author because there is no connection between helping the author and having one's answer accepted.
@Moderator,
Please explain to me how the answer was accepted and I received an email at 09:21 pm CDT on October 25 and the acceptance is finalized as of 09:00 October 26!  Had I been given the chance, I would have objected to the selected "answer" being accepted. :-/

Author

Commented:
HI 8080 Diver,

is you who ask me to read more on the three costing methods. so i thought you are not interested to comment for more.

and  i already forget what mwvisa1 was explain last year. that why i ask again same question.

and mwvisa did comment for more on this post.

pls advice.
aarontham,
I not only suggested that you read more but also provided more information, with which mwvisa agreed.  My point in my most recent message was that, if you intended to select mwvisa's comment that included the link as the solution, then you shouldhave done so rather than selecting the one in which mwvisa, basically, made a social comment.
 
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
@aarontham,

Supporting 8080_Diver, the issue isn't that I was awarded solution, but the comment which was awarded.  I too would have expected:

http:#a33963547
http:#a33963530
http:#a33962752

Or any combination therein to have been selected as solution.  If you would like to correct this, you can ask a Moderator to re-open the question on your behalf.  There will be no objections from me.  Just glad we could help and glad that my posting a link to your own question was actually helpful << I felt silly after that one. *smile*

Kevin

Author

Commented:
Guys,

sorry i guess there is some miss understandting here.

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
No need for apologies.  Just glad we could help.

Best regards and happy coding (or accounting),

Kevin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial