First In, First Out subtraction from rows?

[dee-u]

I need to be able to compute the average cost of a certain item, not sure how to properly design the table as there is a need to subtract from the quantity of the item in First In, First Out order. Here's an example:

Quantity Cost Total
(a) 1000 100 100,000
(b) 500 90 45,000

The first row indicates the beginning balance of the item, and the next one is the next purchase. If we'll sum the quantity then well have 1,500 and the sum for the total is 145,000, and to get the Average Cost we'll do Total/Quantity which would be 145,000/1500 which will yield 96.66 as the Average Cost. Now, for every sales it must be first subtracted from (a) until it gets 0, then proceed to (b), and so on. I could add another column which would indicate the total deductions from the quantity per row but it does not seem to be the best design as I would need to check if it is already 0 before proceeding to the next row (b).

Can anyone recommend a more efficient design such that I could subtract by row (until it gets zero)?

TIA

[Zvoni]

Hmmm, reminds of the "net-worth"-calculation we have in the company i work for.
Basically, we do it as follows:
Each stock-location/item has its net-worth in a separate field beside the stock-quantity.
To stay in your example: as long as we ship quantity from stock location "a" it's calculated according to the cost of that stock location (and subsequently subtracted from that stock-location).
Meaning: A general total-"average" is calculated during runtime in realtime

[wes4dbt]

Hard to suggest anything without more information.

Why are you subtracting from "a" until it's "0", then start subtracting from "b", and so on...

What is the purpose of this process?

[dee-u]

Hard to suggest anything without more information.

Why are you subtracting from "a" until it's "0", then start subtracting from "b", and so on...

What is the purpose of this process?

It's a FIFO as I've already mentioned, "a" is the first so it has to be "zeroed" first before moving to the next "stock" which is "b", and so on.

[dee-u]

Hmmm, reminds of the "net-worth"-calculation we have in the company i work for.
Basically, we do it as follows:
Each stock-location/item has its net-worth in a separate field beside the stock-quantity.
To stay in your example: as long as we ship quantity from stock location "a" it's calculated according to the cost of that stock location (and subsequently subtracted from that stock-location).
Meaning: A general total-"average" is calculated during runtime in realtime

Care to share an example?

[wes4dbt]

It's a FIFO as I've already mentioned, "a" is the first so it has to be "zeroed" first before moving to the next "stock" which is "b", and so on.

I know, you said that in your first post. You haven't added any clarification.

What does each row represent? Is this an Inventory system or Stocks or ...... If this is an Inventory system then I'd say this is a bad design.

It should have at least 2 Tables, 3 Tables would work well too. A simple example,

2 Table design:

Table 1: InventoryItems - Fields: ItemId ItemDescription Price Cost QuantityOnHand QuantityOnOrder

Table 2: ItemTransactions - Fields: TransactionId ItemId Quantity Cost Total TransactionType (this field would indicate if this was a Purchase or a Sale)

The 3 table method would breakup the ItemTransactions into ItemsPurchased and ItemsSold.

It really depends on how much detailed history you want. If you don't care about Purchase or Sales history and the only thing you will ever want to know is the Average cost then you could use one table.

Table 1: ItemTransactions - Fields: ItemId Quantity Cost Total QuantityBalance

With this method QuantityBalance would initially be the same as Quantity and then you would subtract from QuantityBalance as items are sold.

[ChrisE]

Hi,

I would go with the design form wes, the other thing will be when you add Stock again.

No Company will let there Stock go to Zero, there is always a min.Quantity of Stock and some Query will execute and show the Amount on Stock

regards
Chris

[dee-u]

@wes4dbt

Yes, it would be an inventory system. Those rows are the purchases, and there would be sales and those sales would have to be subtracted from those purchases FIFO. I generally am more concerned with the proper design of this before thinking of the other tables so I'm sorry if I didn't provide enough details. Yes, I am initially considering using another column as I have stated in my first post, perhaps call it "balance" as you also suggested. I'm just waiting for suggestions from the others who have dealt with the same or similar scenario. I could find examples on google but they are all calculated at runtime and I thought it would be tedious to always calculate the average cost of each item at runtime.

[Zvoni]

@wes4dbt

*snipp*....and I thought it would be tedious to always calculate the average cost of each item at runtime.

Why not just add a field to the Items-Master-Table called "AveragePrice"?
Mind, you'd have to adjust this value with each transaction (purchase/sales) for each item.
As for an example:

Day 1
Loc a 1000 pcs - 2$/pc - 2000$ worth
Loc b 500 pcs - 2.50$/pc - 1250$ worth
Average: 2.17$/pc (in runtime or written to this column i mentioned)

Day 2 - Sold 300 pcs
Loc a - 700 pcs - 2$/pc - 1400$ worth
Loc b - 500 pcs - 2.50$/pc - 1250$ worth
Average: 2.21$/pc

Day 3 - Sold 500 pcs
Loc a - 200 pcs - 2$/pc - 400$ worth
Loc b - 500 pcs - 2.50$/pc - 1250$ worth
Average: 2.36$/pc

