How Calendars Fix Your Capacity in Planning and Scheduling

by Kien Leong

Calendar Capacity Planning Scheduling in Excel

This is a guide to help you use calendars in planning and scheduling.

It will be useful because capacity is constrained by the availability of resources.  Calendars can define when those resources are on shift, available to do work and when they are off-shift or down.

In a manufacturing business, resources are better known as workers and machines.  So the calendars that people and machines use are central to concept of capacity planning.

In a previous post, I showed you how calendars are built using Excel dates.  This technique allows you to get whatever level of accuracy you need from Excel calculations involving dates and times.

If you understand this simple idea, and follow the steps below, you will have an effective way to fix capacity and compare with demand.

What Are Calendars?

Planning asks “how are we going to meet demand with the time and resources available?”

Scheduling asks “when and in what sequence are are we going to execute the plan?”

“How”, “when”, “time” and “resources available”.  To define them all, you need a calendar.  A calendar tells us the beginning and end time of each shift as below.

You can have as many shifts as you like in a day, Calendar in 2 columnsand holidays are simply left out.  If you add over-time, change the begin and end times for the day in question.   Remember that this is a formatted view.

The real values that Excel stores are like this:

Decimal days, starting from 1 Jan 1900.  This tableCalendar in Excel date format will list every date over the period you want to do planning.  Set it for a year and you will have a long list of begin and end times with all the shifts and working days.

In just two columns, we can precisely set the shifts for any group of production resources.  Let’s call this group of resources a work center.

Each work center will operate to a calendar and can be assigned accordingly.  If we add another column, then we now have a simple three column calendar that lists every working shift for every day and every shift pattern.

 

Then all we need to do is allocate the work center to a calendar in a separate table and define how many resources it has.  Each resource is part of a work center; each work center works to a calendar.

So in two simple tables, you have the capacity for every resource available to production.

How To Use Calendars in Planning?

So here is what you need to do to use calendars in capacity planning.

  1. Group each production resource into a work center.  This is likely to be done already- your process routing that defines standard work will allocate an operation to a work center to perform.
  2. Choose the time period for calendars.  Take a long range.  You do not want to be building calendars every month.  Start with a couple of years and be sure to include all the dates that could possibly come up as a projected due-date or start-date in a planning system.  Adjust the calendars as holidays, over-time and shifts get added and changed.
  3. List different shift patterns in a long table with the begin and end times for each shift.  Identify each shift pattern by a code, usually one shift pattern for a group of work centers.  If you find yourself with more than 4 or 5, you may be setting yourself up with too much overhead to maintain the calendars.
  4. Allocate each work center to a shift pattern and specify the number of resources for each work center.
  5. Maintain the calendar and work center tables in the master data.  This can be in Excel, or derived from the MRP system data.

You can see an example of this in action with our Capacity Planning Tool.  This post describes how it works and provides a download link.

Members can login on the sidebar at the top and download the tool from the article or from our download pages.  If you haven’t joined us yet, you can sign up for free here.

Take a look at how this calendar concept works in this functional capacity planning tool.  You can load up your own data and adjust to fit your shift pattern.  Then add capacity by changing the calendar and see the impact to available capacity to production.

Let us know how you get on with our calendar concept.  Leave us a comment below, we really like to have your feedback.

{ 8 comments… read them below or add one }

tony jones April 7, 2011 at 2:34 am

Kien,

Although I only recently found your site, it has given me a better understanding of capacity planning and production scheduling. I am very familiar with demand planning and have been able to your your demand planning tool w/o many changes. I look forward to subscribing soon, so that I may be able to take advantage of all of the tools availble.
Keep up the good work!

Tony Jones

Kien Leong April 7, 2011 at 3:12 am

Tony,

Thank you for the nice comments. I would be particularly keen to get feedback from an expert demand planner as yourself. We haven’t yet released a public tool with the core demand planning features that clients usually request. I am open to ideas that will help us specify a product.

Leslie Satenstein April 10, 2011 at 4:00 pm

Hi Kien
The calendar is essential for every workgroup. The concept of using excel to manage a calendar is fantastic, for students.

One question though, is how you would adapt it for company running two shifts. Would you add a shift column, or create a separate calendar?

It is a quiet Sunday AM and I had time to concentrate on what you were conveying, and appreciate your dispensing of information.

Regards from Montreal, Quebec, Canada

Kien Leong April 11, 2011 at 2:27 am

Hello Leslie,

The standard three-column format supports multiple shifts day. In fact, the example above has two on 3rd Jan, one starting 8:00AM and another at 4:30PM.

In the typical case, shift 1 ends before/when shift 2 begins. The shifts do not overlap for the same work center, and the work center has a constant number of resources during the working time. This description fits 95% of cases.

If shifts for the same work center do overlap and shift 1 ends AFTER shift 2 begins, then resources are dynamic. Then you need to take an average (imprecise, but often sufficient) or calculate the number of resources for each work center at any given time (more precise, accuracy depends on process capability)

Alvaro Vitola May 25, 2011 at 11:52 am

Good morning,
First i want to congratulate them as valuable tools that we provide. Im project manager of a mold making company and we work like a engineer to order system. Do you have any solution for this production configuration. Thanks a lot for any help. Please advise ASAP.
Greettings

Hannah July 15, 2012 at 7:22 am

I am exploring the capability of your Excel sheets to use with my current ERP system. It seems to work well with what I want to plan. A few questions though.
1st question: How would you import a WIP work order and calculate the workload correctly?
2nd question: This Excel planning tool only goes to the details of Work Center workload. What about individual activities inside each Work Center and certain acitivities can happen in parallel but some need to happen in series? How will this Excel work with this?
Appreciate your feedback,

Kien Leong August 8, 2012 at 12:29 am

Hello Hannah,

WIP can be presented to a scheduling system in 2 ways: either as an inventory of semi-finished items or as a completed quantity against the process routing. The PS Cycles system has a sheet called WIP that shows the first one of this.

On your second question, the process routing can go down to any level of detail you like. If there are individual tasks that need to be done in parallel, then you can define them as operations in the routing. To represent work done in parallel again you have 2 alternative ways: Use a material relationship and use a multilevel BOM to give it a sub-assembly part number; or a process relationship by defining multiple dependencies for the same operation. In a standard linear routing, each operation has at most one upstream and one downstream dependency. In a “network” routing each operation can have multiple dependencies to other work order operations.

If you want to talk this over further, then please email me at [email protected].

Msharad November 3, 2012 at 1:53 am

Hello Kien Leong,

My company is Connector manufacturer company. product category is huge so we could not find best tool ever.we are using in our company very basic capacity tool..could you please suggest to us for best capacity tool.and how it can use for more utilization of plant capacity??

{ 6 trackbacks }

Previous post:

Next post: