Capacity Planning is a key step towards production scheduling.
Production scheduling is often the final goal in production planning. Given any mix of demand and available capacity, how and when will it all complete?
Our new Capacity Planning Tool gets you halfway to production scheduling. Download it and this article will help you understand how it calculates dates and working days in the calendar. It will go a long way towards that scheduling goal.
Production Lead-Time Offsets in Capacity Planning and Scheduling
Capacity planning compares production load with available capacity. It shows this for a given period, usually a day or a week. So, an important part of a capacity planner is to decide which period.
Sales orders have due-dates. The date that the sales item has to be ready to ship or available to inventory.
To produce the sales item, you have a process routing that describes the operations required to produce it. You may also have a bill of materials that defines other child production items that are required to make it.
So, the logical way is to work backwards from the due-date to calculate when each operation needs to complete and start. And then work backwards from the first operation to calculate the start-dates for the operations at each production item.
the question is: How far do you go backwards from the due-date of each operation to calculate it’s start-date? If we were production scheduling, then we would use actual waiting and working time. A scheduler would calculate that the work order queues at a busy work-center, waits for the setup and then completes the production run.
Capacity planning is much simpler. It gives a higher level of result with less precision and usually at a longer range. The advantage is that it relies much less on accurate routing data and business rules that govern the factory.
Finite scheduling is going to make some assumptions about how work orders will be handled in the factory. Which priorities, what sequence, how long to setup, run, what transfer quantities and so on. The factory needs to stay true to the assumptions and stick to the schedule.
Capacity planning has some looser assumptions. We will use a lead-time offset of whole production days.
Working Out Lead-Time Offsets Through a Calendar
In many factories that we work with, the lead-time is assumed to be calendar days, or a simple definition of working days. Using calendar days, an offset of 7 days from Wednesday 25 April will get you to Wednesday 18 April. Using a simple definition of working days, we assume a five-day week and 7 days goes all the way back to Monday 16 April.
The problem is that not every work center is operating on the same calendar. Some operations can be performed 24/7, others 6 days a week. Still more are 5-day weeks. Each work center could have a different shift-pattern. This is an extreme case, usually work centers are managed in a group to the same shift pattern. But it is very common that there could be many different sets of working days.
In previous articles here and here, we have talked about how Excel calculates dates and calendars give capacity to work centers. These same calendars also define which days are working days and which are not.
Capacity Planning Tool Calculates Working Days
The capacity planning tool calculates working days for each work center. If a work center is down for maintenance, or the whole factory is on vacation, this date will not be counted in the capacity planner offset calculations.
If you haven’t already done so, you can download the capacity planning tool here and play around with the data. The offsets are held in the Routing for each operation. The calendar is defined for shift patterns and those shift patterns are set against work centers. You can see it all in the Calendars, Work Centers and Routings tabs.
These date calculations make heavy use of INDEX and MATCH functions. As mentioned before, INDEX is the most powerful Excel formula that you will ever use. I won’t go into them here, easier for you to download the tool, unhide the calculation sheets and check out the mechanics on the Load sheet.
Capacity planning needs a start-date. Start dates are calculated backwards from a due-date using a lead-time offset. This Excel tool for capacity planning is a nice way to ensure that every work order operation is allocated a consistent number of working days for production. And that no work order is scheduled to start or complete on a date when the work center is not working.