------------------------------
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).
------------------------------
After part 2, the quantity of RPM needed to purchase is determined (known as ‘Planned Receipt’).
This section will go through the Python code to calculate the Planned Order Release with the constraints of delivery lead-time and MOQ (varied by [‘Item’, ‘Site’]).
D. Planned Order Release
1. Available data
Planned Receipt: the quantity of RPM to be purchased after taking into account the stock and Pending PO (obtained in part 2)
Leadtime và MOQ: vary by [‘Item’, ‘Site’]
2. Logic & Python: Planned Order Release (exclude MOQ)
- Obtain Leadtime data: Join table 'Planned Receipt' (obtained in phần 2) with table df_item_master on [‘Item’, ‘Site’], select column 'Leadtime_days': pandas.merge
- Shift data in column 'Planned Receipt' backward according to Leadtime_days to obtain Planned Order Release: pandas.DataFrame.shift
Write udf to loop through each rows within group after grouping by ['Item', 'Site'], then use .apply() to apply udf on every group (pandas.DataFrame.apply)
Prior to applying udf, need to remove rows where ‘Period’ = ‘1900-01-01’ (This one is added in the calculation steps of Planned Receipt in order to include Stock On Hand of RPM without expiry date. Because this period does not have demand, value of Planned Receipt in that row is always zero. Therefore, that row should be removed before shifting column backward)
Utilize shift() function in udf. Because parameter ‘periods’ in shift() function requires ‘integer’ data type, we need to convert data type of Leadtime_days from ‘float’ to ‘integer’
Udf is used as below:
Below is explanation of syntax and logic mentioned:
3. Logic & Python: Urgent Order (order without aligned leadtime, supply shortage risk)
From the table Planned Receipt removed the rows where ‘Period’ = ‘1900-01-01’:
Select some first rows in each group after grouping by [‘Item’, ‘Site’], the number of rows equals to Leadtime_days
Then, obtain rows where Quantity larger than 0
Set ‘Period’ of ‘Urgent Order’ to the oldest date in order to push them to the top position of each group after sorting by values (eg, ‘1900-01-01’)
Below is result of Urgent Order (after selecting rows where Quantity > 0)
4. Logic & Python: Planned Order Release (consider MOQ)
4.1. Concatenate orders
- Concatenate 2 dataframes created in section 2 and 3 (Order following leadtime and Urgent Order without leadtime), for Urgent Order, 'Period' is set to '1900-01-01'.
- Sort_values to push 'Period' = ‘1900-01-01’ to the top position of each group. In that case, Urgent Order is the first order to place and place under the constraint of MOQ; any excess will be used to allocate to next order period. Additionally, because of this reason, after obtaining the result of Urgent Order in section 3, we need to Group By ['Item', 'Site'] before proceeding this section 4.
> Python: pandas.concat, pandas.DataFrame.sort_values
4.2. Calculate Planned Order Release under the constraint of MOQ
4.2.1. Obtain MOQ: Join table order in 4.1 with df_item_master on ['Item', 'Site'], obtain ‘MOQ’
> Python: pandas.merge
4.2.2. Write udf and apply after using Group By ['Item', 'Site']
Concept:
Loop through each rows within Group
Use .at[] or .loc[] to access and update the order quantity based on MOQ, called ‘order_final’
If this ‘order_final’ larger than the initial planned order, there will be a remaining amount to use for next period (adding this remaining amount to the variable ‘total_remain’)
Before deciding to order in the next period, the remaining amount from previous period order should be considered (add this amount to initial planned order, as a variable y = x + total_remain and decision is based on this y value, x is the initial planned order which did not consider MOQ)
Logic to execute based on the value of variable y:
If 0 < y <= MOQ, it means that after considering the current remaining amount from previous order periods, we still need to place order and place order by MOQ (order_final = MOQ, and total_remain = y - order_final)
If y > MOQ, it means that after considering the current remaining amount from previous order periods, we still need to place order and the order quantity is larger than MOQ. Therefore, order is placed by the value of variable y and there is no remaining amount to use for next period (order_final = y, and total_remain = 0)
If y < 0, it means that after considering the current remaining amount from previous order periods, there is no need to place an order, and there is remaining amount to use for next period (order_final = 0, and total_remain = y)
Code Python is used as below:
After obtaining the result, can do some customization steps, such as rename column, pivot, ...
Below is final order release based on the data sample:
I have now finished the calculation of Planned Order Release under 3 constraints: Shelf life, Delivery Leadtime, MOQ (Leadtime and MOQ vary by [‘Item’, ‘Site’]), with the assumption that the raw pack materials will be delivered from the supplier to each plant where the finished product is manufactured.
-------------------------------------------------------------------------
P/s: As mentioned before, there are a lot of logic and the ways to automate the calculation of Planned Order Release, and the way I use Python is just one of many that can be done even if you don't have IT background.
~ So, happy to share and exchange with you ~
~ If you are interest, see my next post ~
And a big thanks to you for reading.
 | Lê Ngọc Phương Trinh Enthusiast of Supply Chain, Data Analysis & Storytelling, Automation |
コメント