Commodity matrix: ABC/XYZ analysis, order point, illiquid stock

Not a sales report, but decisions on the assortment: what to order for the next delivery, what to keep in stock at any cost, what to discount, where the money is frozen.


Why is it needed

Reports show how much the store earned. They do not answer the questions that are asked before each purchase: which items should not be left out of stock, which ones are time to be withdrawn, and how much money is lying in the warehouse as dead weight.

The module builds a commodity matrix ABC/XYZ and calculates turnover. One screen shows the core of the assortment, candidates for withdrawal and the amount of frozen money.


What does count?

• ABC — product contribution to revenue. A is the top 80% of revenue, B is the next 15%, C is the rest. Borderscustomizable: retail often puts 70/90.
• XYZ — stability of demand according to the coefficient of variation. X - smooth demand, Y - fluctuating, Z - torn, requiring safety stock.
• Turnover - average sales per day, days of stock, risk of washout (stock less than delivery time) and overstocking.
• Unliquid stock - goods with a balance that have not been sold longer than the threshold, and the amount of money frozen in them.

Matrix 3x3

The rows are ABC, the columns are XYZ, the cell contains the number of products and revenue. Clicking on a cell opens its list.

• AX - core of the range:make revenue, demand is smooth, should not disappear from stock.
• AZ - they make revenue, but the demand is torn: we need a safety stock.
• CZ - candidates for markdown and withdrawal from procurement.
• N - were not sold at all during the period.

Three things the module does honestly

• A product sold in less than three intervals, group XYZ does not receive : the coefficient of variation at two points is noise, not an assessment of stability. Such a line is marked “not enough data”.
• Products without a single sale during the period do not fall intogroup C, but is placed in a separate group N. Otherwise, C turns into a garbage dump, where “sells poorly” is indistinguishable from “does not sell at all”.
• New is marked with the flag: it has low revenue not because it is not needed, but because it has not lived long.

Numbers that cannot be trusted are worse than no numbers—that’s why the module warns about them, rather than assigning letters to everyone.


Works on a large database

The matrix is not calculated on the fly: sales are collapsed into an aggregate by intervals, and the calculation is based on it. The unit is replenished incrementally - only the changed intervals are reassembled, and changes in order status are taken into account retroactively (a canceled order disappears from sales).

Onin a test database with 54,000 orders and 104,000 product lines in orders, a complete reassembly takes about three seconds, a normal pass takes a fraction of a second. The recalculation is started by the progress button or crown.


Order point: what to order right now

A separate tab “For purchase” shows the goods, the balance of which has dropped to the order point, and how many pieces are missing.

• Order point = demand for the delivery period + safety stock, calculated from the variation in demand, and not taken from the air.
• The level of service is configurable: 95% is the usual retail choice, 99% freezes one and a half times more money.
• The delivery time is set as a general one and is redefined by the manufacturer: the local warehouse delivers in three days, China -in two months.
• If there are few sales, the safety stock is not invented, and the line is honestly marked “not enough data.”

Dynamics: who has grown and who has declined

Each recalculation is saved as a snapshot, and any two can be compared.

• Transition matrix: how many products moved from group to group.
• List of goods with the transition type AX → BZ and revenue difference.
• The “revenue has changed the most” mode - for those who lost money without changing the letter: the leader can lose three quarters of the revenue and remain in group A simply because he was already the first.
• The module does not silently allow you to compare images counted using different settings -he warns about this.

Mass actions

The decision made based on the report is immediately applied: discount the group, remove it from the display case, return it.

• The discount is written to the standard OpenCart special offers with a separate module priority - repeated launches do not create duplicates, and discounts set manually are not affected.
• The action applies to the entire current selection, not just the visible page, and always asks for confirmation with the exact number of products.
• The action is not performed without a single filter: it is impossible to accidentally discount the entire catalog with one click.
• Each application is written in the log - what, for how many products and under whatadministrator.

Still in the module

• Filters: store, category with subcategories, manufacturer, mark, stock availability, search by name and model.
• Export the current selection to CSV - opens Excel with a double click.
• Accounting for product options (size, color) in separate items - for clothes and shoes, where the running size ends before the rest.
• Multistor: matrix for each showcase and summary for all.
• Profit and inventory valuation at cost, if the database has a cost column (it is created by commodity accounting); Without it, the module works based on revenue and directly writes about this in the report.
• ABC for profit instead of revenue where there is a cost in the database.

Requirements

• OpenCart 3.0.x / ocStore 3.0.x
• PHP 7.0+ (tested on 8.3), MySQL 5.7+ / MariaDB 10.3+

The kernel is edited with one insert - an item in the “Reports” menu. He doesn’t touch his own tables, other people’s.


Module version: 2.0

What's new in version 2.0

• Order point with safety stock, service level and delivery times by manufacturer, “For purchase” tab.
• Tab “Dynamics”: comparison of images, transition matrix, lists of those who have grown and those who have saggedgoods.
• Mass actions on a selection: discount, removal from display, return - with an action log.
• ABC for profit instead of revenue.

Write a review

Your Name:


Your Review: Note: HTML is not translated!

Rating: Bad           Good

Enter the code in the box below: