Calculate Inventory Costing Based on First in, First Out (FIFO)

Question

I am trying to get the cost of inventory based on FIFO.

I saw a similar question but it does not look for cost of inventory and tables seem to be structured differently.

Using MySQL I have a table purchase_order_items, purchase_order_receipt_items and item_inventories the purchase_order_receipt_items table gets updated with every item that is received and for purchasing control, what was ordered and what was received.

The item_inventories table gets updated with a new line item for every inventory movement in or out and we calculate the current inventory using a simple SUM of all in and out quantity.

What I am missing is the cost of the current inventory. The cost of the same item varies and we don't have a cost added in the item_inventories table.

Table purchase_order_items:

| PO    | Date         | Quantity | Item | Cost  | 
|-------|--------------|----------|------|-------|
| PO001 | 01-Jan-2019  | 1        | AO21 | 12.50 | 
| PO002 | 02-Jan-2019  | 3        | AO21 | 10.99 |
| PO003 | 09-Jan-2019  | 2        | AO21 | 12.00 |

Table item_inventories

| ID    | Date         | adjustment_qty | Item |
|-------|--------------|----------------|------|
| 1O001 | 01-Jan-2019  | 1              | AO21 |
| 1O002 | 02-Jan-2019  | 3              | AO21 |
| 1O003 | 04-Jan-2019  | -1             | AO21 |
| 1O004 | 05-Jan-2019  | -1             | AO21 |
| 1O005 | 09-Jan-2019  | 2              | AO21 |

I currently calculate the item_inventories and get a total of 4 SELECT SUM(adjustment_qty) FROM item_inventories WHERE item='AO21' but that does not give me the cost of the inventory.

How do I join the purchase_order_items table to get the cost and qty in a SUM query of the item_inventories table?

Answer 1

Maybe this is:

select Item, sum(Quantity) Quantity, sum(Cost) Cost,
(SELECT SUM(adjustment_qty) FROM item_inventories i WHERE i.item=o.Item) adjustment_qty
from purchase_order_items o
group by Item;

Answer 2

I want to apologize up front. I don't work with MySQL but maybe this SQL Server answer will help give you some guidance.

I think what you really want is a Remaining_Inventory column added onto each row of purchase_order_items (or a separate table). Then each transaction can update the appropriate row in your FIFO methodology. Then your inventory cost report is simply something like:

SELECT Item,
SUM(Cost_Of_Remaining_Inventory) AS Sum_Of_Inventory
FROM
(
    SELECT Item AS 'Item',
    (Cost * Remaining_Inventory) AS 'Cost_Of_Remaining_Inventory'
    FROM Purchase_Order_Items_Tracker
) Window_View
GROUP BY Item

You would then need to fill this table out with your initial data, and I think I have a CURSOR which will do what you need. Again written for SQL Server.

First I recreated your tables with Table Variables just so i could test everything out:

DECLARE @Purchase_Order_Items TABLE
(
    PO VarChar(5),
    [Date] DATETIME,
    Quantity INT,
    Item VarChar(4),
    Cost DECIMAL(10,2)
)

DECLARE @Item_Inventories TABLE
(
    ID VarChar(5),
    [Date] DATETIME,
    Adjustment_Qty INT,
    Item VarChar(4)
)

INSERT INTO @Purchase_Order_Items (PO, [Date], Quantity, Item, Cost)
VALUES('PO001', '2019-01-01', 1, 'AO21', 12.50),
('PO002', '2019-01-02', 3, 'AO21', 10.99),
('PO003', '2019-01-09', 2, 'AO21', 12.00)

INSERT INTO @Item_Inventories (ID, [Date], Adjustment_Qty, Item)
VALUES ('1O001', '2019-01-01', 1, 'AO21'),
('1O002', '2019-01-02', 3, 'AO21'),
('1O003', '2019-01-04', -1, 'AO21'),
('1O004', '2019-01-05', -1, 'AO21'),
('1O005', '2019-01-09', 2, 'AO21')

Then I created a "Tracker" table for keeping track of the remaining inventory for a given PO as we work through the cursor.

DECLARE @Purchase_Order_Items_Tracker TABLE
(
    PO VarChar(5),
    [Date] DATETIME,
    Quantity INT,
    Item VarChar(4),
    Cost DECIMAL(10,2),
    Inventory_Remaining INT
)

Then running the below code to populate said table and then query off it should give you what you are looking for

DECLARE @Date DATETIME, 
        @Adjustment_Qty INT, 
        @Item VarChar(4),
        @Adjustment_Qty_Transaction INT,
        @Item_Inventories_PO_Transaction VarChar(5)

DECLARE Inventory_Cursor CURSOR FOR
--getting the absolute value of Adjustment_Qty so that we can only work with positive number
---The WHERE condition makes sure we are only working with records where the inventory was subtracted from, not added to
SELECT [Date], ABS(Adjustment_Qty) AS Adjustment_Qty, Item
FROM @Item_Inventories
WHERE Adjustment_Qty < 0 --That way we are only looking at removing of inventory
                         --Not logs where inventory was added

OPEN Inventory_Cursor
FETCH NEXT FROM Inventory_Cursor INTO @Date, @Adjustment_Qty, @Item

