Efficient inventory management, utilizing ABC and XYZ classification methods, can empower SMEs to handle larger revenues with the same operating cost base or reduce current operating costs by eliminating inefficiencies, thereby enhancing financial returns in the current business environment.

SMEs (i.e. Small & Medium Enterprises) account for 99% of the number of enterprises in Singapore or 298,500 (rounded off) enterprises in terms of numbers and contribute 71% of employment by enterprises and 48% of Nominal Value Added of Enterprises (2022).

SMEs are defined as enterprises with operating revenue of not more than S$100 million or employment not more than 200 workers.

As finance professionals we can contribute to helping SMEs to increase their productivity (enhance their ability to handle larger revenues with the same operating cost base or reduce their current operating costs by eliminating inefficiencies) thereby increasing their financial returns.

The Manufacturing industry in Singapore accounted for 19.2% of GDP in the previous 4 quarters (2022 Q4 to 2023 Q3) and the Wholesale & Retail trade industry accounted for 19.7% of GDP in the same period.

Thus, inventory management could be an area we can explore some simple measures for increasing efficiency, keeping in mind the nature of SME operations and their relative limitation of resources compared to large enterprises which can afford expensive ERPs and consultants for advisory & implementation services.

With rising interest costs, storage costs & labour costs, optimising the amount of inventory & maximising the inventory turnaround would offer some critical savings & efficiencies in the current environment.

Generally, to recap, there are two types of inventory accounting methods – periodic inventory accounting and perpetual inventory accounting.

In the periodic inventory accounting method, inventory on hand is counted & accounted at periodic inventory intervals such as monthly, quarterly etc. and cost of goods sold for the period is arrived at accordingly using LIFO (Last-In-First-Out) or FIFO (First-In-First-Out) or Weighted Average Method. This method is suitable for smaller companies with limited inventories.

In the perpetual inventory accounting method, inventory is accounted on a real time basis removing the need for manual periodic counts and providing visibility for better inventory management. LIFO or FIFO or Weighted Average Method can be used to assign the inventory cost to goods sold. This is more suitable for bigger companies with larger inventories and multiple locations.

Inventory managements comprises methods beyond the basic accounting & recording aspects such as optimising the inventory/sales ratio, ensuring the right level of stock to prevent loss of sales, planning & forecasting inventory replenishment etc.

To derive actions & priority areas, all items in the stock-in-trade cannot be treated the same and we first need to categorise the items in the inventory or stock-keeping-units (SKU) into different categories based on the SKU attributes and revenue contribution.

How should the classification of SKUs in inventory be done?

Firstly, a common method of classification known as ABC classification can be done.In the ABC method, the Top 10-20% of items (in terms of number of items or SKUs) which account for approximately 60-70% of sales revenue value in the period are classified as A.

The next 20% of the number of the items are classified as B which account for approximately 20% of sales revenue value in the period. The remaining 60-70% of the number of items are classified as C which account for approximately 10- 20% of the sales revenue value in the period.

Next the ABC classification can be supplemented with XYZ classification which is a method to classify SKUs in inventory based on the variability of the demand from period to period. An item which has consistent demand say every day or every week based on historical sales can be classified as X and reliable forecasts are possible for these items. An item which has moderate demand say once very few weeks and forecasts are less reliable can be classified as Y.

The remaining items which have uncertain demand say once in a few months and forecasting is not feasible can be classified as Z. Thus, each item or SKU would be classified as A, B or C and as X, Y or Z. Once these classifications are done, each item or SKU can be assigned a combined label of AX, BX, CX, AY, BY, CY, AZ, BZ or CZ. Attributes of each label are described below (a combination of the relevant ABC and XYZ attributes).

Attributes of each label are described below (a combination of the relevant ABC and XYZ attributes).

Now the client’s planning team can arrive at customized policies for each combined label category as illustrated below:

