Download Our New 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.

Routing

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.

Calendar

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.

 Reports

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.

{ 34 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 Production-scheduling.com. 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. Production-scheduling.com 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

Roy,

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

Production-Scheduling.com 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

Hi.
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?
Thanks
Dave

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?
Thanks
Dave

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

Hey,

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

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

Awesome

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

good,thanks!

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?

Previous post:

Next post: