Circle method with Excel support

Circle method with Excel support

In the article “layout optimization with the circle method according Schwerdtfeger” I introduced the simple and effective method. The recognized problem of the method was: the preparations are quite time consuming. How to solve this, you will read in this article.

Normally you will not find for such a job a volunteer to build the circle with all relationships. Especially if you need specialized software such as CAD systems or Microsoft Visio to create it.

That was somehow too complicated for me. The best results for me are the ones you have yourself understood completely. This works best if you can do it yourself.

The basic data must always be provided from the ERP system – that is clear – but the arrangement process with the lines and boxes always stays the same.

To me it was clear: I have to improve this problem.

I need a tool

For me, a solution using Microsoft Excel is important. It is for most users available and that is not the case with CAD and Visio.

A further point for Excel is the ability to easily provide data and use them for calculations.

The data base is divided here in two tables: departments and transport information (flow).

  • Data_Departments
    Here are the sections that are arranged along the circumference. The names in row „Caption“ must be unique.


Data extraction for sheet "Data_Departments"
Data extraction for sheet “Data_Departments” with start and end relevance
  • Data_Flow
    Here are the transport relations. In here, the combination FROM – TO must be unique.

Data flow table
Data extraction of sheet “Data_Flow” with FROM-TO relations of departments with annual requirements

So that the processing of the circle, departments and connections is done automatically, I wanted to extend the method with other visual information.

The visualization

For departments I have installed two visualizations: process start and process end information.

Depending on how large the number of departments is, it is not right clear where processes begin and end. This information, the program displays too. For this I use the properties of the rectangle.

  • Start department: filling area
    The darker, the more Start Department
    Beispiel für eine Startabteilung
  • End department: Frame
    The thicker, the more End Department
    Beispiel für eine Endeabteilung

In the transport relations, the frequency will decide in the “Annual demand” over the appearance of the arrows. The thickness is adjusted to the “annual demand” or other content.

Beispiel für Transportbeziehungen

How does it work?

Excel also offers the ability to connect shapes with arrows, the so-called docking. This is what the developed Excel macro does.

In the macro that happens on the ConnectorFormat property:

If ShapeVorhanden("R_" & Von) = True And ShapeVorhanden("R_" & Nach) = True Then        
  ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 0, 0, 100, 100).Select  = "P_" & Von & "_" & Nach        
  Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle        
  Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes("R_" & Von), 3        
  Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes("R_" & Nach), 1

Look at the VBA module of the macro best. The download link can be found at the end of the blog.

To test the functions, call the dialog form of the macro by clicking the “circle method …” in the sheet “Worksheet”.

Settings in Microsoft Excel dialog
Settings dialog form of the Microsoft Excel macro

In the dialog form you can set different properties for the size of the shapes of the departments and select the diameter of the circle.

Further improved visualization of the transportation lanes, the proportions of the “Annual needs” with the colors red-yellow-black. In the example, the first 40% of the annual demand for red, the next 20% yellow, with the remaining 40% is black.

By pressing “Start” everything is inserted in the “Worksheet”.

The dialog form can be accessed by clicking again on “circle method …”. “Delete items” with the button and redraw when changed data.

The trick with the protection

If you want to start now and move departments around the circle, you must be pretty careful not to repeatedly select and arrows and to move them. If that happens, the connectivity has been lost to the shape of the department and the arrow “hangs in the air.”

To prevent this, there is a simple trick. The shapes of the departments are “not locked” on the properties in Visual Basic as (locked = false) set. Before moving you now need to turn on only the protect system of Excel. From now on, only departments can be tackled and moved.

If you are ready you can turn off the protection. The easiest way is directly through the dialog.

Big Data

The above example is preparing little effort to find a viable solution. but the practice usually looks quite different. Here, the tool comes into its own. Because by the automatic, the number of departments and connections are (almost) irrelevant.

As an example, you will find here the initial state of an optimization project of a production line.

Example of circle method with big data
Example of a circle diagram of a mechanical production line in the initial state

Now you just have to get started. With the macro you have the tool for easier optimization with the method of SCHWERDTFEGER.

Good luck for your optimization!



  Download the Excel macro here

Read the basics of the method: Layout optimization with the circle method according Schwerdtfeger or further informations in Material flow can be easy!

Klicken Sie hier, um diesen Artikel in Deutsch zu lesen.

Menü schließen