Capacity Planning Tool

by Kien Leong

Capacity Planning Tool Download

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:

Capacity planning report work center

Capacity planning report - work center over time

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.

Capacity planning report weeks

Capacity planning report- a week over work centers

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.

Capacity planning sales orders

Sales Orders

Capacity planning bills of material

Bills of Material

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:

Capacity planning calendar

Calendar

Capacity planning work centers

Work Centers

Capacity Planning Tool Routing

Routing

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:

Username
Password

» Lost your Password?

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.

{ 28 comments… read them below or add one }

John Ingram April 6, 2011 at 3:51 am

This is great, Kien.

I have got it working fine with the sample data. Now need to format our input files to test it with some live data. Thanks for sharing this tool.

Faris Chesnutt April 6, 2011 at 7:14 pm

We don’t currently have process routings for each of our products, although we know we need to get there. I’ll play with this and let you know what I think.

Kien Leong April 7, 2011 at 1:18 am
nitin chotai April 7, 2011 at 8:03 am

tks for great help
i am gonna try yr program tonight but i feel ours is sheet metal component company we have our special problems do u think you have any body of such industry work with this??

Kien Leong April 7, 2011 at 8:24 am

We have done work before in Sheet Metal. If your components are standard items then I see no reason why a routing could not be created with cut – punch – press – weld – assembly type processes. You may have more BOM levels than this tool will support as standard.
If your components are engineer-to-order then you would first need some kind of routing generator. I discuss this difference in this other post on capacity planning and process routings. Looking at the product range, I would guess that your components are standard. There may be something else that makes your business special. Take a look at the tool and let us know what doesn’t fit.

Tony Rice April 9, 2011 at 9:42 am

The Capacity Planning Tool is a great way to start to see if your sales orders are “doable”. After that, many of our clients want to go further:

- This tool handles a 2 level BOM, but we have tools that explode through multi-levels

- This tool assumes that you start with an empty factory. What if you want to recognize that you have already made some of the sub-assemblies and finished goods? We have MRP logic that handles that.

- What if you want to see if your raw materials will be here in time to meet the demand. We have a purchasing module that handles that.

- Some days the load exceeds the capacity, so what date can I promise my customer? We have finite scheduling that answers that question.

Please let us know your experiences with the Capacity Planning Tool, and if you have the feeling that you want more, please contact us.

J Malone April 9, 2011 at 3:01 pm

This is a good tool. It worked with the sample data and we could swap it out with our own numbers. I wish it was part of our system , now have to prepare texts every day.

Thanks guys fora new tool.

JOSE CHAN April 28, 2011 at 10:00 pm

were trying to find a system to deal with our own defects in the company i will try this one and post up the results of what I think about this. But It sounds really neat I just hope I can get it to work

Vijay Agrawal July 6, 2011 at 1:59 am

I am working with a Glove manufacturing Company and currently exploring for Production planning & scheduling tool. The ppt looked interesting to me. Do you know anybody of this industry using this tool.

Janajit October 4, 2012 at 12:45 am
Ruchin Trivedi July 19, 2014 at 7:08 am

