FIFO, or “First In, First Out,” is a concept used to manage inventory and calculate profits. It means that the first assets acquired are the first ones to be sold or used.

📥 Download the example file: FIFO Calculation.xlsx

A FIFO calculation is crucial when dealing with assets that:

• Were purchased at varying prices across multiple transactions
• Have been sold in several stages at different prices
• Cannot be uniquely identified or tracked individually

Example applications of FIFO

• In Logistics: FIFO ensures that older inventory is used or sold before newer stock, helping to avoid spoilage, obsolescence, or waste.
• In Finance and Accounting: FIFO is commonly applied to stocks, bonds, or cryptocurrencies. When these assets are sold, capital gains or losses are calculated by subtracting the cost of the earliest purchased units from the sale price.

How to create a FIFO model in Excel:

1. First, we create two tables that store data on purchased and sold assets.
   
2. Then, we calculate in a new column how many of the purchased assets have been sold. For the first purchase, the formula would be:

=MIN( [quantity purchased in purchase no. 1] ; [quantity sold in total])
    

The MIN function returns the smallest of its arguments, and in this case, it determines if all 100 units purchased on 01/03/2022 have already been sold.

3. For the next purchase, apply a similar formula, but consider the 100 units already sold. Then, use a SUM formula to add all the values above and subtract them from the total number of units sold.
   

We can now see how all 340 units sold have their own cost basis in the table for purchased assets.

4. Calculate the cost of goods sold by multiplying [Quantity sold] by [Price per unit].
   
5. Finally, we obtain the profit/loss by subtracting the cost of goods sold from the revenues.
   

Note that this calculation only works for products of the same type. If you need multiple types, you can simply create separate sheets for each product.

Tip: You can further develop this model by, for example, calculating the inventory value of products that have not yet been sold!

Do you want to sharpen your skills even more in your role as a purchaser or logistics professional?

Excel is one of the most important tools in both purchasing and logistics – whether you’re managing forecasts, tracking deliveries, or analyzing costs. With our online course Excel for Purchasers and Logistics Professionals, you’ll gain practical skills that can be immediately applied in your daily work.