Calculating COGS through FIFO method

[contactsikanderali]

I am trying to create an excel sheet for a small business. I have calculated and linked most of it however in order to accurately calculate the cost of goods sold, I have to use a method called FIFO method (First in first out). For example if I purchase 300 units of item A at 300,000 and 500 units of items A at a later date for 450,000, so when I sell 350 units, it is basically 300 units @ 300,000/300 and remining 50 units @ 450,000/500. Now there are multiple issues doing this in excel.

1. before anything I have to match the product code (column D)

2. I have to see that the cost of goods sold (column K) first calculates cost based on the oldest purchase and after that if needed the next purchase and so on.

3. I have to make sure that the next time I make sale, it also accounts for the previous sale as the oldest purchase might already be accounted for in a previous sale.

I looked for an attachment button but couldn't find one so here is my sheet

S.no at column B, Purchase Date at column C, ARTICLE CODE at column D, Purchase units at column E, Purchase price at column F, Transport Expense at column G, Cost of Goods Purchased at column H, Sale Date at column I, Units Sold at column J, Cost of Goods Sold at column K.

Reading it back I realized that I have not written this in the simplest terms. Let's hope you guys understand. 

[#2]

I would suggest/recommend using a Gantt chart for that... if you're talking about your "SALES" in my terms it's my "CONSUMPTION"
here's what I did to make a FIFO of my consumptions
if you already made a database for your items. I would suggest that you could list your purchase in FIFO order from your database in another sheet. Like mine: (KOH, KCL and H2O2 are chemicals)

so, from oldest to newest date of issued date.
what I did is I insert a helper column (combination of issued date and lot number) since they're the same lot numbers.
Then I make another sheet for the consumption of those lot numbers (this sheet just contains the amount of consumption of the chemical without lot numbers).
And then another sheet for the visualization or FIFO of my chemicals: (example KOH)

as you can see on the image, I transpose my helper column of KOH to Row 1
then on row 2 is the quantity of issued chemicals
on row 3 is the remaining balance, they are already zero since I need to forecast my consumptions.
then by using a Gantt chart method.
If you could share your file here without some private datas or upload it to the cloud/drive, I can help you to make it. if this is what you're looking for.