Lot Size and Constraints

System constraints are key when it comes to optimizing our value stream. The process bottleneck limits the overall throughput and it determines such things as manufacturing lot sizes.

With this simple manufacturing line simulation you can experience the effects of alternative solutions in order to maximize profit. When resources are shared by several products there is a setup time when changing product and you have to decide what is the optimal production lot size to optimize the process.

Download this Excel file TOCeng5.xlsm from OneDrive folder Polyhedrika

Close other Excel files before you open this one and enable Macros.

Process Objective

Run the simulator to obtain the maximum profit after one simulated week.

You have an initial capital of 1000 € which you can use to buy materials to feed the blue, green and orange machines.

The green machine performs 3 operations: b, c and d in sequence. All parts should be processed through all 3 so you should decide what manufacturing lot size you want and process the lot through each of these operations. Before each operation there is a setup time. In the same way the brown machine has 2 sequential operations: e and f.

The market will accept any amount of product P with a price of 70 €. Spares P1 and P2 can also be sold but their quantities can never be above the number of products P already sold (if you have already sold 5 P's you can sell, if you want, 5 P1's and 5 P2,s).

Fixed expenses amount to 2000 €/ week and they will be subtracted from the cash balance at the end of each week.

Week 1 will start with an empty line so you can simulate one single week leaving an empty line at the end.

You can also simulate several weeks, in which case you don't empty the line at the end.

Simulator Operation

You operate the simulator with control buttons:

You can either press the start button or use Ctrl + s. The same with the others. The reset button will empty the line and start simulation from zero.

The counter will tell you where you are:

One week is 5 working days of 8 hours. The simulator will stop at the end of each day: just press start to continue.

You start by buying materials based on the lot sizes you have decided and you must select the operation you want to run in the green machine from the pull-down menu: b, c or d.

The same with the brown machine: select e or f.

You can see these details in the Help sheet:

To transfer to the next machine type the amount to be transferred on the yellow boxes.

Financial control

You can control your financial situation in real time:

You can buy materials as long as you have a positive balance.

System Constraints

You may want to try your manufacturing strategy with this simulator before you go into a deeper analysis.

These are the constraints of the different machines:

The bottleneck of the whole line is therefore blue machine a: each product P will need 60 minutes of this machine.

You will notice that we are only considering the process times (not the setup times). The reason is that the influence of setup times can be eliminated by using large enough lot sizes as we will see later.

Another constraint is the fact that all products P and spares P1 need machine a (the bottleneck). Spares P2, although they don't need machine a to be produced they can't be sold unless products P (which need machine a) have been sold.

The bottleneck dictates how much we can produce and therefore the profit. We must focus on optimizing the bottleneck time to maximize profit:

To produce a spare P1 we need 60 minutes of bottleneck time and obtain a profit of 30€. If we use that time to produce a product P this allows us to sell also a spare P2 (which doesn't use the bottleneck) and the profit will be 70€.

The conclusion is that we should not produce any spares P1.

Theoretically we should be able to produce and sell 40 P's and 40 P2's per week.

Lot Size

If we decide to produce only P and P2 it will take 60 minutes of bottleneck a to produce one of each. In the green machine we will need to process 2 units: one for P and one for P2; this will take 18 x 2 = 36 minutes of process time.
To process a lot through the green machine we will need 3 setups: one for each operation b, c and d therefore the total setup time for a lot will be = 40 x 3 = 120
If we consider the lot size of the green machine in the time we process this lot size and do 3 setups we need to process 1/2 lot size in the blue machine. 
The process time for the lot size in the green machine is the sum of process times b (7), c (5) and d (6) total: 18. Multiplied by the lot size.

During this time bottleneck a must process lot size/ 2 units (only for P). If we dedicate all spare time in the green machine to do setups we conclude that the lot size is 10.

Indeed, to process these 10 parts we will need 300 minutes which is the time it takes to process 5 parts in machine a.

This means that if we reduce the lot size below 10 the green machine will be more restrictive than the blue and so it will become the new bottleneck.

In practice to avoid the green machine from becoming the bottleneck we must leave a margin choosing a lot size greater than 10.

If we apply the same reasoning to the brown machine:

In this case the minimum lot size is 8. It may not be practical to have different lot sizes in both machines so we may decide on a value above both such as 12 to compensate for any inefficiencies.

Possible Results

Theoretically starting with a full line we should be able to produce and sell 40 P,s and 40 P2,s which gives us a weekly margin of 40 x 70 = 2800 €. If we subtract the weekly fixed cost of 2000 € that leaves us a profit of 800 €

Starting with an empty line and leaving it almost empty at the end of one week we obtained the results:

Capacity Utilization

On the top right corner of the simulator we can keep track of the capacity utilization on each of the machines.

At the end of the week we obtained the following results:

The first thing we notice is that the bottleneck a has been producing almost 100% of the time: one minute lost in the bottleneck would be a loss for the whole line.

The green and the brown machines have been stopped at the end of the week in order to empty the line and also due to inefficiencies in the manual interventions.

Machines g and h were stopped at the beginning of the week due to the empty line and also along the week due to their excess capacity.

Capacity Increase

With this line the maximum capacity we can expect, starting with a full line, is 40 products P and 40 spares P2 per week (one shift)
If we need to produce more the first step would be to try to reduce the process time in the bottleneck (machine a). If we still don't have enough capacity to cover our customer demand we may think of duplicating the blue machine.
If we do that let us see how would this affect our lot sizes and profit per week.

Bottleneck Duplication

See this analysis in sheet Case 2
The situation now with 30 min process time for blue machine a:

We calculate the capacity of each machine and the machines required to produce each product: P, P1 and P2.

We want to decide in this situation the combination of P, P1 and P2 which is most profitable:

The first thing we notice is that the blue machine a is no longer the bottleneck, now it is h. This machine is only required to produce product P which, by the way, has the highest margin. 
So we first try to maximize P. The maximum P is limited by the new bottleneck h to 1.5/ hour.  
The next constraint after h is now a.
Machine a is necessary both for P and P1 therefore the remaining capacity after P: 2 - 1.5 = 0.5 could be assigned to produce P1.
The next constraint is operation g which is required both for P and P2, therefore what is left for P2 is 2.4 - 1.5 = 0.9
The market constraint "P1s and P2s not above Ps" is met with this solution.
We compute the units per week (40h/ wk).
The weekly profit will be 2080 € (starting with a full line) which is more than double the one with a single blue machine (800 €).

Lot Sizes

We have not considered so far the lot sizes. To obtain the previous profit they should be large enough:

We calculate the used units per hour which in the case of the bcd and ef machines is limited by the capacity of operation g (2.4). 
The difference between the machine capacities and the actual throughput of 2.4 constrained by machine g will be dedicated to setups.
We calculate the process time each hour and the difference to the hour will be the minutes available for setups.
Dividing the total setup times by these minutes available we obtain the minimum lot size to make sure setup doesn't become the bottleneck.
There is not much difference with the case of single a machine.


These are the results starting with an empty line and leaving the line empty at the end of the week. 
We were not able to produce the theoretical 60 Ps and 36 P2s due to the start with an empty line.
Looking at the capacity utilization operation a is still at 100% in spite of not being the bottleneck now. The reason is that all the processed units could be sold as spares P1.
On the other hand h which is now the bottleneck is only at 80% this is also due to starting with an empty line: it has started to receive product several hours after the start.


  • System constraints need to be considered when it comes to optimizing a process.
  • The bottleneck defines the maximum possible throughput for the total process.
  • The bottleneck defines the rate at which product should be started in the line: starting above the bottleneck capacity will only build up Work In Process and it will not increase throughput
  • When the bottleneck capacity is increased we hit the next bottleneck.
  • Doubling the bottleneck capacity doesn't necessarily double the line capacity.
  • In machines with several operations we want the minimum manufacturing lot size but not so small that it becomes the bottleneck of the total process.
  • Capacity utilization should be 100% in the bottleneck but not necessarily in the rest of the operations.


Popular posts from this blog

Excel VSM Simulator

Six Sigma Virtual Catapult

Excel Value Stream Map