Download Our Capacity Planning Tool – Version 2.0

by Kien Leong

Capacity Planning Tool

We have a new capacity planning tool for you.

It takes in sales order data, calculates total demand and production load and then compares that to available capacity.

You can add your own data, and use this to predict the production capacity that is required and available to meet customer demand.

This capacity planning tool is an updated version of the one we have previously published here.  There are a number of improvements, including:

  • Better handling of produced parts in the bill of materials
  • Integrated text files for input data
  • Calculation of production lead-time offsets through a working calendar
  • Faster to run and easier to use
  • Written using the latest Fast Excel Development Template

Tutorial on Using The Capacity Planning Tool

Let’s start with the Input Files and the menu sheet.

First, if you are using Excel 2007/10 and have not run macros before, then you need to adjust your Excel security settings to enable macros.

All Fast Excel tools are designed to connect to text files.  Text files are the best way to ensure that the planning tool is working with fresh data.

The tool comes ready with the input text files already to run.  The text files should go in the location “FilePath Input Local” on the menu sheet.  If you hit the button “Create Input Files in this Location” then the tool will create the necessary input files.

Each of the five green sheets represents an input data table.  You can make changes to the data in these sheets and then hit “save Change and Output”.  If you do not do this, the text file import will override your manual changes with the existing text file.

BOMCapacity Planning Tool - BOM

This is a multi-level BOM with all the parent-child relationships between sales product, produced part and raw materials.

This capacity planning tool supports a two- or three-level BOM.  If you have more than three-level BOM, then you need to use exploded demand instead of sales orders.


needs to list all of the operations and work-centers required to do the work to produce each of the finished and produced parts in the BOM.

If there is an item code missing from the Routing, the Capacity Planning routine will ignore it and you will not have the demand represented in the production load.

Each of the Work Centers listed in the Routing needs to be included in the WorkCenter table.

The Offset Days represents the production lead-time that you allocate to each operation.

Work CentersCapacity Planning Work Centers

Work Centers are groups of resources that can each perform the work specified in the routing.  They are resources of labour, machines or a combination of both.

The work center capacity is defined by a calendar.  Read more about Calendars and capacity planning here.

To allocate the work center to a calendar, each is given a shift pattern.  The shift pattern identifies a weekly pattern of shifts that the work center is available to do work or perform setups.

The example SP_11x8 shows eleven times eight-hour shifts in a week.

You can also define the calendar at the work center level.  However, it is more usual for a group of work centers to work to the same hours.  Using shift patterns means that you don’t need to manage separate calendars for each work center.  The shift patterns need to be included in the calendar sheet.


Calendars give capacity planning it’s capacity.  They show the shift periods over which the work center is available.  The calendar period needs to comfortably cover the due dates minus the offset days.

The example here shows the calendar for a single work center.  It has 2 shifts of 12 hours each day.

The Shift Pattern can be either a single work center or a shift pattern ID that represents multiple work centers as above.

It is important for this data to be properly formatted according to Excel date times.   Here is a brief tutorial on Excel date format and how they can be used for capacity planning.

Sales Orders

Last, by no means least we come to demand.  Sales orders are the usual form of demand.  However, this could be an Master production schedule, replenishment plan or exploded demand from an MRP system.

The important fields here are item code (to link with Bill of Materials and Routing), quantity and due date.  Be sure that the calendar sheet covers a period that includes these due-dates.


The Capacity Planning tool has 2 reports:

WC_Report.  This shows the hours of load and capacity for each work center.  The work center is changed by the filter in the pivot table.

WK_Report shows the hours of load and capacity for all the Work Centers in a given week.  The week is selected in the pivot table filter.

Download the Capacity Planning Tool New Version

Here you can download the capacity planning tool.  First, please complete this form so we can show you the download link:

Take it, try it and let us know what you think.

We have consultants available for those who would like to customise this tool for their own manufacturing.  Our planning and scheduling  tools are fast, transparent and flexible to fit with your business.

Contact us here and we look forward to helping you towards better planning and scheduling.

{ 51 comments… read them below or add one }

Fat Kag April 19, 2012 at 9:10 am
vimal April 19, 2012 at 8:11 pm

good tool for working

Gabriel April 20, 2012 at 3:38 am

Once again a nice tool from This tool can be effective if all raw materials are fully avilable i.e. because of just in time supplies or high inventory level. Nowadays for middle or small companies in the old Europe the above condition is rare: some raw materials are sourced in Far East with long lead times and just in time deliveries are effective only for commodities. is giving us all the sample tools required to solve each part of the planning problem and a good method for deploying a good excel system: the challenging job is to get all the component working together as a powerful planning system.

Patrick January 22, 2013 at 12:02 am

Gabriel is rigth, all components must be available in time in working together to realize an effective powerful planning system, absence of one component due to long lead time can fail any schedule in shoop flor.

Kien Leong January 22, 2013 at 10:58 am

You need resource capacity and materials to make product. The capacity planning tool is intended to meet one side of that equation. We also provide examples of material planning that can handle long lead-time materials.

A good integrated planning system will combine both capacity and materials. We show Capacity Planning and Material Planning as stand-alone modules to better demonstrate how each piece functions.

Buree April 21, 2012 at 12:22 am

Thank you. I will comment about this tool after I have evaluated.

nilesh April 21, 2012 at 8:05 am

good one

roy April 26, 2012 at 3:37 pm

thanks for this. the last 2 weeks ive run my shop on this. I have operators (ten) and machines (six) all set up as workcentres and I can now move orders in out and make the shipment. simlpe and effective kien. thank you

Roger Fisher June 2, 2012 at 2:28 am


I am wanting to help a supervisor organize his machine Shop. It sounds similar to yours. Were there any issues you found with the tool? (I have not worked with the tool, thus far)

Low kh June 14, 2012 at 6:21 am is always the site I look for the answer to the solution of production scheduling issue. This article and the tool is just what I needed to develop the system what my organization wants.
Thanks Kien for the great work you have done.

Kien Leong July 5, 2012 at 11:07 am

Thank you for the nice feedback, KH.

David Cox July 5, 2012 at 5:58 am

These look like great tools and I’m having fun experimenting.
Just a couple of questions, though…
1) What does the “Delivery” column on the SalesOrder tab represent and,
2) Could you explain the “OffsetDays” column of the Routing tab in a little more detail, please?

Kien Leong July 5, 2012 at 10:59 am

Hi David,

The Delivery column was used in a previous version, but it doesn’t appear in version 2.00 Capacity Planning that is downloadable here.

The purpose of this column is to allow a single order line to have multiple shipments- either different locations or dates. It doesn’t affect the logic of the planning tool.

David Cox July 5, 2012 at 6:34 am

Hi (again)
“This capacity planning tool supports a two- or three-level BOM. If you have more than three-level BOM, then you need to use exploded demand instead of sales orders”… how do I use “exploded demand” to populate this spreadsheet?

Kien Leong July 5, 2012 at 11:04 am

You would have a table called “sales orders” that actually represented total demand including dependent (via the BOM) and independent (actual sales orders). The due dates would then be MRP due dates rather than sales order dates. This would replace the current sales orders table. The field headings would need to be the same unless you modified the tool.

If you need a capacity planning tool that performs explosions with deeper BOMs then we could help you to develop it. Drop me an email at [email protected] if you would like to discuss further.

Frank September 13, 2012 at 6:35 pm

It is very useful for my work, thank you.

Juan September 28, 2012 at 4:23 am

Thanks.Let’s try it

Dario David September 28, 2012 at 10:48 am

Thanks, will upgrade from previous version

azam October 20, 2012 at 3:17 pm

great learning

chester mkumbuzi October 25, 2012 at 4:19 am

I love the fact that production scheduling explores the capability microsoft excel has in solving day to day changes in industry. Keep it up

Giel November 1, 2012 at 1:42 am


This was just what i’m looking for. I will test it these days and let you know the result.

yasser najjar January 7, 2013 at 3:39 pm
fauzan jaya January 8, 2013 at 6:21 am

thanks for your tools, how we count capacity production so simple and what variable we can use it…thanks

Herman Ahdiat January 19, 2017 at 9:01 pm
Adam January 11, 2013 at 3:39 pm

… will Advise

Patrick January 22, 2013 at 12:06 am

let me try this and explore…..

Innocent Ndlela February 11, 2013 at 2:43 am


bilal haider February 18, 2013 at 11:44 pm
alex March 26, 2013 at 6:33 am


Lila April 7, 2013 at 3:16 am

Capacity Planning Tool Please

Geetha April 17, 2013 at 8:45 pm

Require a capacity planning template

anton p May 27, 2013 at 7:09 pm

Sorry but I’m new to this.
Anyway, I just started to study this tool and the info above says that I can use this for 2-3 level BOM. When I downloaded the file, the input seems to be for a 2 level BOM (parent & child), how can I adjust this to fit a 3 level type? (Sub part under the child).
Thanks in advance.

Kien Leong May 28, 2013 at 2:17 am

Hello Anton,

You can represent a multilevel level BOM by putting the child as a parent, like this:

Parent Child Qty
PartA PartB 1
PartB PartC 1

So the parent column is for all produced items including finished product and sub-assemblies. Child column is for all sub-assemblies and components.

sasithorn tee June 1, 2013 at 6:14 am

very helpful

scott taylor April 21, 2014 at 2:49 pm

Can anyone tell me why the CNC part on the Wk_Report didplays an N/A where the date is?

don smith September 25, 2014 at 7:46 pm

My product basically calls for fab and weldment of steel platforms 12′ x 35′ x 12″ deep and always run 16 units (8 right hand & 8 Left hand) each order. I need to be able to load in this as a standard repeat order and add in new type orders needing a quick cycle time and delivery to give to possible client. Of course I easily display total labor capacity and the amount consumed showing my backlog. When I add new orders it will be of same nature where labor is the main resource 70% of manufacturing requirement and steel being 20-25% of it.
What system of your packages do you suggest for our situation? Can I send you data I encounter so you can create and display what would be best fit for us?

Best regards,

Don smith
off- 937-748-2937

