1
Webandmacros logo
 
2
   
| Make Homepage | Add to Favorites | Recommend |
 
3

:. HOME
 
:. EXCEL MACROS
 
 
:. ARTICLES

 

Example MRP.

MRP

 

Next, it is going to be exposed the practical case of the operating MRP, we will see again the making up of the scissors; remember the bill of materials (BOM) is the following:

Bill of materials

Bill of materials

To a better understanding of the MRP, imagine you need 2 screws to make the scissor , therefore the bill of materials will be the following

Bill of materials

The initial dates are the following:

Master Production Schedule (MPS)

The Master Production Schedule shows we have to make 400 scissors during the 3 rd week, in the 4 th week 600, in the 6 th week 800, and in the 7 th week 300 scissors.

We will name (GR) Gross Requirements to the demand of fabrication of the products, the final products (in this case the scissors) correspond to the quantity appeared in the MPS. To the intermediate products (in this case the screws) you have to multiply the necessary quantity to make the final product with its demand.

Gross Requirements of the MRP

Gross Requirements of the MRP

Inventory Master File

The file indicated us that we have since the first week 550 scissors in stock, also it indicates that the security stock do not have to be less than 50 scissors.

We will name Availability (A) to the initial stock of the final product we have to satisfy the before mentioned necessities.

We Will name Security Stock (SS) to the final quantity of the final product that cannot be use to satisfy the necessities.

We will denominate Net Requirements (NR) to the quantity that we really need to satisfy the Gross Requirements (GR), taking into account the Availability (A) and the Security Stock (SS), the calculations will be the following:

  1. If the availability is more than 0 , NR = GR-A+ SS
  2. If the availability is equal to 0 , NR = GR

Net Requirements of the MRP

Net Requirements of the MRP

Week 1: Gross Requirements are invalid, the availability is of 550 units, inside the security stock is 50, and therefore if we do not have Gross Requirements there are not also Net Requirements.

GR = 0

A= 550, SS = 50

NR = 0

Week 2: the same as the week 1, so we have an Availability of 550 units and the Security Stock is 50 units.

Week 3: The Gross Requirements are 400 units, but we have an Availability of 550 inherited of the last week, so we can satisfy the 400 units with the 550 available and we make sure that 50 we have 50 units for the Security Stock.

NR = GR- A + SS; NR = 400- 550 + 50; NR = - 100

It is important to realise that the result is negative, that means that we do not need to make more scissors, because there are 150 available. 550-400 = 150

Week 4: We need to make 600 scissors but we only have 150 units that were left the weeks before, so the Nets Requirements are:

NR = GR- A + SS; NR= 600-150+50; NR = 500

We have to make during the 4 th week 500 scissors; we ensure that the Security Stock has 50 units.

Week 5: The GR are invalid, we do not need to fabricate more because the NR are also invalid.

Week 6: The Gross Requirements are 800 units, the Availability is invalid so,

NR= GR, NR=800

We have to fabricate 800 units during the 6 th week, and our security stock is 50 units.

Week 7: The same as in the week 6, our Net Requirements are 300 units,

NR= GR, NR=300

Net Requirements of the MRP

Net Requirements of the MRP

Lead Time - Emission of planned orders.

The last step to apply is to convert the Net Requirements in Emission of Planned Orders (EPO) by the lead time.

We denominate Lead Time as the necessary time to convert the initial state to a final state; we will see it better with some examples:

The lead time can be the time processed by a machine and the time needs to acquire a product, or the addition of both times. In the present case we see that in the 4 th week we should have 500 scissors, so the lead time will be the time we need to make them, it can be one week, 2 weeks. it is very important to maintain constantly the Lead time, that is having an infinity capacity but by means of the MRPII we consider the capacity and the loading of work to adjust it in the indicated lead time.

The Emission of Planned Orders consists of indicating the quantity and the date in which we have to launch the new warning of fabrication, the EPO is calculated transferring the time into the quantities resulted from the calculation of the Net Requirements, this process is defined by the lead time.

We consider that the lead tie for the code SC is of two weeks; therefore the EPO will be calculated transferring the time into 2 weeks the net necessities.

 Emission of Planned Orders

Emission of Planned Orders

The final analysis would be that in the 2 nd week we need 500 units of row material in order to fabricate the 500 units in 2 weeks, so, in the 4 th week we can satisfy the Net Requirements. This 500 units are referred to the screws , left and right side, but according to the Bill of Materials to make 1 scissor we need, one left side, one right side and two screws therefore to make 500 scissors we will need, 500 to the right side, 500 to the left side and 1000 screws. In the second week to ensure that the row material is going to be available we have to explosion the MRP with the articles of the lowest level.

Explosion of MRP.

The explosion of the MRP is just applying the before mentioned steps to the articles that belong to the inferior levels of the Bill of Materials but, taking into account that now the Gross Requirements of the articles are the Emissions of Planned Orders of the highest level.

Bill of materials

According to what we have said, the calculation of the Gross Requirements articles R, S, L will be making automatically.

Explosion of the MRP according to the bill of material.

Explosion of the MRP according to the bill of material.

Knowing that our stock has 700 units of the article L , 500 units of the article R, and 300 units of the article S which its Security Stock has 125 units, we will calculate the Net Requirements of this articles applying the two rules mentioned before :

  1. If the availability is more than 0 , NR = GR-A+ SS
  2. If the availability is equal to 0 , NR = GR

 Net Requirements according to the bill of material.

Net Requirements according to the bill of material.

The last step is the explosion of the MRP that will be to apply the Lead time of each article to calculate their EPO considering the following Lead Time of the articles, the explosion will be the following:

Emissions of Planned Orders according to the bill of material.

Emissions of Planned Orders according to the bill of material.

With this first practical case, I have tried to introduce the concept and operating of the MRP, next it is explained the valorous outcome information that the MRP give to us as well as global summary.

Once we have this concepts clear, I will explain the operation of the MRP taking into account the identification techniques, the variation of the availability and also I will introduce the concept of MRPII, but this will be later on.

If you like, share it

 

Macro Excel MRP - Calculation and automatic explosion of MRP with Excel

In webandmacros you can download the macro Excel MRP, you can:

Go to Excel macro MRP
Know the application macro Excel MRP
Download Excel macro.
Download the application of macro excel MRP

 

Next Chapter : Outcome information of the MRP

Google & Webandmacros.com Search engine.
Google
 

 
5
 
 
   
www.webandmacros.netWorld webandmacros.
     
webandmacros.net 2007 - 2014
     
4
6