FIFO and Inventory Database Design

Get Started
1

Hi there,

I’d really appreciate some advice on the following if anyone has time. I’m designing an inventory control system. At present my ‘inventory’ table structure is as follows:

id, item_id, supplier_id, quantity, unit_price, buy_price, creation_date

This will allow for multiple entries for the same Item from the same Supplier at a different buy_price. The store owner wants to set a standard mark-up (e.g. $10) on each product. The store owner would also like to display the items ordered by most profitable first and then sell them them in this order.
Ok, ordering the products most profitable first is not a problem, however, I’m having trouble deciding how to decrease the stock levels.

Should I allow the inventory to be sold this way? In other words, does the principal of First-In First-Out (FIFO) apply to the order in which items are sold, or is this only applied to the accounting side of things?

I’ve been doing some calculations and the profit reports differ greatly depending on which order the items are sold.

e.g.
Lets say I have the following item in stock at the following two different prices (only relevant columns are shown for clarity):

item_id | quantity | unit_price | buy_price | Date
1234 | 4 | $25 | $10 | 2006-08-28
1234 | 2 | $30 | $20 | 2006-08-21

If the customer sells the cheapest (yet most profitable) one here and I use FIFO then the bottom quantity will be decremented first. This means 2 of Item 1234 will be sold for $25 but cost $20, making a total of 2 x $5 = $10 profit.

The Inventory table will then look like this:
item_id | quantity | unit_price | buy_price | Date
1234 | 4 | $25 | $10 | 2006-08-28
1234 | 0 | $30 | $20 | 2006-08-21

Due to the FIFO the inventory still shows 4 of Item 1234 at $25 because the first to be put in inventory (last weeks intake) are to be sold first. When the remaining four are sold they will make $15 each giving a total profit of $15 x 4 = $60 +$10 (from the first two) = $70

If FIFO is ignored the profit will be (4 X $15) + (2 x $10) = $80

What if the bottom quantity was larger than the top?
e.g.

item_id | quantity | unit_price | buy_price | Date
1234 | 2 | $25 | $10 | 2006-08-28
1234 | 8 | $30 | $20 | 2006-08-21

If the 2 for $25 were snapped up by some bargain hunting customer, then if FIFO is applied, the bottom quantity will decrement. Leaving the following:

item_id | quantity | unit_price | buy_price | Date
1234 | 2 | $25 | $10 | 2006-08-28
1234 | 6 | $30 | $20 | 2006-08-21

Now there are still 2 bargains even though these are supposed to be bought.

Please tell me if going way off track here. Something has to be wrong with this but I’m suffering from the old “paralysis by analysis”.

I also heard a suggestion that inventory should never be decremented, rather the difference between stock in and stock out should be taken as the current stock levels. I’m concerned that this will slow down the system when listing out the stock levels for all products as this difference will have to be calculated for each item. Any thoughts on this?

Any help will be greatly appreciated.

Cheers,

Psychie

2

your database design is fine

your problem regarding how to decrease your inventory when purchases are made is not a database question

if you wish, i could move this thread to the General Development Issues forum

3

I’d appreciate that if you think that would be the best place for it.
I put it here since it deals with data and thought others may have had similar problems.

Cheers,

Psychie

4

you might want to consult a business process analyst, especially one with warehouse or retail inventory experience. this is one of those very thoroughly thought-through processes. in fact, there’s probably a “warehouse and retail business processes for dummies” book.

5

Thanks for the tip.

I guess this is one of those times where you have to put in some substantial groundwork outside the area of programming.

Ah well,

Cheers,

Psychie

6

Psychie, please let me know, as simple as possible, what exactly you are looking for. I know some people who I may ask them about your real problem.
Further, I’ve got some experience with Procurement systems which is not relevant to what you’re looking for.

7

Hi Borna,

Thank you for your reply. I’d appreciate any advice you can give me. The question I’m asking in it’s most simple form is how should I control the management of inventory coming in and going out, if the supplier prices vary as time goes by, causing a resulting change in the marked selling price. Once this is established, how should I then extract these figures for profit reports.

There are different methods of managing inventory, e.g.:
First In First Out (FIFO):
The oldest item is stock is the first item sold no matter what.

Last In First Out (LIFO):
The newest item is stock is the first item sold no matter what.

Specific Identification Method:
When each individual item of inventory can be clearly identified.

The store owner wants to add a set amount to every item he buys and sell each item at this new price. e.g.
He buys 100 of item 1234 for $50 each, he wants to add $10 and sell those 100 items for $60 each.
When he is running low on stock, (he has sold all but 10 items) he goes and orders another 100 of item 1234. This time he gets them for $45 each. Again he wants to add $10 and sell them for $55 each. Now he still had 10 of the same product left from the last batch which he wants to sell at $60 each. However, because the new batch are cheaper, he will sell those first.

I’m wondering how I should go about handling this and how should I export the resulting profit figures for the company accounts?

Does this make more sense?

Cheers,

Psychie

8

This is just my $0.02, but…

Your issue is in deciding if real-time calculations are important or all-time calculations.

First in First out or Last in Last out, would work spot-on for figuring all time profit figures. If you used First in Last out, when the merchant goes to clearance the last of an item, it would be calculating profit by using the new lower price and the oldest cost, which is usually the highest (when a product is first available). This would greatly distort any real time info.

First in First out would also throw off your real-time profit calculations, but once the last of a previous batch of items is gone, the calculations would be correct again. An example of this is a vendor raising the cost of an item from $10 to $20. If you’re selling at $20, the system will tell you you’re making $10 till you run out of the old batch then it will tell you you’re getting screwed. If your customers won’t buy the item at $20 then the system caused you to make a bad judgement call (buying more). This method used with time period averages will give the most accurate results.

Lastly, some systems just use average cost. If your merchant is not selling highly volatile commodities, this method should be fine for both short-term and long-term.

Let me know if this help your logic or just makes it worse…

9

Hi JSebastian,

Thanks for your reply and I’m sorry didn’t get back to you sooner. I was away for a few days and am only getting back to normality now. I’ve been wondering which method would be best to keep the stock figures as accurate as possible for accounting purposes. You’re explanations of FIFO, FILO and weighted average have certainly made things a bit clearer on the inventory management side of things but how will these affect the accounting figures? Or does it matter? I think since the shop owner wants to work on a standard mark-up basis (e.g. add $10 to the cost of each item) then I’d probably be better off to use real-time figures due to the fact that if he gets a new batch of Item 1234 in for a cheaper price, then this new batch will sell first. This means that there may be an amount of item 1234 in stock for years because he will always sell the cheapest ones first. His only option will then be to reduce the selling price to match that of the new batches.
Is this making sense?

Here’s an example:
He buys 20 of item 1234 for $15 each in month 1.
He wants a mark-up of $10 on each item so he sets the selling price at $25.
By the end of the second week he has sold 16 of item 1234, so he orders another 20.
However he buys this new batch for $12 each so to add a mark-up of $10 he will set the selling price for these at $22 each. During the next 2 weeks he sells an additional 18 of item 1234.

Now he goes to do the accounts for month 1.
He sold 34 of item 1234.
16 of these he sold for $25 and bought them for $15 (Batch 1)
18 of these he sold for $22 and bought them for $12 (Batch 2)
He has 4 of item 1234 left from Batch 1 and 2 or item 1234 left from Batch 2.

What should I export as the accounting figures for Month 1?
Depending on whether I use FIFO, LIFO, Weighted Average the cost of sales will vary. Therefore should I use real-time figures instead?
If I use one of the other methods, how will this balance out?

Cheers,

Psychie

10