Kurt Jensen January 17, 2015 at 6:01 pm

Looks like a nice tool. Could you tell me if this would work for my operation.
1. We assemble, integrate and test five complex electro-mechanical machines, each of which take between 8 to 30 hours to complete, depending on the model.
2. We also assemble four electronic sub-assemblies for each of the machines, each taking between 30 minutes and 7 hours to complete.

3. My staff is three full-time assemblers and a team lead (part-time direct labor) working one 8-hour shift, 5 days per week

4. We build to order combined with a limited sales forecast (To reduce our delivery lead time because material lead times vary between 3 days and 16 weeks)

5. Order volume varies greatly

6. My people capacity is fixed/finite so I need this tool to provide availability (Ship date) for sales orders

Will this work?

Thank you in advance for any clarification you can provide.

Best regards,

Kurt Jensen

Kien Leong January 19, 2015 at 8:44 pm

Hi Kurt,

Thank you for a thoughtful and detailed question. Short answer is that the Capacity Planning Tool will do all that you ask, apart from item 6.

A longer answer: The Capacity Planning Tool is designed to show you the required load and available capacity on any given day. You have a long production cycle that would spread across multiple days, and the tool can accommodate that. If you start on a monday then the load will apply to monday and maybe tuesday/wednesday if the product is 30 hours.

This tool is a infinite model so that the output shows you load vs capacity but does nothing to reschedule if load > capacity. So to adjust the ship date in this situation, you need a finite scheduling tool. There is one included in the PS Cycles system but this is a big integrated supply chain system and you would probably spend a week looking for it. Better we have some time on the phone and I can guide you in the right direction. Please contact me at [email protected] and we can arrange a time to talk.

frank Jones February 5, 2015 at 5:03 pm

Hi Kien,

This is a great tool capable of doing much more than I could have achieved alone; thank you.

One problem I have encountered is that when data is being entered directly into the worksheets it gets duplicated when you RunAll. For example when entering Sales Orders any new data entered outside of the existing SalesOrders named range will get duplicated. When the duplicates have been deleted some will re-appear again on the next run. They get fewer each run until they disappear. The text file is saving correctly after pressing Save Change and Output so it must be something to do with the paste down macro but I can’t work out what it is. Any thoughts!


saeed ghafori February 12, 2015 at 3:36 pm

thanks for this file if it is possible for you please learn production planning in access

aasar ahmad October 16, 2015 at 3:29 pm

very informative


Ray Vincent Ayuda February 16, 2016 at 6:34 am

Dear All

Anyone please help me how to use this tool because I am a beginner.


Carlos Martinez March 8, 2016 at 8:28 pm

In this tool ‘Set Up’ Time is suposed to be the same for a certain Item/Operation/WorkCenter. But in many cases, it depends of the PREVIOUS ITEM. Since it could require different set up possibilities: to change tooling, cleaning, … or nothing. How do you propose to take into account this variable set up time?
Many thanks in advanced.

Mehmet Oralp Tekuzman March 19, 2016 at 11:57 pm

Hi Kien,
Hi All,
I downloaded “PS_Capacity_Planning_v2-00.xls” and managed to populate BOM and Routing TABs with my info. Once I reached the Calendar worksheet, however, I ran into what seems to be a locale problem. Please let me try to describe:
The Excel installation on my PC (and all my colleagues’) uses comma as the decimal separator. The locale correct date representation of dates in “Calendar.txt” is:
ShiftPattern Begin End
CNC 0 0
CNC 40875,33333 40875,83333
I verified that this imports correctly when I click “Refresh Input Files”. However upon clicking “Save Change and Output” button, the “Calendar.txt” at once becomes
ShiftPattern Begin End
CNC 0 0
CNC 40875.33333 40875.83333
The subsequent click of “Refresh Input Files” leads to not converting to commas and thus I end up with this data in the Calendar worksheet.
ShiftPattern Begin End
CNC 0 0
CNC 4087533333 4087583333
It seems I got stuck here. Could you somehow help me surmount this ?
Kind regards,
Mehmet Oralp

thao duong April 9, 2016 at 12:40 am

I have fixed this template and It will work well with Power pivot.
You can design tables ,relationships and more.

Save all data to text file.

Emircan Senol December 11, 2016 at 2:03 pm

thank you

Hossam El Menshawy January 14, 2017 at 10:22 am

Good job

Erin Areyan March 26, 2017 at 6:29 pm


Jambal Tumendelger July 20, 2017 at 12:45 pm

Thank you, Guys u r genius.

Salvador Soriano August 1, 2017 at 4:08 pm

Hi guys. First of all, thank you for allowing us to access these tools for free.
I have a problem with the templates in general as the macros associated to the buttons do not seem to work properly. For example, if I use the create names, it makes nothing. If I use update, I shows me the list of files in the default directory…
In any case, thanks.

ahmad zamroni August 16, 2017 at 8:09 pm

Hi Mr Kien Leong,
How can i use this shortcut in MacOs Excel?
windows Excel: ctrl+shift+c to create column name.
MacOs Excel : ?
Please help

Previous post:

Next post: