How to Go From Excel to Manufacturing Software / MRP System in Stock Management and Production Scheduling.
You can also listen to this article:
Excel is a very efficient and flexible tool when managing material stocks and production plans. This tool allows you to describe the evolution of manufacturing processes. You will be able to quantify and pursue the continuous improvement of your business.
Yet, you have noticed that this tool loses its greatest potential every time the organization expands. There are more users and functions required in the organization. It is necessary to have real-time information. So, it is necessary to migrate from Excel to MRP System.
First, keep in mind that the introduction of software involves the integration of people. Thus, the following essential points must be considered:
- Communicate and integrate your work team about the migration process.
- Define all involved areas of your organization.
- Distribute tutorials for managers and the work team.
- Train the personnel involved in the usage of the new system.
- Introduce pilot tests to ensure the proper system operation.
- Ensure that the new software is fully used and the Excel becomes a complement tool.
Once the organizational aspects have been overcome, the process of software change must be taken into account. The most relevant aspects of this migration process are presented below:
Set Up the Items Database
Items comprise of 4 categories:
- The raw materials, parts, and components.
- The semi-finished products and sub-assemblies.
- The finished goods.
- It is also possible to manage indirect materials, such as spare parts, cleaning materials, Health & Safety items, etc.
Before the items can be loaded into the system, they must be properly organized.
Each must have a unique code, a proper description. Use an intelligent coding system that allows stock managers to recognize the type of material just by reading the material code. A combination of alphanumeric digits is generally the best alternative.
Organize the items by product groups for easier grouping, looking up, and analysis.
You must also consider the unit of measurements, unit conversions from vendors, e.g. number of units by the type of packaging and packing.
Also, define the safety stock levels to prevent shortages.
Define a name for each area in your warehouse. This will help with finding each item as quickly as possible.
It will be necessary to identify also the prices and costs of the materials. Detail the purchasing options for each item: the vendors, expected prices, lead times, minimum order quantities, vendor unit conversions, etc.
Later, when ready to go live with the system, ask your accountant to supply the current inventory position and unit cost (paid price) for each item on hand for correct data. And less important is to freeze your inventory levels during the go-live process. It will ensure that all quantities keep the correct figures.
Add the Recipes or Bill of Materials to the System
What is necessary to do once the items are in the MRP System?
It is necessary to create relationships that show the connection of materials to the products.
Put together the Bills of Material (BOM) for each of your products, semi-finished product, and sub-assembly.
Each unit of the finished product is made up of the sum of pieces that are added until the final product is ready.
Possibly, the BOM should also include scrap and waste quantities for materials, for better planning and accurate consumption.
Once this data is connected with the production lead times of sub-assemblies and supply lead times of materials, the MRP System can run. This means:
- The explosion of materials can be made for purchasing purposes.
- It is possible to calculate when and how many materials are required.
- Forward or reverse planning of the times required by the factory for planning purposes.
You will be able to manage your purchasing orders with just one click. Suppliers, prices, delivery times, everything will be there. Manage your inbound logistics using accurate statistics and know your purchasing needs.
Set Up the Machinery Data and Set the Process Layout
The machinery and equipment are fixed assets of the organization. These assets transform the raw material into finished products. There are two types of production systems:
- Assembly line production.
- Batch production.
First, organize and group your machinery and equipment into workstation groups. A workstation group is a group of machines that can all do the same tasks.
Then, for each product, define the production routing: enter and describe all the production stages following the production sequence through various workstation groups to get a unit of product starting from the raw material.
The available working hours for each workstation (machine) combined with the information from the routing – required operations and their durations by workstation group – make up the production capacity.
Set the Economic and Financial Conditions of Your Production and Inventory System
It will be necessary to provide direct costs to determine the cost of each product. This implies incorporating:
- The direct cost of materials.
- The direct cost of staff per hour of work.
- The manufacturing overhead rate (e.g. variable cost of machinery, electricity, consumables…).
The production and inventory system automatically links operational activities with the commercial and financial processes.
Define the mark-ups finished product will have, the billing system, and the payment terms.
This will allow you to send quotes and invoices in less time.
Besides, the commercial area may track inventories and the entire sales process. Manage the whole supply chain from quotation to delivery. You can also manage reverse logistics, invoicing and payments as part of an integrated process.
You will have the possibility to clearly visualize the performance of your business. You can better track your cash flows, balances and real-time gains/losses, as well as understand the profitability of the business.
Add the Staff
Include your staff, organized into departments, and assign them to the right operations, or workstations.
The ERP or MRP System presents a friendly user-interface both for planning and reporting purposes from a PC or a mobile device.
This way you can also get real-time reports of the production processes.
Start Planning Production
It’s time to manage your resources (workstations, materials, and workers) using a realistic production schedule.
A more detailed article about MRP can be found here – Material Requirements Planning.
Let the system figure out when operations can and should be performed.
Use dynamic planning tools by dragging and dropping work orders on the calendars or Gantt charts. You will see how easy the planning process becomes in comparison to the traditional Excel spreadsheets.
You will enhance your customer’s satisfaction, avoiding undesirable stock-outs, and shipment delays. Streamline your stock management and production scheduling with all areas of your organization. See your business as an integrated unit and say goodbye to the Excel spreadsheets!