image_2022-08-20_151615032 | eFinancialModels

Video Tutorial:

If you have historical purchases of inventory units at varying prices, when you go to sell that inventory, the financial statement process will require you to enter a value for the cost of goods sold (COGS). That value is often required to be calculated using the FIFO accounting methodology. FIFO means the value of inventory sold is based on depleting the older inventory units that were purchased first.

Example: Buy 100 units at $20.00 and then buy 50 units at $14.00. If you then sell 75 units, those 75 will be valued at $20 (value of initial units purchased). If you then sell another 50 units, the remaining 25 from the original inventory purchase (of 100) will be valued at $20 and the next 25 will be valued at $14.00 to get the final COGS for that second batch sale you need to know the above logic in order to know what the actual COGS was for the sales batches.

Imagine having a whole bunch of purchases and sales of a given inventory item and having to figure all that out manually. It is time-consuming and difficult. The benefit of this inventory tool is the automation of all those manual calculations so that all the user needs to do is enter their purchase and sales history. The resulting COGS will automatically populate based on the FIFO methodology.

There is an automated total COGS per month calculation based on the date of each sale as well.

The two matrix tabs were required in order to automate this calculation. It was one of the more difficult pieces of logic I have ever did build. The formulas can easily be drug out for more than 350 batches if needed (but that should be sufficient for most organizations).