Difference Between Turnover and Age
In this article, we discuss the age of the inventory, but in another article, we also covered the inventory turnover analysis. In short, with turnover, you look at how often inventory is converted, whereas with an aging analysis, you look at how old the current inventory is.
Inventory turnover is useful if you want to know how efficiently inventory has been used throughout the year. You look at throughput and turnover speed. This is especially useful for companies with many items and continuous sales.
An aging analysis is useful if you want to know how old the current inventory is on the balance sheet date. You are looking more at the current risks of obsolescence or write-down. This is particularly important for items with a limited shelf life, that are seasonal, or where the industry is trend-sensitive.
In practice, both analyses complement each other: turnover shows the dynamics over time, while age provides a snapshot of the moment. It might well be that the turnover was quite good at the beginning of the year, but the remaining stock on the balance sheet date is already quite old.
What Do We Need?
Alright, let's start with the analysis itself. For this analysis, we will use Python with pandas and the dummy dataset with stock movements:
The dataset contains all movements per product (purchases, sales, corrections, and opening/closing balances).
import pandas as pd
from datetime import datetime
# We load the data directly from the website.
# This makes the code immediately executable.
url = "https://www.theauditanalytics.com/data/movement/dummy_stock_movements.csv"
df = pd.read_csv(url, sep="|")
# Ensure the 'Date' column is of type datetime
df['Date'] = pd.to_datetime(df['Date'])
FIFO Logic
Next, we implement the FIFO logic. After all, we want to know the age of the current inventory. If a product was purchased six months ago but has since been sold, it should not count as old stock. It's only about the inventory layers that are still present on the balance sheet date. To process this correctly, we use a simplified FIFO (First-In, First-Out) approach:
- Every purchase creates an inventory layer with the receipt date.
- Every sale first reduces the oldest available layer.
- On the balance sheet date, only the remaining layers are left. Only these count in the aging analysis.
Step 1: Build the FIFO Layers
We simulate purchases and sales in chronological order and track which quantities are left on the balance sheet date.
# Sort transactions by date to apply FIFO logic correctly
df = df.sort_values(by='Date')
# We use a deque for efficient 'pop' operations from the left (oldest layer)
from collections import deque
# Dictionary to track stock layers per product
stock_layers = {}
# Loop through all transactions
for _, row in df.iterrows():
item = row['ItemID']
qty = row['Quantity']
date = row['Date']
price = row.get('UnitPrice', 0) # Use .get() in case the price is missing
# If it's a purchase, add a new layer
if row['MutationType'] == 'Purchase':
stock_layers.setdefault(item, deque()).append([qty, date, price])
# If it's a sale, update the oldest layers
elif row['MutationType'] == 'Sales':
to_sell = qty
# Continue as long as there is quantity to sell and stock exists
while to_sell > 0 and stock_layers.get(item):
# Get the oldest layer
layer_qty, layer_date, layer_price = stock_layers[item][0]
# If the layer is completely sold
if layer_qty <= to_sell:
to_sell -= layer_qty
stock_layers[item].popleft() # Remove the oldest layer
# If the layer is partially sold
else:
stock_layers[item][0][0] -= to_sell # Decrease the quantity in the layer
to_sell = 0
# Collect all remaining layers after processing all transactions
fifo_layers = []
for item, layers in stock_layers.items():
for layer_qty, layer_date, layer_price in layers:
fifo_layers.append({
'ItemID': item,
'Date': layer_date,
'Quantity': layer_qty,
'UnitPrice': layer_price
})
# Create a DataFrame from the remaining FIFO layers
fifo_df = pd.DataFrame(fifo_layers)
Step 2: Calculate the Age of the Remaining Stock
We only analyze the layers that still exist on the balance sheet date.
# Set the reference date for the age calculation (e.g., balance sheet date)
reference_date = datetime(2024, 12, 31)
# Calculate the age in days for each stock layer
fifo_df['AgeDays'] = (reference_date - fifo_df['Date']).dt.days
# Calculate the value of each stock layer
fifo_df['StockValue'] = fifo_df['Quantity'] * fifo_df['UnitPrice']
Step 3: Classify into Age Categories
# Define the age categories (buckets) in days
bins = [0, 30, 90, 180, 9999]
labels = ["0-30 days", "31-90 days", "91-180 days", ">180 days"]
# Assign each stock layer to an age category
fifo_df['AgeBucket'] = pd.cut(fifo_df['AgeDays'], bins=bins, labels=labels, right=True)
# Group by category and sum the stock value
age_summary = fifo_df.groupby('AgeBucket')['StockValue'].sum().reset_index()
# Display the summary
print(age_summary)
Example result (dummy data):
| Age Category | Total Value |
|---|---|
| 0-30 days | € 48,200 |
| 31-90 days | € 21,400 |
| 91-180 days | € 5,100 |
| >180 days | € 12,600 |
Step 4: Visualization
import matplotlib.pyplot as plt
# Create a bar chart to visualize the results
plt.figure(figsize=(8,5)) # Set the figure size
plt.bar(age_summary['AgeBucket'], age_summary['StockValue'])
# Add titles and labels for clarity
plt.title('Inventory Aging Analysis (FIFO)')
plt.xlabel('Age Category')
plt.ylabel('Total Stock Value (€)')
# Show the plot
plt.show()
Conclusion
With this aging analysis, you can quickly see which products are staying in stock for a long time. Large stocks in the >180 days category may indicate obsolete inventory and a risk of write-down. Hopefully, this helps with your audit process, good luck!
