Related Articles
This article is about material requirement planning (MRP). I would like to introduce a supportive tool to MRP system. As many of you know, modern ERP systems have evolved from MRP systems. The core is the calculating material needs for the production in discrete environment (assembly lines).
Although MRP calculates the component needs based on the parameters like MOQ, MULTQ and lead time etc, sometimes it can’t give the exact out of stock time of the component. There are several reasons why MRP can’t give it exactly. First, customer orders or forecast may not be scattered through the horizon smoothly. Second reason may be about open manufacturing orders. There could be big batches of manufacturing orders. When you look at on MRP screens you consider that the components will be consumed in a short time under these circumstances. However, due to capacity constraint, these materials would be consumed in longer period of time. There are big customer orders or manufacturing orders but it doesn’t mean that they are going to be produced in one shot. Besides this, some MRP systems cumulates the weekly total demand on Mondays or other specific days and it brings forward the material requirement. If these large customer orders or manufacturing orders were opened at the late dates of the horizon, this time MRP pulls back the needs.
Therefore, we need to additional tool to understand exactly on which dates we’ll need to some specific components. We can use critical component list for this purpose. I am going to explain how to prepare it in detail.
First of all, we should calculate daily consumption amount of the components and raw materials. It’s possible to use S&OP figures for this. Normally, sales target and production targets are being kept in ERP system. We can extract monthly proposed production quantities of the finish goods (level zero in BOM) from the system. The table contains finish goods references and monthly quantities. Then, we’ll divide these quantities to number of working days of the month. We figure out average daily production quantities of each products. Let’s consider there are 800 lines of products on the list. Then, we’ll explode bill of materials in MRP system. The system simply multiplies daily production quantities by every stock codes in the bill of materials. We’ll transfer this list to Excel. Then, we’ll delete finish goods and semi-finish goods (WIP) stock codes from the list. Only components remained on the list. I assume that you have the parameters in your system for identifying stock types (finish good, WIP, component, service item etc). Since many components are being used in a lot of different products, you’ll see same stock codes in different lines on the list. We can use pivot table or subtotal function to get subtotal of these repetitive stock codes. Of course they have different quantities. Finally, we got the total needs of each components. Basically, these quantities indicate the daily average consumption.
Another way of finding total requirements based on the stock codes is to look at MRP requirement database file. MRP systems keep requirements detail in a specific database file. You can learn from your IT manager which file contains them. It’s possible to take total requirements for some interval. For instance, you can take all component requirements for 01/11/2015-30/11/2015. Then, you will divide it to number of working days and calculate the daily consumption. This is another solution.
After calculating daily consumptions, we need several columns in the critical component list. One of them is stock on hand. It’s easy to extract stock quantities from the system, it’s not a issue. Another very important column is good in transit. Many companies enter their invoices which sent by the vendors in their ERP system. You can take goods in transit data from the system. Last information that we should see on the critical component list is open purchasing orders against the components or raw materials. Again through PUR module in ERP system, it’s possible to get purchasing orders detail. If you are good on SQL or Access, you can make only one querry and extract stock on hand, good in transit and purchasing orders with only one execution. You can prepare list for all components or you can filter according to vendors, products family or what you want. As long as you have the parameters in your stock master file, it’s easy to filter.
Now, we have daily consumption figures on component basis, their quantities in transit and open purchasing order quantities and due dates. The only thing we have to do is two very simple calculations. First, we’ll divide stock on hand qty to daily consumption qty. Thus, we’ll figure out how many days of stock we have. Then, we’ll get total amount of good in transit and stock on hand in another column. We’ll divide this total to daily consumption and it gives us the result with the effect of good in transit.
Please look at the table down below. You can see stock codes of the components or raw materials on column A. Column B represents the item descriptions. Column C indicates stock on hand. You can see daily consumption figures on column D. Goods in transit was placed in column E. Purchasing order quantities are in column F, G and H based on due dates.
We calculate how many days of stock we have in column I. Equation is C2/D2. We calculate it with the effect of good in transit in column J. The formula is (C2+E2)/D2. Then we’ll sort the table by column J in ascending order.
What do column I and column J mean? It depends on many criteria such as lead time, vendor response time, express options, even customs regulations. If you have 3 days stock and there is no good in transit you have a big trouble. However, if you have goods that just arrived the port you need to clear it maximum in 3 days. You need to weight the situation considering many factors. The critical component list is a very useful tool to see clearly critical components that may put your production in danger. The list above is prepared based on one specific vendor. You can prepare it vendor based or planner based. As I indicated before, you can extract all data from ERP system in one shot. Then you only need to put daily consumption quantities by VLOOKUP function on the table. Then complete the list and take action according to the result.
