ABC Analysis is a technique used by inventory managers to determine how frequently to perform a physical count of stock based on consumption and value. You perform physical counts to ensure that you do not unexpectedly run out of stock, but apart from the yearly audit you need a method to determine when to perform other physical counts.
Each Item has an ABC Type with values of A, B, C or N (None). The cycle count frequency is modified by running a report which updates the database value (see note below). The report uses the last cost of the item and how much of the item has been issued in the year. We will start with some of the setup that you need to perform. An ABC Type of A is deemed to be more important than an ABC Type of B or C.
Inventory is handled for both Items and Tools using the Inventory and Stocked Tools applications respectively. The reports are based on a storeroom and if items and tools are kept in the same storeroom they will appear together in the report.
Note. In Maximo 184.108.40.206 there is a version of the ABC reports which updates the database with the results of the analysis. In March 2022 MAS 8.7 was released (MAS Manage 8.3) and this included BIRT 4.8 reports and the database update feature for the ABC Analysis report was dropped. This effected 6 inventory reports which updated the database. The following link is a question I raised on the IBM Community asking what the plans were for the replacement of these features. https://community.ibm.com/community/user/asset-facilities/discussion/is-there-a-replacement-for-the-inventory-reports-which-updated-the-database-in-mas#bmc22f7e15-bc26-4f2e-946f-52f0b9baf81f
The workaround is to run the ABC Analysis report, save the data to a spreadsheet, clean-up the columns and then import the data through an Application Import. An Object Structure would need to be configured for this.
Organizations – Inventory Defaults
In the A structural element of a Maximo database which is used for data sharing and is often aligned to a legal entity of an organisation. More application and the Inventory Options – Inventory Defaults action there are a set of fields called ABC Breakpoint Options for setting the percentage of each item that will be given an ABC Type of A, B or C and the Cycle Count Frequency in days used for each ABC Type.
In the screenshot, for ABC Type:
- A – 80% of the value of items will be counted every 30 days
- B – 15% of the value of items will be counted every 60 days
- C – 5% of the value of items will be counted every 90 days
Notice that you enter 0.80 rather than 80, otherwise for the Breakpoint fields you will receive the error message “You entered more digits before the decimal point than is permitted in the field. (BMXAA4081E)”.
In the Inventory application next to the balance information there are two fields in the ABC Analysis section, the ABC Type and the Count Frequency. These are both enterable fields, the Count Frequency is a mandatory field and will be defaulted to zero for a new record. The ABC Type is supported by an ALN Domain (ABCTYPE) with values A, B, C, and N (None), but the field also accepts nulls, and this is the default for a new inventory record.
These values are set by running one of the reports called Inventory ABC Analysis or Inventory ABC Transaction Date Analysis.
The ABC Type of N (None) should be entered for items which you do not want to be affected by the running of either of these two reports. A new item with an ABC Type of null will be considered by these reports.
Note. You’ll find in a while that the Inventory ABC Analysis report also considers tools, but the ABC Analysis fields do not exist on the Stocked Tools application, not without some configuration.
Report – Inventory ABC Analysis
The Inventory ABC Analysis Report derives the ABC Type based on turnover of stock and the value of that stock. The items are ordered by the YTD Issued Cost, and this is calculated from the Last Cost column and the YTD Issued (ISSUEYTD) column. The Last Cost column sometimes comes from the Last Receipt Cost on the Inventory Costs table window (INVCOST.LASTCOST), but other times, for example for tools where there is no INVCOST record, then the Last Price field found on the Reorder Details tab and Vendors table window has been used, it is the record that relates to the record with the latest Last Order Date.
The Inventory ABC Analysis Report takes a Storeroom and A structural element of a Maximo database that is used for data separation. More as parameters both of which are mandatory, therefore you can only run this report for one storeroom at a time. The report does not consider inventory items that have a status of OBSOLETE.
The graph only displays on the first page and shows a count of the number of items with each ABC Type, both before updating the database and what it would be afterwards. The count of before and after are the same, there are 109 items. The before figures are correct but the after figures for B and C are incorrect, they should be 7 and 100 for ABC Type B and C respectively. I’ve raised this with IBM Support.
The total YTD Issued Cost is 2105.73 and the total for the first two rows with an ABC After of ‘A’ is 1665.00 which is 80% of the total, the value entered in the Organizations application. The total for A+B is 1998.18 which is 95% of the total YTD Issue Cost, therefore the new settings of ABC Type are as expected according to the Organization Breakpoints defined.
The list part of the report also shows the change in the Cycle Count Frequency (CCF).
Note. For Condition Enabled Items there may be multiple rows in the Inventory Costs table and the report sums the Last Receipt Cost and uses this as the Last Cost, over inflating the value, it would be better if the report only used the Last Receipt Cost for the record with a Condition Rate of 100%. I’ve also raised this with IBM Support.
Note. This report does calculate an ABC Type for items with an Issue Cost Type of LIFO or FIFO. But it cannot derive a Last Receipt Cost because there is no Inventory Cost record, therefore zero is being used instead and this will mean that the resulting ABC Type is likely to be a C. If you are using LIFO/FIFO, then you should consider using the Inventory ABC Transaction Date Analysis report.
Note. When you use the Inventory Adjustments action – Zero Year To Date Quantities – The Year To Date value is moved to the Last Year field and the Year to Date value is zeroed. Therefore, if you use the action at the beginning of the calendar year, you should reset any ABC values prior to this. If you ran the Inventory ABC Analysis at the beginning of the year you might find a lot of zeroes for items where there had been no issues and consequently, they would be changed to an ABC Type of C.
The last page of the report has a link which will run another report that updates the database with the two fields ABC Type and Cycle Count. The report will be found in Report Administration with a description of Inventory ABC Analysis Update. If this is not found then, you are likely to be on a version of Maximo running BIRT 4.8.
Report – Inventory ABC Transaction Date Analysis
The Inventory ABC Transaction Date Analysis Report derives the ABC Type based on turnover of stock and the value of that stock, but it does it by examining the MATUSETRANS records for issues and returns.
The other difference to the original Inventory ABC Analysis report is that it does consider items which have an Issue Cost Type of LIFO/FIFO.
The Inventory ABC Transaction Date Analysis Report takes four parameters, Storeroom and Site both of which are mandatory, and a Start Date and End Date. Again, you can only run this report for one storeroom at a time and similarly the report excludes inventory items that have a status of OBSOLETE. The four parameters entered can be seen at the top of the report. The Start Date and End Date needs to be wide enough to stop a lack of records skewing the report results. I am running this on a new MAXDEMO database, and my Total Transaction Costs are zero for all items.
The graph doesn’t reflect the data, there are existing records with ABC Type of A, B and C and I would expect to see four blue bars. The graph is looking at the resulting ABC Types expecting there to be at least some A’s and B’s but as there are no transactions everything is set to C. I have also reported this as an issue to IBM Support, but it is unlikely to happen in reality as there will have been some issue transactions in the period chosen, it might only happen on a MAXDEMO database. What it does illustrate is that if the Total Transaction Cost is zero then the ABC Type is going to be C, which is why you must select a wide range for the report, a year perhaps.
When running the report with no Start Date or End Date, the graph now shows all four blue bars, but the ABC After green bars do not tie up with the data shown in the list. The ABC After shows 5 A’s and 4 B’s and the green bars show 6 and 7 respectively. Also, I think the green bar for ABC Type of C has ignored records where the Total Transaction Cost is zero because there have been no transactions. This results in the graph totals for Before (Blue) and After (Green) not being the same, 85 items have been considered, and according to the graph only 40 will receive an ABC Type of A, B or C. IBM Support have been contacted on this as well.
The Total Transaction Cost column uses the Total Transaction Qty multiplied by the Average Transaction Cost. At the bottom of the report is the Total Transaction Cost which if you use the Export Report button in the report toolbar and use the Export Format of XLS Microsoft Spreadsheet you’ll be able to check for yourself that the total is incorrect. This has also been reported to IBM Support, but I think the two issues are connected. The report total showed 9114.12 and in Microsoft Excel the total was 9900.12. The ABC After column uses this report total correctly to set the A, B and C values. The Total Transaction Cost of the A’s will not exceed the Breakpoint, set at 80%, and the Total Transaction Cost of the A’s and B’s will not exceed the sum of the Breakpoint for A and B, i.e., 95% for my settings.
If an item has only been returned to the storeroom and there are no issues during the period chosen, then it will appear at the end of the report because it will have a negative Total Transaction Cost. Negative values will not be included in the total at the bottom of the report, and it will not be included in the graph totals for the green (After) bars. The ABC After value will be set the same as the ABC Before, the same applies to the Before/After CCF values, and these records are ignored in the update of the database. The Total Transaction Quantity is the number of Issues minus the number of Returns for the item.
The Inventory ABC Transaction Date Analysis Report uses the unit cost on the issue or return and averages it. The Inventory ABC Analysis report uses the Last Receipt Cost.
Had you realised that the total number of records considered for the two reports was different? There were 109 items in the Inventory ABC Analysis report and only 85 items in the Inventory ABC Transaction Date Analysis report, can you guess the difference? There are no tool items in the Inventory ABC Transaction Date Analysis report. Tools are always capitalised, and in this case, there are no Inventory Costs, and in the Inventory ABC Transaction Date Analysis report these will be suppressed as there is no issue cost to determine a valuation.
The last page of the report has a link which will run another report that updates the database with the two fields ABC Type and Cycle Count. The report will be found in Report Administration with a description of Inventory ABC Transaction Date Analysis Update. If this is not found, then you are likely to be on a version of Maximo running BIRT 4.8.
Report – Inventory Cycle Count
When you perform ABC Analysis and update the database Maximo also updates the Cycle Count Frequency field (CCF). The Inventory Cycle Count report uses this Cycle Count field by listing all the items which have been previously counted and where today is after the last physical count date plus the CCF value in days. This uses the Inventory Balances Physical Count Date to determine whether a count is overdue which is why a count must have been performed once for it to appear in this report.
The report will not include items with a zero CCF value and the report is bin based, and so the same item may appear multiple times. The end column of the report is used to enter the New Count Quantity.