Capacity Planning Chart in Excel

Download Our Capacity Planning Tool – Version 2.0

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.

60 thoughts on “Download Our Capacity Planning Tool – Version 2.0”

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

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

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

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

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

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

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

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

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

      1. Mohan Prasad Patta

        If you still have concern with this tool may be I can try to help. I am using this tool for more than 3 years now with some modifications to suit my production and it helps me a lot. I have also prepared a MRP tool linked with capacity tool and i can alone manage both production planning and procurement planning with no worries. this MRP tool is also from Production scheduling.com but modified to my needs
        these tools are pretty basic compared with PS Cycles tool provided as demo but that is way too advanced for me to implement. you can reach me at [email protected] i would be happy to share what I learnt.

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

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

  7. 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
    cell-513-256-8814
    off- 937-748-2937

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

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

  9. 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!

    Regards
    Frank

    1. Mohan Prasad Patta

      I too have a similar problem and had a terrible mistakes as they went unnoticed until one day where i have some unusual results. later I stopped working text files and had to keep the data just within excel file with being pulled from the text files. as i am the only user of this file maintaining data integrity wasn’t a problem.

  10. Carlos Martinez

    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.

  11. Mehmet Oralp Tekuzman

    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

  12. Salvador Soriano

    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.

  13. 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
    Thanks

  14. Hi,

    This tool is working very well for me on a single level, but when I try to add anything with a BOM, the child’s resource demands are not being reflected in the load, only the parent operations. Any idea what I might be doing wrong?
    If I put on a sales order using the child part only, this works fine, but I really need the child and parent loads to be shown just by putting in the parent sales order.

    Thanks,

    John

    1. Mohan Prasad Patta

      Hi my guess you need to fix the bom summary sheet where some times the pivot is not refreshed. go to BOM summary sheet, hit “create names” and “update”
      should probably work.
      Also see if you are using the same text for item presented as child in level 1 and as parent in level 2. only these points have crossed my mind, you can give it a try.

  15. sales order will have priority
    routing table will have pending operations and new sales order.
    is it possible to calculate and arrive at the date of possible delivery using existing capacity at 100% or 80% for the given standard schedule based on priority.

  16. let me rephrase
    sales order will have priority with old and new sales order.
    routing table will have pending operations.
    is it possible to calculate and arrive at the date of possible delivery using existing capacity at 100% or 80% for the given standard schedule based on priority and capacity.

Leave a Comment

Scroll to Top