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_Flow
Here are the transport relations. In here, the combination FROM – TO must be unique.
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
- End department: Frame
The thicker, the more End Department
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.
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
Selection.name = "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“.
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.
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!
Links
Read the basics of the method: Layout optimization with the circle method according Schwerdtfeger or further informations in Material flow can be easy!