WHILE(@@FETCH_STATUS = 0)
BEGIN
    --Load in any new inventory
    INSERT INTO @Purchase_Order_Items_Tracker (PO, [Date], Quantity, Item, Cost, Inventory_Remaining)
    SELECT
    I.PO, 
    I.[Date], 
    I.Quantity, 
    I.Item, 
    I.Cost,
    I.Quantity
    FROM @Purchase_Order_Items I
        LEFT OUTER JOIN @Purchase_Order_Items_Tracker T
            ON I.PO = T.PO
    WHERE I.Item = @Item --Transaction is for our item in scope
    AND I.[Date] <= @Date --Purchase order recieved at or before this inventory change
    AND T.PO IS NULL --Record not already inserted into the tracker

    --Then Subtract from inventory
    WHILE(@Adjustment_Qty > 0)
    BEGIN
        SET @Adjustment_Qty_Transaction = NULL
        SET @Item_Inventories_PO_Transaction = NULL

        --You will need some kind of error handling for if there are no records found
        --which means at the time of trying to take out inventory
        --our records show that there was no inventory for that item
        SELECT TOP(1) 
            @Adjustment_Qty_Transaction = Inventory_Remaining,
            @Item_Inventories_PO_Transaction = PO
        FROM @Purchase_Order_Items_Tracker
        WHERE Item = @Item
        AND Inventory_Remaining > 0
        AND [Date] <= @Date
        ORDER BY [Date] DESC

        IF(@Adjustment_Qty <= @Adjustment_Qty_Transaction)
        BEGIN
            SET @Adjustment_Qty_Transaction = @Adjustment_Qty_Transaction - @Adjustment_Qty
            SET @Adjustment_Qty = 0
        END
        ELSE
        BEGIN
            SET @Adjustment_Qty = @Adjustment_Qty - @Adjustment_Qty_Transaction
            SET @Adjustment_Qty_Transaction = 0
        END

        UPDATE @Purchase_Order_Items_Tracker
        SET Inventory_Remaining = @Adjustment_Qty_Transaction
        WHERE @Item_Inventories_PO_Transaction = PO
    END

    FETCH NEXT FROM Inventory_Cursor INTO @Date, @Adjustment_Qty, @Item
END
CLOSE Inventory_Cursor
DEALLOCATE Inventory_Cursor

--Then make sure that we have all other inventory additions added
--In case we got new inventory but have not had inventory taken out since it was added

INSERT INTO @Purchase_Order_Items_Tracker (PO, [Date], Quantity, Item, Cost, Inventory_Remaining)
    SELECT
    I.PO, 
    I.[Date], 
    I.Quantity, 
    I.Item, 
    I.Cost,
    I.Quantity
    FROM @Purchase_Order_Items I
        LEFT OUTER JOIN @Purchase_Order_Items_Tracker T
            ON I.PO = T.PO
    WHERE T.PO IS NULL --Record not already inserted into the tracker

SELECT *
    FROM @Purchase_Order_Items_Tracker

SELECT Item AS 'Item',
    [Date],
    (Cost * Inventory_Remaining) AS 'Cost_Of_Remaining_Inventory'
    FROM @Purchase_Order_Items_Tracker

SELECT Item,
SUM(Cost_Of_Remaining_Inventory) AS Sum_Of_Inventory
FROM
(
    SELECT Item AS 'Item',
    (Cost * Inventory_Remaining) AS 'Cost_Of_Remaining_Inventory'
    FROM @Purchase_Order_Items_Tracker
) Window_View
GROUP BY Item

The 3 final SELECT queries return the below 3 Tables:

+-------+-------------------------+----------+------+-------+---------------------+
|  PO   |          Date           | Quantity | Item | Cost  | Inventory_Remaining |
+-------+-------------------------+----------+------+-------+---------------------+
| PO001 | 2019-01-01 00:00:00.000 |        1 | AO21 | 12.50 |                   0 |
| PO002 | 2019-01-02 00:00:00.000 |        3 | AO21 | 10.99 |                   2 |
| PO003 | 2019-01-09 00:00:00.000 |        2 | AO21 | 12.00 |                   2 |
+-------+-------------------------+----------+------+-------+---------------------+

+------+-------------------------+-----------------------------+
| Item |          Date           | Cost_Of_Remaining_Inventory |
+------+-------------------------+-----------------------------+
| AO21 | 2019-01-01 00:00:00.000 |                        0.00 |
| AO21 | 2019-01-02 00:00:00.000 |                       21.98 |
| AO21 | 2019-01-09 00:00:00.000 |                       24.00 |
+------+-------------------------+-----------------------------+

+------+------------------+
| Item | Sum_Of_Inventory |
+------+------------------+
| AO21 |            45.98 |
+------+------------------+

Again all of this is based around SQL Server but it should at least give you a framework to build off of. And I think in the real implementation you want a Remaining_Inventory Column (or a separate table) which keeps track of what is left on that PO

Answer 3

I ended up using the following operation.

Either in 2 queries and using php to run it or in a single MySQL query.

2 Queries:

SELECT SUM(adjustment_qty) FROM item_inventories WHERE item='AO21'

I then take the result, let's say 34 and I run the following query

SELECT SUM(cost) FROM (select cost from purchase_order_receipt_items WHERE item='AO21' ORDER BY id DESC LIMIT 34) i

Or, we can run the same in a single query.

SET @sql = CONCAT('SELECT SUM(cost) FROM
(select cost from purchase_order_receipt_items WHERE item='AO21' 
ORDER BY id DESC 
LIMIT ', (SELECT SUM(adjustment_qty) FROM item_inventories i WHERE item='AO21'), ') i');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This gives you a perfect cost of goods report based on FIFO