Monthly Cost of Goods Sold Valuation Template: FIFO-Based Methodology
Trying to figure out the value of cost of goods sold (COGS) can become quite difficult when using the FIFO method of valuation. This means first-in-first-out or in other words as you sell a unit of inventory, it’s cost basis is going to be a function of the earliest inventory that was purchased within that unit SKU. When you have many buy orders and sell orders of a given sku, it can be time consuming and tedious to try and figure out what the actual value of inventory that is sold is and the corresponding price. This template helps a lot. All the user needs to enter is a buy order, $ paid, units purchased and then units sold. Based on highly advanced logic, a matrix automatically calculates the value of each sell order depending on how many units were bought at various prices.
I also added a secondary template that is available upon request (it is nearly 40MB in size), which will handle up to 5 different SKU’s. The logic is quite clever and the formulas use reference positions in an extremely effective way so that with very little data input, the complex math of FIFO valuation can be done. The resulting COGS will then auto-populate within a 12-month schedule.
