I need help calculating Cost of Goods Sold in Multi Sku Inventory Tracking Model Please!!

In Column AA (or AB) I want to calculate cost of goods sold (or unit cost of goods sold) here. I have scoured the internet for weeks looking for answers and I have covered some ground but not entirely satisfied.

Currently I sort the data then drag down formulas in columns R, S, and T to calculate cost of goods sold. However this takes too much time to calculate because I have so much data and I am expecting to double the amount of data too.

Now I have recently come across the greatest solution I have seen yet here at

HTML Code: 

.

By some kind of sorcery this guy jindon decimated this challenge by not only providing code for FIFO but for LIFO and Average Costing as well!! Simply Amazing! But.. the code is designed for one sku item. None the less the calculation is carried out virtually instantly from what I have tested using many rows of data like my own model has. I am not smart enough to figure out how to modify this code to achieve calculation for my multiple sku data set.

So, In summary, I would love to acquire the same solution(s) (FIFO, LIFO, and Ave Costing) as the afore mentioned thread for my multiple sku model. I don't really need it written into sheet module, a standard module would be just wonderful. I just need cost of goods sold to be calculated as fast as possible.


Thanks in advance for any advice!!

That would be awesome thank you!

trickyricky,

That was over 10 years ago and made many PM exchanges with the OP specially about Average Cost calculation, as I am not an accountant.
(Learned them all in my school days, but ...)

FIFO for each SKU can be done but I need to know.

Inventory IN & OUT with dates for each individual row like you see in the Link.

Thankyou for taking the time to help. I have revised the sample file and listed three different sku items.

So basically in Columns L M N O are purchase details of quantities coming in. In columns X Y Z are sales details of quantities going out. Column AA or AB is where I need your magic. The current values in them are correct but as I said earlier my current method of getting them require much time to calculate when a lot of data has been entered.

I know I can be terrible at explaining things sometimes but let me know if I can explain further.

Thanks

So, basic logic is to calculate AB(Total COGS) when goods sold and divide by Qty sold.

Is this correct?

Yes exactly. I'm sure you have a better approach, but my thought was maybe you could sort the data by sku, then apply your previous code but modify it so it accounts for each change in sku as it calculates down the list.

OK it is clear now.

Do you still want to
1) keep FIFO1(Col.R)?
2) calculate ENDING SKU INVENTORY(Col.Q) for each row?

My idea for 2) is to calculate only when the goods sold, so that the worksheet will a bit lighter.

I just use FIFO1 (Col.R) to calculate the running cost of goods sold, I sort the list by col F, then fill down the formulas in R and S and T as well and I can get the COGS for each sale in T. It is a good UDF but it takes too long with more data. So for 1) We don't need to keep and 2) is fine to calculate only when goods sold.

OK thanks.

If you can upload a workbook without the column(s) that you don't need for the calculation, it will be easier.
Adjusting the columns afterwards confuses sometime.

VBA will calculate
1) Total COGS & Unit COGS
2) Date in Col.X will also appear in Col.L, so that sort will be easier. or even Col.X can be removed...
etc.

Basically, idea is to reduce columns as much as possible to make process faster.

I don't know what you do in the hidden columns/rows, but the worksheet for this kind should have less column for the raw data entry.
So that any kind of reports will be produced easily to the other sheet.

You know what I mean?

OK thanks.

One thing, you have row 165 to 168 formatted.

Are you inserting row(s) when you add new transaction(s)?

Yes so when I add new transactions I would insert rows before row 165 (the last row) so I wouldn't mess up other calculations I was doing to keep the ranges intact.

Inserting row(s) for raw data entry is not a good idea.

Can you just create a dynamic named range for "other calculations" or let vba to create one.

Yes absolutely. I don't have to insert rows

OK, I will work on it from here and would give changes to the sheet, if I think it is necessary.

I'm getting compile error, for

Please Login or Register  to view this content.

It says variable not defined?

Ahh nevermind, fixed it added

Please Login or Register  to view this content.

