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.