FIFO inventory method
The FIFO (first-in, first-out) inventory method implies that the first goods purchased are also the first goods to be sold. FIFO inventory can be seen as a theoretical model of the actual flow of goods, used for accounting or financial purposes. FIFO inventory can also be considered as a supply chain practice, intended to limit expiration or obsolescence issues that negatively impact stocked goods. FIFO inventory analysis allows to compute the stock “age” as well as to identify slow moving or dead inventory. In the following, we see how the FIFO analysis is carried out in practice, and also outlines the limits, both theoretical and practical, of this approach.
From a supply chain perspective focusing on the actual flow of goods, it is usually considered to be a good practice to have the earliest purchased goods shipped first. By adhering to this process, companies can typically mitigate most expiration write-off as long as the goods are not overstocked. This practice can also limit minor goods obsolescence associated with long storage periods (e.g. tarnished packaging).
For example, in fashion e-commerce, product returns may represent close to 50% of all goods being shipped. In this context, it is frequently considered as a best practice to first reship the goods that have been previously returned. This rule is an extension of the FIFO method, with the returns properly being taken into account. This method facilitates dealing with secondary sales channels at the end of a product collection.
Many warehouse systems do not differentiate at all as to which units get shipped, and ship a random unit of stock instead of the oldest one. The analysis of this practice goes beyond the scope of this document.
Unlike the physical FIFO, the FIFO analysis adopts a theoretical perspective on inventory, assuming that the earliest purchased units are shipped first, irrespective of the actual physical flow of goods. The FIFO perspective greatly simplifies the financial analysis of inventory.
In practice, all it takes to perform a FIFO analysis is:
- the current stock levels
- the history of purchase orders with delivery dates
Based on this data, FIFO analysis provides a way to compute the following:
- inventory valuation, taking into account varying purchase prices
- expected gross margin, which depends on the purchase prices
- average inventory age (and extremes too)
In the following section, we illustrate how the above can be computed from a practical perspective.
Envision and FIFO analysis
Envision provides a
fifo() function that is precisely dedicated to the FIFO analysis. Intuitively, given the current stock levels and past purchase orders, the
fifo() method would be expected to return, in one way or another, a detailed composition of stock, indicating the age of each unit left in stock. It turns out that each unit is associated with one - and exactly one - purchase order line. This insight is a bit subtle, but it yields a method that consists of computing, for each purchase order line, how many units are still unsold considering the current stock levels.
Consequently, it is straightforward to see how these unsold quantities can be used for deriving the relevant financial KPIs. Let’s see how the
fifo() function actually computes the unsold quantities associated with each purchase order line:
read "/sample/Lokad_Items.tsv" read "/sample/Lokad_PurchaseOrders.tsv" as PO with "Quantity" as Qty PO.Unsold = fifo(StockOnHand, PO.Date, PO.Qty) show table "Unsold" with Id PO.Date PO.Unsold
The script above begins by reading two files: the list of items and the list of purchase orders, as obtained from the sample dataset. Then, on line 5, a call to the
fifo() function is performed. This function takes three arguments:
- the current stock level
- the date of the purchase order, typically the delivery date
- the quantity associated with the purchase order
On line 6, the
show table statement displays the results of the calculation.
Inventory valuation is straightforward to compute with the following script:
read "/sample/Lokad_Items.tsv" read "/sample/Lokad_PurchaseOrders.tsv" as PO with "Quantity" as Qty PO.Unsold = fifo(StockOnHand, PO.Date, PO.Qty) StockValue = sum(PO.Unsold * PO.NetAmount / PO.Qty) show table "Total stock value" with sum(StockValue)
The script above multiplies the unsold quantity
PO.Unsold by the original purchase unit price, computed as
PO.NetAmount / PO.Qty.
Averaged Purchase Price
The purchase price can be averaged using the FIFO method. The script below is a variant of the previous one tailored for this purpose:
read "/sample/Lokad_Items.tsv" read "/sample/Lokad_PurchaseOrders.tsv" as PO with "Quantity" as Qty PO.Unsold = fifo(StockOnHand, PO.Date, PO.Qty) StockValue = sum(PO.Unsold * PO.NetAmount / PO.Qty) PP = StockValue /. sum(PO.Unsold) where PP > 0 // filter items without purchase orders show table "Purchase prices" with Id Name PP
PP contains the purchase prices for all items. As we are using the
/. division operator (which returns zero if the denominator is zero), all items where no purchase price can be computed are filtered on line 9 due to the lack of corresponding purchase orders.
The stock age is also relatively straightforward to compute, however, it requires one extra ingredient: the date reflecting the present. Inventory data might sometimes be a few days old, and the current calendar date might not be the correct date for performing the analysis. In the example below, we introduce a variable named `today` which can be further adjusted in a real-life situation.
read "/sample/Lokad_Items.tsv" read "/sample/Lokad_PurchaseOrders.tsv" as PO with "Quantity" as Qty // last observed date in data today := max(PO.Date) + 1 PO.Unsold = fifo(StockOnHand, PO.Date, PO.Qty) PO.Age = (today - PO.Date) // age in days Age = sum(PO.Unsold * PO.Age) /. sum(PO.Unsold) show table "Stock age" with Id Name Age order by Age desc
The script above displays the list of all items in stock sorted by stock age, placing the oldest items at the top of the list. This calculation is performed on lines 8-10, averaging the age of each purchase order against its unsold quantities.
Limits of FIFO analysis
The expressiveness of the FIFO analysis comes from its underlying assumptions about the inventory flow. However, when data or the actual physical flow of goods diverge from these assumptions, the quality of the results tends to suffer. In this section, we review the most commonly encountered issues associated with FIFO analysis.
Truncated purchase history
While it might not be required to have the full purchase history since the beginning to carry out an accurate FIFO analysis, it is frequent that the oldest stocked units are present in stock for longer than the available depth of purchase history. Although in some industries, like aerospace, having 10 year-old parts is not uncommon, extracting 10 years’ worth of purchase history from company systems can be a tough challenge.
Stock-out replays are hazardous
If both the purchase order history and the sales order history are available, then it might be tempting to consider that the full history of stock-outs can be recomputed by replaying the whole sequence of purchase and sales orders in chronological order. In theory, through such a simulation, all inventory levels at any point in time could be re-computed, hence flagging all the periods where inventory levels were at zero as stock-outs.
Unfortunately, Lokad’s experience indicates that this approach almost never works. In this case, the slightest inventory discrepancy - such as a single unit not accounted for - wreaks havoc on the whole analysis because the periods of stock-out will almost never be at zero, but will rather consist of small quantities (positive or negative) which cannot be used to positively identify stock-outs.
When all units have a unique serial number, and when all inventory movements are identified at the level of this serial number, then, the FIFO analysis should usually be considered as a very rough approximation. If the inventory is worth being physically tracked at the serial number (S/N) level, then this inventory is most certainly worth being analyzed by leveraging the extra level of detail provided by serial numbers.