Kick-Start Your Capacity Planning

Last week, we released our free Capacity Planning Tool.

We had some great feedback – companies are using it right now, getting better visibility in their manufacturing operations.

If you have yet to try it, you can download Capacity Planning in Excel right here.

We developed this tool because many manufacturers make the mistake of trying to plan capacity using units of product.  After all, customers place orders for a quantity of units.  Sales people provide a forecast expressed in production or sales volume.  So it is natural that planners want to express capacity in units.

In a factory that produces only one product, this is fine.  For a factory with a mix of products, this will work if and only if production lines are dedicated to making products that have identical work content.

As the mix grows, products with different work content will need new production lines.  This leads to lines or cells that sprawl across the factory floor.  Sooner or later, this factory will have to change a line over from one product to another.  To keep control of the capacity, they might force each line to run the same product for a whole shift or day.  The result is a prolonged response and inflexible production.

The simple truth is, if  I have products with different work content, I can’t rate my line capacity in pieces-per-hour.  Capacity in pieces-per-hour will change, according to the mix.  But this is wrong:  Capacity is a supply constraint; mix is a characteristic of demand.  If the definition of available capacity is defined by demand, this approach is not going to work.

Mixed-model capacity planning is properly done in hours – Hours of demand compared with hours of supply.

The result looks something like this report.  At a glance, what does it tell us?  Easy.  We are going to need to add some shift hours onto Saturday 12th, or pull some of that work into Friday 11th.

So, a capacity planning tool helps you convert demand from units to hours and compare it with the capacity available from the calendar.  The mix doesn’t matter and this calculation is done for total demand of finished products and dependent parts.

How the Capacity Planning Tool Works

The Sales Orders tab lists customer orders.  This is exploded by the BOM to get total demand: dependent demand for parts and independent demand for sales items.  Total demand is exploded by the Routing to get work orders, production operations, times and their work centers.   Meanwhile, the Shift Calendars for each Work Center define how many hours of work are available across all the resources in the factory.  The resulting reports look at a comparison with capacity required versus capacity available.

Now, the tool is supplied with a sample set of data.  It is imported from text files, or you can change the input data source and run the tool from data cut-and-pasted into the input sheets.

To get the capacity planning tool working with your data, you will need to create your own text files. Make sure that it fits the same tables and that it is fully consistent.  Here are some things to look out for:

BOM.  This should be a Product BOM, that lists top level parent items to every lower level part.  If some of your part numbers are numerical-only, make sure that the format is the same for all columns that have item codes including BOM Parent/Child, Routing and Sales Orders.

Sales Orders. Item codes here need to have a corresponding process routing.  All Due Dates and Sales Quantities should be non-blank.  Due Dates are offset in the routing and need to be covered with shift records in the calendar.

Routing. All work centers need to also be listed in the Work Centers sheet.  Products and parts with no routing record will be treated as raw materials, i.e. no capacity required.

Calendar.  All times should be digital days, in Excel 1900 date format.  Dates need to cover the period required by due date in the sales orders sheet minus accumulative offsets in the routing sheet.

Work Centers. This needs to be a list of all the work centers in the Routing.  Each work center is given a shift pattern that needs to have a corresponding calendar.

Get a FREE Consulting Call to Implement Capacity Planning

We are offering five people the chance for a free 30-minute consulting phone call with myself or Tony Rice.  This call is intended to give you support in preparing your input data, or bringing it into the Excel-based Capacity Planning Tool.

We usually provide this service to our clients at a rate of $250+ per hour.  For the people we select, this will be given free of charge.

Why are we offering this?  As mentioned, there are hundreds of members who are already using this Capacity Planning Tool and others in their business and getting great results.  We want to extend the community of people who are using Fast Excel Method to improve visibility in their manufacturing and supply chain.

Here is what you need to do.  Leave a comment below, tell us the product your company makes and the biggest challenge you face in capacity planning.  If you would prefer to send an email to me, then you can do so instead.

