Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2006
    Posts
    10

    Question Help on Database design for Inventory with FIFO costing

    Hello. I am a software engineer and I am trying to design a database for an inventory module. I have a problem about the application of the costing methods (e.g. Standard/FIFO/LIFO/Average/etc.).

    Here's what I have come up with after numerous design modifications and issues.
    Basically, I have a record of all stock transactions in a header and a line table, called StockTransactionHdr and StockTransactionLne, respectively. These tables will record all types of stock transactions (purchases/receipts, issuances, adjustments, sales). To make discussion simpler, I will just combine both into one table called StockTransaction and remove all other unnecessary fields.

    The fields are as follow.

    StockTransactionID | Type | Date | ItemID | Qty In | Qty Out | Cost

    Let's say we have the following transactions for Item A w/ FIFO costing. Let's also assume we have no prior transactions.
    Note: Please forgive me for the alignment problems. HTML Tables are not allowed. I hope it's understandable.

    Transaction # | Type | Date/Time | ItemID | Qty In | Qty Out | Unit Cost
    1 Purchase 7-25-2006 A 2 0 $1.00
    2 Purchase 7-26-2006 A 5 0 $1.50
    3 Issuance 7-26-2006 A 0 1 $1.00
    4 Sale 7-27-2006 A 0 3 $1.33 (aprox.) = 1 @ $1.00 and 2 @ 1.50
    For purchases/receipts we know the cost w/c is the purchase price (plus other charges like freight/delivery but more on this later).

    In the example we bought item A at 2 for $1.00 and 5 for $1.50 for Transactions 1 and 2.

    In transaction 3, we issued 1 of item A for internal use. Since this item uses FIFO, the cost will be from the first purchase w/c is $1.00. Our stock on hand of item A is now 6. And the value/cost of our stocks for item A is 1 @ $1.00 and 5 @ $1.50.

    In transaction 4, we sold 3 of item A. Based on our stock on hand and cost, selling 3 of item A cost us 1 @ $1.00 and 2 @ $1.50. Total Cost of Sale = $4.00. Dividing it by the number of items sold means our cost of sale per unit is approx. $1.33.

    Now here's the problem, I wanted the transactions to be very flexible to changes. I want to be able to enter the transactions in any order and delete or modify them as much as possible with a few restrictions, and the costs will adjust based on the "Ins" to the inventory.

    To do this in an efficient manner, I'm thinking of giving all deductions to the inventory no cost (yet). There will be another procedure to update the costs in whatever costing method is applied for that item. Maybe this could be done at the end of the day.

    So the data will look like this after encoding:

    Transaction # | Type | Date/Time | ItemID | Qty In | Qty Out | Unit Cost
    1 Purchase 7-25-2006 A 2 0 $1.00
    2 Purchase 7-26-2006 A 5 0 $1.50
    3 Issuance 7-26-2006 A 0 1 NULL (to be calculated)
    4 Sale 7-27-2006 A 0 3 NULL (to be calculated)

    After running the Calculate Costs procedure:

    Transaction # | Type | Date/Time | ItemID | Qty In | Qty Out | Unit Cost
    1 Purchase 7-25-2006 A 2 0 $1.00
    2 Purchase 7-26-2006 A 5 0 $1.50
    3 Issuance 7-26-2006 A 0 1 $1.00
    4 Sale 7-27-2006 A 0 3 $1.33


    The Calculate Costs procedure is quite costly and complicated but this will guarantee accuracy and the least redundancy of data. And doing this procedure must start at the beginning of all transactions. The more transactions, the slower it goes. I still can't find a way to make the Calculate Costs not start at the beginning. Any suggestions?

    Aother solution might be to add a quantity available column like so:

    Transaction # | Type | Date/Time | ItemID | Qty In | Qty Out | Unit Cost | Qty Available
    1 Purchase 7-25-2006 A 2 0 $1.00 2
    2 Purchase 7-26-2006 A 5 0 $1.50 5

    Every time we add stock, this is the same as qty in. Every time we deduct stock, we look for the oldest transaction with qty available > 0 and use it in our costing. We also deduct the qty available by the qty we deducted. Of course, if the qty available is not enough, we go the next transaction with qty available > 0.

    So after transaction 3, the data will look like:

    Transaction # | Type | Date/Time | ItemID | Qty In | Qty Out | Unit Cost | Qty Available
    1 Purchase 7-25-2006 A 2 0 $1.00 1
    2 Purchase 7-26-2006 A 5 0 $1.50 5
    3 Issuance 7-26-2006 A 0 1 $1.00 0

    After transaction 4, the data will look like:

    Transaction # | Type | Date/Time | ItemID | Qty In | Qty Out | Unit Cost | Qty Available
    1 Purchase 7-25-2006 A 2 0 $1.00 0
    2 Purchase 7-26-2006 A 5 0 $1.50 3
    3 Issuance 7-26-2006 A 0 1 $1.00 0
    4 Sale 7-27-2006 A 0 3 $1.33 0

    The problem with this is that we must enter the transactions in sequential order as to time. And this will wreak havoc when we allow modifications/deletions with the transactions. We have to validate that we can't delete records whose qty available is less than the qty in (since their costs are used in succeeding deductions). And when deleting deductions (sales/issuances), we must also recompute the qty available for the previous "Ins" (purchases/adjustments) affected and any existing transactions for succeeding dates will also be affected. We are having more problems with this solution.

    Moreover, it is a requirement that additonal costs be added later when they are known like Freight/Wharfage/Taxes/Trucking/etc. And these costs will be distributed to the cost for the transaction by either weight/cost/qty thereby increasing the cost for the "Ins". And ultimately, cascading these costs to succeeding transactions. There will be another table to store additional costs and the result will increase the cost. Example, The data will become:

    Other Costs Table:
    Transaction # | Cost Type | Amount
    1 Document Stamps $1.00
    2 Document Stamps $1.00

    Transaction # | Type | Date/Time | ItemID | Qty In | Qty Out | Unit Cost | Qty Available
    1 Purchase 7-25-2006 A 2 0 $1.50 0
    2 Purchase 7-26-2006 A 5 0 $1.75 3
    3 Issuance 7-26-2006 A 0 1 $1.50 0
    4 Sale 7-27-2006 A 0 3 $1.67 0

    The customer wants to really see the unit cost of the purchase including other costs. I think this is known as landed cost.

    I'm also thinking of putting a posting procedure that will finalize these transactions so that no direct modifications will be allowed. But, I'm still confused how this will be implemented in the database.

    Any suggestions how to solve this problem? Is my solution too complicated to work? Any other robust designs you know of?
    A good thing to note about my design is that it is easy to get a transaction history.
    Last edited by chris.jurado; 07-27-06 at 13:03.

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I think you are approaching your transactions in the wrong manner.

    A transaction has the following characteristics:

    1. Time
    2. Cause
    3. Location
    4. Object
    5. Method
    6. Actor

    And the following frequencies:

    1. Quantity
    2. Price

    The characteristics are textual values. The frequencies are positive or negative numeric values.

    Typical transactions may look appear as follows:

    001 2006-07-25 Purchase Store005 Widget CreditCard John Doe +5 -$100.00
    002 2006-07-26 Order Store007 Gizmo Cash Jill Smith -6 +$120.00

  3. #3
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    You should have as many Causes as you have phases in your business. For example:

    1. Purchasing
    2. Receiving
    3. Stocking
    4. Ordering
    5. Packing
    6. Shipping
    7. Invoicing
    8. Mailing
    9. etc.

  4. #4
    Join Date
    Jul 2006
    Posts
    10
    I am simplifying the problem because that is not the issue. The main concern is on calculating the cost of sale for sales and how to allow for modification/deletions to the transactions while maintaining consistency with the cost of sale in a LIFO/FIFO fashion.
    I am only assuming there is one item and only the necessary fields to explain the problem is shown. This does not reflect actual implementation.

    So, any ideas?

  5. #5
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    If you are a software engineer, you will understand that you cannot treat a cat like a dog, just because you want to measure the cat's bark. No you have to treat the cat like a cat, and a dog like a dog because that is what they are, not what you want them to be. THEN, you can measure their bark, etcetera.

    You cannot obtain a solution by focussing on the solution; you have to analyse the problem and break it down into components; then you have to manage th components based on what they are.

    You are asking this forum a question. Listen to certus, he has bothered to spend time on your problem.

  6. #6
    Join Date
    Jul 2006
    Posts
    10
    Ok. Maybe you did not understand the problem.

    If you are a software engineer, you will understand that you cannot treat a cat like a dog, just because you want to measure the cat's bark. No you have to treat the cat like a cat, and a dog like a dog because that is what they are, not what you want them to be. THEN, you can measure their bark, etcetera.
    Weird analogy. Don't think that applies much here.

    You cannot obtain a solution by focussing on the solution; you have to analyse the problem and break it down into components; then you have to manage th components based on what they are.
    I did explain the problem pretty well and detailed enough even giving samples of what happens step by step. I also gave all the necessary components/data/fields to analyze the problem.

    Ok, since you want it that way, I'll use something like certus' table. There is not much difference between my simplified table and certus' simplified table anyway, if you really compared them. Maybe there are just some added fields but those are not necessary to analyze the problem. And mind you, I have those fields--even more. And even if I use certus' model, that still does not solve or help analyze the problem.

    So, here's certus' simplified table:

    Time | Cause | Location | Object | Method | Actor | Qty | Price

    Many of these are irrelevant, namely--Location/Warehouse, Method/Mode of Payment, Actor/Supplier. Plus, we'll focus on one object/item since having plenty is irrelevant as we'll only look at one. This is what I meant by simplification. It is better to discuss without these. And DerakA seems to want the whole thing w/c makes it more difficult to comprehend. As Albert Einstain said and I quote: "Make everything as simple as possible, but not simpler".

    001 2006-07-25 Purchase Store005 Widget CreditCard John Doe +5 -$100.00
    002 2006-07-26 Order Store007 Gizmo Cash Jill Smith -6 +$120.00
    We also need to distinguish between selling price and cost of sales. My discussion only focuses on cost of sale or unit cost or how much did you spend to get that sale/order. Certus' sample transactions above is confusing about the price for the order--is this selling price or the cost of sale? Both should be separate. Bet never mind that, we're only talking about the cost of sale or unit cost.

    So, let's say we have the following transactions. The costing method is First-In, First-out (FIFO). Let's say we have zero stock for the item at the start.

    # Time | Cause | Qty | UNIT COST | Selling Price (not relevant, but shown for emphasis)

    1 2006-07-25 Purchase +5 $1.00 NULL
    2 2006-07-26 Purchase +5 $1.50 NULL
    3 2006-07-27 Purchase +10 $1.75 NULL
    4 2006-07-28 Sale -4 $1.00 $2.00
    5 2006-07-29 Sale -2 $1.25 $2.00
    5 2006-07-30 Sale -5 $1.55 $2.00

    Resulting Stock on Hand: 9 at $1.75.

    I hope you understand how the unit costs for transaction 3,4 and 5 are computed using FIFO. Or do I have to explain FIFO costing? There are other costing methods but FIFO/LIFO are the interesting ones.

    The problem is this.
    1. I want an efficient way to calculate the next cost of sale.
    2. I want to allow editing/deleting of transactions as much as possible and the costs for sales should correctly cascade in a FIFO fashion efficiently without passing by all transactions or starting from the beginning.

    I already found a good solution but it might be an interesting problem to ponder about.

  7. #7
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I agree with you. Some parts of my example are outside the scope of your problem, so I can understand there being confusion.

    I would appreciate if you would explain your understanding of FIFO costing.

    I am thinking of an alternate solution, but I don't want to put in the effort of presenting it until I am certain we are on the same page.

  8. #8
    Join Date
    Jul 2006
    Posts
    10

    Smile

    Ok, FIFO goes like this. Basically, the cost of sale is computed from the oldest purchase price or cost but taking into consideration the many you have for that cost. If the qty is not enough for your sale, then you would have to include the next oldest purchase price and get the average cost.

    In our example:
    1 2006-07-25 Purchase +5 $1.00 NULL
    2 2006-07-26 Purchase +5 $1.50 NULL
    3 2006-07-27 Purchase +10 $1.75 NULL

    Then, we sell 4 units.
    4 2006-07-28 Sale -4 $1.00 $2.00

    To get the cost we use cost of transaction 1 since it is the oldest. So, we use 4 units @ $1.00 based on transaction 1. This also means 1 unit from transaction 1 was still left for costing.


    Next, we sell 2 units.
    5 2006-07-29 Sale -2 $1.25 $2.00

    Since we have 1 unit left from transaction 1, we use that. But, we sold 2, so we get the next one from transaction 2 w/c is at $1.50.

    Cost for transaction 5 = (1 ($1.00) + 1 ($1.50)) / 2 units = $1.25/unit

    Next, we sell 5 units. (I had a typo error before. this is transaction 6. not 5)
    6 2006-07-30 Sale -5 $1.55 $2.00

    Using the same pattern:

    Cost for transaction 6 = (4 ($1.50) + 1 ($1.75)) / 5 units = $1.55/unit

    Got it?

  9. #9
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Yes. However, I do not think this is a legitimate FIFO accounting practice.

    The correct formula is:

    Beginning Inventory + Net Purchases - Ending Inventory = Cost of Goods Sold

    Transaction 1 through 3 would give you:

    0.00 + (5 (1.00) + 5 (1.50) + 10 (1.75)) - 0(0.00) = 30.00

    Transaction 4 would give you:

    30.00 + 0.00 - (1(1.00) + 5(1.50) + 10(1.75)) = 4.00

    Transaction 5 would give you:

    26.00 + 0.00 - ( 4(1.50) + 10 (1.75)) = 2.50

    Transaction 6 would give you:

    23.50 + 0.00 - ( 9(1.75)) = 7.75

    A seventh transaction could be:

    7 2006-07-30 Sale -9

    Transaction 7 would give you:

    15.75 + 0.00 - 0(0.00) = 15.75

    However, even this is not valid FIFO. For FIFO do not concern yourself with cost per transaction at all. Unless you make a physical confirmation of the inventory after every transaction. If you are making a purely analytical tool; you can break the rules all you want, but you cannot depend on the results matching the true inventory.

    In normal practice a physical inventory would be taken at the end of each accounting period giving us the following after Transaction 6:

    Sales --(11@2.00)-----------------------------$22.00

    Inventory 01/01/07------00.00
    Purchases---------------30.00

    Cost of Goods Sold-----------------------30.00
    Less:FIFO Inventory 12/31/07--(9@1.75)--15.75


    Net Cost of Goods Sold-------------------------$14.25

    Gross Profit on Sales---------------------------$07.75
    Last edited by certus; 07-29-06 at 05:19.

  10. #10
    Join Date
    Jul 2006
    Posts
    10
    Beginning Inventory + Net Purchases - Cost of First In Queue (Quantity) = Ending Inventory
    I disagree. I don't think this is just the "First In Queue". This would cause your accounting to be imbalanced. It should take into consideration the qty and the next in queue in case the qty is not enough.

    Transaction 5 would give you:

    26.00 + 0.00 - 2 (1.00) = 24.00
    This is the part that causes the imbalance. It should have been:

    26.00 + 0.00 - 1 (1.00) - 1 (1.50) = 23.50

    In your example, you undervalued your cost. Imagine this simpler example.

    #1. you purchased 1 at $1.00.
    #2. you purchased 1 at $1.50.
    At this moment, you have inventory worth 1(1.0) + 1(1.50) = $2.50

    #3. you sold 2 units. Do you mean this will cost us $1.00/unit as cost of goods sold? This will cause an imbalance in the accounting since you deducted only $2.00 from your inventory. Subtracting this from our existing inventory of $2.50, this leaves you with $0.50 worth of inventory. Why do you still have $0.50 of inventory when in fact you sold them all already? There is none left in your stock! You sold both your items. It should be zero.

    In accounting, these should be the entries (simplified) :

    #1
    Inventory - Debit 1.00, A/P Supplier - Credit 1.00

    #2
    Inventory - Debit 1.50, A/P Supplier - Credit 1.50

    #3
    Cost of goods sold - Debit 2.50, Inventory - Credit 2.50
    (in your case, do you mean we debit and credit $2.00 instead of $2.50?)
    A/R Customer - Debit XXX, Sales - Credit XXX

    I heard about perpetual method of costing vs. periodic inventory method. Maybe you are doing the periodic inventory method (and in a mistaken way, I think. correct me if I'm wrong). In computerized systems, it would be preferable to have the perpetual method of costing. It gives more detail and insight. You can get a financial statement at any point in time. You can get information on a daily basis--not only on month end. And other useful information is immediately available. Like, the customer wants to know the exact cost per unit since how can he sell if he doesn't know the cost. Plus, some businesses I know adjust their selling price to a certain margin beyond the unit cost w/c means they need to know the exact cost per unit.
    Last edited by chris.jurado; 07-29-06 at 05:25.

  11. #11
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    See what I mean ...

    I have three rather more fundamental (higher level, more generic) problems with your question (and your responses to the answers other have provided).
    1 You are using acronyms which have meaning but you have a private definition (eg. FIFO)
    2 You have already decided what is meaningless and meaningful (rather than completing the analysis before making that decision; rather than letting us make that decision). Therefore all the cards are nto on the table. You have not "explained the problem pretty well at all".
    3 You do not appear to appreciate that db design, modelling, is the result of a process of normalising the data, INDEPENDENT of the processing [eg. programming] needs. You are focussed on your processing needs, which is understandable, since you have gone through many mods, etc., but it IS a limitation. Modelling will put cats and dogs in the correct tables, allow joins to animals, etc. I do not intend that as analogy, I intend it as a completely removed example.

    If you want to explain the problem, give us your input forms and your output reports.

    Cheers
    Last edited by DerekA; 07-31-06 at 02:33.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  12. #12
    Join Date
    Jul 2006
    Posts
    10
    1 You are using acronyms which have meaning but you have a private definition (eg. FIFO)
    That is not a private definition. It is understood in the realm of cost accounting. I was hoping for someone knowledgeable on Inventory costing. I've studied FIFO costing through multiple sources (books, internet). It is what I understand of it. And I may be mistaken in my understanding. I'm not all-knowing and perfect. So go ahead and correct me if I'm wrong. We are here to learn.

    2 You have already decided what is meaningless and meaningful (rather than completing the analysis before making that decision; rather than letting us make that decision). Therefore all the cards are nto on the table.
    What "cards"? Why don't you tell me exactly what information I did not give necessary to solve the problem? You are being too general.

    You have not "explained the problem pretty well at all".
    Each is entitled to his own opinion. And some people understand, some don't. If you can't understand, why don't read the problem very well and ask specific questions? If you really wanted to help, that is what you should have done. Like what Certus did.

    3 You do not appear to appreciate that db design, modelling, is the result of a process of normalising the data, INDEPENDENT of the processing [eg. programming] needs. You are focussed on your processing needs, which is understandable, since you have gone through many mods, etc., but it IS a limitation. Modelling will put cats and dogs in the correct tables, allow joins to animals, etc. I do not intend that as analogy, I intend it as a completely removed example.

    If you want to explain the problem, give us your input forms and your output reports.
    I am not here designing a whole application/system. It IS only a part of it (some procedures) that we are discussing. We need a design or pseudo-design that will make these procedures efficient and flexible.

    1. Calculating next cost of sales for a stock transaction that deducts from inventory.
    2. Adjustment/Cascading/Correction of cost of sales after editing/deleting stock transactions that deducts from inventory.

    I already gave all necessary information (data, fields, sample transactions) for the problem. I cannot "lay all the cards". That is impractical. All that information will already make a book. I will only give the necessary. If you think some information is lacking, be specific. Tell me what.

  13. #13
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    Relax, mate

    I will only give the necessary. If you think some information is lacking, be specific. Tell me what.
    That is what YOU think is necessary.

    In that case, sorry, I cannot help you. The context is too large and undefined, and the question and answer required is too narrow. For my small mind, at any rate. I have done my time as a maintenance programmer, and I can smell it before I call it.

  14. #14
    Join Date
    Apr 2008
    Posts
    1
    Thank for this thread, it give me an idea to design my accounting database.

    I have another problem, when i need to average the cost of goods. (when stock in / return).

    At the end of the month. The P&L for the closing stock:
    If i use the 1200(qty in hand) @ 4.0941 = 4912.92

    If i use the (opening stock + purchases) - (sales's cost) = 49.12.94

    Both are not tally. In accounting practice, which method should i use?

    ** Noted: My client request to average the cost for (stock in / stock return)

    All the calculation above is on cost, not selling price.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Chris Jurado's method of calculating FIFO is incorrect. It does, in fact, use a "Queue" algorithm.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •