**In every factory planning project, one is confronted at some point with finding an optimal arrangement of work****centers**** or production facilities. The SCHWERDTFEGER method is a good starting point. But it is very rough. It does not help in detail. The triang****le**** method according to SCHMIGALLA is more interesting. ****It ****is more detailed, but graphically complex to create. With a little coding, however, this can be changed so that the method is really fun. But see for yourself.**

In factory planning, layout planning is called object-space allocation optimization. This mainly refers to the arrangement of production facilities in a factory hall area. But the same applies to the arrangement of hall areas in a factory system or the factory systems in an overall factory. It is also used in detailed areas, for example in warehouses, to assign different storage compartments to stored goods.

## Triangle method according to Schmigalla

There are various methods of optimizing this arrangement according to criteria. One optimization principle is the triangle method mentioned by SCHMIGALLA. Like SCHWERDTFEGER, it is a graphic process based on a linear optimization process. The method has been around since 1970 in several stages of improvement.

The frequency of transport is used as a minimization criterion. Kurt W. Helbing presents a detailed explanation of the method in his manual for factory planning.

The modified triangle method uses the frequency of transport between the objects and no longer relates to the places. System inputs and outputs are also considered. The places result from the arrangement on the triangle grid.

Start is the from-to matrix based on the frequency of transport. The optimization goal remains the overall reduction of the necessary component movements.

SCHMIGALLA proceeds in steps:

- Calculation of the from-to matrix with the transport frequency
- Selection of the maximum transport value in the matrix and arrangement of the two dependent workplaces on the triangle grid
- Extension of the from-to-matrix with a total line, filled with the transport frequencies of all connected objects with the selected ones
- Selection of the object with the highest total value
- Insert the object on the triangle grid with selection of the appropriate position on the grid
- Repeat from step 3 until all objects are planned

The choice of the optimal layout space is decided by the planner. It can also be optimized after inserting the next object.

## High manual effort

With the method, a transport-optimized object assignment can be reliably developed. However, it is a long process to determine the next object and draw it on a triangle grid. If this is done with standard software such as Microsoft Word, Excel, Powerpoint or Viso, shapes must first be inserted, named, formatted and connected with lines. Best of all with the line thickness adapted to the transport volume.

But the method also works well with a relatively high number of objects. With each additional object, however, the from-to matrix increases and the manual effort increases further.

## Partial automation with dialogue guidance

How can the effort be reduced? The from-to matrix is available as a table with rows and columns. Calculations are necessary to find the next object to be placed. The working basis is therefore two documents: a calculation table based on the from-to matrix and a drawing document with the triangle grid and the placed objects.

That speaks for me in favour of using Microsoft Excel. The calculation tables, the drawing grid and the program code in VBA can be accommodated in one document.

Most of the steps in the SCHMIGALLA sequence deal with determining the next object to be placed. The algorithm behind it, however, pulls the planner out of the concentration phase for each object / workcenter in order to develop an optimized arrangement. That’s why I start here with VBA to automatically calculate the algorithm and offer the planner the next object to place directly. A Visual Basic form is best suited for implementation. The dialogue need not contain many elements. There must be one way to start the planning process and another way to insert the next object. Nothing more is necessary. This allows the planner to fully concentrate on finding the best possible layout position.

## From-to matrix

The basis for the application is the from-to matrix or the derived transport frequency matrix. I have described how this can be easily created in the German article Material flow matrix from ERP data . I also use this data here. This means that I am not using a matrix, but a from-to table. The from-to table is the data-technical preliminary stage to the matrix, the source before the execution of a crosstab query, which builds up the matrix. The table structure consists of only three fields:

- From-object
- To-object
- Quantitative number of transports

In order to find the next object it is necessary to have information about it. That is why there is a second table in which the objects are stored. The evaluations for the next property are also saved and evaluated there.

This is where the change from the from-to matrix to the from-to table shows its strengths. It is very easy to display the input and output connections for each object using a *VLOOKUP* .

