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