To illustrate the same with an example, a public transaction data set of a UK based online retailer from 01-Dec-2010 to 09-Dec-2011 has been used. The method is equally applicable for B2B datasets as well. A B2C dataset has been chosen since it is more easily publicly available.

The data is available in excel from this link: https://archive.ics.uci.edu/dataset/352/online+retail and has been imported into and analysed using Python since the number of rows (527,793 rows after cleaning) is quite large and processing in Microsoft Excel is often slower for larger datasets.

Below is a screenshot of the first few rows of the dataset. This is a typical transaction dataset which is available in a similar form at most companies in the manufacture and/or trade of goods.

First step is to perform exploratory data analysis (EDA) to understand the data & identify cleaning needed.

The Second step is to clean the data of matter which is irrelevant for the ABCXYZ analysis. Accordingly, the below rows have been deleted from the data before performing the ABC XYZ analysis of inventory items:

• a. Rows where unit price is 0 or less than 0 (due to some accounting entries)
• b. Miscellaneous entries (not related to any inventory item) such as ‘Discount’, ‘Postage’, ‘Bank charges’, ‘Samples’, ‘Amazon Fees’, ‘Manual’, ‘Carriage’, ‘Dotcom Postage’ & ‘Cruk commission’.

The dataset after cleaning is summarized below with key metrics:

The Third step is to summarize the data by ‘StockCode’ summing the Sales_Value (Unit_Price x Quantity) & Quantity Columns for each ‘StockCode’ and Counting the number of unique invoice numbers (i.e. number of orders received) for each ‘StockCode’. The data looks like below after these steps:

Fourth Step: We can then proceed to make the ABC classification by classifying the items accounting for 60% of sales (sorted in descending order of Sales_Value as above) as A, next 20% of sales as B and remaining 20% of Sales as C.

We find that 362 items or SKU (9% of the total 3914 SKUs) get classified as A (accounting for 60% of sales revenue in the given period), 463 SKUs (12% of the total 3914 SKUs) get classified as B (accounting for another 20% of sales revenue) and the remaining 3089 items (79% of the total 3914 SKUs) are classified as C (accounting for the remaining 20% of sales revenue).

Fifth Step: We can then proceed to make the XYZ classification. We have used the number of orders received (number of unique InvoiceNo) in the period as a measure of the frequency of demand and classified SKUs with 300 or more orders in the given period as X, SKUs with 150 or more orders as Y and the remaining SKUs as Z (this can be varied as per business scenario or preference of business user).

We find that 468 SKUs (12% of the total 3914 number of SKUs) get classified as X, 619 SKUs get classified as Y (16% of the total 3914 number of SKUs) and the remaining 2827 SKUs are classified as Z (72% of the total 3914 number of SKUs).

Sixth Step: We can then create a combined ABC-XYZ class label for each SKU. The final dataset looks as below:

The ABC, XYZ and ABC-XYZ classification can be presented visually for devising policies as prescribed earlier.

Closing Summary: This kind of a classification system can help optimise the inventory planning by affording the right type of prioritisation and policies amongst many SKUs & large datasets ensuring maximisation of sales and minimisation of obsolete inventory & extra holding costs and is not difficult or expensive to implement.

Source :

• i. https://tablebuilder.singstat.gov.sg/table/TS/M600981
• ii. https://tablebuilder.singstat.gov.sg/table/TS/M015651
• iii. https://www.ascm.org/ascm-insights/the-xyzs-of-inventory-management/
• iv. https://www.ascm.org/ascm-insights/the-xyzs-of-inventory-management/
• v. https://www.ascm.org/ascm-insights/the-xyzs-of-inventory-management/
• vi. https://archive.ics.uci.edu/dataset/352/online+retail

This article first appeared in the newsletter of the ICAI Singapore Chapter (page 36, H2 2023). Link: https://icai.org.sg/newsletter/ICAI%20SG%20Chapter%20-%20Namaskar%20-%20H2%202023.pdf