Hi everyone!
This is my first post here on ExcelForum. Hope you can help me on my issue :-)
I'm running an international webshop and sell multiple products. At the end of each month I need to do my accounting, including calculating the cost price via the FIFO principle.
I've attached my example (FIFO_COGS). Here goes the simplified explanation:
- In the "Purchase" sheet I enter all purchases, including the quantity and cost price
- In the "Sale" sheet I enter the sales at the end of month
What I would like is a formula (gladly VBA) that calculate the cost price via the FIFO method. Meaning for row 3 it will be 100 * 10 + 20 * 12.
The tricky part - at least that's what I'm thinking - is for the calculation in row 4 and onwards. Here it needs to adjust for the already sold items in row 3.
Also, please consider that there are multiple types of products, which it should consider.
The best reference I found online was one by thesmallman (I attached his free version as well - fifo-sm).
However, it does not account for already sold units. But the VBA code almost does the trick.
I've spend the evening browsing through pretty much ALL threads in here, but none of them solved this problem :-(
Hope you guys can help me with this,
Christian
Bookmarks