------------------------------
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).
------------------------------
In Part 1, RPM having shelf life has been allocated to demand. In this part, stock of RPM without expiry date and Pending POs are taken into calculation. After this part, final 'Planned Receipt' of all RPM shall be acquired.
C. Calculate all unmet demand of RPM
1. Processed data
Gross demand of all RPM (not yet deducted stock and pending POs): df_raw_gross_full
Unmet demand of RPM having shelf life (after allocating stock according to expiry date): df_net_demand
2. Unprocessed data
RPM having shelf life: demand at period in the absence of stock with qualified expiry date
RPM without shelf life: demand at every period (as it has not been deducted stock yet)
In-transit goods (PO Pending)
3. Logic
3.1. Obtain all unmet demand
- RPM having shelf life: Consider the demand that has not been included in part 1 (for example, MAUTRANG is needed on 12 April 2022 but the farthest expiry date is 10 April 2022, therefore, the available stock cannot meet demand dated 12 April 2022; and the demand row of 12 April 2022 is not included in the calculation of the previous post).
- RPM without shelf life: Obtain the demand of these items (reason: in part 1, when using the filter criteria ['Demand period'] <= ['Expiry date'], it also filter out the rows where ['Expiry date'] is NULL, therefore, in part 1 stock of RPM without shelf life is not allocated yet). Below are 3 sample ways to obtain this demand:
Leverage pandas.merge with df_raw_gross_full to obtain this demand as well as the aforementioned one
From df_demand_stock_raw (joined table of df_raw_gross_full and df_stock_rpm), select rows where ‘Expiry date’ is NULL. Then, remove duplicates with the subset = [‘Item’, ‘Site’, ‘Period’]
From df_item_master, get the list of items having no shelf life (‘No shelf life’), then filter the items in this list from df_raw_gross_full
- Concatenate these 2 tables with df_net_demand of part 1, then table df_net_demand_full_item is done.
3.2. Obtain all Stock that has not been used for allocation and Pending POs
- This data consists of:
Stock of RPM having no shelf life: group by [‘Item’, ‘Site’]. Set Period = ‘1900-01-01’ or any oldest date to differentiate with Pending POs in the later calculation steps.
Pending POs of all RPM: expiry date is unknown until receipt at storage facilities. And unpivot this table.
- Concatenate 2 tables above, then table df_supply_full is done.
3.3. Do subtraction between df_supply_full and df_net_demand_full_item
- Do calculation: df_supply_full ‘subtract’ df_net_demand_full_item (remember to ‘set_index’ at these 2 dataframes before subtracting)
- Using this subtraction result, Final_Net_Demand (Planned Receipt) is calculated based on the logic explained below:
(This idea is referenced from Mr. Nguyễn Thế Anh - Data Engineer @Parcel Perform)
If the result of subtracting df demand from df supply is negative, there is remaining amount of inventory to allocate in the following period. Conversely, need more inventories.
Loop through each element in the column that contains the output of mentioned subtraction
In case there is a remaining amount of stock, keep this figure in a variable to allocate in the next loop
4. Python
- Get the demand of RPM having shelf life at the period in the absence of qualified expiry date: pandas.merge
The output of this step (using sample data):
- Get the demand of RPM having no shelf life: pandas.DataFrame.loc, pandas.loc and .isin(), pandas.DataFrame.drop_duplicates (if needed)
- Get the stock of RPM having no shelf life: pandas.DataFrame.loc, pandas.loc and .isin(), pandas.DataFrame.drop_duplicates (if needed)
- Concat tables: pandas.concat
- Unpivot Supply table: pandas.melt
- Do subtraction between df demand and df supply: pandas.DataFrame.subtract, pandas.DataFrame.set_index
- Write a udf to allocate and use .apply() to apply udf on the column that contains the subtraction results: pandas.DataFrame.apply
(This udf is referenced from Mr. Nguyễn Thế Anh - Data Engineer @Parcel Perform)
- The result of Planned Receipt acquired from sample data (Planned Receipt > 0):
In this part, I have allocated all stock and Pending POs to determine how much additional amount of RPM that I will ultimately need (referred to as ‘Planned Receipt’).
In next part, I will use the outcome of this phase to calculate Planned Order Release under the condition that leadtime is varied by subset [‘Item’, ‘Site’] and identify the orders that must be placed urgently (shortage risk for manufacturing, not align with leadtime delivery), while also ‘finalizing’ the order quantity in accordance with MOQ constraints.
-------------------------------------------------------------------------
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 |
 | Nguyễn Thế Anh Technology. Supply Chain. Automation. https://www.linkedin.com/in/anhng151/ Data Engineer at @Parcel Perform Python at @SCH |
Comments