Day 4 - Sold 400 pcs
Loc a - 0 - 2$/pc - 0$ worth
Loc b - 300 pcs - 2.50$/pc - 750$ worth
Average: 2.50$/pc

Day 5 - Purchase 600 pcs
Loc b - 300 pcs - 2.50$/pc - 750$worth
Loc c - 600 pcs - 2.40$/pc - 1440$ worth
Average: 2.43$/pc

EDIT: There is a reason why we're using backwards-calculation from worth (instead of just using qty x price per location):
Imagine now you do a stock inventory
Day 6 - Stock Inventory (No Transactions with clients/suppliers)
Loc b - Should be: 300 pcs - Found: 200 pcs (100 pcs stolen/lost whatever) - 2.50$/pc - 750$ worth
Loc c - Should be: 600 pcs - Found: 600 pcs - 2.40$/pc - 1440$ worth

Now for Loc b you could argue the new worth is 500$ (200pcs x 2.50$/pc).
In our case: WRONG!
The worth is still 750$ for the 200 pcs, because you paid an invoice to a supplier!
So new average: 2.74$/pc

EDIT2: I forgot:
This "Backward"-Calculation could be implemented via
Triggers on the Stock
After-Insert --> Purchase
After-Update --> Sales
(After-Delete --> Sales - Quantity on Stock location runnig to zero)

[dee-u]

Yes, that's what I am considering. I'm just wondering if there was another design which wouldn't need another column to store that value. Thanks anyways guys, I guess it's settled but I will just leave this open just in case someone wants to suggest another way.

[Zvoni]

It's the way how it's done in the company i work for, and our ERP is a professional industrial-grade Software, which passed any test of the IRS when they came calling to check everything is done according to law

[FunkyDexter]

I've had to deal with some fairly big FIFO inventory systems and I'd say there are 3 broad approaches. To some extent you can mix and match these if you want.

1. Row per individual item. That means every single screw, sticker or widget gets an individual row in the table complete with purchase date, serial number etc. Note, there is no quantity field - the quantity is always 1. You will need some sort of "Order By" column (usually Purchase DateTime) in order to satisfy the FIFO requirement. This approach tends to work well in inventories where you have a small quantity of large items, eg a car dealership.
2. Row per Purchase. NB this is a row per purchase by the inventory owner, not a row per sale to a customer. So it'd be keyed by part no (or similar) and would have a purchase datetime and quantity. This is a bit more pragmatic than approach 1 particular when dealing with large amounts of consumable items, eg paper, widgits and screws. It keeps the data reasonably small but does mean you lose track of an individual item (generally not an issue when dealing with consumables but should be considered).
3. Current stock row only. Just maintain a single record for the current stock level of a given item and calculate the necessary values to meet the FIFO requirement. This may or may not be possible depending on exactly why you want the system to be FIFO. If it's just about being able to report stock values this can be achieved through weighted averages and by querying the purchase ledger but it can get tricksy and the value can start to drift over time due to issues like lost stock etc.

Of the three 2 is usually the most pragmatic and looks like what you're thinking about from your sample data. It's pretty easy to implement and the only thing your sample data is missing is some kind of ordering field (purchase date, received date etc)

[Zvoni]

FD described it perfectly.

Now the funny thing:
The Company i work for is a trading company for screws, bolts, nuts, washers (Fasteners, you name it).

FD,
you psychic, or what?

In our company it's kind of like this (very simplified and stripped down):

Table StockLocations
ID
Coordinates (Aisle, Column, Level)
SomeMoreFields

Table ItemMaster
PartNumber
Description
Weight per unit
AveragePurchasePrice
SomeMoreFields


Now we have a m:n relationship between these two tables

Table ItemOnStock
StockID (Meaning Coordinates!)
ItemID
DateReceived
QuantityOnStockLocation
NetPurchaseWorth
SomeMoreFields

[wqweto]

@Zvoni: What are the keys on ItemOnStock table and other tables? You might be missing Lot column here, particularly if dealing with expiry dates, serial numbers and/or FIFO/LIFO/FEFO warehouses.

@dee-u: For FIFO/LIFO/FEFO inventory you'll need to differentiate (a) and (b) in item's ledger by some unique (sequence) number. What I usually end up is a simple Lot column as a VARCHAR, with the actual information keyed in LotsInfo table -- delivery date, expiry date, etc.

When the system has to fulfill an outbound inventory document it automatically selects (for each item) the Lot for (a) or for (b) based on warehouse setup -- preferring min delivery date for FIFO, max delivery date LIFO, min expiry date for FEFO.

cheers,
</wqw>

[Zvoni]

wq,

(very simplified and stripped down)

Yes, Lot/Batch-No (and whatever important information for backtracing items) have to be included.
But no idea about the Keys (PK/FK), since i didn't write it, i just read it
Oh Yeah, the whole thing runs on an OS/400 - DB2 with the clients being Windows-Clients in a Citrix-Environment.