October 18, 2006 at 11:44 am
I have done a fair amount of research on this including Joe Celko great articles at http://www.dbazine.com and I'm still comming up a bit short.
My inventory is not in buckets, but more of a log format. One table that stores receipts, adjustments and sales. Onhand totals are all calculated. Here is the simplified table and sample data:
CREATE TABLE [i_invent] ( [item] [char] (10), [trans_type] [char] (1), [quantity] [numeric](8, 2) , [unit_cost] [money] NOT NULL , [date_time] [datetime] NOT NULL )
INSERT INTO i_invent SELECT 'ADVIL ','P', 25, 2.00,'2004-06-11 13:46:49.000' UNION ALL SELECT 'ADVIL ','P', 6, 2.75, '2004-06-28 19:37:57.000' UNION ALL SELECT 'ADVIL ','S', -1, 0.00, '2004-06-29 10:21:31.000' UNION ALL SELECT 'ADVIL ','S', -1, 0.00, '2004-08-12 10:20:35.000' UNION ALL SELECT 'ADVIL ','S', -1, 0.00, '2004-09-27 10:10:50.000' UNION ALL SELECT 'ADVIL ','S', -1, 0.00, '2004-09-27 10:11:44.000' UNION ALL SELECT 'ADVIL ','S', -1, 0.00, '2006-10-16 17:30:50.000'
Right now, only average weighted cost is offered. This does not fit the bill too well as suming to the begining of time presents a few problems. One of them being really old costs are always affecting the current cost, when it shouldn't)
I'm trying to come up with 3 solutions.
1 - average weighted cost - Instead of summing to the begining of time, only look back at costs as far as the current onhand inventory goes. (call it a modified LIFO?) In the above example, the onhand is 26 so it would include both receipt records to come up with the average weighted cost (onhand value = 55.774186). However, if there were only 5 left, the only cost record to be counted would be the one for 2.75.
2 - Genuine FIFO - the onhand value would be 52.75
3 - Genuine LIFO - the onhand value would be 56.50
Does anyone have an idea of how to approach this?
Many thanks in advance! - Tim
October 18, 2006 at 11:35 pm
An observation/question to clarify. I believe that LIFO stands for "Last In First Out" & FIFO "First In First Out". If so, then the values you have for solutions 2 and 3 should be inversed. LIFO = 52.75 & FIFO = 56.50
FIFO
P/S Qty Cost TotCost RunningBalance
P 25 2.00 50.00 50.00
P 6 2.75 16.50 66.50
S -1 64.50
S -1 62.50
S -1 60.50
S -1 58.50
S -1 56.50
LIFO
P/S Qty Cost TotCost RunningBalance
P 25 2.00 50 50
P 6 2.75 16.5 66.5
S -1 63.75
S -1 61.00
S -1 58.25
S -1 55.50
S -1 52.75
The best way I have found to accomplish this kind of process is using temporary tables with a identity column & a cluster index on your date_time column. To do FIFO you'll pick your first product with a valid quantity, and loop through your temporary table to match records one by one with the same item and either adding or substracting the values from the quantity column.
Here's a script I just wrote to give you an idea of the process
CREATE TABLE dbo.#i_invent (
itemID int IDENTITY(1,1),
Item char(10),
trans_type char (1),
quantity numeric(8,2),
unit_cost money NOT NULL ,
date_time datetime NOT NULL)
CREATE CLUSTERED INDEX idx_i_invent_date_time ON dbo.#i_invent(date_time)
CREATE TABLE dbo.#i_inventJOIN (
Purchase int,
Sale int,
SettleQuantity numeric(8,2))
DECLARE @PID int,
@PQty numeric(8,2),
@sid int,
@SQty numeric(8,2),
@Item char(20)
--you may want to modify the SELECT statement
--to look back at costs as far as the current
--onhand inventory.
INSERT INTO dbo.#i_invent (
Item,
trans_type,
quantity,
unit_cost,
date_time)
SELECT Item,
trans_type,
quantity,
unit_cost,
date_time
FROM dbo.i_invent
ORDER BY date_time ASC
--find the first purchase transaction
--I'm assuming that the initial inventory qty
--should be a positive qty
SELECT @PID = MIN(itemID)
FROM dbo.#i_invent
WHERE SIGN(quantity) = 1
--First item to be processed
SELECT @Item = Item,
@PQty = quantity
FROM dbo.#i_invent
WHERE ItemID = @PID
--find the first sale transaction
SELECT @sid = MIN(itemID)
FROM dbo.#i_invent
WHERE Item = @Item AND
trans_type = 'S'
WHILE @PID IS NOT NULL
BEGIN
SELECT @SQty = quantity
FROM dbo.#i_invent
WHERE ItemID = @sid
--insert purchase and sale identity values into
--joining table
INSERT INTO dbo.#i_inventJOIN (
Purchase,
Sale,
SettleQuantity)
SELECT @PID,
@sid,
@SQty
SELECT @PQty = @PQty - @SQty
IF @PQty = 0
BEGIN
SELECT @PID = MIN(itemID)
FROM dbo.#i_invent
WHERE SIGN(quantity) = 1 AND
ItemID > @PID
END
SELECT @sid = MIN(itemID)
FROM dbo.#i_invent
WHERE Item = @Item AND
trans_type = 'S' AND
ItemID > @sid
END
[font="Verdana"]Sal Young[/font]
[font="Verdana"]MCITP Database Administrator[/font]
October 19, 2006 at 1:40 am
Hi,
we are using FIFO in our system, so I can corroborate Sal's observation - since first goods received were of lower value, current stock with FIFO must be higher than with LIFO (because the older, cheaper pieces will be sold first in FIFO).
In my personal opinion FIFO is more logical than any other solution (LIFO or weighted average) and if it is up to you, I think you should pursue this course. However, I suppose that this should not be a decision to be made by DB admin/developer, but by the bookkeeping and managers of the company.
I'm not sure in which position you are at the moment and what you can change. As I see it, the easiest solution would be to modify the parts of code working with the log table, and include inventory value not only with every purchase (inventory increase), but also with sales. As of now, you have value of 0.00 in unit_cost if it is a sale. Do the calculation of price based on FIFO once when inserting the row with sales into your log table, and you will not have to recalculate it anymore. All later selects to find inventory value can be based on the data in this table only, and can use just a simple
SELECT i.item, SUM(i.quantity), SUM(i.unit_cost*SIGN(i.quantity))
FROM i_invent i
GROUP BY i.item
You can either use summary value of the pieces being sold, or break up the sales row and create a separate row for each FIFO fraction used.
You may have to add another column for purposes of inventory value calculation to i_inventory table and use it instead of unit_cost, if column unit_cost is required to contain 0 for sales because of other processes.
In short : Idea is to do the FIFO calculation only once - not every time you need information about stock value.
October 20, 2006 at 11:32 am
Thank you Sal & Vladan,
You are right about the values, I typed them in wrong. My apologies.
I'm afraid I cannot change the data structures. This is also for many clients who's individual accounting departments will be making the call on how they want to value their inventory.
I was hoping for an approach that would not involve temp tables or looping.
Looking through Joe Celko's article http://www.dbazine.com/ofinterest/oi-articles/celko32 I found some excellent ideas, but I can't seem to make the jump from his bucket-based data set to my audit or log based data set.
I'm still at a loss, any ideas?
Thank you for your help!
Tim
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply