Material flow can be easy!

material flow with Excel

„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.

Basics

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.

materialfluss_massstab_en

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!

Links

symbol_download

  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 6 Kommentare

  1. Johannes

    Hallo Thomas,

    das ist ja ein richtig geiles Tool, welches ich gerne für meine studentische Zwecke nutzen möchte. Ich habe auch schon einige Anpassungen vorgenommen z.B. von Prozesskapazitäten gleich mit in den Abteilungsrechtecken oder Veränderungen hinsichtlich der Liniendarstellung.

    Gerne würde ich noch die Transportvolumen von Abteilung zu Abteilung direkt (vielleicht als Textbox) auf die einzelnen Linien projizieren, doch ich scheitere ständig daran.
    Hast du eine Idee wie ich das bewerkstelligen kann? Gerne auch nur ein paar Hinweise geben und ich probiere das selbst zu coden.

    Mit freundlichen Grüßen,

    Johannes

    1. Thomas Angielsky

      Hallo Johannes,

      danke für das Lob. Die Möglichkeit Texte anzuzeigen, habe ich mir auch überlegt. Meine Umsetzung funktioniert aber nicht immer gut.

      Ich gehe mit einer Schleife über alle Pfeil-Shapes und überlagere sie einfach mit einer Textbox. Die Textbox hat die gleiche Dimension wie der Pfeil (x,y und Länge, Breite). Dadurch ist der Text immer auf Pfeilmitte. Sie wird transparent formatiert und der Text vertikal und horizontal zentriert.

      Das sieht dann so aus:
      https://techpluscode.de/wp-content/uploads/2020/12/schmigalla-mit-mengen.jpg

      Nachteil: Positionierung muss immer nach einem verschieben von Abteilungen angepasst werden. Deswegen brauchst du auch eine Routine, um alle Texte zu löschen und wieder neu einzufügen.

      Ich bin schon auf deine Version gespannt. Verlinke sie gerne hier. Viel Erfolg bei deiner Umsetzung!
      Thomas

      1. Johannes

        Hi Thomas,

        war eigentlich ganz leicht. Einfach wie du gesagt hast und dann noch das Editor Fenster ein wenig verändern. Ich lasse dir in Zukunft mal meine fertige Version zukommen und zeige was daraus geworden ist.https://ibb.co/C8yq5Syhttps://ibb.co/sw2vZDF

        Grüße,

        Johannes

Kommentar verfassen