Integration of production planning and inventory management processes – part 1
The first part of the article described the problem of integrating production planning processes in a factory with inventory management in a distribution center (DC) storing manufactured goods. It also presented an example of solving this problem by introducing specific inventory management and ordering policies, as well as production planning procedures.
After completing the first stage of the production planning and inventory management integration project, which involved transforming the two previously existing subsystems into one coherent logistics system, it was necessary to develop a tool enabling remote collaboration between the production planner and the DC planner, in accordance with the adopted principles and procedures. It was decided that a mathematical model of the problem would be built, based on which the production schedule would be prepared each time. This problem was modeled using the assignment method [1], [2]. Minimum production time was adopted as the optimization criterion. The required inventory level in the DC constituted the constraint conditions. However, optimization attempts carried out using the extensive MS Solver add-in for MS Excel proved that a multi-criteria approach would be necessary [3]. It also turned out that due to the large number of machines and a wide product portfolio, the proposed tool was ineffective. It was decided that production planning would be carried out using a tool presenting the forecasted inventory levels of individual products as a function of the adopted machine operating schedule. Although such an approach did not ensure the creation of an optimal schedule based on the shortest production time criterion, it allowed for a solution that represented a compromise between the goals of the factory and the DC. Due to the high degree of complexity of the problem and the unacceptably high costs of adapting the existing ERP system, the tool was to be created in an MS Excel spreadsheet.
Work on building the tool began with collecting all the data necessary for planning. For this purpose, an MS Access database was built, pulling the necessary information directly from the ERP system. Data was exported to an MS Excel file, which served as the source of information for planning. Static and dynamic data were collected in this spreadsheet. The first group included: average weekly sales, quantity of products per pallet, information on the category in the ABC classification, and ROP and SS levels. Dynamic data consisted of: current inventory levels in the DC and the production warehouse, registered sales orders from the DC, and demand generated by the ERP system.
Based on the data collected in this way and the production planning principles discussed in the first part of the article, a tool was created that allows for forecasting inventory levels as a function of the production schedule. This tool was an MS Excel spreadsheet consisting of the following tabs:
- Planning – a tab where production planning was performed;
- Forecasted stock – a tab where forecasted inventory levels in the DC were presented based on the adopted production plan;
- Changeovers – tabs (separate for each machine) where data regarding changeover times were stored.
It should be recalled that the first planning for the week in question took place on the Friday of the preceding week (Table 1). It should also be added that planning was carried out by the DC planner and the production planner from the factory, who had the ability to use the same file via a network drive. The “Planning” tab, used for creating the production schedule, is shown in Figure 2. In the upper part of the tab, a summary of the efficiency of each machine for each product was placed. This data is a source of information indicating which machine to assign the production of a given product to in order to achieve maximum efficiency. Below the table, charts were placed showing, using horizontal bars, the production times of subsequent products and changeover times for each machine. These charts are updated automatically after any changes are made to the schedule and provide planners with clear information about machine load over time.
The article comes from the magazine “Logistyka” 6/2008.