Excel Tool for FIFO Based Cost of Goods Sold (COGS) Calculation

If you are looking to save some time on figuring out what your cost of goods sold value is, this Excel template is going to be of assistance. It automates a calculation that nearly always is done manually by Accountants. The reason being is that you can't easily setup formulas to do it.


$75.00 USD

The templates will be immediately available to download after purchase (all versions). This is included in the Accounting Templates for Excel and Inventory bundle. A Google Sheets version is included in the download.


fifo inventory calculator

IFRS (International Financial Reporting Standards) and GAAP both accept valuing the cost of goods sold based on FIFO (first in, first out). IFRS actually requires this method. What it means is the units of inventory you sell should be valued (from a cost perspective) based on when the oldest units were purchased. If you are preparing financial statements, this stuff is vital to fully understand.

Extended Version (up to 5 SKUS) - Included in Purchase Above



For example, if I buy 5,000 units of something at $1.50 and then 2,000 units of the same thing at $1.25 and then I go and sell 4,000 units, then those 4,000 units would all be priced at the original purchase of $1.50 per unit.

Likewise, if you then sell another 2,000 units, it would mean 1,000 should be based on the $1.50 price and 1,000 at the next purchase price of $1.25.

You can see how this can get complicated when you have a bunch of purchases for varying prices and varying purchase amounts as well as sales volume. The Excel tool here will take all of that information and give you a nice clean COGS value for each sales batch.

The only thing the user needs to do is enter their purchase orders and sales orders in order from oldest to newest (top to bottom). If you have the data and a date tag, make sure you sort it by date (oldest to newest) before pasting it into the proper columns.

I have put in sample data so it is clear on how the data should be entered in relation to the date attached to the transaction happening.

 The accounting manager or whoever is having to figure out what the GAAP compliant or IFRS compliant value of COGS is will immediately save time by using this instead of trying to manually do the calculation.

There are up to 400 unique batches that can be taken into account at one time. It is not hard to expand the formulas, but I would imagine most businesses that use this will be able to fit their yearly data onto this. If not, it can be expanded indefinitely.

More Accounting Spreadsheets / Calculators / Tools: