
Active Member

July 21, 2021

Hi
I want to calculate the cost of good sold in FIFO method (First in inventory brought in will be the First inventory sell out). Currently I have to do everything by hand and things got very confusing.
I was wondering if there's any way I can get excel to do this for me automatically. Preferably without VBA code?
Thank you


Trusted Members
Moderators

January 31, 2022

Perhaps not the most elegant solution but I had an older model that calculated FIFO cost and adapted it a little to be used with your example.
Start by putting both IN and OUT in one table, sorted in date order. Then you need to create a some column headers with the various Unit Costs and Incoming Quantities. Below, you can then calculate from which cost layer the outgoing quantities are taken and thus calculate GOGS.
I'm tempted to further develop this and make it all a bit more dynamic, but this is all for now.
Edit: I now realize that you have different SKU's within the table. My file will work if you would have one table for each SKU.
1 Guest(s)
