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.

The template will be sent to you after purchase via your PayPal/purchasing e-mail (both versions).

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

IFRS (Internation 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.

I.E. 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.