I am trying to create an excel sheet for a small business. I have calculated and linked most of it however in order to accurately calculate the cost of goods sold, I have to use a method called FIFO method (First in first out). For example if I purchase 300 units of item A at 300,000 and 500 units of items A at a later date for 450,000, so when I sell 350 units, it is basically 300 units @ 300,000/300 and remining 50 units @ 450,000/500. Now there are multiple issues doing this in excel.

1. before anything I have to match the product code (column D)

2. I have to see that the cost of goods sold (column K) first calculates cost based on the oldest purchase and after that if needed the next purchase and so on.

3. I have to make sure that the next time I make sale, it also accounts for the previous sale as the oldest purchase might already be accounted for in a previous sale.

I looked for an attachment button but couldn’t find one so here is my sheet

S.no at column B, Purchase Date at column C, ARTICLE CODE at column D, Purchase units at column E, Purchase price at column F, Transport Expense at column G, Cost of Goods Purchased at column H, Sale Date at column I, Units Sold at column J, Cost of Goods Sold at column K.

Reading it back I realized that I have not written this in the simplest terms. Let’s hope you guys understand. 

​I am trying to create an excel sheet for a small business. I have calculated and linked most of it however in order to accurately calculate the cost of goods sold, I have to use a method called FIFO method (First in first out). For example if I purchase 300 units of item A at 300,000 and 500 units of items A at a later date for 450,000, so when I sell 350 units, it is basically 300 units @ 300,000/300 and remining 50 units @ 450,000/500. Now there are multiple issues doing this in excel.1. before anything I have to match the product code (column D)2. I have to see that the cost of goods sold (column K) first calculates cost based on the oldest purchase and after that if needed the next purchase and so on.3. I have to make sure that the next time I make sale, it also accounts for the previous sale as the oldest purchase might already be accounted for in a previous sale. I looked for an attachment button but couldn’t find one so here is my sheetS.no at column B, Purchase Date at column C, ARTICLE CODE at column D, Purchase units at column E, Purchase price at column F, Transport Expense at column G, Cost of Goods Purchased at column H, Sale Date at column I, Units Sold at column J, Cost of Goods Sold at column K. Reading it back I realized that I have not written this in the simplest terms. Let’s hope you guys understand.   Read More

​