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