FIFO Excel template: Automate Flow from inventory to Cost of Goods Sold based on First in First Out

This is an attempt to automate the tabulation of Cost of Goods Sold amount from an existing list of inventory based on First in First Out (FIFO) Principle.

The template consists of 2 tabs:

1. Inventory worksheet: On the left hand side is for the inventory listing; the right hand side is for the current sales orders pending to match COGS with inventory on FIFO basis

2. LOG worksheet: A log of all matches done. Meant as an Audit Trail for tracking the detailed breakdown of the source of COGS

How the template works

Upon Clicking the FIFO Matching Button

• Will sort the inventory list based on date

• Will pull stock from inventory list to Sales Orders' COGS column on a FIFO basis

• All Sales Orders where there isn't sufficient inventory to cover will be skipped and tagged as "insufficient stock"

• An audit trail log could be located over at the LOG worksheet for all the matches done

• After triggering the macro, any further additions to either inventory or Sales Orders could still be appended at the respective columns. Click the button again for another round of matching after adding any new records. Any new matches done will be added accordingly to the LOG worksheet

• Note: New inventory should be added as new records in another line and not directly update Qty in existing inventory records

Sales Orders to be filled from Column K till Column M

Click on Button to run the matching macro

List of transnational log of all matches are recorded in the LOG worksheet

DOWNLOAD FIFO-Template HERE

https://docs.google.com/spreadsheets/d/1mM1INRs5HFaKr9DCM-sUncT3UFr_YSWB/edit?usp=sharing&ouid=102583703753746949912&rtpof=true&sd=true

Updated 12th Feb 2018: Fixed Bug which ignores "Insufficient stock" on subsequent matches after the 1st run

Updated 13th Apr 2019: Tidied up and Fixed Bug (which ignores possibility of similar SKUs) ~ Replaced "Find xlPart" with "Find XlWhole" to ensure exact matches

On a side note, there are examples where FIFO could be achieved through DATA Tables (non-VBA method) and Custom Functions method. As per listed below for reference as it may be more applicable for you depending on your unique set of circumstances / requirements.

By Somkiat Foongkiat: A very creative use of DATA TABLES (non- VBA method) ~ explanation with sample file could be found over at his site http://pakaccountants.com/fifo-costing-inventory-excel-data-tables/

By Marcus Small: 2 examples, One through Custom functions ;another a VBA solution ~ explanations with sample files for both could be viewed over at his site http://www.thesmallman.com/fifo-calculator/

Next: Logic of Capitalism