FIFO-Based COGS Inventory Valuation Template in Excel

Description
Any business that sells and buys inventory in bulk will benefit from this template and it will save time. The excel tool will automatically calculate your COGS value based on using FIFO (first in, first out) methodology. This method is required by IFRS (international financial reporting standards) and accepted by GAAP in the US. The reason why it is so beneficial to have an automated way to calculate this is because a manual calculation can get quite difficult and time consuming when you have a long history of purchases as well as a long and varying amount of batch sales. The difficulty is assigning the right cost to that inventory that has been sold.

I spent a many hours trying to figure out a way to automate the calculation based on the user entering their order and sales history only. It was possible in the end and all the user needs to do is make sure they enter their data in chronological order (oldest dates at top).

As long as the data is entered in the order it was purchased from top to bottom, this template will automatically calculate the value of your COGS for each sales batch. By tagging a date on the sales, this will also calculate the total COGS for each month over a 12-month period.The nice thing about this tool is that it is 100% dynamic and can handle any kind of variant in how many units are bought and how many units are sold over time. It includes up to 400 batches of unique purchases and sales. If you need more slots, the formulas are all dynamic and all that is required is dragging them over and down. You will also need to update the sumproduct range accordingly (adding more columns).

The problem this solves has been something bugging me for a long time. I never could figure out how to know what purchases units had been used and how to do the partial allocation of cost when the sales came between two purchases. Finally, it is done here.

Note, this works for a single SKU at once. To track multiple SKU's, you would need to use multiple workbooks.

This template is also included in two bundles:
- All Models Bundle: https://www.eloquens.com/tool/P8Y4TX4v/finance/financial-forecasting-models/financial-models-120-useful-and-usable-logic
- Accounting Tools: https://www.eloquens.com/tool/axKzHNRV/finance/accounting/accounting-bundle-useful-excel-templates-for-accounting-department