Posted on 2 July 2025
Free FIFO Template Download: Excel, Word, PDF & Google Sheets
What it is
A FIFO template is a sheet that costs every sale from your oldest stock first, then shows what the sale really cost you and what your remaining batches are worth.
When you need it
When the same product comes in at different prices. One supplier raises a price by a dollar, and your average stops telling you what any single sale earned.
What's inside
A FIFO ledger, cost layers, a sale breakdown, a pick list, an item summary, a net realizable value check and printable labels.
What Is the FIFO Template?
You bought 100 cartons at 10. Then 80 more at 12. You sold 70 yesterday.
What did those 70 cartons cost you?
You can't answer that with a calculator and a good memory. You need to know which cartons left first, and that is the question a FIFO template answers on every line.
FIFO stands for first in, first out. The cost of your oldest stock goes to cost of goods sold first. Your newest costs stay in inventory. The FIFO template, sometimes called a FIFO sheet, a FIFO register or a FIFO table, applies that rule for you across every purchase and sale.
oldest cost
next cost
takes batch 1 first
older costs go here
newer costs stay
Don't confuse it with the shelf, though. FIFO makes no promise about which carton physically left. FIFO in your books is a cost rule. What your warehouse picks is a separate decision, and the two can differ on purpose, which we will come back to.
It also is not the same thing as our FIFO and LIFO calculator. The calculator gives you a quick answer to one question. If you want a fifo calculator Excel file you can keep and build on, the fifo template holds the record, sale after sale.
FIFO Format: The Table Every Ledger Needs
Search "fifo format" and you are really asking one thing: what does this look like when it is filled in?
This. A standard fifo table format, or fifo method format as some textbooks call it, follows every move through three questions. What came in? What went out? What is left?
| Date | Move | Units | Cost taken | COGS | Layers left |
|---|---|---|---|---|---|
| Jan 2 | Buy | 100 at 10 | 100 at 10 | ||
| Jan 9 | Sell | 60 | 60 at 10 | 600 | 40 at 10 |
| Jan 14 | Buy | 80 at 12 | 40 at 10 80 at 12 | ||
| Jan 20 | Sell | 70 | 40 at 10 30 at 12 | 760 | 50 at 12 |
| Jan 25 | Buy | 50 at 13 | 50 at 12 50 at 13 | ||
| Jan 30 | Sell | 60 | 50 at 12 10 at 13 | 730 | 40 at 13 |
| Total | 230 in, 190 out | 2,090 | 40 units worth 520 |
Look at January 20. One sale, two prices. That split is the whole reason FIFO is hard to keep by hand.
And here is the check that tells you it is right: cost of goods sold of 2,090 plus ending inventory of 520 equals the 2,610 you paid for everything. If those three numbers don't agree, something in the fifo table is wrong.

What Does a FIFO Excel Template Contain?
A date and a price will not get you far. A working fifo format in Excel, or any fifo method format worth keeping, needs enough detail to trace any sale back to the batch that paid for it.
| Field | Why It Matters |
|---|---|
| Date | FIFO depends on order. A wrong date can change which cost a sale uses. |
| Move | Buy, sell, customer return or write-off. Each one touches stock differently. |
| Item | Layers only make sense within one product. |
| Batch number | The link between your books and the physical carton. |
| Expiry date | Tells the warehouse what cannot wait, even if the books say otherwise. |
| Units in | Creates a new cost layer. |
| Unit price | What the supplier charged per unit. |
| Freight and duties | Part of what the stock cost you, so it belongs in the batch cost. |
| Landed unit cost | Unit price plus the freight and duties per unit. This is the number FIFO uses. |
| Units out | Consumes the oldest cost layer first. |
| Selling price | Without it, you know your cost but not your profit. |
| FIFO cost | Cost of goods sold for that line, even when it runs across batches. |
| Gross profit and margin | What the sale actually earned at the cost it actually carried. |
| Units on hand and stock value | Your running position after every move. |
| Check | Catches overselling, missing prices and lines out of date order before they spread. |
If you only look at one field, make it landed unit cost. A batch bought at 22 with 1 per unit in freight did not cost you 22. Leave freight out and every margin on that batch looks better than it is.
What the Enerpize FIFO Template Includes
We built this around one question: what would you need to defend any number on this sheet if your accountant asked? It runs on formulas only, with no macros, so it opens in Excel and Google Sheets without a security warning. If you came for a fifo excel template free download with formula columns you can read and check, this is it.
Many Products, One Ledger
A FIFO sheet that handles one product and one sales total is a math exercise, not a stock record. Real businesses buy and sell in between, across many items. This fifo template Excel file lets you mix up to 40 items and 500 lines on one ledger. Each item keeps its own layers.
Every Sale Finds Its Batch
Type a ledger line number into Sale Breakdown and it shows the batches that sale used. When a margin looks strange, you stop guessing and look.