## Build the planning grid

Another plus point for Microsoft Excel is that the typical triangle grid can be set up very comfortably with simple frame formatting.

To do this, the column width and row height must be coordinated. In my grid I use 8.67 as column width and 65.40 as row height.

## Objects on the grid

Objects or workcenters are inserted as shapes. I describe how this works with VBA using the SCHWERDTFEGER method in the article Circle Method with Excel Support. I use the same routines here to insert shapes and connect shapes with line elements. The best way to get inspiration on how this works is as a download in the example.

## Find start objects

In this article I would like to deal with the implementation of the method in VBA. In the first step, the two start objects must be identified. Again, it is an advantage to use the from-to table. The start objects are the row in the table with the highest value in the transport column.

We find the value by looping through the table and saving the name of the from and to object for each higher value.

i = 1 JBmax = 0 Von = "" Nach = "" Do i = i + 1 JB = StrToDbl(Sheets("FromTo-Matrix").Cells(i, 6)) If JB > JBmax Then JBmax = JB Von = Sheets("FromTo-Matrix").Cells(i, 1) Nach = Sheets("FromTo-Matrix").Cells(i, 2) End If Loop Until Sheets("FromTo-Matrix").Cells(i, 1) = ""

The two workcenters found are then inserted on the planning grid using the methods of the article referred to above. In this case, however, it is helpful to calculate the *left* and *top* position of the shapes in such a way that they are already placed on suitable triangle points.

For the calculation, the set column widths and row heights can be accessed and a position can be determined:

e.g. 6 grids in X, 4 grids in Y

LeftPos = ActiveSheet.Columns(1).Width * 6 – ObjectShape.Height / 2 TopPos = ActiveSheet.Rows(1).Height + ActiveSheet.Rows(2).Height + ActiveSheet.Rows(3).Height * 4 - ObjectShape.Height / 2)

## Determine the next objects

As with the manual procedure of the method, the greatest effort is in determining the next object.

In the software implementation, I use a column in the table of objects (Sheet *Workcenters* ) to store the valuation *sums* .

Before that happens, however, all fields are reset to 0. For me it’s column 14.

i = 1 Do i = i + 1 Sheets("Workcenters").Cells(i, 14) = 0 Loop Until Sheets("Workcenters").Cells(i, 2) = ""

In the actual calculation, the from-to table is run through and it is checked whether the transport frequency of the from and to object may be added to the object table.

This is done in via the Boolean variable *canAdd*.

i = 1 Do i = i + 1 Von = Sheets("FromTo-Matrix").Cells(i, 1) Nach = Sheets("FromTo-Matrix").Cells(i, 2) wert = Sheets("FromTo-Matrix").Cells(i, 6) canAdd = False j = 1 Do j = j + 1 If (Sheets("Workcenters").Cells(j, 2) = Von Or Sheets("Workcenters").Cells(j, 2) = Nach) And Sheets("Workcenters").Cells(j, 12) = "X" Then canAdd = True Loop Until Sheets("Workcenters").Cells(j, 2) = "" If canAdd = True Then Call WorkcenterAddSum(Von, wert) Call WorkcenterAddSum(Nach, wert) End If Loop Until Sheets("FromTo-Matrix").Cells(i, 1) = ""

Adding to the calculation total is only permitted if one of the two objects has already been inserted on the triangle grid. This is why objects that have already been inserted are marked with an X in column 12 *Planned*.

Sub WorkcenterAddSum(workcenter As String, value As Double) Dim i As Integer Dim s As String i = 1 Do i = i + 1 If Sheets("Workcenters").Cells(i, 2) = workcenter And Sheets("Workcenters").Cells(i, 12) = "" Then Sheets("Workcenters").Cells(i, 14) = CDbl(Sheets("Workcenters").Cells(i, 14)) + value End If Loop Until Sheets("Workcenters").Cells(i, 2) = "" End Sub

