In audits where inventory is a material item, it is important to understand how efficiently it is managed. Products sitting too long can become obsolete, leading to write-downs and unnecessary storage costs. A simple turnover analysis gives insight into fast- and slow-moving items. This information is useful when assessing inventory valuation and risk.
You can also analyse the age of the inventory. In that article we dive also into the differences between that analysis en the current one.
What do we need?
For this analysis we use Python with pandas.
To try the script below, use the dummy file with stock movements. It contains all product movements (sales, purchases, corrections and balance positions). Download it here:
Reliability
Make sure the inventory movements are reliable — complete and correct. For that, start with a goods movement check.
Step 1: Determine Sales Volume
Okay, lets start with the analysis. We first select only the sales ('MutationType = Sales') and calculate total sales per product:
import pandas as pd
# Load stock movements
df = pd.read_csv("dummy_stock_movements.csv", sep="|")
df['Date'] = pd.to_datetime(df['Date'])
sales = df[df['MutationType'] == 'Sales']
sales_summary = sales.groupby(['ItemID', 'ItemName'])['Quantity'].sum().reset_index(name='TotalSales')
Step 2: Calculate Average Inventory
We use 'Balance Begin' and 'Balance End' to estimate average inventory over the year. If you have daily balances, it's even more accurate to use the daily average instead.
balance = df[df['MutationType'].isin(['Balance Begin', 'Balance End'])]
avg_stock = balance.groupby(['ItemID', 'ItemName'])['Quantity'].mean().reset_index(name='AverageInventory')
Step 3: Calculate Turnover Ratio
Now we combine the data and compute the ratio:
rotation = pd.merge(sales_summary, avg_stock, on=['ItemID', 'ItemName'], how='inner')
rotation['TurnoverRatio'] = rotation['TotalSales'] / rotation['AverageInventory']
Example Results
And that's basically it.. Now you can sort the result and take a look at the findings.
rotation.sort_values(by='TurnoverRatio', ascending=True, inplace=True)
print(rotation)
| Product | Sales | Avg. Inventory | Turnover Ratio |
|---|---|---|---|
| Ultra-High Performance Gaming Laptop | 44 | 379.5 | 0.12 |
| Smart Home Security Camera | 516 | 391.5 | 1.32 |
| 24-Port Gigabit Ethernet Switch | 508 | 345.0 | 1.47 |
| High-Speed 1TB SSD | 744 | 206.5 | 3.60 |
| Multi-Port USB-C Docking Station | 434 | 110.0 | 3.95 |
Interpreting the Results
A low turnover (< 1) suggests the product turns less than once over the period (in this case over the year), so possibly obsolete or overstocked.
A high turnover (> 5) indicates fast movement and efficient capital use.
In the dummy data, laptops have a very low turnover. It is restocked during the year but only 44 were sold. There might be a good reason; maybe a big order was cancelled or there was a good discount from one of their suppliers. You can decide to verify the age of this inventory of these products.
Conclusion
This simple analysis reveals fast-moving and slow-moving products. Especially for companies with large inventories, it is a valuable tool during financial audits.
