The biggest headache in material planning is long lead-time materials.
Most manufacturers source product from all over the world. Shipping and transit times span multiple weeks.
The supply base is often a mix of sister factories, contract manufacturers and component suppliers. The influence that a buyer has over order lead-times, on-time delivery performance and quality ranges from some to little or nothing.
Here is a download that may help.
If don’t see the download link, you can log-in or register here >>.
This download is based on the ten-minute material planning tool that we released with an instruction video on how it was built. This version extends the logic for multiple sites, working with an example that includes a product sales company, overseas factory and third party supplier. Combine it with Dropbox to create the ultimate low-cost (i.e free) supply chain system.
Material Requirements Planning -Does It Help?
With hundreds of suppliers and thousands of parts, that you can’t do this planning manually. An material or inventory planning system is likely to be involved.
There are a couple of common limitations to the typical MRP system. Depending on your software system, you may also face these problems.
- Material requirements are only calculated in the MRP run. It can be difficult to see potential shortages without entering the sales order with crossed fingers and waiting for the next run.
- Material planning is not pegged. The system only gives gross and net requirements, but doesn’t link each to a demand record. This makes it difficult to see the impact of accepting priority orders and to mitigate shortages to get the best customer results.
- MRP System is not integrated with a sister factory. The production company in China (or wherever) has a different system, a local version or none at all. Communication with an overseas factory is manual, dependent on emails and phone calls and constrained by a time difference.
These limitations commonly occur with our clients. The result is a lack of visibility, flexibility and much manual expediting on both sides of the material planning process.
Using an Alternative Solution
I have built a simple example of an alternative. It is built in Excel using the Fast Excel Development Template and following the principles in our ten-minute material planning tool.
Am I suggesting that a spreadsheet replaces MRP? No.
Use an Excel-based tool to enhance the MRP system if you have one. This will give you instant calculations, clear visibility, direct pegging to demand and enormous flexibility.
Or, use an Excel-based tool to extend the MRP system across the supply chain and hook in a supplier or sister company that may not have the right capability in material planning.
The Multi-Site Scenario
This material planning tool is intended for a both product sales company and a production unit. The company buys finished goods from both a sister factory and third party supplier. The production company buys components and raw materials from local suppliers.
Both the production company and the third party product supplier have long transit lead-times for product to reach the market. Most raw materials are sourced by the factory locally and they might have shorter transit and order lead-times.
There are two stocking locations: at the factory for raw materials and at the sales company for finished product.
The product sales company only manages finished goods inventory and purchasing. However, they need visibility on the raw material shortages that will cause customer orders to be late. They also place product orders for different SKUs with a third party product supplier, but leave them to manage their own materials.
The factory company plans all their own work and purchase orders. Their sales orders are product purchase orders from the product sales company.
This is just one of many scenarios that can occur in a modern product supply chain. I have chosen a common example – the tool can be configured to fit with other supply relationships and order policy.
Understanding the Input Data
There is one material planning tool that can be used by both the product sales company and the factory production unit. There is a single set of input files, the tools share to ensure they are looking at the same data.
- Sales_Orders.txt. Customer orders for finished products.
- Material_PO.txt. Open orders for raw materials.
- Product_PO.txt. Open orders for products.
- Inventory.txt. Inventory of raw materials and finished product.
- Master Data.
- Item.txt. Item master with attributes and lead-times.
- BOM.txt. Bills of material for products made at the production company.
How To Get Started With The Material Planning Download
Here are some simple steps to get going with the material planning tool. You can run this example on a single machine, or across two locations using Dropbox to synchronize the input files on the internet.
The download link will appear here for members.
Sign in here
or sign up for free.
- Set up input file location. Either:
- Create C:DropboxMaterial_Planning Input_Files and unzip the files there.
- Unzip the folder anywhere and paste the file path in each tool, Menu sheet, “Input File Location”
- OPTIONAL. Open a free account for each user at https://dropbox.com and make one of the above folders sharable.
- Run each tool with the menu sheet, RUN button