Material flow can be easy!

Material flow can be easy!

“Everything is in the flow”. The statement of the Greek philosopher Heraclitus (about 540-480. Chr.) applies in particular to the work of factory planning. Here, it is long-term use concepts with changeable processes to make. One way to visualize results or review concepts is the material flow simulation.

The development in software engineering is making huge progress in this area and offers many opportunities for doing so. But the path to a conclusion can be reached only with great effort and expertise in the software.

In many cases, however, extends a static representation of the current situation to push next thoughts or decisions. A fast speed – at least at the start – is much better than a detailed analysis.


With the already introduced Excel tool “Circle method with Excel support” a favorable arrangement of departments in the layout can be achieved. The departments themselves and the relationships between them are required as a basis: for example transport frequency or annual requirements.

Excel macro "circle method" for automatic material flow
Excel macro “circle method” in the original version

If the tool is a little bit extended, the visualization of the material flow is possible – directly in your layout!

New functions for our material flow

Basically, the “old” macro brings already with all the functions you need to do that immediately. The trick is, instead of a circle, insert the layout of your factory or workshop as an image and put it into the background.

With a little effort you have to moves all departments to the corresponding position in the layout.

The problem: but as soon as there are new departments or a change in transport relations, you have to start over again and place all departments and again in the layout.

It would be much better, if the functions for drawing the elements in the macro are executed one by one. Then there are the possibilities of the circle, the departments or the transport relations (arrows) to delete individually and over again. And that with always current values ​​from the data in the sheets.

Enhancements in dialog form
Enhancements in dialog form the macro

There are only items re-inserted, which do not yet or no longer exists. By that the departments on the sheet are left in their current layout position.

To add new departments, it is only necessary to insert them into the sheet “Data_Departments” and click on “Insert departments” in the macro dialog. So that the connections can be drawn, entries in the table “Data_Flow” are necessary too.

Insert new department
Insert new department

An example

Button to open the dialog box
Button to open the dialog form

Open the new macro and click on “Circle method”. In the dialog form press “Insert all”. Now move the positions of any departments. Then open the dialog form again.

Click the “Delete arrows” button, and then on “Insert arrows”. You see that all departments remain in place.

Layout without and with material flow
Layout of a factory shopfloor with departments and material flow in the initial state

As in the circle diagram, you can now move the departments on the best possible position.

Layout im optimierten Zustand
Layout in a optimized state

Are there new departments or changed transport relations can be modified in the sheets “Data_Departments” and “Data_Flow” and update your layout at any time.

Planning KPI’s

Which layout is now but better: Version A version B? The decision can be evaluated better with key figures. A planning figure may be the length of the entire material flow.

To calculate this, the “Worksheet” contains an additional button “Length”. Each time you click it, the total route of all arrows is been calculated. The calculation is done with help in the sheet “Data_Flow”. There are 3 columns. The first calculates the length in points (the internal unit of Excel). The seconds does the conversion to meters. For a better comparison we need a weighting. I prefer a factor with the length and the annual requirement. The sum of it is inserted into third column as the KPI for the material flow.

Convert the arrow lengths in meters
Convert the arrow lengths in meters

For the calculation from points to meter we have to use a scale factor. That is defined in the cell M1. This factor depends only on your layout. The best way to get a correct one is to add a special “measurement flow” between to departments you know the real distance.


This will be as well an easy way to accompany the development of your factory and consider again whether the arrangement will still satisfy the current processes.

I’m excited to hear about your experiences.

Good luck for you optimization!



  Download the macro from my Dropbox

Blog article: Circle method with Excel support and Layout optimization with the circle method according Schwerdtfeger

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

Dieser Beitrag hat 3 Kommentare

Kommentar verfassen