FIFO Calculation in Excel
FIFO is a fundamental principle in logistics, finance, and accounting. FIFO stands for ‘first in, first out,’ indicating that assets acquired first should also be sold first.
Download the sample set: FIFO Calculation.xlsx
A FIFO calculation is essential for determining costs and profits for assets that:
- Have been acquired at different prices in multiple rounds.
- Have been sold at different prices in multiple rounds.
- Cannot be uniquely identified.
In logistics, FIFO ensures that older goods are used or sold first, minimizing the risk of selling expired or obsolete items.
In economic contexts, this principle is primarily applied to assets such as stocks, bonds, or currencies. When selling these assets, the capital gain or loss is often calculated by subtracting the cost basis from the selling price according to the FIFO principle.
To create a dynamic FIFO model in Excel:
- First, we create two tables that store data on purchased and sold assets.
- 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.
- 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.
- Calculate the cost of goods sold by multiplying [Quantity sold] by [Price per unit].
- 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!
