Pareto Report for Products - Functional Specifications
The rationale for a Pareto report is usually that 20% of your products have 80% of the value and therefore these reports are usually structured as:
· Product Code
· Product Description
· Quantity On Hand
· Cost (either std or avg)
· Value (Qty*Cost)
· Class (A, B or C)
Then, based on the classification you assign different frequency of cycle counting (A products are counted weekly, B products are counted monthly and C products yearly or never).
This project has two different purposes.
First of them is to develop a report that classifies the products into three classes (A, B or C) depending on a concrete conditions selected by the user (Average Cost in this case).
The second one is to update one property in the product following the same criteria so it could be used as filtering condition in the Create Inventory Count List process.
This Pareto Report will be applied only to Products, being possible to develop another reports for Customers, Warehouses, etc.
Also, the intention of this report is to provide a simple ABC report for products that helps user in a subsequent inventory counting process. Is not its intention to develop a complex comparative ABC report that can be achieved using Business Intelligence tools.
The products that will be classified will be the existing one in the warehouse at the moment to execute the process and they will be classified depending on the average cost of each product.
User can be able to see the report showing all the products classified according to her/his criteria and then process them, updating the value of an org-specific product attribute.
User roles & profiles
Roles with access to Physical inventory window (Warehouse Management).
Business process definition
- One user can need a list of the products ordered by Quantity, Amount sold, etc. as a simple Business Intelligence tool.
- Sometimes it is needed to make Inventory Process, and it would be desirable that could be filtered by the ABC classification.
- The user can create a list of the products ordered by Quantity, Amount sold, etc.
- Once the user has obtained the desired order, he/she is able to update the value of the ABC classification column following this classification
- This value could be used finally as filtering condition in the Create Inventory Count List process
Mary is the person in charge of the warehouses of the organization. Now she has been ordered to carry out periodical counts of the products stored in the warehouses depending on the value of the product, understanding value as the amount multiplied by the quantity sold in the last year. The products with a higher value (A) must be count weekly, next value products (B) monthly and the rest (C) yearly.
She would like to choose one of the different warehouses of the organization, selecting the dates to be used as reference (date from and date to) and being able to order the results by units sold in the period (Quantity), Amount sold in the period, etc, in ascending or descending mode.
Currently Openbravo lets her filter by Storage Bin, Locator, Search Key, Product Category, Inventory Quantity and Regularization, but she is not able to filter by the value.
Functional requirements based on business processes
This process must have two working modes: one for just output data and another one updating data.
To achieve the desired functionalities are needed one window and a new process:
- Product ABC report: New report window with filtering conditions and results.
- Classify Products ABC: Process to classify the products in a warehouse following the Pareto rule.
Product ABC report
|1.1||Ability to filter by organization and warehouse||Must have|
|1.2||Ability to filter by date from and date to||Nice to have|
|1.3||Ability to export the resulting list to CSV, PDF or XLS||Nice to have|
|1.4||Process button to store the classification results to database||Should have|
Classify Products ABC process
|2.1||Able to classify products filtered by organization and warehouse|| Must have
|2.2||Able to classify products filtered by date from and date to||Nice to have|
|2.3||Able to order by Quantity or Amount sold in order ascendant or descendant||Nice to have|
|2.4||Able to determine if any product is of type A, B or C depending on the ordering conditions||Must have|
|2.5||Able to return an ordered list of products||Should have|
|2.6||Ability to update ABC_class column of the product following the filtering and ordering conditions|| Must have
User Interface Mockups
- Report output will break the results by Organization, ordering the results by their percentage.
- The Organization filter will be aplied to the warehouse's organizations.
- Create a new column in database to store the classification ABC of the product (organization specific information, M_PRODUCT_ORG.ABC). This column will be used later in the Create inventory process as another selection parameter (to be able to create Inventory Lists for A, B or C products)
- Create a new procedure to generate the list and update the values. This procedure is called M_UPDATE_PARETO_PRODUCT. This procedure uses an auxiliary function GET_PARETO_ABC that classifies the products according the product cost. The GET_PARETO_ABC divides the products following the Pareto distribution with rates: 80%, 15% and 5%. This values are specified in this function as variables (v_limitA, v_limitB)
New column ABC_class will be created in the Organization Specific table for Product in order to store the calculated classification.
Open Discussion Items
- It could be desirable to have the ability to order by Quantity or Amount sold in order ascendant or descendant