Forum

Notifications
Clear all

Automatingly Calculating Cost of Goods Sold in Multi Sku

3 Posts
2 Users
0 Reactions
244 Views
(@garb01)
Posts: 4
Active Member
Topic starter
 

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

 
Posted : 03/02/2022 4:07 am
Riny van Eekelen
(@riny)
Posts: 1195
Member Moderator
 

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.

fifo.png

 

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.

 
Posted : 03/02/2022 8:15 am
(@garb01)
Posts: 4
Active Member
Topic starter
 

Wondering if there's an easier way to do this as I have almost 100 SKUs

Regards

 
Posted : 11/02/2022 12:06 pm
Share: