Help on Database design for Inventory with FIFO costing
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Help on Database design for Inventory with FIFO costing

  1. #1
    Join Date
    Jul 2006
    Posts
    6

    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.).

    How do you design that?
    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.

    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-2006 at 12:10 PM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It seems that you have a handle on the logic, but the problem you have is possibly with the performance of the code. I would argue in favour of calculating the costs of deductions at the time at which they are executed, and optimizing the physical structure of the data by implementing a hash cluster on ItemID in which you create the required transaction tables. This willl co-locate records for a given ItemID and vastly reduce the amount of physical reads you typically require to retrieve all the records for that ItemID.

    It seems to me that you also need to provide a serializing mechanism for these transactions, so that two transactions cannot execute deductions simultaneously, which could lead to incorrect results for one of them. One handy way of doing this would be to use a table of ItemID's in which you lock the record corresponding to the required ItemID by means of a "Select for update". This would tie in very nicely with your idea to store "quantity available", which would be placed (maybe with the transaction id's for the last purchase, issuance and sale) in that same table. That table would also be a member of the cluster, of course.

    That table does represent a denormalisation, which I'd not be generally in favour of because it reduces concurrency (aside from the regard for good theoretical practices in general), but in this case you really do need a serialization mechanism if you are to price the sales at the time they occur.

    Hope that helps.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I have 2 questions:
    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)
    Take the SALE transaction, 4. Qty sold 3.
    How do you know one item come from the first purchase, and 2 items come from the 2nd purchase? How do you physically handle this in the store?

    2nd question:
    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.
    Does one invoice show 2 different rates for the same item?

    Dave, Instead of HASH cluster, did you consider IOT?

    Tamil
    Last edited by tamilselvan; 07-25-2006 at 12:53 PM.

  4. #4
    Join Date
    Jul 2006
    Posts
    6
    Thanks for your reply.

    The "store available qty" solution will work but because of its denormalization, we don't have the flexibility to easily edit/modify the records due to user mistakes. I still want the flexibility to edit/correct the input. This is also a requirement since some of the costs associated with a purchase comes late. The supplier was already paid but the freight costs/taxes/trucking/etc. costs are not yet known. So, when we issue/sell the item, its expense incurred or the cost of goods sold is actually the purchase price plus the other costs. But these other costs are known later. W/c means a lot of transactions may be affected.
    Anyone knwledgable on cost accounting?

  5. #5
    Join Date
    Jul 2006
    Posts
    6
    Tamilselvan,

    This has nothing to do with which item was sold. That does not matter. What matters internally is how much income are you going to recognize upon sale? Ex. you bought item at $1.00. Next day bought at $1.50. Your selling that item for $2. You made 1 sale. It does not matter which one you gave--the first or the second. What matters is how w/c income you will recognize first--the $1 income or the $0.50. Hence, the reason for LIFO and FIFO costing. This is cost accounting. There are other costing methods too.

    Note, that a sale is composed of the following debits/credits to accounting ( if you know accounting):
    1. We decrease/CREDIT our Inventory account by the cost you acquired the item. (By how much? The $1 or the $1.50?)
    2. We increase/DEBIT Cost of Sales by the equal amount as in no. 1.
    3. We Increase/DEBIT the Account Receivable or the Cash on Hand or Cash in Bank account depending on the mode of payment of the customer. (This is the selling price w/c is $2).
    4. We Increase/CREDIT the Sales Account for the item. This is equal to no. 3.
    And your Income is basically your SALES - COST OF GOODS SOLD.
    Take note of the difference between cost and selling price, as the difference is your income.
    Last edited by chris.jurado; 07-25-2006 at 01:20 PM.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    It does not matter which one you gave--the first or the second.
    I am not a cost accountant. In 1994 I developed the costing system that solved exactly your requirements.

    It is easy to write a PL/SQL procedure to get the COST of an item.


    Tamil
    Last edited by tamilselvan; 07-25-2006 at 02:45 PM.

  7. #7
    Join Date
    Jul 2006
    Posts
    6

    Exclamation

    No, you don't get the problem.
    It is not that efficient to get the cost in a First-In, First-out (FIFO) fashion.

    To get the cost of an inventory deduction (either sale/issuance) in a FIFO fashion, there are two methods I can think of:

    1. Calculate on the fly.
    You would have to get a sequential list of all stock additions (purchases/other adjustments) and subtract your deductions (sales/issuances) and get the next available cost(s). And this procedure has to start from the beginning. The more transactions exist in the table, the more computations are done, the slower it becomes.
    Plus, I want to allow modifications to any transaction's quantities/dates/other information as much as possible w/c would mean that these deletions/modifications will again cause recalculations to the cost for existing transactions that follows our deleted/modified transaction.

    Tamilselvan, Are you saying this is how you did it? And what about changing the order of transaction inputs, or changing the dates of the transactions? Will your cost recalculate because the order has changed?

    This is why FIFO is difficult in this case, but it is a requirement.

    2. I proposed an alternative solution requiring an additonal column in the transactions to store the available qty for use in costing. That way we would not have to start from the beginning and just use that column. This is explained in first post.
    But, this would limit you from flexibility in modifications and the transactions have to be input sequentially and not in any order.

    So, how de we get the cost efficiently while allowing cost to be corrected upon deletions/modifications to existing transactions?

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Tamilselvan, Are you saying this is how you did it? And what about changing the order of transaction inputs, or changing the dates of the transactions? Will your cost recalculate because the order has changed?
    Yes, but with 2 restrictions.
    1 The cost calculation is done end of the month.
    2 End users are restricted to change/modify only last 30 days of transactions.

    Tamil

  9. #9
    Join Date
    Jul 2006
    Posts
    6

    Smile

    Thanks, tamilselvan.

    You confirmed the solution I'm thinking about but I just had a slight variation. I'm applying my "Availably Qty" solution to improve performance on cost calculation. Any deductions to inventory will have no cost yet. There will be a Finalize procedure that will assign the costs up to a certain date that the user (w/c has the appropriate right to do so) specifies. Before doing this, all transactions not finalized on or before that date must be reviewed and deemed correct. Once costs are calculated no deletions/modifications will be allowed. Any additional transactions' date must also be beyond any finalized stock transaction.

    Sorry if that accounting discussion may have kind of insulted you, I think. My apologies. I am also not a cost accountant--not even close. I just know a little.

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Sorry if that accounting discussion may have kind of insulted you, I think. My apologies. I am also not a cost accountant--not even close. I just know a little.
    Not at all.

    I had developed accounting system and cost accounting system in the past for many comapanies. But before designing and developing the systems in late 80s and early 90s, I got basic training from a cost accountant. It helped me a lot next 15 years.
    It is nothing wrong to go for training where our knowledge is ZERO, otherwise we will never come up in our life.

    Tamil

Posting Permissions

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



Click Here to Expand Forum to Full Width