none
Find the cost of goods sold RRS feed

  • Question

  • Dear All

    In the below dropbox linked excel file have the Purchase price, quantity and sales quantity. I need to find the cost of sales quantity in the cell F6: F200 by FIFO Method.

    I have added the expected results in the cells G6: G24.

    https://www.dropbox.com/s/8cyj46po2g538pj/COGS%20MSDN.xlsx?dl=0

    Please do the needful. 

    Thanks in advance

    Wednesday, December 25, 2019 7:42 AM

Answers

All replies

  • It is better to put purchase in column and sale in row and better to use excel user interface.

    https://1drv.ms/x/s!Ao913KTUSrrUgRONogk712qnpaMF?e=W8XqeD

    Some time one sale may have multiple price rate. What is your choice ?


    Best Regards, Asadulla Javed

    Saturday, December 28, 2019 7:41 AM
    Answerer
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Cost of Goods.
    Added random dates and costs.
    Added Date Table for optional PP conversion.
    http://www.mediafire.com/file/ihaukyixg6bwumt/12_31_19.xlsx/file
    http://www.mediafire.com/file/n9iccyt71ij6cqu/12_31_19.pdf/file

    Sunday, January 5, 2020 12:44 AM
  • Dear Asadulla & Herbert

    Thanks for your valuable reply and sorry for my delay in reply. 

    Please find the below linked file which is having more than 50 numbers of RM purchase and sales. 

    https://www.dropbox.com/s/cfnj7rak4kslmva/COGS_MSDN_All_Data.xlsx?dl=0

    I couldn't transfer all data from columns to rows because there may be any missing or loss of data will occur. 

    Also, I don't have any basic idea in the power query to get the desired results. 

    Note:

    Purchase date and sales date not in the calculations at all. Only the price and quantity playing the major role to find the cost of goods sold here. 

    Expected results also given in my first post for only one RM. In this post attachment I have added more than 50 RM and actually it is more than 1000 RM. 

    So, If you are providing the solution to find the COGS for one RM with Excel formula or VBA code it is great helpful for me. 

    Thanks in advance. 

    Tuesday, January 14, 2020 6:59 AM
  • If dates play no part at all in the calculations,
    why did you include dates in your latest file?
    If price and quantity play the major role,
    what plays the minor role?
    Why are some RM quantities always fractional
    and others always multiples of 10 and almost identical?
    Why are there zero sales gaps in all weeks
    and nothing but purchases in others?
    Why do sales greatly outpace purchases in all years?
    Why is purchasing quantity shown in a cumulative form
    instead of daily terms?
    What is the official definition of COGS in accounting terms?
    Power Query will always be part of my solutions, so good luck.

    Wednesday, January 15, 2020 2:15 AM
  • Dear Herbert

    My apologies if there was any confusions in my previous post than the first post. In my #1 post I did not mention any dates as it was not in the calculations of COGS. 

    The link is https://www.dropbox.com/s/y4yuoegshgub3us/COGS%20MSDN.xlsx?dl=0

    #4 post I added the actual file which is having date but not for the calculations. 

    In this post above linked sheet I have added the expected results, In that I did not used date for the calculation. 

    My expectation is as below. 

    Purchase of 100kg with 1.5$ price and then 200kg with 2$ Price. 

    and the sales is 10kg, 10kg, 70kg, 20kg and 30kg

    So the expected results of COGS is

    1.5$ for 10kg

    1.5$ for 10kg

    1.5$ for 70kg

    1.75$ for 20kg (1.5$ for 10kg + 2$ for 10kg So 1.75$ for this sales of 20kg)

    and 2$ for 30kg

    Hope you can help me to find the COGS (Please check my expected results for your study and better understanding)

    Thanks in advance. 

    Wednesday, January 15, 2020 6:52 AM
  • The definition of COGS and FIFO is given here:
    https://www.investopedia.com/ask/answers/09/weighted-average-fifo-lilo-accounting.as

    The timeline is integral to FIFO and is expressed as dates.
    My solution conforms to weighted average, FIFO and timeline.
    Your latest post does not answer any of my questions
    and your latest data does not conform to any COGS standard.
    Pass to Asadulla.

    • Marked as answer by Anbuselvam K Thursday, January 16, 2020 6:09 AM
    Wednesday, January 15, 2020 6:01 PM
  • Purchase can be pasted in coumn using Transpose facilty of paste special

    Any way, stick to the solition which is comfortable for you.


    Best Regards, Asadulla Javed

    Thursday, January 16, 2020 10:27 AM
    Answerer
  • Dear Asadulla Javed

    Please find the linked sheet with the solution for your information. 

    https://www.dropbox.com/s/lvxly5dunplpcgc/FIFO%20COGS.xlsx?dl=0

    Anyway, thanks a lot for you and Herbert time spending towards my question. 

    Sincerely Yours

    Anbuselvam K

    Thursday, January 16, 2020 10:48 AM