Answered by:
Find the cost of goods sold
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
Answers

The definition of COGS and FIFO is given here:
https://www.investopedia.com/ask/answers/09/weightedaveragefifoliloaccounting.asThe 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
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

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 
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.

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. 
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.

The definition of COGS and FIFO is given here:
https://www.investopedia.com/ask/answers/09/weightedaveragefifoliloaccounting.asThe 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


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