This completes the calculation. In the last step, the highest value in the object table that has not yet had the *Planned* flag can be searched for . The next object to be inserted has now been found.

It is saved in the *NextWorkcenter* variable.

i = 1 wertmax = 0 Do i = i + 1 If Sheets("Workcenters").Cells(i, 12) <> "X" Then wert = StrToDbl(Sheets("Workcenters").Cells(i, 14)) If wert > wertmax Then wertmax = wert NextWorkcenter = Sheets("Workcenters").Cells(i, 2) End If End If Loop Until Sheets("Workcenters").Cells(i, 2) = ""

In theory, it can happen that no object is found this way. This is when there are independent material flows between two production lines. In this case, the next higher value is simply selected from the object table. The calculated field is then not used, but the sum of the transport volume.

In my case this is no longer column 13, but column 14.

If NextWorkcenter = "" Then i = 1 wertmax = 0 Do i = i + 1 If Sheets("Workcenters").Cells(i, 12) <> "X" Then wert = StrToDbl(Sheets("Workcenters").Cells(i, 13)) If wert > wertmax Then wertmax = wert NextWorkcenter = Sheets("Workcenters").Cells(i, 2) End If End If Loop Until Sheets("Workcenters").Cells(i, 2) = "" End If

## Application to “Gear Production”

How does the whole system look in the application? I would like to show you an example of this. I would like to present my dialogue-guided method using the example of a Gear Production. This production is a case study by Prof. Dr.-Ing. Uwe Prêt (HTW Berlin) and already a few years old. However, the document still explains the process of factory planning very well and is one of my favourite examples in this area. The source data can still be downloaded from Uwe Prêt’s website ( http://www.uwe-pret.de/transmission.pdf ). If you don’t already know, be sure to check it out.

Prof. Dr.-Ing. Prêt presents all planning activities on over 80 pages, including layout optimization according to SCHMIGALLA.

You can find the example of its sequence determination under Figure 1 above in the article.

Its calculation results in the following order of insertion of the objects on the triangle grid:

- Deburring place
- WA
- CU72H
- DMU50V
- WE
- ZX1
- TNC65
- ABS280
- PE150C
- RISZ
- PF150

## Replication of gear production with the dialogue-guided Schmigalla method

I transferred the input data for the method to the tables of the objects and the from-to table. The corresponding tables are already shown in the upper part of the article.

The dialogue is started on the planning grid. There I inserted a button that calls a SUB, which displays the form. This only works if the SUB is housed in a module.

Sub ShowSchmigallaForm() Unload SchmigallaForm SchmigallaForm.Show End Sub

So that you get a feeling how intuitively and quickly an optimized arrangement can be created with my dialogue-guided SCHMIGALLA method, it is best to watch this video.

It shows the complete layout planning in a triangular grid of gear production in 2 minutes. The dialog-guided Schmigalla method offers you, the planner, the same sequence that Prof. Dr.-Ing. Prêt has calculated in its execution.

In this way, you can quickly and easily develop different variants for further layout planning and you can always focus on the positioning of the objects.

## Let’s start with coding

I hope I was able to give you a little pleasure in dealing with layout planning, the SCHMIGALLA method and VBA.

You make the greatest development step in the direction of coding when you combine the listings with your own Excel form and find out the rest of the subject of shapes and arrows yourself.

If it doesn’t work, you can download the current version of the tool here.

Have fun optimizing!

## Links and Literature

Kurt W. Helbing, 2020, Factory Planning Manual, from page 1155

Hans Schmigalla, 1970, Methods for the optimal machine arrangement

Prof. Dr.-Ing. Prêt, 2018, factory planning: parts manufacturing and assembly project “gear production”, http://www.uwe-pret.de/getriebe.pdf

Article material flow matrix from ERP data

Article Circular method with Excel support

More articles on the Excel Schmigalla Tool