Hello Vijay,
I am a resource coordinator myself and using eResource Scheduler ( http://www.linkedin.com/company/enbraun) software by Enbraun for resource scheduling and Planning.
I think you should also try your hands on this software, I am sure you will love its features and speed.
Moreover, it also provides a bit of project management.

adetunji shokunbi April 15, 2012 at 4:48 am

is there any course on the site that is meant to train excel and theory literate on production scheduling

Kien Leong May 2, 2012 at 9:43 am

Hi Adetunji,

We do have a detailed tutorial on production scheduling for Excel literate people. You can find it here Scheduling Excel Tutorial

Fateh Kamal May 2, 2012 at 8:00 am

Will this app work on a Mac?

Kien Leong May 2, 2012 at 9:41 am

Hi Fateh,

I have not tested this on Mac, but it should work if you are running Office for Mac. I’d be interested in your feedback if you try it.

Fateh Kamal May 2, 2012 at 9:49 am

Hi Kien,

Thank you for responding.

I am having a heck of a time getting it to work.

I am getting “can’t find file BOM.csv” when utilizing the update from input file function. Here is the path for the input file loaction,
[file://localhost/Users/fateh/Downloads/PS_Capacity_Planning/Input_Files/]

Also, I get “can’t find macro” error message when utilizing the toggle function.

If you like I can provide you with the input files that I am using and maybe you can tell me what is wrong with them.

Kien Leong May 2, 2012 at 10:13 am

First, check if you are using our latest version of the capacity planning tool. You can download it here: Capacity Planning Version 2.

You should choose a local drive location for the input, one starting with an allocated drive letter, not localhost.

If it fails to run with the above, then maybe it is a MAc issue. Like I said, it hasn’t been tested with Office for Mac – I know that VBA has been dropped from one of the versions.

Fateh Kamal May 2, 2012 at 8:16 pm

Hi Kien,

Thanks again for responding. I will try the new version on my Mac, if I can download it. Every time I try to download it, it is requiring my contact info, even though I ma logged in.

I am also going to try the 2003 version on a PC. Maybe I have better luck there.

rajan sharma May 5, 2012 at 11:26 pm

hi i am working with garment manufacturing unit will this be helpful in this industry

vinay joshi October 20, 2012 at 2:07 am

I am working with manufacturing company in which scheduling is complex can you please help

Kien Leong October 20, 2012 at 3:24 am

Hello Vinay Joshi,

I would be happy to help. I have sent an email to you- I look forward to speaking further.

Ganttic April 5, 2013 at 7:29 am

This capacity planning tool has been very helpful. Thank you very much for sharing this one. If not because of this, I would really have a hard time. You’ve helped me big time.

John McLoughlin April 17, 2014 at 7:22 pm

I’ve got my data loaded but something is going wrong. The Capacity Calculation tab it is not updating with my calendar data. It is still populating with the sample data even though I do not reference it anywhere. On the Load_Capacity Calculation tab I am getting #N/A under Week for a SheetName of Load and I am getting and I am only getting capacity for the sample WorkCenters.

Teodora Boninska May 15, 2014 at 11:59 am

Hello sir,
Thanks for sharing.
We are producing tools from steel ( mostly instruments and equipment for machines). As we have mostly two levels BOM (I am not counting complicate items like vises, mills, presses) I will try to use this tool as I think is useful.
I have couple of questions concerning this tool and I would be happy if you can give me some feedback or directions.
We have Sales order with 10 positions for example. Every position is transformed in Confirmed prod. Order and then in Released prod. Order. ( So we have 10 prod. Orders). I have BOM-s and Routings, Calendars, Shifts and I can easy adapt them in this sheet, but I am still wondering how I should work with WIP. I think this work with Sales orders ( or prod. Orders) which are still not released but should be released. How I will calculate the load of Work center when maybe some orders are already finished on particular operation, but they will load the capacity of this particular work center?
I am trying to get deeper into the formulas and I am searching for the data for PIVO CHART. Where did you get the information for PIVO CHART from ( Load hours, capacity hours, week and so on)? How and where we can see that set up time is included in the load of work center ( or offset days)? Is some sheet missing here? Or where for example are BOM_Summary and Routing_ summary, which are used in Match formulas Sales order sheet?
I will be happy if you give me some feedback? I am also have problem with dates in our ERP system – it calculates correct ending date of prod. Order but can not calculate correct starting date – it constantly go back. Yes, the method of calculation is backwards but still does not work? Can you help me with this?
Thanks in advance!

Teodora Boninska May 15, 2014 at 12:01 pm

Another one question concerning charts – where we can see the comparison between requred load and possible capacity – it does not appear, only the requred load is there?

Dessire Rocha June 9, 2014 at 11:42 pm

Hello, I’m working in the shoe industry, is this tool good for me?

Thanks

Kien Leong June 14, 2014 at 11:35 am

Hello Dessire, There is no reason why not. If you have the right data inputs as mentioned above it should work fine for shoe manufacturing.

phuong nguyen September 28, 2014 at 4:04 pm

Dear Mr.Kien
I am very interested your planning production capacity tool. I would like to customize your tool such as i do not need BOM sheet. I assume BOM is ready. I would like to make something simple.
Thanks for your attention to my matter.

{ 10 trackbacks }

Previous post:

Next post: