Hello Group Members,
In a Procurement Review you wish to identify A Class High Value Material being bought from a Single Vendor. This poses a significant risk for ongoing operations giving the dependency on a single source of supply.
So let us say we have a Procurement file with the fields -
Purchase Order Number, Line Item Number, Purchase Order Date, Material Code, Material Description, Material Type, Plant, Vendor Code, Vendor Name, Purchase Order Quantity, Unit of Measure, Currency, Net Price per Unit, Net Purchase Order Value, Deletion Status, Approval Status and Approved By
Now we are required to identify say Material bought in the review period where Total Net Purchase Order Value is more than Rs. 1,000,000 (threshold will change from Company to Company) and the Count of Vendors per Material bought is 1.
We set about arriving at the required result by performing the following steps in IDEA ~
(A) Summarization - choose Fields to Summarize as Material Code, Vendor Code and Numeric Fields to Total On as Net Purchase Order Value. In Fields to Display please select Material Description and Vendor Name
Now often we conclude that the count arrived at in the Summarization in (A) above is the Count of Vendors. Far from correct - this is the count of line items per Material and Vendor. But you need to get this to arrive at the next Count of Vendors.
(B) Summarization on the database obtained in (A) above. Choose Fields to Summarize as Material Code and Numeric Fields to Total On as Net Purchase Order Value. In Fields to Display please select Material Description and Vendor Name
This Summarization will give you the Count of Vendors per Material.
(C) Direct Extraction - now apply the criteria through the Equation Editor on the database in (B) above as No of Recs = 1 .AND. Net Purchase Order Value > 1,000,000
This is to filter out high value purchase Material where the Count of Vendors is 1.
If you click on any count 1 in the database you will get to see a fly out window in IDEA with the Material Code, Material Description, Vendor Code and Vendor Name
You can try the logic from above on another reporting requirement of Low Value C Class Material being bought through multiple Purchase Orders instead of a single Standing Annual Order where the Ordering Cost is more than the Net Purchase Order Value of the Material.
We will provide the steps for the Low Value C Class Material being bought through multiple Purchase Orders instead of a single Standing Annual Order where the Ordering Cost is more than the Net Purchase Order Value of the Material tomorrow.
Best Regards
Group Admin Team