Database and Statistical Controls for Raw Materials

When I first became Purchasing Manager of raw materials for Latin America, I noticed that there were no efficient controls for these raw materials that allowed me to carry out my work with good solid results.

Our department did not have in the budget enough money to pay a third party to provide this service, and there was an internal resistance from all those involved to start gathering the necessary data. To complicate this situation even further, I had been at the company only 4 months and I had yet to acquire in-depth knowledge of the systems utilized by the department.  

Despite these difficulties we developes a database that included all the raw materials purchased in Latin America in the previous 12 months.

 In only two months we were able to set up a database that provided all the information for the previous 12 months regarding raw materials purchased, which were sorted by: Region, Country, Manufacturing Plant, Buyer, Supplier, Type of Business, and by several other categories that could be of interest, since the database was developed in Excel, which allowed us to make different kinds of filters to provide the information we needed.  

With this database we were able control all the raw materials purchasedm which allowes us to improve our ability to negotiate with our suppliers, simply because we knew exactly how much and what we were purchasing from our suppliers fromm all over Latin America, which made it possible for us to obtain an annual savings of US$5,000,000.

            This database also allowed us to develop statistical controls, such as:

1.   Quantity of orders received divided by range of amount purchased. Example: number of orders purchased for amounts between US$1.00 and US$10,000, between US$10,001 and US$50,000, between US$50,001 and US$200,000, over US$200,001, and so forth.

2.   List of major suppliers, with information regarding what had been purchased from each one in the previous 12 months, for which manufacturing plants and countries, quantity purchased, price paid and total amount purchased for that period.  

3.   Statistical charts showing the evolution of prices for raw materials by: Supplier, Product, Country, Manufacturing Plant, Region and, particularly, by buyer, which allowed us to follow-up on their purchasing and see if they were obtaining actual savings or not for all the raw materials under their responsibility and not only regarding just a few of them, thus providing healthy competition between the buyers.  

 

to return