On Thursday 21 April 2011, we will select five responses, get in touch and arrange your consulting call.  If you are interested in getting your free consulting call, act now and leave your comment.  Preference will be given to those comments or emails given early – it will give us more time to think about the best way to customize Capacity Planning for your business.

Update Thursday 19 April 2012.  We have released a new version of the capacity planning tool.  It has better calculations for production lead-times, simpler to use and an upgraded template.  You can download it here.

17 thoughts on “Kick-Start Your Capacity Planning”

  1. I’d like to get some help with this. We are an electronics manufacturer.

    My biggest challenge in capacity planning is in utilizing equipment. The process is very capital intensive so we batch up the work orders for part, but this extends the leadtime. Customers want fast response so how to balance batch size with capacity utilization?

  2. Hi, I’m interested in the capacity planning consulting call.

    My company makes food product. We have sterile production. Changeovers take many hours and have long runs. Our challenge is we have many different packaging sizes for the same product line. The SKU rate is different, even through the line is running the same product. Changing from one pack form to another is fast, change one product to another is slow.

  3. Free consulting please.
    Our factory makes hydraulic controls for construction equipment. We have over 1500 different sales items, adding new items every month.
    Biggest problem in capacity is manpower. most parts are outsourced and we do assembly. balancing lines every day needs hours of planner time. Part shortages mean the plan has to be flexible. We simply cannot plan every day, takes too much overhead. Working on a way to give production supervisors flexibility to work on orders with parts.

    Thanks and look forward to the call 😉

  4. I would like to enter for the coaching call. I have got the capacity planning tool working with a sample list of orders for our product. Questions I have relate to configuration. We have some business rules to release orders to production and they drive planning.

    1. Hi Stanley,

      Could you say something about the product line your company makes? This helps us get a feel for how capacity planning can be adapted to fit with your business.

        1. antonio villatoro

          My company makes preety much the same, sheet metal, plastic molding, fitting, assembly, cooling, foundry.
          I am responsible specifically I am responsible for planning plastic molding, we do have routing, bom, etc.
          I cannot figure how to handle high mix to make an accurate and fast daily machine schedule.
          We have 18 machines that go from 85 to 440 tons.
          We have 85% dependent demand and 15% spear, independent demand.
          There are 1500 build items for plastic, not all are active.

  5. Hi
    I have just been hired for a company that manufacturers automotive parts. They are using an older ERP system, but not really using it. They do not understand how to plan capacity, using work orders, they enter work orders by osmosis, they do not enter when demand is present employees has it in their heads what work centers can handle what. “hot list are created on paper not in any system. Their delivery time has quadrupled and need someone to get on board (that’s me) and ramp up producing visibility and coordinate their material. I feel your tool could help get on board quickly and to understand processes & procedures. I haven’t downloaded your template yet, but will be looking at it in the next day or so.


  6. Congratulations. That’s really a nice tool easy to setup ! This gives a very clear load.capacity picture.
    Thanks to your very well designed worbook, I’ve also added a report showing each customer contribution to the factory loading. (geology chart). This chart is to help commercial people to negociate the best due dates and better level the shop load.
    BTW do you know if it”s possible to fix the issue of the decimal dot with Excel localized in French.
    When I export/save the data in the .txt files, the decimal values have a dot i.o. of comma. So the program cannot re-import the data for the next run.
    Thanks. Yves

  7. Hi Kein –
    my company is in manufacturing of disposables items and its very complex process, from raw materials of different grade to different items. Process involves extrusion, forming, printing and packing. Could you please help me to build one simple program?

    1. Hello Sanjeev,

      The Capacity Planning Tool has simple logic with very few assumptions and can support a complex process – if that process can be defined using data in a Routing. I have done work with it before in the printing and packaging sector.

      If you would like some help please contact me on [email protected] with an example of your input data.

Leave a Comment

Scroll to Top