Question Stock FIFO example

[crossy5575]

Hi all,
I know this has been posted before, however the link, and database put up by someone is locked so that I cant see how the person has coded their database to show how to sell stock at a FIFO basis for cost of sales?
Does anyone have a working example of a stock database which I could strip down and learn from to incorporate into my database please? Failing that the person who posted the fifostock example, explain how to get into it would be amazing!
thanks
Simon

 

[JHB]

There is one sample here, down load it, save it to your disk, keep Shift-key down while you open it.

http://www.access-programmers.co.uk/forums/showthread.php?t=143776

 

[gemma-the-husky]

I haven't looked at the database linked - but just wanted to point out that as soon as you need to consider the cost of inventory as well as the quantity then the exercise becomes murderously difficult - because stock is predicated on the basis that all items are homogenous - and once you need to distinguish the price, then they are no longer homogenous.

 

[crossy5575]

JHB thats brilliant thanks for the tip! - once i have sorted out this issue will be asking how the user managed to do the security bit!

Gemma - T-H Fifo is quite an important bit of coding - mainly for items that are low value and relatively high turnover, ie birthday cards etc, where we have lots of items that change value over 1 year of cycle and holding them, and consequently the cost of sale and cost of stock is relatively important to get correct P&L (sorry nerdy accountant!) but yes it is a headache!

 

[gemma-the-husky]

JHB thats brilliant thanks for the tip! - once i have sorted out this issue will be asking how the user managed to do the security bit!

Gemma - T-H Fifo is quite an important bit of coding - mainly for items that are low value and relatively high turnover, ie birthday cards etc, where we have lots of items that change value over 1 year of cycle and holding them, and consequently the cost of sale and cost of stock is relatively important to get correct P&L (sorry nerdy accountant!) but yes it is a headache!

I will have a look at the link to see how he deals with the valuation side of things. As I say, the requirement to manage prices changes inventory from being relatively easy, to very complex

 

[crossy5575]

Right – many thanks so far however I am trying and getting nowhere fast!
So as a quick summary I am trying to have a database working on a FIFO basis and record items.
Having taken the previous database, I am tyring to adapt it.
I have a few tables:
1. Products (no and name)
2. Customers (no and name)
3. purchase, which is just an ID and date,
4. purchase detail, which will be a subform of purchase having product item, quantity and price
5. Stock, which holds stock no, purchase date, stock item(product no), stock quantity (quanity) and stock price(price), and out of stock (yes/no)
So when I enter a purchase on the purchase form, I want it to copy the records to the stock table. The reason for this is having a complete purchase ledger, and a stock ledger which can be added to and removed when stock is sold. (And the sales cost calculated.)
I tried to set up a loop where you would count the number of items in the subform and loop n times to add records to stock using the same purchase date and default out of stock to false to no avail, as it didn’t seem to like the way I wrote
tquant = me.suform.purchase_detail.puq
And then copied this into stock
Docmd.stock,, acnewrec
Stq=tquant

Part 2 of the problem is the same in reverse where on processing a sales quantity and price a corresponding reduction in the stock would happen – this is where I have no idea how to start!

But… what I want is to
1. Check to see if the order can be fulfilled – ie that sold q is < total stq (in a query) if not write a message. Or else:
2. Filter stock for all out of stock false and product id
3. Sort items by purchase date
4. if sold q < first stock line (Stq)
a. then copy stock date(std), stock price (stpr) and sold q to a new table sales purchase ( which links to the sales detail record)
b. subtract soldq from Stq

5. else if sold q > first stock line (stq) Then
a. then copy stock date(std), stock price (stpr) and stock quanity to stock quantity(stq) to a new table sales purchase ( which links to the sales detail record)
b. put stq to 0 for that line and make out of stock true.
c. Sold q = sold q –stq for that line
d. Refilter for all out of stock = false
e. Loop from 4 till order fulfilled.
Sorry if this is a long boring process – but having read some of the more complex coding solutions I was wondering the best way to do this? I have attached my DB for any help.
Thanks a lot in advance!

 

[JHB]

I've only taken a very, very short look - but what you're missing is a price table of each item, include a date from when a price is to be used.
Don't put in the price in the purchase detail, (table and form).

 

[crossy5575]

I've only taken a very, very short look - but what you're missing is a price table of each item, include a date from when a price is to be used.
Don't put in the price in the purchase detail, (table and form).

unfortunately that is one of the parameters - as each week when we buy some nails for example the price will change - thus putting in a price when we purchase.

likewise for sales this too can be flexible, and easier at this stage to keep it variable.

Thus the key for FIFO is to attribute the oldest sock price first.

 

