Build a Material Planning Tool in 10 Minutes

by Kien Leong

Post image for Build a Material Planning Tool in 10 Minutes

I have demand, on-hand inventory and open purchase orders.  What orders can I cover with materials and which orders are going to be short?

This is a simple question in material planning.  We frequently get asked by clients to build spreadsheet tools that can provide an answer.  You can download a simple example of such a tool here.

Now, most of these companies run some kind of MRP system.  You might think that this Material Requirements function would be central to MRP.  The problem is that many MRP systems lose the direct connection between demand and material requirements known as pegging. This means that users find it difficult to make decisions about priorities and allocation when there is some kind of shortage.  It is often helpful to have the supply conditions for each component expressed for each sales order.  This means no logic is hidden by a software system and material requirements are transparent and open.

There is a neat trick that you can use to build such tool.  It is so simple and yet powerful that with practice you could build it in 10 minutes.  Here is a short video explaining the logic and showing the build from start-to-finish.


We use a Fast Excel Development Template to build this material planning tool.  You can download it for free here (or in Excel 2003 here).  If you like what you see, you can learn more about how to use the Development Template with these tutorials.

This is a very simple technique for material planning that you can use as the foundation for a powerful material planning system.  Usually, the input files come directly from the MRP system, so you can be sure that the data is accurate and up-to-date.  Material availability is one half of finite scheduling and this method helps you to quickly project inventory cover and identify shortages before they appear.

View the demo video and download the Development Template and example tool.  By following the steps, you can use this neat material planning trick to project inventory cover and shortages; all with direct pegging and full transparency to each sales order.

{ 18 comments… read them below or add one }

Michael Vachon October 29, 2010 at 3:53 pm

Excellent tutorial to an even better template. Kudos on the design.

Derek Winter November 7, 2010 at 8:09 am

Simple and effective. This can be done alongside our MRP to give some granularity and detail on where material should be going. Thanks for this.

poorvasahu November 13, 2010 at 7:39 am

prefect.

Robert Young January 19, 2011 at 6:01 pm

None of the macros are enabled?

Kien Leong January 20, 2011 at 2:37 am

Robert

It is likely that you need to change your Excel security settings. Here is a quick guide to enabling macros in Excel 2007/2010.

Mykoyy August 19, 2011 at 10:32 am

Genius!!!!!!!!

linch yang October 26, 2011 at 10:35 pm

it makes work much easier, thanks

Mike Newman January 28, 2012 at 7:05 am

easy to use and understand.

Leo Trujillo April 27, 2012 at 4:32 pm

I would like to learn more about supply chain planning

Marcus September 20, 2012 at 8:31 am

Fantastic work, excellent tutorial!

Many, many thanks!
-m

effendi wijaya October 12, 2012 at 5:40 pm

Hi,
I still can not implement to my material planning,

scenario like this:
1. BOM ( available)
2.Inventory on hand available
3.sales order received

want to calculate:
let say :
Product A sales order 500
use component
X=2 per
Y=1 per
Z= 3 per
next ….product B calculate how many can produce..because some component was use up by product A…..and so on..
i have product 500 item and material 3000 item.

is there any one can help…

Benjamin Cai January 19, 2014 at 8:48 am

perfect

sam yang July 5, 2014 at 6:05 pm

excellent

ahmad zamroni December 7, 2016 at 8:37 am

Hi,

i think ExplodeAll function not working, to explode cells from BOM that marked with colour.

Thank you,
Zamm

Kien Leong December 7, 2016 at 10:04 am

Hi Zamm,

The material planning tool referenced here does work – it has been tested and downloaded 7000+ times.

Perhaps you referring to the ExplodeAll function in the template? If so, please email me your example and I’ll have a look. The function does work, it is likely to be something in the setup. We do not yet have detailed tutorials on this- sorry. You can reach me on [email protected]

ahmad zamroni December 9, 2016 at 5:58 am

Hello Mr Kien,

Upss..
Sorry, i mean not for stack template above.
but for explode template. that file was great, but i don’t know, am i wrong when set it up?
i’ve sent you an email and enclosed my file.

Many thanks,

Zamm

Binh Pham January 20, 2017 at 2:11 pm

Perfect tutor and tool

Fernando Pinto February 13, 2017 at 8:36 pm

Hi guys,

How should I interpret the 09/09/2099 date on my Pivot Table?

I don’t know what you mean by the “Demand is short”. Does that mean I will be able to deliver this order?

Thanks!!

{ 5 trackbacks }

Previous post:

Next post: