Excel MRP spreadheet - MRP (Material Requirement Plan).
The application of MRP (Material Requirement Planning) is an optimal solution to the typical problem of production: Control and coordinate the materials to available them when necessary without having an excessive inventory.
The great disadvantage of the MRP is the calculations that have to be done to obtain the solution. This can be corrected with the programming of macros in Excel, and in just few seconds the Macro Excel MRP will calculate and expose all its articles and levels.
It is suppose the reader knows the theory and the practice of the MRP, but if not you can go to the web and macros web and find the article related to the MRP, in which it is described and defined the technique of the MRP as well as many examples of its calculations and explosions.
Bill Of Materiales BOM- Excel Macro MRP.
Starting with the Bill of materials needs to the production, it has several levels.
When pressing the bottom "DATA", it is shown a window requesting the level of the article we want to introduce.
Depending on level that we introduce, the information necessary will be asked to us to compliment.
Article Code: We indicate the code of the article which is on the bill of material.
Availability: Quantity of stock of the article that is available.
Stock of security: Quantity of stock that can not be consumed, also known as reserve stock.
Quantity: Number of articles necessary to make the article of superior level.
Lead time: Time of provision, of processing, it is necessary to provide the article or to make it.
Relation: It is the code of the article of the highest level, relation father-son.
Weeks: Number of which the MRP is planned; it is defined by the master production schedule.
Every time the article is introduced, it is positioned in the indicated level, with all the demand dates, and finally it will adopt the following format:
Alert Bill of Materials of the MRP
The own program of Macro Excel MRP will show us several alerts to guide us into the introduction of data. It will avoid many errors, and will make it easy, fast and intuitive.
Calculation and explosion of the MRP- Excel Macro MRP.
Once all the data are introduced, the following steps consist of the addition of the Gross Necessities in the article with level 0 (father article) to calculate the MRP. We have pressed the bottom with the name "GROSS REQUIREMENTS" in the Excel sheet. Automatically the modified format will be created and it will include a calendar per weeks that will be filled with the Gross Requirements (row GR) weekly, according to the number of weeks of the master production schedule which should be previously introduced.
The particular programme Macro Excel- MRP will emphasize (in a yellow colour) those weeks that have not been introduced.
When this is finished, we click into "Explosion" and the macro Excel MP will be in charge automatically to calculate it in all levels.
Alert Calculation and Explosion of the MRP
Once this is realised, an alert will be shown to us, indicating the time execution of the explosion of the MRP, also it informs us about the creation of the reported actions.
Report of Actions. Excel Macro MRP
Finally the programme Macro. Excel MRP creates automatically a report of each article, indicating the warnings of the obtained results , this is known as " Report Actions "
Macro VBA Excel MRP.
If we had to do that explosion of three levels and eight articles we will waste 45 minutes, having the risk of being mistaken. But, on the contrary, if we use Macro Excel MRP we will spend just one minute to introduce the information and less than two seconds to calculate the MRP, and surely it will be correct.
How much time do you spend in exploding list of 5 or more levels with 10 or more articles ?
How much time does it suppose to you updating the MRP daily or weekly ?
With Excel Macro MRP the tme will not be a problem and the information will be trustworthy.
If you like, share it
You have enjoyed this Excel macro, it is available in downloads Excel Macros VBA
If you want to add some comments about this macro you can do it here