[gemma-the-husky]

I've only taken a very, very short look - but what you're missing is a price table of each item, include a date from when a price is to be used.
Don't put in the price in the purchase detail, (table and form).

But if you want FIFO pricing, then you can't use just a price table. You do have to store the price applicable to each inward consignment - and when you book materials out you have to book them out depending on how many items remain from each particular consignment, in date order.

That is why managing inventory quantities is, in general, relatively easy, but managing inventory prices and values is decidedly not.

OP -

I can assure you that I understand the principles and implications of using FIFO.

So, just to repeat - Any stock valuation system (such as FIFO) adds enormous complexity, and makes it a very very difficult project for non-professionals - and even difficult for professionals. That's why commercial inventory systems are expensive.

The only system that may be more easily doable (I think) is an AVCO system. Revalue the stock holding and average price for any product when you get a new consignment. That way you do not have to manipulate each issue to identify the FIFO quantity and cost. All products are priced at the new AVCO, and to be honest to my mind that is a better way to cost items than FIFO, as it smooths price fluctuations - although there really is no absolute right and wrong.

The only issue is that for final accounts AVCO may not be acceptable, and you may have to restate inventory to the correct FIFO valuation, with an appropriate adjustment, but this will be far easier to manage than a true FIFO system.

 

[crossy5575]

Dave,
I have no doubt you know what you are talking about from your first post! I dont want to get too complex, while practicing coding and access for a real world problem, so any help would be greatly appreciated .
To try and keep it simple - i need to firstly copy the records to the sock table from purchase. What is the best way to do this? I presume VBA?
Then deal with the sales in a slightly lumpy but effective way.
S

 

[gemma-the-husky]

Sorry, this is a long one.

Ok - assume you have these 4 tables.

stock_items - showing product codes, descriptions etc
(products in the sample database)

stock_transactions ** - showing qty movements, type, date, reference, value- related to stock_items
(purchase detail in the sample database) - note - this is the same as the purchase invoice line

purchase_invoice_header
(purchase main in the sample database)

purchase_invoice_line **
(purchase detail in the sample database) - note - this is the same as the stock transaction


So in point of fact the stock transactions table, and purchase invoice line table are effectively the same thing. Indeed in the sample database, there is only a purchase detail table, and no stock transaction table. So the following discussion examines the requirements of a stock databases, with this in mind.


So, thinking out loud: for items that are stocked, the stock_transaction record is virtually synonymous with the Purchase_Invoice_Line. In fact all you would need to do is store your product code (stock_item ID) against the invoice line, for any product that is in the inventory system. You can then evaluate stock inwards/purchases for any data range by simply summing the purchase invoice lines. You do not have to use a separate stock_transactions table. This is way the sample database was designed

Now, it then becomes a matter of taste whether you have redundancy by duplicating some of the information on the purchase invoice line, by re-entering some of it in the stock transaction table. It may be useful to do so, and speed up some processes.

On the other hand, if you do not have a stock_transaction table, then you will need to consider having separate tables for purchases, sales, adjustments and so on - which brings you to needing union queries. (Again, as per the sample database).

You could think of it from the other way, and have a general transaction, or stock_transaction table to include all types of stock movement - purchases, sales adjustments. Now you do not need a distinct purchase invoice lines table and sales despatch lines table, and you will not need union queries - but now you have a different problem of being able to manage purchase invoices for items that are not stocked, and deal with the fact that you need to consider different bits of information the different types of transactions you have (ie purchases are not exactly the same as sales)

If you decide instead to duplicate the data, then you need some VBA, or maybe a simple query to select items in the purchase_invoice records, that are not in the stock_transactions records, and insert them. The code to actually do this is relatively trivial


The fiendishly difficult part now remains that you can't just say you have 1000 widgets in stock. You need to know that you have 1000 widgets in stock, and the order in which these were purchased historically

so the last 600 cost 1.30 each
the previous 200 cost 1.20 each
and the previous 500 cost 1.10 each

so when you sell 300 items, you need to be able to work out that this 300 comprises the last 200 items that cost 1.10, and 100 of the items that cost 1.20. And it is these calculations that make the whole project very difficult.


So you need to think very carefully about the best way to design the data to manage the entire system. Do you need separate records for purchase invoice lines, and stock transactions. (And therefore do you need separate records for despatch_record lines)


The example you linked to does not include separate tables for stock_transactions, and uses the purchase details, and sales details for that purposes. It does though have a FIFO table. I have not checked in detail, but I think this is managing the process I alluded to earlier.

Overall this is not easy at all. You can spend a lot of time on this, and it is a big challenge. You may get so far, and then decide you need a different data model.

I would examine the database that JHB provided carefully to make sure you understand it, as it may meet your requirements with just a little tweaking.

 

[crossy5575]

Right starting from basics I have tried the following code to take the fields off the purchse & purchase detail form /subform and copy it into stock table.

it isnt working!!!

Dim strSQL As String
Dim pd As Date
Dim puit As Integer
Dim puq As Integer
Dim pupr As Currency

Dim sdate As Date
Dim stit As Integer
Dim stq As Integer
Dim stpr As Currency


pd = Me!pudate
'stock = Forms.stock1
puit = Me.purchase_detail.Form.puit
puq = Me.purchase_detail.Form.puq
pupr = Me.purchase_detail.Form.pupr
With Me

strSQL = "INSERT INTO stock ([stdate], [stit], [stq], [stpr]) VALUES (pd, puit, puq, pupr );"



Call CurrentDb.Execute(strSQL)
MsgBox "Fixture Information Updated!", vbOKOnly, "...Complete..."
End With
End Sub





I also dont know how this will work if I have multiple purchase details (nails, glue and screws for example) on one purchase?

Can anyone help G_T_H totaly get what you are saying and I hope I can have a good practical work around - my logic planning is far ahead of my programming skills!

I have worked with this as well as another way of doing it neither very successfully!

I have attached the broken down db for an example.

Simon

 

[Isskint]

Hi crossy5575

I do not want to misdirect you or run interference on Dave's (see some of us know Gemma was the name of your dog ) advise thus far. I have been involved in logistics for 25+ years, much of that in a stock control related environment. FIFO is a bitch (sorry if the language offends). The best systems i have encountered for dealing effectively with it use customer, product and range related rules to handle allocation. Each customer, product and range (even sub ranges) has a flag to indicate which rule applies. So if you have a query based around cost of item (eg produces a list of stock from low to high or vv), you would apply that to your sales/allocation problem.
The piece of the jigsaw i am not seeing is STU - Stock Take Unit - identification. Every pallet/bin/cage/bucket of stock you receive must have a unique STU (the STU record would hold data on product, receipt date, value (total or per item) perhaps a PO reference) used to control that stock during its life. With STU in place, allocation should be a bit simpler. You will not be able to do this with queries alone, it will require coding, but the queries would be the base data to work from. With that your coding should be just a simple loop:-

• Take the next STU on the list
• Deduct the balance of the order, the whole order or the balance of the STU.
• Run APPEND/UPDATE/DELETE queries
• Check order fulfilled
• Rinbse and repeat

Have a look at the data flow diagram attached.
If i have gone off on the wrong tangent here i appologise, but i honestly do not see how you can FIFO control multiple products with multiple purchase prices without a STU.

 

[crossy5575]

Hi Isskint, and thanks for the message – no not affended with the language but am surprised how am loving my nerdy side coming out! Having just come back from watching age of ultron with the kids decided I need my own Jarvis to help!!!

The allocation flow diagram is exactly what I have in my mind at the moment, so thanks for drawing it! - however in words:

Anyway the stock table I think is your STU table – which allows to filter purchase record to be copied to stock (purchase and purchase detail items to be copied into stock) – it is at this point I am having trouble trying to use code attached from my last post to:
• count the number of entries to put in
• auto number stockno in ascending order
• copy the date from purchase into stdate
• copy the first line from purchase detail into stock, stit, stq,stpr
• copy the date and next n lines … in a loop

At this point I will have a stock list with dates which can be filtered (out of stock) and ordered by sno /stdate.

Once I get to this point – I can start working out the reverse in removing stock – ie selling it!

Any help with what I have coded so far would be really appreciated, as it isn’t working for 1 and I don’t know how to code it to pick up multiple lines of purchase detail. There are a lot of beers I need to buy for you guys, it is massively appreciated!

Simon

 

[gemma-the-husky]

Thanks for the further guidance, Isskint.

Simon, I hope you are able to achieve what you want.

All I was trying to do was illustrate how difficult this area is. Because of the complexity, I really do not have time personally to research this it in detail, without a specific project requirement of my own.

In general terms, though, when working with inventory quantities, you do not need to store a running/current balance of the quantity on hand, as you can calculate it on demand by adding all the transactions, which you generally do not need to do very often.

When you bring price and valuation into the mix, though, you do need to monitor the allocation, not only of a stock item, but of the individual batches of each stock item (as Isskint is discussing). So you either need to undertake many more calculations, or you need to denormalize by carrying a running balance of the quantity on hand of each consignment of stock item, both of which ideas will add complications.