SmartHelping / Accounting / Inventory
FIFO Cost of Goods Sold Calculator for Excel
Calculate cost of goods sold as inventory moves through purchase batches with different unit costs. Enter purchases and sales in date order, then let the template assign the oldest available costs to each sales batch.
Includes the Google Sheets version and the extended version for up to 5 SKUs.
Immediate download after purchase. By purchasing, you agree to the Terms of Service.
Video walkthrough & spreadsheet preview
See the FIFO calculation in action.
What the template does
Turn purchase and sales records into FIFO COGS.
Different purchase costs
Account for inventory bought in different quantities and at different prices. FIFO uses the earliest remaining purchase costs first.
Cost for each sales batch
Calculate the COGS associated with a sale, including sales that draw inventory from more than one purchase batch.
Up to 400 unique batches
Use the existing batch capacity for your transaction data. Additional capacity requires extending the formulas and checking the expanded ranges.
Sample data included
Follow the example transactions to understand how purchase quantities, unit costs, sales quantities, and dates should be entered.
Getting started
Start with records sorted from oldest to newest.
-
Review the sample data
Use the included examples and video walkthrough to identify the purchase and sales input areas.
-
Enter purchase batches
Sort purchases by date, oldest to newest, and enter the relevant quantities and unit costs in the input columns.
-
Enter sales batches
Sort sales by date, oldest to newest, before pasting them into the corresponding columns. Keep dates and quantities together when sorting.
-
Review the calculated COGS
The formulas allocate the oldest available inventory costs to each sales batch. Review the results against your underlying transaction records.
A simple FIFO example
One sale can use more than one purchase cost.
Assume you first purchase 5,000 units at $1.50 each, followed by 2,000 units at $1.25 each. You then make the two sales below, in order.
| Sales batch | FIFO cost allocation | COGS |
|---|---|---|
| First sale: 4,000 units | 4,000 × $1.50 | $6,000 |
| Next sale: 2,000 units | (1,000 × $1.50) + (1,000 × $1.25) | $2,750 |
The second sale uses the last 1,000 units from the first purchase and 1,000 units from the next purchase. The remaining 1,000 units carry a cost of $1.25 each.
Included in your purchase
Extended version for up to 5 SKUs.
The purchase includes an extended version for up to five stock keeping units (SKUs). Watch the walkthrough below to see how this version is organized.
More templates in one purchase
Explore the bundles that include this tool.
Questions before you start
A few useful details.
What does FIFO mean?
FIFO stands for first in, first out. For cost allocation, the earliest remaining inventory costs are assigned to sales before newer purchase costs are used.
Do the transactions need to be sorted?
Yes. Enter purchase and sales records from oldest to newest, top to bottom, in their respective input areas. Sort the complete records together so each date stays paired with the correct quantities and costs.
Is a Google Sheets version included?
Yes. A Google Sheets version is included with the download, along with the Excel templates and the extended version for up to five SKUs.
How much transaction data can the template handle?
The template is set up for up to 400 unique batches. The formulas can be extended for additional capacity; review the expanded references and results when making changes.
Does IFRS require FIFO for every inventory calculation?
No. Under IAS 2, ordinarily interchangeable inventory can use FIFO or weighted average cost. Specific identification applies to certain other inventory. This template calculates FIFO COGS. See the IFRS Foundation's IAS 2 overview for the accounting standard.
How is the tool delivered?
The $75 one-time purchase provides immediate access to the downloadable files. The Google Sheets version and the extended version for up to five SKUs are included.
FIFO inventory costing
Spend less time allocating purchase costs.
FIFO Cost of Goods Sold Calculator — $75, one-time purchase.
