+ Reply to Thread
Results 1 to 38 of 38

FIFO inventory method and COGS

  1. #1
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    FIFO inventory method and COGS

    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
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    Hi Christian, welcome to the forum.
    I've downloaded your files and will see if I can help, I'll look and see if I understand your question, sounds simple but the most simple things always turn out to be more complicated
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    I've taken a quick look to see if I understand your set-up.
    This is what I think you're asking:
    The Purchase sheet is where you enter the items YOU buy and place in stock to be sold
    So you have different purchase dates and in the case of Product X you have 100 in stock on January 1 and then again 50 more on February 2 and 80 more in February 14
    So March 1 you have 33 in stock

    The Sales sheet is where you fill in the quantity you SELL and that quantity is taken out of Stock
    On March 1 you sell 10 so your stock is reduced to 23 and you want this to be deducted from firs form the stock bought on January 1st?
    Etc ...

    What you need then is also to know when you're selling how much you really have in stock, you cannot sell 30 COGS of Product X on March 12 because you only have 23 in stock

    Is this the idea?

    The FIFO rule I understand and I think you could use the example of the thesmallman but you'll have to add an extra macro that calculates the available stock and als a check to avoid you sell more than you have and thus you need a trigger that warns you when your stock drops below a certain value, so you see if my above understanding is close to what you're looking for then the simple answer becomes a lot more complicated
    Last edited by Keebellah; 04-06-2021 at 11:18 AM. Reason: misinterpretation

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    Nothing Fancy just an extra table with some formulas on the Purchase sheet
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    Re: FIFO inventory method and COGS

    Hi Keebellah

    Thanks for your quick reply.

    What you mention as your understanding is "correct":
    "The FIFO rule I understand and I think you could use the example of the thesmallman but you'll have to add an extra macro that calculates the available stock and als a check to avoid you sell more than you have and thus you need a trigger that warns you when your stock drops below a certain value, so you see if my above understanding is close to what you're looking for then the simple answer becomes a lot more complicated"


    Hence, I'm afraid the solution is complicated.

    So in the spreadsheet I attached, this is the definition of the different columns:
    - QTY = Quantity purchased/sold
    - COGS = Cost of good sold = Cost price per unit for purchased good/Cost price per unit for sold good (this is what's marked in yellow and should be calculated)
    - Cost = Total cost for purchased goods/sold goods

    So it's not 33 in stock on March 1, but 100+50+80-120-30=80

    In terms of the solution, I think you're right that it maybe need to add another macro to adjust for the sold items (I can live without checking for the negative stock - this won't happen in my case). What I was thinking was:
    - In the yellow column E in sheet Sale, a macro must calculate the cost and COGS (I inserted the manual formulas in the attached sheet)
    - Then for cell E3 in Sale, it should find the first 120 purchased goods and calculate the COGS (this is the simple step)
    - For cell E4 it needs to adjust for the sold units in E3, and take the next available goods (again, it's the FIFO principle, so the first ones available)
    - Ideally, a macro would recalculate the available stock for the previous sold items. I.e. for cell E4 it would then include cell E3, but for cell E90, it would be cell E3:E89 (if this makes sense?)

    Let me know what you think, and if this can be solved :-)

    //Christian
    Attached Files Attached Files

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    I'm not that good at these calculations, but what you mean to say is that the price of Product X varies, the first batch purchased on Januari1 cost 10, the sane on February 1 cost 12 , is that true?
    Maybe it can be done with formulas and helper columns but yes, I would go for a macro.
    I'll see if I can help, no guarantee

  7. #7
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    Re: FIFO inventory method and COGS

    Sounds awesome - thanks so much!

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    Try this one, needs fine tuning, textbox on the opening sheet explains more
    The other worksheets (yours) are not used
    Attached Files Attached Files

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    Refined and included a Sales Profit column
    Version 1.0
    Attached Files Attached Files

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    I hope you do have the time to check this out and let me know if it works.
    I fixed a bug and now think it works like it should.

    An acknowledgement would be appreciated since we do this because we like to puzzle with VBA

    Hope to hear from you
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    Re: FIFO inventory method and COGS

    Thanks so much for your effort! It's definitely close to what I was looking for - so big high-five :-)

    Two questions:
    1) I'm not sure, if it calculates the COGS correctly. For example for line 1, it shows 1,239.6 in cell P4. My calculation shows 1,240 exactly. Do you know what's causing this? Maybe it's because of rounding? If so, could we leave this out, or at least increase the rounding to like 5 decimals?
    2) If I add a new product X below the last line in the sales table, it and enters 40 sold units, it shows a Cost of 816. However, according to my calculation it should be 450? Maybe I'm not entirely sure how I should add a new sales (and purchase) to this setup?

    Feels like we're close, I'll be sure to endorse your work mate!

  12. #12
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    Maybe I did not understand correctly, the values are Currency values thus 2 decimals, no rounded calculations there.
    I've attached the Excel file again with the subtotals rows and a pdf document explaining how it works.
    Attached Files Attached Files
    Last edited by Keebellah; 04-09-2021 at 06:19 PM. Reason: attachments added

  13. #13
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    Well, just playing around with the file, combined Purchase and Sale in one action
    Hope the pdf in my previous post explained my calculations
    Have a nice weekend
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    Re: FIFO inventory method and COGS

    Hi again

    First of all, I'm amazed by your engagement to help me - it's really appreciated :-) I think we are almost there and I think I know where we are not fully on the same page.

    Your methodology of working your way downwards is spot-on, but the COGS in the sales table is what should be calculated based on the FIFO principle. Hence, it is not something you know, but what we are calculating via FIFO.

    Example:
    I sell 120 of Product X on March 1, 2021.

    I bought 100 for 10€ and 50 for 12€.

    This means that the "Sales COGS" or just "COGS" for the sold items is: (100*10+20*12)/120=10.33

    That also makes column I and J irrelevant, unless it's necessary for the above calculation (not sure it is though?).

    My overall goal is to understand that if I sell 120 units what was the purchase cost for those items. And then this cost (COGS) should be adjusted everytime I buy new items or sell items.

    Do you follow my line of thinking on this?

  15. #15
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    I'll have to digest it, I'll read it and juggle it and the re-read it to see if I get the idea.
    So the Sale price is not something you enter but calculated based on the cost of all the purchased items that you need for that sale.
    The fist batch of 120 is then as you explain and if I sell another 20 it's (20*12)/20 = 20 so no gain, the Sale = Purchase?

  16. #16
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    I think this more or less does it

    If you just press the Update button see if it calculates it accordingly.

    The USeform for Sales needs editing because the COGS there is no longer necessary just the QTY sold
    Attached Files Attached Files

  17. #17
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    Disregard the fact that I thought it was incorrect
    Last edited by Keebellah; 04-12-2021 at 05:23 AM. Reason: previous info incorrect

  18. #18
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    Hi Christian,
    I think this now works a lor better and the calculations (As far as could check) looked correct.

    The Registration form's functionality has changed a little as well as some macros.
    When the file opens it automatically updates the FIFO calculations that are incomplete, you can switch this off by removing the last command in the Workbook_Open event

    When you enter a new Sale it automatically calculates the sales cost based upon the qty sold
    Attached Files Attached Files

  19. #19
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    You're probably not waiting for this but I did find a minor bug which needed correction.
    Hence the new version and build number
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    Re: FIFO inventory method and COGS

    Hi Keebellah

    Sorry for not getting back to you any sooner on this.

    So basically you don't differentiate between a sales COGS and purchase COGS. But what you do is that you calculate a COGS when you sell an item based on an inventory method. In this case FIFO. Hence, you don't talk about gains/losses. But rather what is the cost of the units I just sold.

    I had a look through the thread and the updates you made. I've troubles using the last 2 editions (2.0 and 2.1). I've attached a screen shot of the error code. Does this have something to do with the coding?

    I did though play around with version 1.3 and it seems to be calculating the COGS correctly now. But when I add a new sale it requires a COGS in the input field? (maybe this is what you changed in 2.0?) If not, I think this needs to be removed, as this is what's calculated, right?
    Attached Images Attached Images

  21. #21
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    You never mentioned you were using the 64-bit version.
    This module dates from some way back but it's updated now
    The version 2.1 (and of course 2.2) only require the cogs when purchasing (it's not COGS but cost op purchase, the COGS (Cost of sales) is calculated base on the purchased produdcs and their prices
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    Re: FIFO inventory method and COGS

    Thanks Keebellah, this is awesome!

    I added to your reputation. Let me know if I can acknowledge your work in some other way as well :-)

  23. #23
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    Your appreciation is more than enough for me. I'm glad I could help.
    I had to sink in for me to grasp the whole idea and the challenge was fun, others will maybe read the posts and become curious, that's great and... I hope that these kind of challenges help others and als tickle their curiosity; it's not rocket science, it's all in your own imagination and logic, put some time into it and read the code like you would a book, everyone can do it; I was lucky to have had a jog that gave me all the time and opportunity and I put it to good use there.
    Thanks for the reputation, and, you know where to find me, us, all the guys on this forum, they're all great in what they do
    Happy coding

  24. #24
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    Re: FIFO inventory method and COGS

    Hi again Keeballah

    Hope you're doing well.

    I noticed that when you register a purchase, it doesn't include decimals in the Purchase Table. What's this related to?

    Best regards
    Christian

  25. #25
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    You will have to check that the cell format is set to show decimals and that the correct decimal sign is used

  26. #26
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    Re: FIFO inventory method and COGS

    Hi again

    I did actually just try this.

    It seems that when my Decimal separator = "." and Thousands separater = "," that the Purchase table works, but not the Sales table.

    And when I change this around. The opposite happens.

    Do you have any idea how to solve this?

  27. #27
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    Re: FIFO inventory method and COGS

    Hi again

    Think I found the issue now.

    Now my next question is how I get the "Sale cost" to include more decimals when the Purchase cost is for example 16.280505.

  28. #28
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    Set the format to show more decimals and you will have to edit the code for Textbox3 to accept mre numbers after the decimal

  29. #29
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    This is the code section to modify:

    Please Login or Register  to view this content.
    The + 7 stand for 6 decimals

  30. #30
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    Re: FIFO inventory method and COGS

    I don't see this in the VBA code :-( In which module should I look?

  31. #31
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    Re: FIFO inventory method and COGS

    Alright, I think I got it now.

    But I still have the issue that the Sales cost apparently does not calculate the exact amount, which is due to decimal rounding (I assume).

    [See screenshot]

    As you can see, it calculates the Sales cost at 16,12222€, but the correct Sales cost is 16,12286402 =(1572*16,1+228*18,2805054)/1800.

    Do you have any idea where this can be corrected in the code?

    Again, I cannot underpin how much I appreciate your help :-)

    Fingers crossed
    Christian
    Attached Images Attached Images

  32. #32
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    I suggest vou reattach your file

  33. #33
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    Re: FIFO inventory method and COGS

    It's here :-)Thanks!
    Attached Files Attached Files

  34. #34
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    I forgot to attach this

    and set the number forat for the currency cells to

    Please Login or Register  to view this content.
    Attached Images Attached Images

  35. #35
    Registered User
    Join Date
    06-14-2021
    Location
    Brazil
    MS-Off Ver
    2016
    Posts
    1

    Re: FIFO inventory method and COGS

    Hi Christian and Keebellah,

    This is also my first post here in ExcelForum.
    When I read Christian's question I remembered of my accounting course when I had the same problem a few years ago, so I decided to try solving it again this time.

    I did a different approach that does not require VBA but it does require an auxiliary matrix next to the Sales table.
    I`m not sure how it would work with a large database but I did put some IF functions to avoid unnecessary calculations.

    Also, this workbook does not take the dates of Sales table into consideration, so it requires the sales to be inputted in an ascending order.

    I hope this helps!

    Best regards,
    GuzzEt0
    Attached Files Attached Files

  36. #36
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    Re: FIFO inventory method and COGS

    Do you mean cell formating? If so, I tried this, and I don't get the correct decimal numbers :-(

    If it's somewhere in the code, could you maybe send over your version, as it seems you have the correct number^^

  37. #37
    Registered User
    Join Date
    04-05-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    Re: FIFO inventory method and COGS

    Hi GuzzEt0

    Thanks for your input!

    It seems to be working fairly well - also like the fact that it's not VBA. Maybe this can do the trick for me :-)

    //Christian

  38. #38
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: FIFO inventory method and COGS

    Here's the file (last version) I have
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Looking for help with VBA for COGS using FIFO Method
    By cosmeticfiend in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2019, 08:56 AM
  2. FIFO Inventory Tracking by Day - Not COGS
    By devlinari in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-18-2018, 11:56 AM
  3. Calculate weekly CoGS for a FIFO inventory
    By kurama101 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-03-2016, 10:30 AM
  4. [SOLVED] FIFO Method Inventory Calculation
    By pakhare.kiran in forum Excel Programming / VBA / Macros
    Replies: 31
    Last Post: 10-10-2012, 01:13 PM
  5. Inventory working FIFO Method
    By Chandrashekhar in forum Excel General
    Replies: 1
    Last Post: 04-11-2012, 05:57 AM
  6. FIFO Inventory method balance tracking
    By artinj in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-14-2010, 11:52 AM
  7. FIFO Inventory- COGS & Inventory Valuation
    By aromaveda in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-24-2009, 02:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1