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