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:

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
Capacity planning work centers
Work Centers
Capacity Planning Tool 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:

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.

50 thoughts on “Capacity Planning Tool”

  1. 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.

  2. Faris Chesnutt

    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.

    1. @Faris

      You are not alone with this issue. We discussed it a little in another article on capacity planning. As you can see from the comments, there are a number of people with the same challenge.

      We are currently researching a process routing generator tool. If you are interested in giving us some input and helping us specify it, then let me know. email me at [email protected].

    1. 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.

  3. 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.

  4. 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.

  5. 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

  6. 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.

    1. Ruchin Trivedi

      Hello Vijay,
      I am a resource coordinator myself and using eResource Scheduler ( 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.

    1. 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.

      1. 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,

        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.

        1. 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.

          1. 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.

  7. John McLoughlin

    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.

  8. Teodora Boninska

    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!

  9. Teodora Boninska

    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?

  10. 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.

  11. Ruben Villanueva

    Good night, let me tell you I had visited this site earlier and didn’t catch me interested but I just revisted and I am finding it incredibly interesting. In my company we are at a changing point in our planning process and the input you have giving are opening my mind to a whole lot things I wasn’t sure if I was right on thinking about them but nowI fill more secure with my thoughts and you have giving me more confidence to aboard my superior on the changes that we need to put in place.

  12. antonio talayero

    Hello, I use the capacity tool, and I have a problem when exporting csv, I always dump the data with dot instead of with coma. I suppose it is because of the regional configuration of the operating system and excel. (Spanish) I have to change by hand in the file. There is some way to avoid this.
    Thank you so much

    1. Hello Antonio,

      This is a good question. It sometimes comes up for people like you who have a delimiter other than tab or comma (the most common) for the input files. Here is the way you fix it for the Capacity Planning tool and any other sheet that has a data input from text file:

      1. Go to the Input Table (green sheet) put the cursor in the table of data and right click. Select “Edit Text Import”
      2. Select the input file from the list
      3. Keep “Delimited” as the file type; Next
      4. Select your delimiter or choose “Other” and type it in the box; Next
      5. Choose the correct formats; Finish
      6. Repeat for each data input table
      7. Save the workbook

      This should now convert the tool to work with your delimiter. You can also make this change on the Query Template sheet on the development template and create a version that works with your chosen delimiter.

  13. Dear all,

    In BOM file, can we change to product recipe ?

    I have a lot of recipe but I confuse how to make BOM file.

    In each recipe, there are amount of materials about 4-10 types.

  14. Hello Mr. Kien,

    May i asked about P-S Cycles System, what i have to do to input data when i have a different forecast spread, it is not like on the template, there is no depot, ( dallas, chicago, etc, it’s finish part all in house inventory, and the product not typicaly like on the template, there is no category for kids, color, it is only part name, part number/ item code .

    I have sent an email my input data model to [email protected], but it failed delivered, i really hope that you guide me how to use it, i also use 3 phase schedule section38 from scheduling excel but i have problem with priority setting for example when on one work center theres process that use same machine, i am trying to send this section but it is to large to attach on an emai.

    Please guide me Sir.

    Thank You in advance.

    Herman Ahdiat.
    ( From Java )

  15. hi guys…
    i was assigned to a asst planner and my task is to use the download capacity planning tools in sorting out jobs. i have the spread sheet but unfortunately is the manual to explain how to use it?

Leave a Comment

Scroll to Top