------------------------------
Change the language setting at the top of page to go to Vietnamese version.
------------------------------
MRP (Material Requirement Planning) converts production schedule into a timetable for making decisions on ordering materials and components to meet production needs in an adequate and timely manner. MRP answers three main questions:
What is needed?
How much is needed?
When it is needed?
Using Python, I calculate the quantity of materials that need to be purchased at each factory/site (referred to as 'Planned Order Release' - separated by ['Item', 'Site']), taking into account Shelf life, MOQ (Minimum Order Quantity) and several common inputs like Production Plan of Finished Goods, Stock On Hand, PO Pending (in-transit goods).
A. Overview
1. Abbreviation
Abbr. | Meaning | Abbr. | Meaning |
SOH | Stock On Hand | RPM | Raw Pack Material |
PO | Purchase Order | FG | Finished Goods |
MOQ | Minimum Order Quantity | BOM | Bill of Material |
PP | Production Plan | df | dataframe |
2. Scope
Materials that have an expiration date or not.
The FEFO rule (First Expired, First Out) shall be followed when taking raw materials out of the storage facility for manufacturing.
Place MOQ constraint on orders.
Finished Goods can be manufactured in more than one plant/site, and raw materials are stored at the respective sites and also delivered from Supplier to there.
For goods in transit (called 'PO Pending'), there is no information about expiry date until they are received at the storage facilities.
BOM level: input is BOM that has been converted to 1-level BOM, consisting of 1 level of Finished Goods and 1 level of Raw Pack Materials.
3. Input
There are 5 data tables:
BOM (df_bom): converted BOM (1-level BOM)
Item Master (df_item_master): information of raw pack materials (Item name, Site, Leadtime delivery, MOQ, Shelf life)
FG Production Plan (df_fg_production): production plan of finished goods (starting from the date of MRP calculation) - Pivot Table format
Stock RPM (df_stock_rpm): material inventory by Site, Expiry date
PO Pending (df_supply): amount of materials will be received from pending POs - Pivot Table format
4. Structure
5. Data
Sample data can be downloaded here.
The sample looks like:
B. Allocate Stock on hand according to Shelf life to Demand of RPM (called 'Raw Gross Requirement')
Based on the flow illustrated above, this first part explains the way obtain Net demand (1)
1. Pre-processing
Before implementing stock allocation based on shelf life, some steps shall be done:
Unpivot FG Production Plan (df_fg_production)
Join df_fg_production with df_bom on column [‘Item’] (df_fg_production) or also be called ‘parent’ (df_bom). Then, obtain 2 columns from df_bom: ‘component’ and ‘norm_total’ (the quantity of material to construct 1 finished goods)
Multiply the amount of finished goods produced at each period (‘FG Planned Receipt’) by the material usage rate (‘norm_total’)
Group by ‘component’, ‘site’, ‘period’ to obtain gross demand of raw pack material (also called ‘Raw Gross Requirement’)
The result of this step is the gross requirement of RPM by ['Item', 'Site', 'Period'] (unpivoted for an easy view)
2. Logic
2.1. Based on demand period, collect stock having the qualified shelf life (not expire before it is needed)
- After pre-processing steps, gross requirement of RPM is determined (df_raw_gross_raw)
- Join df_raw_gross_raw with df_stock_rpm on [‘Item’, ‘Site’]
- Select rows where Expiry date of stock is qualified to use in Demand periods: ['Demand period'] <= ['Expiry date']
- RPM without shelf life and RPM with Demand Period in the absence of inventory with qualified shelf life: will be filtered out and processed along with PO Pending in the following post.
2.2. Group By and loop through each rows within group to allocate stock in accordance with shelf life
- Group df_demand_stock_raw by [‘Item’, ‘Site’] and loop though:
Each group
Each rows within group
- At every loop, do subtraction (Raw gross requirement - Quantity = remain)
In case RPM requirement is met (remain <= 0): from this data row onward, replace the number of material needs in the same period with 0, and stock having same expiry date with |remain| (absolute value of remain)
Conversely, if RPM requirement is not fulfilled (remain > 0): from this data row onward, replace the number of material needs in the same period with |remain|, and stock having same expiry date with 0
- Use .loc[] to access the elements in df and assign new value before next loop is executed
- The change of RPM requirement and stock in each period after each loop is described below:
2.3. After loop, group and get results
- Group by [‘Item’, ‘Site’], use MIN for column ‘Raw gross requirement’ to acquire Net Demand of RPM having shelf life. This will be used for further steps to process with PO Pending and RPM without shelf life.
- Simultaneously, it is possible to answer the question, how much stock left for each RPM by Expiry date.
3. Python
- Import essential libraries, such as Pandas, Numpy, Datetime
- Read Excel file: pandas.read_excel
- Unpivot FG Production Plan: pandas.melt
- Join FG Production Plan (unpivoted) with BOM table: pandas.merge
- Do multiplication to acquire Raw Gross Requirement, then Group by, and Join with table Stock RPM: pandas.DataFrame.groupby
- Select rows where Stock having qualified Expiry date for Manufacturing date (not yet expired): pandas.DataFrame.loc
- Group By, then loop through each Group and each rows within Group (Below is my code)
- Based on above steps, we finally get the amount of Net demand (1) (which is mentioned in the flow), and Stock left by ‘Expiry date’: pandas.core.groupby.DataFrameGroupBy.agg
Below is result of Stock Left of RPM having shelf life, and Net Demand of RPM when there is inventory having qualified expiry date to serve:
In this part, I have made allocation of stock according to shelf life to each Demand period, and also obtained the remaining stock of each mentioned materials by Shelf life.
In the following post, I will use that result (unmet demand of RPM after allocation by shelf life) and the demand which was not included in above calculation, along with the inventory of RPM having no shelf life and pending POs, to calculate the final_net_demand ~ referred to as ‘Planned Receipt’ of all RPM.
-------------------------------------------------------------------------
P/s: In my opinion, there is a lot of logic to calculate Planned Order Release, and can be done on a variety of platforms and languages, including Power Query. And the way I use Python is only one of many.
~ So, happy to share and exchange with you ~
~ If you are interest, see my next post ~
 | Lê Ngọc Phương Trinh Enthusiast of Supply Chain, Data Analysis & Storytelling, Automation |
Comments