Capacity Planning Tool

Here’s what I’ve got for you: a functional capacity planning tool, built in Excel.

What it will do for you: Calculate the required load on production from sales demand and compare it with capacity that is available from a calendar of work centers.

What you can do next: read on to find out exactly what this is, and how it works. After that you can download it and swap out the sample data for your own.

To get this straight away, Members can login on the sidebar to the right.  If you haven’t joined yet , you can sign up for free here and then download immediately.

We developed this capacity planning tool to show you how capacity planning works in the Fast Excel Development Method.  This is how planners can get good visibility on capacity needed to meet demand.  Compare it with capacity available from the current calendar,  work centers and production resources.

Many production planners and master schedulers try to plan capacity using units of product.  This can work if the factory is low mix and has very steady demand.  If different products have different work content, then planning a mix of orders into shared resources can be impossible to do this way.

Good capacity planning compares apples with apples.  Effective planners need to balance demand and capacity using a single measure of time.

So, exactly what are we giving you here?  Let’s look at the results.  Here are the reports that it generates:

This is a view of required production load for a given work center over days and weeks of production. It compares it with capacity based on the actual shift calendar.

This is a view of required production load for a given week over different work centers.  It also compares the production load required for demand with the capacity available from the calendar.

The work centers you see here are just the ones in the sample data.  You can upload your own work centers, calendar and process routing that matches your own production.

Here is the input on the demand side.

The sales orders exploded with the BOM create a total demand for all parts and products. Of course, you need to be sure that your BOM contains parent records that match the sales order items.

The inputs on the supply side are as follows:

Naturally, you need to be sure that the manufactured products and parts in the sales orders and bills of material have process routing records. Also, that the due-dates in the sales order table are covered with calendar records.

 

Input files are text files that need to be placed in a specified folder location on your hard drive.  Read this post about using text files to link between Excel planning tools.  Alternatively, for a “quick-and-dirty” alternative you can paste your data on top of the input sheets and run the tool from a Cut-and-Paste option.  I recommend that you use the text file method, as you run the risk of refreshing an imported file  over the top of cut-and-paste data.  That could be frustrating.

We have tried to make this tool reasonably foolproof, but it will only perform as well as the data that is shoved into it.  If you get errors, then it is probably the wrong format of data.  Run the tool with the sample data set a few times first, and get familiar with the way that the inputs will change the result.

Let us know how you get on in the comments or by email.

Update: You can download the new version of our capacity planning tool here.  Capacity Planning Tool Version 2

Downloads are available to members. If you have already joined us, you can sign in here:

or sign up here for free. Instant access to downloads for this tool and other systems and templates from our download page.

 

Please be sure to let us know how you get on with this. We’d like to hear your feedback.