### Theory of Constraints with Excel Solver

With this simple value stream to produce products A and B we want to illustrate the way to maximize profit taking into account the system constraints.

- Each product is made by assembling two sub assemblies, one of them common to both products.
- We have three special purpose workstations with a corresponding operator trained for the specific job:
- Manufacturing
- Test
- Assembly
- The line operates one shift with 2400 productive minutes per week.
- The fixed costs, which include the salaries of the three operators, are 8000 € per week.
- Sales Price:
- A: 90€
- B: 100€
- The maximum weekly market demand is:
- 200 As
- 100 Bs
- Total materials cost:
- A: 50€
- B: 40€
- We want to know how many products A and how many B we should produce to maximize profit.

### System Constraints

- Time available per week for each of the three operators is 2400 minutes
- Weekly market demand: 200 A and 100 B.

### Product Margin

We want to maximize overall profit so we calculate the margin of each product by subtracting price minus materials cost:

Since we can’t satisfy all the market demand we must decide which product should be our priority. If we ask Finance they will tell us B is the best, since it has the highest margin.

### Maximize Product with the Highest Margin: B

Let’s see how many resources we will need to produce all the market demand for B and how many A’s we will be able to produce with the remaining resources:

- manufacture 140
- only test 40
- assemble 380

It makes no sense to manufacture more A’s than we can test. The excess assembly capacity will be wasted because we can only assemble the units which have been tested. This means that in fact we will only be able to produce 100 B’s and 40 A’s. This gives us a total loss per week of 400€.

Maybe the Finance criteria of giving priority to the product with the highest margin was not so good after all.

The use of bottleneck time is therefore a critical factor we should consider in optimizing the line.

We need to ask ourselves how much bottleneck time is consumed to obtain a 40€ margin in product A, and the same for B.

This means that each minute of test time (bottleneck) if used to test product A gives us a margin of 4€ while B only gives us 3€.

So the bottleneck plays an important role in finding the optimal solution: it defines the effective capacity of the whole line, therefore we need to optimize the use of bottleneck time.

Now we go to Solver to setup the parameters as shown:

We want to maximize Profit (B10) changing cells H3:H4 where Solver will place the solution we are looking for.

The constraints in Solver:

Press resolve and we will get the results:

This suggestion would be difficult to justify with Finance, since we are increasing the overall operator time of product A one minute.

But let us look at our Excel to see what Solver has to say:

We have been able to produce 10 more B’s increasing the profit from 1200€ to 1800€.

This is telling us that maybe the way we compute product profit may not help when we have idle time in some of the operations.

Now let’s see another example of Theory of Constraints solution with Solver:

In this case we want to minimize the cost of distribution of goods containers from warehouses X, Y, Z to shops A, B, C, D, E, F:

Matrix C4:H6 contains the transport costs of one container from each warehouse to each shop.

Matrix C9:H11 will contain the number of containers from each warehouse to each shop we are looking for.

The blue cells have the formulas shown.

The Solver parameters will be therefore:

Notice we must meet all the shops requirements: C12:H12 = C13:H13

And the resulting solution:

This is the optimal solution which meets all the constraints.

Maybe the Finance criteria of giving priority to the product with the highest margin was not so good after all.

### Bottleneck

We have already noticed that the bottleneck in this line is Test: it is limiting the effective capacity of the whole line.The use of bottleneck time is therefore a critical factor we should consider in optimizing the line.

We need to ask ourselves how much bottleneck time is consumed to obtain a 40€ margin in product A, and the same for B.

This means that each minute of test time (bottleneck) if used to test product A gives us a margin of 4€ while B only gives us 3€.

### Maximize Product with Highest Bottleneck Profitability: A

So when we take into consideration the existing constraints A is more profitable. Let’s give priority to A and use the remaining capacity to produce B:

So the bottleneck plays an important role in finding the optimal solution: it defines the effective capacity of the whole line, therefore we need to optimize the use of bottleneck time.

### Capacity Utilization

We can clearly see that due to the existing constraints this line is very inefficient. Let’s look at the capacity utilization of each operation:

The only operation with 100% utilization is test. This is what we would expect since it is the bottleneck. We see that final assembly has very low utilization (less than 50%).

We can see that the only way to improve further the productivity is to reduce test time by making it more efficient or to transfer some work (if possible) to the assembly operation.

The red area is the forbidden area due to the constraints: maximum A, maximum B and the constraint of the 2400 minutes available at the bottleneck (test): each A consumes 10 minutes of test and each B 20 minutes.

We now look at the point of maximum profit: It will be the furthest point from the origin within the allowed area (green) which is the point of maximum A (200 units) and 20 units of B.

Now let’s see how to solve the problem with Microsoft Excel Solver. First we need to set all the data in a spread sheet:

In matrix B3:D4 we have the unit times for each operation in products A and B.

Column E has material costs and F product selling price. In column G (margin) we need a formula which just subtracts F – E.

In column H (Produced) we want Solver to place the results we are looking for: number of A’s and B’s to produce.

Column I has the market constraints for A and B.

Row 5 has the formulas above to calculate time consumed on each operation based on the products produced. These values we will compare with row 6 (available times for each operation)

Finally B10 has the shown formula to calculate the profit. This is the cell we will ask Solver to maximize.

The only operation with 100% utilization is test. This is what we would expect since it is the bottleneck. We see that final assembly has very low utilization (less than 50%).

We can see that the only way to improve further the productivity is to reduce test time by making it more efficient or to transfer some work (if possible) to the assembly operation.

### Graphical Solution

Let’s now look at the problem graphically:The red area is the forbidden area due to the constraints: maximum A, maximum B and the constraint of the 2400 minutes available at the bottleneck (test): each A consumes 10 minutes of test and each B 20 minutes.

We now look at the point of maximum profit: It will be the furthest point from the origin within the allowed area (green) which is the point of maximum A (200 units) and 20 units of B.

### Solution with Solver

Download this Excel file

**Solver1.xlsx**from OneDrive. Open sheet**Production**In matrix B3:D4 we have the unit times for each operation in products A and B.

Column E has material costs and F product selling price. In column G (margin) we need a formula which just subtracts F – E.

In column H (Produced) we want Solver to place the results we are looking for: number of A’s and B’s to produce.

Column I has the market constraints for A and B.

Row 5 has the formulas above to calculate time consumed on each operation based on the products produced. These values we will compare with row 6 (available times for each operation)

Finally B10 has the shown formula to calculate the profit. This is the cell we will ask Solver to maximize.

### Constraints:

- Available operators time
- Market demand
- Number of products should be an integer and not negative

Now we go to Solver to setup the parameters as shown:

We want to maximize Profit (B10) changing cells H3:H4 where Solver will place the solution we are looking for.

The constraints in Solver:

- B5:D5 ≤ B6:D6 Used times ≤ Available times
- H3:H4 ≤ I3:I4 Don’t produce above market demand
- H3:H4 Must be integers and not negative

Press resolve and we will get the results:

### Process Improvement

Now imagine that an engineer has found a way to reduce test time of product A from a total of 10 minutes to 9 minutes by adding some work to the assembly operation which would involve an increase from the previous time of 5 minutes to 7.This suggestion would be difficult to justify with Finance, since we are increasing the overall operator time of product A one minute.

But let us look at our Excel to see what Solver has to say:

We have been able to produce 10 more B’s increasing the profit from 1200€ to 1800€.

This is telling us that maybe the way we compute product profit may not help when we have idle time in some of the operations.

### Container Distribution Example

Open sheet

**Distribution**in Solver1.xlsxIn this case we want to minimize the cost of distribution of goods containers from warehouses X, Y, Z to shops A, B, C, D, E, F:

Matrix C4:H6 contains the transport costs of one container from each warehouse to each shop.

Matrix C9:H11 will contain the number of containers from each warehouse to each shop we are looking for.

The blue cells have the formulas shown.

### Constraints:

- Available containers on each warehouse
- Container demand from each shop
- Numbers of containers should be integer (no decimals) and not negative

The Solver parameters will be therefore:

Notice we must meet all the shops requirements: C12:H12 = C13:H13

And the resulting solution:

This is the optimal solution which meets all the constraints.

### Cargo Plane Loading Example

A cargo plane has three compartments for storing cargo: front, centre and rear. These compartments have the following limits on both weight and space:

Furthermore, the weight of the cargo in the respective compartments must be the same proportion of that compartment's weight capacity to maintain the balance of the plane.

The following four cargoes are available for shipment on the next flight:

Any proportion of these cargoes can be accepted. The objective is to determine how much (if any) of each cargo C1, C2, C3 and C4 should be accepted and how to distribute each among the compartments so that the total profit for the flight is maximized.

We will put all this data in Excel (sheet

**Cargo**):### Constraints:

- Available cargos
- Compartment capacities
- Maximum compartment loads
- Compartment load balancing

Solver parameters:

### Conclusion

- All Value Streams are subject to multiple constraints such as:
- Time
- Cost
- Delivery
- Workforce
- Market demand
- The optimal operating point very much depends on the existing system constraints which may vary along time.
- Product cost and profit depend on capacity utilization
- Microsoft Excel Solver is useful to optimize a Value Stream which is subject to multiple constraints.
- Manufacturing lot size minimization is also dependent on existing constraints

## Comments

## Post a Comment