The Layers Stay Visible
FIFO gets confusing when you can't see what is left. Cost Layers lists every cost layer with units used, units left, value left and whether it is untouched, part used or used up. It doubles as your fifo inventory tracking view, so you always know the next cost layer in line.

A Warning Before the Mistake
The Check column speaks up when you sell more than you hold, enter a line out of date order, leave out a price or sell below FIFO cost. A separate NRV Check flags stock that may now be worth less than it cost you. That is an easy test to put off until an auditor asks.
From the Books to the Shelf
Pick List tells the warehouse which batches to take, by oldest received or earliest expiry, which also makes this useful for fifo inventory tracking on the floor. FIFO Labels prints batch labels for cartons and shelves, for anyone looking for a fifo sticker format or fifo tag format. The Word and PDF versions add a stock rotation register, which is the fifo register format your floor team can fill in by hand.


Prefer not to start from a blank fifo excel template? The filled sample below shows it all working.
Which Sheet or Template Fits Your Situation?
| If your situation is... | Use this |
|---|---|
| You need to cost sales from the oldest stock, sale by sale | FIFO Ledger in this template |
| You want to see which batches one sale used | Sale Breakdown |
| Your warehouse needs to know what to take first | Pick List |
| You sell food, medicine or anything that expires | Pick List on earliest expiry, plus the stock rotation register |
| You need a one-time answer and nothing saved | FIFO and LIFO calculator |
| You want FIFO, LIFO and weighted average compared, or a manufacturing statement | Cost of goods sold template |
| You report under the last-in, first-out method | LIFO template |
| You only need a stock value at the end of the period | Inventory valuation template |
FIFO Template Example (Filled Sample)
Blank sheets are hard to trust. So we filled one in, so you can see the fifo format working with real moves.
Make your own copy of the filled sample here. It belongs to Northgate Foods, a fictional wholesaler, and covers January 2026 with three products:
- Sunflower oil, the same numbers as the table above: cost of goods sold of 2,090 and 520 left.
- Basmati rice, with freight added to each batch and a customer return, so you can see landed cost at work.
- Canned tomatoes, with a write-off and a batch close to expiry.
How to Read the Sample
Start on Item Summary and work backward. The rice bought in two batches has a landed cost of 23 and then 25. Cost Layers shows 40 units left at 23 and 150 at 25.
Now open Pick List. The newer batch, BR-202, expires first, so the warehouse takes it first under the expiry rule, while the ledger keeps costing rice from the older batch. That is FIFO in the books and FEFO on the floor, side by side.
Then open NRV Check. The tomatoes carry a cost of 710, but at today's expected price they would bring in 660 after selling costs. That 50 gap is exactly the sort of thing a spreadsheet usually lets you miss.
FIFO Formula in Excel
There is no single FIFO function in Excel, so an excel fifo formula is really a small method. People search for a fifo formula in excel expecting one line, and what they need is one idea: how many units has each batch given up so far?
List your batches in date order. If units bought are in column B and the total units sold is in a cell named SoldTotal, this goes next to the first batch and copies down:
Units used = MAX(0, MIN(B2, SoldTotal - units used by the batches above))
In plain English: give up as many units as are still unallocated, but never more than the batch holds, and never less than zero. Multiply by each cost layer's price, add them up, and that is your FIFO cost of goods sold.
The FIFO Formula for Multiple Products
This is where homemade sheets tend to break. With item names in column A, units in B and unit cost in C on a Purchases sheet, and total units sold per item on a Sales sheet, use two helper columns:
D2, units before this batch: =SUMIF($A$2:A2,A2,$B$2:B2)-B2
E2, units used: =MAX(0,MIN(B2,SUMIF(Sales!$A:$A,A2,Sales!$B:$B)-D2))
F2, cost used: =E2*C2
That gives you a total for the period. It will not cost each sale separately, and it can't show you the running balance after every move. If you are learning how to make FIFO in Excel, build it once. Then decide whether you want to maintain it, because spreadsheet audits should make you careful. Raymond Panko's review, older but still widely cited, reports errors in 91% of the 54 spreadsheets audited in studies from 1997 onward. Our template carries that logic per sale, with checks on every line.
How to Use the FIFO Template
1- Fill in Settings
Add your business name, currency, and how many days of expiry warning you want. Those values feed the other sheets, so you only enter them once.
Tip: Currency is a label only. The sheet does not convert one currency into another.
2- List your items once
Spelling matters, because every layer is matched by item name. The list feeds the dropdown in the ledger, so nobody types the same product two different ways.
Tip: Add a reorder level for each item so Item Summary can tell you when to buy.
3- Enter your opening stock as Buy lines
Your opening stock is your oldest cost, so it goes first. Use one line per batch at its original cost, not one line at an average.
Tip: If you are carrying over from last year, copy the remaining layers from your old Cost Layers sheet.
4- Record every purchase
Enter the units, the price and any freight and duties for the whole line. The landed unit cost calculates itself.
Tip: Add the batch number and expiry date now. You will not remember them later.
5- Record sales in date order
Enter units out and the selling price. The FIFO cost appears, including when a sale crosses two batches.
Tip: If a late sale needs adding, insert it at its proper date. Order is the whole method.
6- Record returns and write-offs properly
A customer return goes back to the most recent layer used. A write-off uses the oldest layers and shows as its own cost on Item Summary. For a return to a supplier, reduce the units on the original Buy line.
Tip: Enter return units as a positive number. The Move column handles the direction.
7- Fix anything in the Check column
Red means stop. A wrong line today becomes a wrong cost on every sale after it.
Tip: Scan the column before you close each week. It takes a minute.
8- Review the month and keep your records
Item Summary gives you cost of goods sold, gross profit and stock value per item, and tells you if the totals tie to Cost Layers. Run the NRV Check, then save a copy for the period. Keep it for as long as your tax rules require.
Tip: Count your stock against Units On Hand at least once a period.
FIFO vs LIFO vs Weighted Average vs FEFO
Four names get mixed up here, so let's separate them. Three decide what your stock costs. One decides what your warehouse picks.
FIFO
Oldest cost goes to the sale. Newest cost stays in stock. Accepted under IFRS and US GAAP.
LIFO
Newest cost goes to the sale. Oldest cost stays in stock. Not permitted under IFRS, permitted under US GAAP.
Weighted average
One blended cost per unit, updated as you buy. Simple and accepted widely, but it can't tell you what one batch earned.
FEFO
First expired, first out. A picking rule for the floor, not a cost method. The earliest expiry leaves first, even if it arrived last.
Here is the same stock from our table under each cost method, costing each sale as it happens:
| Method | Cost of goods sold | Ending inventory | Total |
|---|---|---|---|
| FIFO | 2,090.00 | 520.00 | 2,610.00 |
| Moving weighted average | 2,123.33 | 486.67 | 2,610.00 |
| LIFO | 2,210.00 | 400.00 | 2,610.00 |
The total never moves. Only where the cost lands changes. The method doesn't change what you spent. It changes when you see it.
Periodic or Perpetual?
Good news: under FIFO, the answer is the same. Costing 190 units at the end of the month gives 100 at 10, 80 at 12 and 10 at 13, which is 2,090, the same total as costing each sale as it happened. Perpetual records simply show it to you sooner, and let you see the profit on each sale.
Common FIFO Mistakes
✕ Entering sales out of date order
Why it matters: A sale posted late can use a batch it should never have touched, and every later cost moves with it.
Fix: Keep lines in date order, and let the Check column catch the rest.
✕ Leaving freight out of the unit cost
Why it matters: Your margin looks healthier than it is, on every unit of that batch.
Fix: Add freight and duties to the batch they belong to.
✕ Recording a return as a new purchase
Why it matters: You create a layer that never existed, at a price nobody paid.
Fix: Use the Customer Return move, so the units go back to the layer they came from.
✕ Confusing FIFO with FEFO
Why it matters: The oldest receipt is not always the first to expire. Follow the wrong rule and good stock spoils on the shelf.
Fix: Cost by FIFO in the books. Pick by earliest expiry where dates matter.
✕ Selling more than you hold
Why it matters: Negative stock means a purchase is missing or a sale is wrong, and the cost is unreliable until you fix it.
Fix: Stop at the first "Not enough stock" flag and find the cause.
✕ Ignoring stock worth less than it cost
Why it matters: Inventory is carried at the lower of cost and net realizable value, so an unchecked value can overstate your assets.
Fix: Run the NRV Check at each close, and speak to your accountant about any flag.
Importance of the FIFO Template
Why bother, when an average is easier?
Because FIFO tells you something an average can't: what each batch actually earned. When a supplier raises prices, you see the squeeze the moment that batch starts selling.
There is a catch, and it deserves honesty. When prices rise, FIFO puts your older, cheaper costs into cost of goods sold. In our table, that means 120 more reported profit than LIFO on exactly the same stock. More profit on paper can mean more tax. It does not mean more cash. So watch your cash, not just your margin.
And there is the floor. The UN's Food Waste Index Report puts global food waste at more than a billion tons a year, with 12% of that at retail. Nobody fixes that with a spreadsheet alone. But a business that knows which batch to move next has one less reason to throw stock away.
Spreadsheet vs Inventory Software
| FIFO template | Inventory software | |
|---|---|---|
| Best for | Small product ranges, businesses that report under FIFO, learning the method | Growing stock, several warehouses, many users |
| Setup | Download and start | Add products, warehouses and users |
| Effort | Manual entry for every move | Stock updates from purchases and sales |
| Linked to invoices and accounts | No | Yes |
| Limit | 500 lines per file | Grows with your business |
How Enerpize Handles Inventory Costing
What happens on the day the ledger gets too long to trust?
Enerpize keeps inventory on a perpetual basis and values stock at a moving weighted average, recalculated with every purchase. You stop entering moves in two places, and cost of goods sold follows each sale. If your business reports under FIFO, keep this template for those records and talk to your accountant about what fits.
Enerpize Inventory Management Software
With Enerpize inventory management, every tracked product updates its quantity, stock value and cost of sales the moment it moves. You can track products by serial number, lot number or expiry date, get low stock alerts, and see reports such as Inventory Value and Product Average Cost.
Enerpize Stocktaking Module
The sheet knows what should be there. A count knows what is. The stocktaking module compares the two, then records the shortage or surplus with its accounting entry when you reconcile.
Enerpize Purchase Management System
Every layer starts with a purchase. Purchase management runs the cycle from quote to supplier invoice, and AI purchase capture pulls the supplier, items, amounts and taxes from uploaded invoices so you can review them instead of typing them.
Need to bill a customer today? The free invoice maker handles that. And if you are building your accounting basics, our accounting guide is a good next read.
FIFO Rules for Businesses in India
India sends this page more search impressions than any other country, and the rules make FIFO an easy choice.
Under AS 2, LIFO is not permitted, and companies following Ind AS apply Ind AS 2 instead. On the tax side, ICDS II values inventory at the lower of cost and net realizable value, allows FIFO and weighted average, and says a method cannot be changed without reasonable cause.
What to add for Indian businesses:
- Set the currency to INR in Settings.
- Include freight and duties in landed cost, and keep the purchase invoice number in your batch records.
- Run the NRV Check at year end.
Practical note for Indian businesses: pick a method and stay with it. Moving between FIFO and weighted average every year is exactly what the reasonable cause rule is there to question.
FIFO Rules for Businesses in Thailand
Thailand's tax rule leaves you room, but only once. According to PwC's summary of Thai corporate tax, inventory is valued at the lower of cost or market price, any recognized method of ascertaining cost may be used, and changing the method needs prior approval from the Director-General of the Revenue Department.
Private companies under TFRS for NPAEs also write inventory down to the lower of cost and net realizable value.
What to add for Thai businesses:
- Set the currency to THB in Settings.
- Write your chosen method into your accounting policy.
- Keep each period's Cost Layers sheet as support for your closing stock value.
Practical note for Thai businesses: treat your first choice of method as a long-term one, since changing it is not simply your own decision.
FIFO Rules for Businesses in the United States
The US gives you a real choice. IRS Publication 538 covers specific identification, FIFO and LIFO. When goods are mixed together and can't be matched to specific invoices, FIFO or LIFO is what you use, and adopting LIFO takes Form 970 filed with your return.
Small business taxpayers get an extra option: the same publication says they can choose not to keep an inventory, as long as the method they use still clearly reflects income. The gross receipts limit is indexed for inflation, so check the current figure.
What to add for US businesses:
- Set the currency to USD in Settings.
- Keep landed cost support, such as freight bills and duty paperwork.
- Note your method in your books, and use it consistently.
Practical note for US businesses: if you are comparing methods because prices are rising, compare the tax effect with your tax preparer, not just the margin. Remember, a US GAAP write-down is not reversed when values recover, unlike under IFRS.
FIFO Rules for Businesses in Indonesia
In Indonesia, the tax law narrows the choice to two. DDTCNews explains that Article 10 of the Income Tax Law allows inventory cost to be valued by the average method or by first in, first out. LIFO is not an option for tax.
What to add for Indonesian businesses:
- Set the currency to IDR in Settings.
- Use the ledger as your stock card, known locally as kartu persediaan.
- Keep each period's ledger with your supporting records for your closing stock.
Practical note for Indonesian businesses: FIFO is one of only two methods the tax rule accepts, so a clean FIFO ledger keeps your books and your tax position pointing the same way.
Who Can Use the FIFO Template?
Food and Beverage Businesses
Your costs and your shelf life both move. Use FIFO for the books and earliest expiry for the pick list, and use the labels so the night shift can see what goes first.
Pharmacies and Medical Suppliers
Batch and expiry are not optional detail here. Cost Layers shows every batch and warns you before it expires, so a recall or a date check starts with a list and not a search.
Retailers and E-commerce Sellers
Seasonal lines and shifting supplier prices make averages blurry. For retail businesses, Item Summary shows which products really carried the margin.
Manufacturers and Wholesalers
Raw materials bought at changing prices can use the same layers. Use a batch per delivery, and add freight to the landed cost. Manufacturers also get a rotation register for the floor, along with labels for each pallet.
FIFO Glossary
| Term | What it means |
|---|---|
| Cost layer | One batch of stock at its own cost, waiting to be used. |
| Cost of goods sold (COGS) | The cost of the units you sold in a period. |
| Ending inventory | The value of the units still on hand at the end of the period. |
| Landed cost | Purchase price plus the freight, duties and handling needed to get stock to you. |
| Net realizable value (NRV) | Expected selling price minus the costs of completing and selling. |
| Perpetual inventory | Records update with every move. |
| Periodic inventory | Records update at the end of a period, after a count. |
| FEFO | First expired, first out. A picking rule based on expiry dates. |
Key Takeaways
- A FIFO template assigns your oldest cost to each sale first, and leaves your newest costs in inventory.
- One sale can cross two batches. Our example's 70 units cost 40 at 10 plus 30 at 12, which is 760.
- Cost of goods sold plus ending inventory must always equal what you paid. In the example, 2,090 plus 520 equals 2,610.
- Add freight and duties to each batch, or your margins will look better than they are.
- FIFO is a cost rule and FEFO is a picking rule, so your warehouse can pick by expiry while your books cost by FIFO.
- Under FIFO, periodic and perpetual records reach the same total cost of goods sold.
- Higher reported profit under FIFO can mean more tax without more cash when prices rise.
- LIFO is not permitted under IFRS and is not an option in India or for Indonesian tax, so check your local rules before you choose a method.
Frequently Asked Questions
What is a FIFO example?
You buy 100 units at 10 and 80 units at 12, then sell 120. Under FIFO, the first 100 cost 10 each and the next 20 cost 12 each, so cost of goods sold is 1,240. The remaining 60 units stay in inventory at 12, worth 720.
How do I use the FIFO method in Excel?
List your purchases in date order, then work out how many units each batch has given up, using MAX and MIN. Multiply those units by each batch's cost and add them up. To skip the build, download the fifo template, enter your moves, and the formulas do the rest.
What is the difference between FIFO, LIFO and FEFO?
FIFO sends the oldest cost to the sale first. LIFO sends the newest cost first. FEFO is not a cost method at all. It tells the warehouse to pick the stock with the earliest expiry date first.
What columns go in a FIFO table format?
At a minimum: date, units in, unit cost, units out, cost of goods sold, units on hand and the value of the layers left. A batch number and expiry date connect the table to your physical stock.
Can a FIFO template handle multiple products?
Yes, if each item keeps its own layers. Our FIFO Ledger handles up to 40 items in one file, matches layers by item name, and totals each product on Item Summary.
How does FIFO handle returned items?
A customer return goes back into stock at the cost of the layer it came from, and revenue and cost of goods sold reduce. In this template, it returns to the most recent layer used. For a return to a supplier, reduce the units on the original purchase line.
Does FIFO increase tax?
It can. When prices rise, FIFO uses older, lower costs, which raises reported profit, and that can raise the tax due. Your cash does not rise with it. How much it matters depends on your local rules, so check with your accountant.
How does Enerpize calculate inventory cost?
Enerpize uses a moving weighted average, recalculated with each purchase, and records the cost of each sale automatically. Before you change the method your business reports under, speak to your accountant, because some tax authorities restrict changes.
About the Author
Omar El Bahr is a Senior Digital Growth Specialist at Enerpize, where he leads SEO, content strategy, and organic growth across international markets. He is a Forbes Communications Council contributor and has written for Entrepreneur on business communication and digital strategy.
Disclaimer
This template and the information on this page are for general guidance only and are not accounting, tax or legal advice. Inventory rules differ by country and change over time, so check your figures with a qualified accountant before you rely on them.