let me do some testing now

Okay after some testing.. It works fantastic even after I added in a lot of data. A couple things I would like to improve:

1) Is it possible to show the inventory in cells when purchases were made too like it does for sales?
2) Is there any chance I can add back in my details columns? Or would I need to have that on another sheet

1) Formula inserted in Col.Q (ENDING SKU INVENTORY), so need to know if it slows with the bulky records.

2) Make a change to your original worksheet "multiple products" and post back.

Yeah I used to have that in my old model it was to bulky.

1) Would it be possible to remove code and keep one or the other for calculating cost of goods sold?
For example take out code for calculating total cost of goods sold because if we have code that calculates unit cogs a simple formula in col T can calculate total.

2) And then is there any way to insert columns? For instance if I add a column in to the left of DATE column I assume we would just need to shift the code over so that it realigns with the right columns? I can't figure it out though..

I don't understand why you need to remove the calculation for Total Cogs.
It is a key factor that need to be calculated and it is a sum of multiple different Quantities and unit prices.

If you don't need this calculation, what else do you expect the code to do?

Anyway, if you want to remove them all, just leave Sort by date part.
i.e

Please Login or Register  to view this content.

Originally Posted by jindon

I don't understand why you need to remove the calculation for Total Cogs.
It is a key factor that need to be calculated and it is a sum of multiple different Quantities and unit prices.

If you don't need this calculation, what else do you expect the code to do?

Anyway, if you want to remove them all, just leave Sort by date part.
i.e

Please Login or Register  to view this content.

No no we need it! I just didn't know for sure but it look like in your code you were getting the answer for total cost of goods sold and then coding that divided by quantity to get unit cost of good sold. And my thinking was if you're getting total cost of goods sold we can just put a formula in Excel to take that divided by the quantity to get the unit cost of goods sold instead of using code.


No worries though we'll just leave it alone it's working fantastic.

Now is there any work around to be able to insert columns?

OK, formula in "ENDING SKU INVENTORY" removed.
If you want to add a column before "DATE" column, all the BOLD numbers in brackets should be increased by 1.

Please Login or Register  to view this content.

This is really coming together now! I appreciate all you've done thus far. I have spotted an error in the Ending Inventory Calculation. It seems to be keeping a running total of purchases when a purchase line is entered, and then shows running total for sales when a sales line is entered.

Right...
Try this one

Please Login or Register  to view this content.

Yes that looks better! Now I cannot thank you enough. You have a great heart to help people like me out like this.

One last thing, would it be difficult for you to make alternate code where it calculates average cost instead of FIFO.

Don't need LIFO I don't see myself ever using that, but it would be nice to compare FIFO and ave costing.

I really appreciate it! I will have to step away for a while to spend time with the family but I will come back to this asap later in the day. Thanks so much

I totally forgot how to calculate it.

Ok, one final request. Could you make this work for standard module too? Just in case I get tired of the worksheet change event going on all the time.

Remove current code and to a standard module

Please Login or Register  to view this content.

jindon thank you. You have made my life easier. Thank you so much for your time and I appreciate your willingness to help others. I hope this maybe helps others in their quest for this kinds of model. I know I have been searching the internet for weeks for multiple sku fifo models and though there are some fairly brilliant ones out there, they just don't cut it when it comes to inputting large amounts of data. I have entered 6000 rows of entries and that's only for half a year, and I know this will handle it all fine. Until next time my friend, this thread is solved for me!

You are welcome and thanks for the rep.

If I remember Average cost logic and have spare time, will post it someday.

Sounds good! You know in hind sight I wonder if I could have just had you look at your code from the posts you did back in 2005 and seen if you can just implement some modifications to account for multiple sku's? They are relatively the same model now that I look at it...

If you ever feel like it maybe take a look at that model again and if you can modify the code easily for multiple sku's, post it on here that would be cool.. I would just look at that model and change column C to be the column for the different sku's and then have the column H be coded for the ending inventory for each sku.

thanks man!