Need Help With Code VBA For FIFO (2 Viewers)

[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

 

[gemma-the-husky]

For what it's worth, an AI answer gives the following as acceptable valuation methods for inventory under International Accounting Standards. My understanding is that FIFO is the generally accepted method, although I no longer have to deal with such issues.

Edit.
Although there are other methods of absorbing costs (average cost, standard cost, etc), which msg be more convenient for a business, but the aim is to show inventory at the lower of cost and net realisable value, and FIFO is, I believe, the accepted way of determining "cost" in this context. Therefore the value of inventory returned by the accounting method actually used may need adjusting to comply with accounting standards, although the difference may not be "material".
End Edit


FIFO assumes that the current inventory is valued at the most recent costs paid to purchase such inventory. Older items are absorbed into cost of sales at the cost of those items at the time purchased. Therefore evaluating the cost of those items in theory requires assessment of the sequence (and cost) of acquiring the items.


[Quote from AI]
IAS 2 permits the use of the following methods for assigning costs to inventories:

• • Specific Identification:
    Costs are specifically assigned to each item of inventory, suitable for items that are not interchangeable.
  • First-In, First-Out (FIFO):
    Assumes that the first units purchased are the first ones sold, with the remaining units valued at the latest purchase prices.
  • Weighted Average Cost:
    Assigns costs based on the weighted average of the cost of similar items, suitable for items that are not individually interchangeable.

 

[CJ_London]

it all depends on the nature of the business - FIFO is OK for retailers and wholesalers where typically you have large quantities coming in and small quantities going out where all they are basically doing is providing shelf space. But not suitable for businesses who have to value work in progress and finished goods - unless perhaps they also need to track any finished goods back to a variety of specific shipments in. Specific identification would be used by vehicle showrooms, housebuilders and the like.

Surprised LIFO is not in the list, it is in others - In inflationary times, FIFO will tend to provide a maximised view profit and taxes, bonuses, etc thereon, LIFO will reduce it.

Standard pricing is a management accounting method and in all my years in preparing published accounts I've never had an auditor question the resulting stock valuation. Also works well for manufacturing concerns.

But if restricted to that list, I would go for the weighted average cost method which is much simpler to implement and maintain than FIFO as you effectively take one cost forward until it changes when a new shipment comes in.

 

[CJ_London]

but the aim is to show inventory at the lower of cost and net realisable value, and FIFO is, I believe, the accepted way of determining "cost" in this context

agreed - but if you are buying widgets for £1 (cost) and are able to sell them for £2, that is the net realisable value. If a competitor starts selling them for £1.50 and you drop the price to compete, the cost is still £1. Only time net realisable value drops below cost is when the widget becomes obsolete or you have a fire sale to clear the stock (or you are a really bad salesperson ). So I don't think FIFO comes into it. If the widget becomes obsolete or you have a fires sale, much easier to handle using weighted average rather than FIFO.

One company I worked for had a policy for stock 'not selling' - effectively obsolete. Depended on the item (some were seasonal) they would reduce the 'cost' and price by 25%. After another period of time, costs and prices were reduced by another 25% and again after another period of time. At this point, costs of storage were taken into account and if that was going to be higher than the remaining potential reduced revenue, the stock was simply disposed of. This had the benefit that the balance sheet was not hit with a big writeoff in one go, it would typically be spread over two financial years. And of course the warehouse did not fill up with bays and bays of worthless stock.

 

[gemma-the-husky]

agreed - but if you are buying widgets for £1 (cost) and are able to sell them for £2, that is the net realisable value. If a competitor starts selling them for £1.50 and you drop the price to compete, the cost is still £1. Only time net realisable value drops below cost is when the widget becomes obsolete or you have a fire sale to clear the stock (or you are a really bad salesperson ). So I don't think FIFO comes into it. If the widget becomes obsolete or you have a fires sale, much easier to handle using weighted average rather than FIFO.

One company I worked for had a policy for stock 'not selling' - effectively obsolete. Depended on the item (some were seasonal) they would reduce the 'cost' and price by 25%. After another period of time, costs and prices were reduced by another 25% and again after another period of time. At this point, costs of storage were taken into account and if that was going to be higher than the remaining potential reduced revenue, the stock was simply disposed of. This had the benefit that the balance sheet was not hit with a big writeoff in one go, it would typically be spread over two financial years. And of course the warehouse did not fill up with bays and bays of worthless stock.

Well FIFO comes into it because if you buy your widgets for 1.00, then the next lot are 1.02, then 1.05 the cost of the inventory should be evaluated by counting back the cost of the most recent orders. (Starting with the 1.05 batch). The corollary is that the absorbed cost of sales/production uses the oldest batch first. (First in, first out). So you need to know how many items you still have from the ones you bought at 1.00, 1.02 and 1.05 respectively, which is intrinsically much more complicated than merely managing the quantity on hand.

This all comes back, I imagine, to the problem faced by the OP, deciding what the the cost is for any given batch of items being processed.

 

[CJ_London]

My point is any method of valuation comes into it that is appropriate for the business, not just FIFO.

It is a decision a company makes as they would for any other aspect of their finance such as depreciation.

Anyway the OP is not coming back and I’m trying to clear my desk before going away so will have to call time on this discussion