• How To Plan for Long Lead-Time Materials

    by Kien Leong

    Shipping supply chain material planning shipping

    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.

    Be sure to use the same data that your MRP system has by generating the input files from system.? This way you eliminate spreadmarts and work with a single source of truth.

    The Multi-Site Scenario

    Supply chain material planning 2 sites

    Material Planning Across Two Sites


    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.

    • Demand.
      • Sales_Orders.txt. Customer orders for finished products.
    • Supply.
      • 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


    » Lost your Password?

    or sign up for free.

    1. ?Set up input file location.? Either:
      1. Create C:DropboxMaterial_Planning Input_Files and unzip the files there.
      2. Unzip the folder anywhere and paste the file path in each tool, Menu sheet, “Input File Location”

    2. OPTIONAL.? Open a free account for each user at https://dropbox.com and make one of the above folders sharable.

    3. Run each tool with the menu sheet, RUN button


    { 12 comments… read them below or add one }

    Johan Sanchez July 28, 2011 at 2:31 pm

    i liked this!!! So fast & Easy! ThankS.

    le dieu September 20, 2011 at 5:48 am

    Thanks it is usefull to me

    Declan McTiernan October 31, 2011 at 8:01 am

    Very useful stuff. I will pass this on to our purchasing and materials planning guy. I think he will find it helpful

    Byron Inventor November 1, 2011 at 3:09 am

    The article is very true in regards with our MRP system . Because it is not pegged, our materials usually become overstock or understock. I have started incorporating this program with my MRP system and it helps me lot.

    jimmy dang November 1, 2011 at 6:19 am

    I am also wondering that how we can monitor/control the Goods in transit (did order but not arrive factory)?

    natarajan.c November 22, 2011 at 11:36 pm

    dear sir,
    Pls send the production planning,control plan,raw materialplanning and scheduling,qapp, qc,bill of material all format to use my feature.

    Kit Johnson January 5, 2012 at 10:42 pm

    Thanks for this, still working out the details, start up business has no MRP systems in place. Finding the info to input is a nightmare:)

    Alex Badertscher August 31, 2013 at 1:50 am

    Really effective tool Kien! Thank You

    Laxmanachary September 15, 2014 at 7:11 am

    Effective and useful tool

    green hand February 10, 2015 at 11:10 am


    green hand February 10, 2015 at 11:11 am


    thao duong July 11, 2016 at 10:31 pm

    Free Excel Template Warehouse Stock Location Systems

    { 5 trackbacks }

    Previous post:

    Next post:

  • 彩客彩票网官方端口