Capacity model with balance algorithm

This post is a continuous post to “Building a capacity model – first post”. Please read the first post since i will start where the first post end. I will assume you know the simple capacity model already, introduced in the first post.

Balancing the capacity model

In the simple capacity model every product can only be produced on one type of tool. In reality we have more than 1 tool type that can produce the product. Sometimes we prefer the product to run on 1 type of tool, which we will call the main tool, but when we don’t have the capacity, we will use a secondary tool. If we want to know whether we have the capacity to produce the product mix that we want, we should use both the main and the secondary tool. The algorithm will use the main tool and if the loading goes over the loading, we define that the algorithm will start using the secondary tool. I call this action “tool balancing”.

The output of the model (on top of the loading data) is a list of which tool should produce each product. The list is made after the algorithm has balanced the tools. The algorithm shown here is a good algorithm that produces great results, BUT it is not an algorithm for optimization.

With this algorithm you can run a short code that will run the algorithm automatically, just like in the attached file, or you can do the algorithm manually – if you don’t have too many products and tools, it shouldn’t take long. Both ways get you the same results.

Balancing algorithm

Before we start:

1. we need to decide what we consider a balanced tool. I use loading=85% since it leaves room for surprises. (that means that the algorithm will stop trying to balance the tool if it has 85% or lower loading value). In my model I call this a “Target balancing value” parameter.
2. if we have tools that we want to balance first, we can give these tools a higher priority so that the algorithm will try to balance them before the others. In the attached excel file I added a column in the tool sheet that helps you decide which tools are in priority. The higher the priority, the more the algorithm will try to balance it first.

The algorithm

1. Filter out tools of which the loading is lesser than the target balancing value parameter. Sort the rest of the tools by their priority, then by loading – high to low.  Pick a tool to balance (I will call this tool “the giving tool”).
2. Start with the product that consumes the most production time on the giving tool and have a secondary tool (that can run the product):

a) move the product to the secondary tool (I will call it the “receiving tool”).

b) if the loading on the receiving tool is higher than the target balancing value parameter, return the product to the giving tool, pick the next most time consuming product on the giving tool, and go to point A.

3. If the current giving tool loading is lower than the target balancing value parameter, go to the next tool. If not, go to 2 until there are no other products to balance.

4. Continue until there are no more tools to balance.

Here is a visual diagram of the algorithm:

To demonstrate the algorithm, I built a capacity model in excel that runs this algorithm. Notice that the excel file includes a VBA code, so you might need to give security clearance to run it. Notice that if you use your mobile phone, you probably won’t be able to run a VBA code.

How to use the algorithm

There are 3 types of sheets:

1. Input – the sheets names are colored green. The input is similar to the simple capacity model except for the parameter sheet. This sheet contains 2 parameters.
   1. The first is the time bucket that we will balance (we can only balance one time bucket each time). You don’t need to pick the time bucket from here, you can easily do it in the “Tools loading” sheet.
   2. The second is the target balancing parameter which is the maximal loading that beyond that value we will try to balance the tool. For example, if the value is 85%, then a loading of 60% will not trigger the algorithm, but 90% will.

1. 1. Calculations – the sheets names are colored orange. Some calculation sheets are hidden, but you can always unhide them. On the remaining sheet you can see how much time every product consumes on every tool.

1. Output – the sheets names are colored blue. Those are the important sheets in the model since they contain the results:
   1. 1. Tools loading sheet – in this sheet we can see the loading of each tool for every time bucket before balancing on the left. In the right column labeled Output we can see the loading after balancing. This sheet includes a button that will run the algorithm (make sure you choose the time bucket that you want to balance).
      2. What products need tool change – this sheet shows each product that needs to move from the main tool to the secondary tool.
      3. After balance HoursPerProduct sheet – shows the number of hours needed to produce each product on each tool after balancing.

Conclusion

In this post I explained the balancing algorithm. We have a working capacity model that uses the automatic balancing algorithm. I also explained the capacity model file.

I will really appreciate if you leave me a comment.

You can also contact me at:

Gal Merom:  theplanningmaster@gmail.com