Need Help With Code VBA For FIFO

[OmarDahy]

Good evening/morning, depending on your time zone.
I don't want to get into an introduction, so I'll get straight to the point.
I have a problem with the attached file code.
This is code for calculating the cost of goods using the FIFO method.
The code works well, but it has a serious problem.
There are some records or invoices that are not being calculated, resulting in incorrect inventory balances and incorrect results.
I need help resolving this problem.
Among the samples with a clear problem are item number 356, item number 3, item number 19, and many others, unfortunately.
I want a solution to this problem and to understand its cause.
------------------------------------------------------------------------
Note: There is a form attached and a button labeled "Start FIFO Code."
When you click on it, you must wait for the calculation cycle to complete.
------------------------------------------------------------------------

Thank you very much.

 

[DHookom]

First, welcome to Access World Forums. Do you have comments in code that explain COGS with FIFO or do we need to research this prior to answering?

 

[jdraw]

Omar,
You may find these older links helpful:
fifo inventory
Fifo related

 

[OmarDahy]

First, welcome to Access World Forums. Do you have comments in code that explain COGS with FIFO or do we need to research this prior to answering?

First of all, thank you very much for your kind comment.

Yes, there are some comments in the code that explain the COGS calculation using FIFO. However, I'm facing an issue specifically in the Case 2 section (Sales)

my problem

and this other sample

All I need to know is why this happened and what's the correct way to fix the code?

 

[jdraw]

Omar,
Have you tried taking one of the items where you see a discrepancy (say Item 3) and worked it (the specific item) through your processFIFO and queries to see if you can find the issue?

 

[plog]

Totally agree with jdraw. Here's the 2 codes that set those 2 boxes on your form:

...
txtSI_FIFO = Nz(DLookup("[SoldQty]", "[TblFifoSummary]", " [ItemCode] =" & [Forms]![Form1]![Combo1] & " "), 0)
...
txtSI_Qry = Nz(DLookup("[SumOfxQty]", "[Query2]", " [InvType] =2 "), 0)
...

They are looking up different fields, they look at different datasources and use different criteria. Why would you expect them to come to the same result? If you really want to find out why you need to pick one specific discrepancy, identify the specific value difference between the two, manually walk through the first calculation method, manually walk through the second calculation method and try to find out which records account for the difference.

 

[OmarDahy]

Yes, I have used two data sources here.
The first one is based on queries, which are intended to verify the accuracy of the FIFO code.
The second is the summary table TblFifoSummary, which reflects the details found in the table TblFifoStockLocal.


For example, in the case of item number 3, the actual quantity sold in real invoices is 145,
but the code calculated only 144.
Where did the missing quantity go?
That’s why I set things up this way — to identify such discrepancies.


If you have any other methods to verify the correctness and accuracy of the FIFO logic,
I would be happy to learn from you.


Just to clarify the tables used:

• TblInvHead: Invoice header table
• TblInvType: Table of invoice types (Purchases, Sales, etc.)
• TblInvDetails: Invoice details table
• TblItems: Items table

I’ve been trying to solve this issue for more than five days now.
My goal is to correctly calculate inventory, cost of goods sold (COGS), the value of goods sold, and profit based on the FIFO method.


If there are any other ways or approaches that produce these outputs, I would really like to explore them —
especially since most available examples focus only on sales, without considering returns (whether purchase or sales returns).

 

[CJ_London]

You also need to consider stock changes such as damaged items - can you identify which price to assign to stock writeoffs ?

 

[OmarDahy]

You also need to consider stock changes such as damaged items - can you identify which price to assign to stock writeoffs ?

No, I haven't thought about this point.
If I can add a new point to the invoice types, let's say number 99. We'll need to address this here.
I feel like the issue is much bigger than my current level.
If anyone can help me recreate this code from scratch until we reach a good point, I don't know if this is possible or not.

 

[CJ_London]

Have you looked at the links provide by JDraw in post #3? Is there any reason why are you using FIFO? it is fraught with problems and often shows no real benefit in these days of relatively low inflation.

Have you considered using standard costing/variance analysis or average costing instead? Much easier to implement

 

[jdraw]

Omar,
Chris (CJ_London) is an accountant and will have experience and focused knowledge.
Here is another link that may offer some insight.
Good luck with your project. Let us know your solution once resolved.

 

[Minty]

I'll chuck my 2pence worth in here, as I had to deal with FIFO pricing for COGS at a previous place of work, and it's an unmitigated PITA.
If you can possibly avoid it I would.

From memory, we eventually manage to talk them into doing average cost based on stock held and date based purchased and stock balances.
Much easier IMHO.

 

[gemma-the-husky]

Quantity management of inventory is straightforward. Cost management is decidedly not. FIFO is the method that meets accounting standards, but will be very hard to code, because you need to manage the sequence of both goods received and goods issued.

 

[CJ_London]

FIFO is the method that meets accounting standards

I would say FIFO is a method, it is not the only method