Activity Relationship Charts for layout optimization in factory planning

When factory layouts are replanned or changed, the arrangement of departments, workstations or equipment plays a key role in maximizing output and minimizing costs. The focus is on the flow of materials between the departments. This is exactly right for many projects. However, the flow of materials alone is not the solution; all relationships count.

Material flow alone is not the solution

The material flow is important because it reflects the movement of the product. But what if other criteria are more important than the material flow in certain places? The potential to develop an optimal factory layout is then wasted.

These places in the material flow are usually known to those involved, but are simply not taken into account when the focus is on the pure material flow in the layout planning.

There are a number of criteria that are not mapped via the material flow. Some examples are:

Sharing of tools between multiple workstations
Simultaneous process monitoring
Multi-machine operation of machines
Noise and quiet areas
Dirty and clean areas

In order to systematically identify and take into account these quantitative criteria, Tompkins/White/Bozer/Tanchoco (Facilities Planning, page 115ff) use the “Activity Relationship Chart” (ARC).

It is a type of diagram based on a triangular Mileage diagram. The ARC visually represents the relationships between departments in a triangular shape.

Structure of an Activity Relationship Chart

The chart contains all the departments and the criteria they are related to. The departments are on the left side.

Evaluation of a relationship in the ARC via the layout position closeness and its reasons

In the triangular fields, the relevance of the relationship is shown in the upper half, expressed as a letter.

A Absolutely necessary
E Especially important
I Important
O Ordinary closeness okay
U Unimportant
X not desireable or prohibited

The lower half explains the reason for the selected relationship rating. These criteria are defined by the planning case being examined.

For example, Wikipedia lists these:

Same table
Flow of material
Service
Convenience
Inventory control
Communication
Same personnel
Cleanliness
Flow of parts

Problem data collection

Collecting the necessary relationship data is the much more difficult part. Surveys or interviews with the people involved are ideal for this. When looking at things from an individual perspective, different classifications are likely to emerge. However, the Activity Relationship Chart can only provide an assessment of relevance.

That’s why collecting data in workshops with all the people involved is a more efficient option. Every relationship between two workplaces can be discussed and recorded directly.

To do this, the focus must be on the relationship between the two departments currently being evaluated. The Activity Relationship Chart always shows all relationships, but it is more difficult to focus on one in particular.

It is better to carry out each evaluation in a focused manner in the workshop.

Theoretically, the method works with any number of departments. From 10 items, 45 relationships must be examined. With 8 departments, there are still 28 relationships. This can be easily examined in a workshop. If there are more departments, it makes more sense to divide them into groups beforehand and analyze the groups separately.

Number of departments to be examined with the resulting relationship evaluations

A self-coded Activity Relationship Chart for workshops

If you have read this far, you will definitely want to try out an Activity Relationship Chart. A suitable template can also be found quickly via Google. The templates for Microsoft Office usually do not look very good. The graphically appealing ones are based more on web applications, such as creately.com.

Now that we know what we want and need, we can program it ourselves for Microsoft Excel.

The result is the ARC generator. With it, you can very quickly create Activity Relationship Charts in any size and it also supports you in filling them out – especially in workshops.

A chart automatically generated by the Activity Relationship Chart Generator
The Activity Relationship Chart Generator when evaluating in a workshop situation
Departments and variable data are recorded flexibly via lists
Automatically generated data table during evaluation

There are two challenges to be solved. Firstly, the unusual form of representation as a triangular Mileage chart must work. Secondly, we need a way to fill it out and evaluate it in a workshop situation.

Part 1: Implementing the Activity Relationship Chart in Excel

Find a suitable representation

If you look at the chart, Excel’s row and column orientation doesn’t provide a good starting point for the display. There isn’t really a perfect implementation. But Excel does provide the option of not only displaying a frame on the outside of individual cells, but also across the cell.

Diagonal border formatting as a solution for visual implementation

Possible visual implementation with frames, but with an unsightly text alignment problem — Diagonal border formatting as a solution for visual implementation

This logic makes it very easy to construct the chart. The rating codes can only be arranged right-aligned to ensure they are in roughly the right place.

The evaluation also allows for multiple answers to justify the reasoning. With the right-aligned arrangement, this looks unsightly.

There’s a little trick that makes things better. To do this, you add an additional column for the label and select center alignment. This is just an auxiliary column. In the finished chart, you set the column width to the smallest value that still shows the text. In my case, that’s 0.03.

Manual input may be a bit more complicated, but the result is much nicer.

Make criteria freely selectable

Depending on the planning task, the criteria for the relationship reasons vary. It therefore makes sense to take this variability into account right away. Likewise, the jobs to be evaluated must be able to be specified somewhere.

Our ARC generator becomes even more variable if the relationship relevance can also be freely defined.

This makes it clear that an input table with three lists is required. Each list consists of two columns. The first contains the code and the second a description of the code.

Departments and variable data are recorded flexibly via lists

We have all the information we need to format the Excel cells accordingly using VBA.

In order to work, our algorithm for the ARC needs the data from the input worksheet. Global constants define where which data is located.

'Input
Global Const SHEET_INPUT = "ARC data" 'name of the worksheet
Global Const ROW_INPUT_START = 5 'row where all input data starts
Global Const COL_DEPARTMENT = 2 'column no
Global Const COL_DEPARTMENT_DESCRIPTION = 3 'column no
Global Const COL_RATING_SYMBOL = 5 'column no
Global Const COL_REASON_CODE = 8 'column no
Global Const COL_ARC_DATA = 11 'column no

'Output
Global Const SHEET_OUTPUT_ARC = "Activity relationship chart" 'name of the worksheet
Global Const ARC_TOP = 4
Global Const ARC_LEFT = 2
Global Const ARC_WIDTH = 6 'column width of the triangular cells
Global Const ARC_WIDTH_TEXT = 0.03 'column width of the "invisible" text
Global Const ARC_WIDTH_TEXT_EDIT = 10 'column width for manual editing

Now it’s time to format the chart. If exactly this happens in the source code, it will be stated in the comment lines directly in the source code.

The algorithm does a lot of drawing border lines. To make this a little more convenient and shorter, the functionality has been outsourced to a separate procedure DoBorder().

Private Sub DoBorder(rng As Range, bi As XlBordersIndex, bw As Double)
  With rng.Borders(bi)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .weight = bw
  End With
End Sub

Sub CreateARC() 
Dim i As Integer
Dim j As Integer
Dim si As String
Dim sj As String
Dim x As Integer
Dim y As Integer
Dim shArc As Worksheet
Dim shData As Worksheet

'First define the worksheet for the input data
  Set shData = Sheets(SHEET_INPUT)

'The same for the worksheet in which the Activity Relationship Chart is drawn
  Set shArc = Sheets(SHEET_OUTPUT_ARC)
  
'If ARC already exists delete it first
  shArc.Rows(CStr(ROW_INPUT_START) & ":" & CStr(Rows.Count)).Delete
  
'Now two loops run through the relationships between all departments
'start with the first and the relationships to all the others
'then continue with the second
  i = 0
  Do
    'get the department code from the input worksheet
    si = shData.Cells(ROW_INPUT_START + i, COL_DEPARTMENT)
    'set department code to destination worksheet
    shArc.Cells(ARC_TOP + i * 2, ARC_LEFT) = si
    shArc.Cells(ARC_TOP + i * 2, ARC_LEFT).Font.Bold = True
    
    'and the draw the cell borders top and left
    Call DoBorder(shArc.Cells(ARC_TOP + i * 2, ARC_LEFT), xlEdgeTop, xlThick)
    Call DoBorder(shArc.Cells(ARC_TOP + i * 2, ARC_LEFT), xlEdgeLeft, xlThick)
    
    shArc.Cells(ARC_TOP + i * 2 + 1, ARC_LEFT) = shData.Cells(ROW_INPUT_START + i, COL_DEPARTMENT + 1)
    Call DoBorder(shArc.Cells(ARC_TOP + i * 2 + 1, ARC_LEFT), xlEdgeLeft, xlThick)
    Call DoBorder(shArc.Cells(ARC_TOP + i * 2 + 1, ARC_LEFT), xlEdgeBottom, xlThick)
    
    'now format the arrowheads to the right of the department name
    shArc.Cells(ARC_TOP + i * 2, ARC_LEFT + 1) = ""
    Call DoBorder(shArc.Cells(ARC_TOP + i * 2, ARC_LEFT + 1), xlDiagonalDown, xlThick)
    Call DoBorder(shArc.Cells(ARC_TOP + i * 2 + 1, ARC_LEFT + 1), xlDiagonalUp, xlThick)
    
    'if it is not the last department in the list, then run the second loop
    If shData.Cells(ROW_INPUT_START + i + 1, COL_DEPARTMENT) <> "" Then
      j = i + 1
      Do
        'x and y indicate the left and top position of the rating code text
        y = ARC_TOP + i * 2 + j - i
        x = ARC_LEFT + (j - i) * 2
        sj = shData.Cells(ROW_INPUT_START + j, COL_DEPARTMENT)
        
        'now again format cell borders
        Call DoBorder(shArc.Cells(y, x + 1), xlDiagonalDown, xlThick)
        Call DoBorder(shArc.Cells(y, x + 1), xlEdgeBottom, xlThin)
        Call DoBorder(shArc.Cells(y + 1, x + 1), xlDiagonalUp, xlThick)
        
        If shData.Cells(ROW_INPUT_START + j + 2, COL_DEPARTMENT) <> "" Then
          Call DoBorder(shArc.Cells(y + 1, x - 1), xlEdgeTop, xlThin)
        End If
        Call DoBorder(Range(shArc.Cells(y, x - 2), shArc.Cells(y, x + 1)), xlEdgeBottom, xlThin)
         
        'Delete the rating codes here and prepare the formatting
        'WarpText ist essential for display text correctly
        shArc.Cells(y, x) = ""
        With shArc.Cells(y, x)
          .HorizontalAlignment = xlCenter
          .VerticalAlignment = xlBottom
          .WrapText = False
        End With
        Call DoBorder(shArc.Cells(y, x), xlEdgeTop, xlThick)

        shArc.Cells(y + 1, x) = ""
        With shArc.Cells(y + 1, x)
          .HorizontalAlignment = xlCenter
          .VerticalAlignment = xlTop
          .WrapText = False
        End With
        Call DoBorder(shArc.Cells(y + 1, x), xlEdgeBottom, xlThick)
        j = j + 1
      Loop Until shData.Cells(ROW_INPUT_START + j, COL_DEPARTMENT) = ""
     End If
    i = i + 1
  Loop Until shData.Cells(ROW_INPUT_START + i, COL_DEPARTMENT) = ""
  
' With this sub, a routine sets the column widths so that the ARC looks nice
  Call SetArcColumWidth(ColumnTypes.ArcColumn, ARC_WIDTH)

'This routine prepares the input worksheet for statistical evaluations
  Call UpdateReasonCodes

'At the end, switch to the target worksheet and display the ARC
  shArc.Activate
End Sub

The complete source code is available for download at the end of the article.

Part 2: Integrating the workshop functionality

In workshops, we need to focus attention on the two departments being evaluated, with an easy option to select the relationship relevance and the reasons for this. At the same time, participants must be able to see an overall overview.

This suggests that the created Activity Relationship Chart must be shown as an overall overview and, at the same time, separate evaluations of the departments must be visible.

A good way to implement these requirements is to use a VBA dialog form. The workshop leader can place the dialog anywhere and still make both pieces of information visible.

The dialog needs the departments to be evaluated. In my case, these are two frame objects with a label for the department code and the name. You can easily append this by your needs, e. g. for department picture objects.

The evaluations are made using two listboxes. The listbox for the reasons can contain several selected elements. We control this using the MultiSelect property.

Visual Basic Dialog Form for Workshop Evaluation

The events in the dialog provide the functionality for the evaluation.

Option Explicit

' the variable stores the position of the current rating
Dim position As Integer

' first the form is initialized, the two list boxes are filled with the values
' from the input worksheet
' the position is set to the first relationship that has not yet been evaluated
Private Sub UserForm_Initialize()
  Call UpdateReasonListbox(ListBoxSymbol, COL_RATING_SYMBOL)
  Call UpdateReasonListbox(ListBoxCodes, COL_REASON_CODE)
  
  position = FirstEmptyRating()
  Call UpdateView
End Sub

' make the next evaluation and increase the position
' before that, the entries in the listboxes are saved in the ARC
Private Sub CommandButton1_Click()
  Call Save
  If position = RatingsCount() Then Exit Sub
  position = position + 1
  Call UpdateView
End Sub

' back to previous rating
Private Sub CommandButton2_Click()
  Call Save
  If position = 1 Then Exit Sub
  position = position - 1
  Call UpdateView
End Sub

'start a new evaluation, the ARC will also be recreated
Private Sub CommandButton3_Click()
  If MsgBox("Start a new rating? All chart data will be deleted.", vbYesNoCancel, "New rating") <> vbYes Then Exit Sub
  
  Call CreateARC
  position = 1
  Call UpdateView
End Sub


' save current rating in ARC
Private Sub Save()
Dim rating As RatingType
Dim i As Integer
Dim s As String

  rating.department1 = LabelDepartment1.Caption
  rating.department2 = LabelDepartment2.Caption

  For i = 0 To ListBoxSymbol.ListCount - 1
    If ListBoxSymbol.Selected(i) = True Then
      rating.symbol = Sheets(SHEET_INPUT).Cells(ROW_INPUT_START + i, COL_RATING_SYMBOL)
    End If
  Next
  
  s = ""
  For i = 0 To ListBoxCodes.ListCount - 1
    If ListBoxCodes.Selected(i) = True Then
      s = s & Sheets(SHEET_INPUT).Cells(ROW_INPUT_START + i, COL_REASON_CODE) & ";"
    End If
  Next
  If Right$(s, 1) = ";" Then s = Left$(s, Len(s) - 1)
  rating.codes = s
  
  Call SetRating(position, rating)
End Sub

' update the view: read the data from the ARC of the current position
' and display it in the dialog boxes
Private Sub UpdateView()
Dim rating As RatingType
Dim i As Integer
Dim arr() As String
Dim code As Variant
Dim total As Integer

  rating = GetRating(position)
  LabelDepartment1.Caption = rating.department1
  LabelDepartment2.Caption = rating.department2
  
  LabelDepartmentDescription1.Caption = rating.description1
  LabelDepartmentDescription2.Caption = rating.description2
  
  For i = 0 To ListBoxSymbol.ListCount - 1
    ListBoxSymbol.Selected(i) = False
  Next
  
  For i = 0 To ListBoxSymbol.ListCount - 1
    If Left$(ListBoxSymbol.List(i), Len(rating.symbol & " ")) = rating.symbol & " " Then
      ListBoxSymbol.Selected(i) = True
      Exit For
    End If
  Next
  
  arr = Split(rating.codes, ";")
  
  For i = 0 To ListBoxCodes.ListCount - 1
    ListBoxCodes.Selected(i) = False
    For Each code In arr
      If Left$(ListBoxCodes.List(i), Len(code & " ")) = code & " " Then
        ListBoxCodes.Selected(i) = True
      End If
    Next
  Next
  
  total = RatingsCount()
  Frame2.Width = Frame1.Width / total * position
  LabelPosition.Caption = CStr(position) & " von " & CStr(total)
End Sub

Private Sub UpdateReasonListbox(lb As msforms.ListBox, column As Integer)
Dim i As Integer
Dim k As Integer
Dim shData As Worksheet

  Set shData = Sheets(SHEET_INPUT)
  lb.Clear
  i = ROW_INPUT_START
  Do
    lb.AddItem (shData.Cells(i, column) & " " & shData.Cells(i, column + 1))
    i = i + 1
  Loop Until shData.Cells(i, column) = ""
End Sub

A little statistics

An Activity Relationship Chart should contain very few A and X relationships (no more than five percent).

No more than 10 percent should be E
No more than 15 percent should be I
No more than 20 percent should be O
About 50 percent of the relationships should be U

Das in einer Workshop Situation vorzugeben halte ich für schwierig und als Hemmnis. Schließlich sollen Einschätzungen besprochen und gemeinsam entschieden werden. Eine Vorgabe kann das ausbremsen.

Things are different for later use of the Activity Relationship Chart, as it is the basis for optimizing the arrangement. To ensure that basic evaluations are not restricted, the SetRating() method saves all rating data in a list.

Automatically generated data table during evaluation

The sub enters the evaluation data at the specified position in the input worksheet. The summarized reason codes are broken down using the split function and written into individual columns.

Sub SetRating(position, rating As RatingType)
Dim i As Integer
Dim j As Integer
Dim p As Integer
Dim c As Integer
Dim arr() As String
Dim code As Variant
Dim shArc As Worksheet
Dim shData As Worksheet

  Set shArc = Sheets(SHEET_OUTPUT_ARC)
  Set shData = Sheets(SHEET_INPUT)
  
  i = 0
  p = 0
  Do
    If shData.Cells(ROW_INPUT_START + i + 1, COL_DEPARTMENT) <> "" Then
      j = i + 1
      Do
        p = p + 1
        If p = position Then
          shData.Cells(ROW_INPUT_START + p - 1, COL_ARC_DATA) = position
          shData.Cells(ROW_INPUT_START + p - 1, COL_ARC_DATA + 1) = rating.department1
          shData.Cells(ROW_INPUT_START + p - 1, COL_ARC_DATA + 2) = rating.department2
          shData.Cells(ROW_INPUT_START + p - 1, COL_ARC_DATA + 3) = rating.symbol
          shData.Cells(ROW_INPUT_START + p - 1, COL_ARC_DATA + 4) = rating.codes
          
          If rating.codes = "" Then
            shData.Cells(ROW_INPUT_START + p - 1, COL_ARC_DATA + 5) = 1
          Else
            shData.Cells(ROW_INPUT_START + p - 1, COL_ARC_DATA + 5) = 0
          End If
          
          arr = Split(rating.codes, ";")
          c = ROW_INPUT_START
          Do
            shData.Cells(ROW_INPUT_START + p - 1, COL_ARC_DATA + 5 + c - ROW_INPUT_START + 1) = 0
            For Each code In arr
              If CStr(code) = shData.Cells(c, COL_REASON_CODE) Then
                shData.Cells(ROW_INPUT_START + p - 1, COL_ARC_DATA + 5 + c - ROW_INPUT_START + 1) = 1
              End If
            Next
            c = c + 1
          Loop Until shData.Cells(c, COL_REASON_CODE) = ""
          
          shArc.Cells(ARC_TOP + i * 2 + j - i, ARC_LEFT + (j - i) * 2) = rating.symbol
          Call AssignFont(shData.Cells(ROW_INPUT_START + RatingSymbolPosition(rating.symbol), COL_RATING_SYMBOL), shArc.Cells(ARC_TOP + i * 2 + j - i, ARC_LEFT + (j - i) * 2))
          
          shArc.Cells(ARC_TOP + i * 2 + j - i + 1, ARC_LEFT + (j - i) * 2).NumberFormat = "@"
          shArc.Cells(ARC_TOP + i * 2 + j - i + 1, ARC_LEFT + (j - i) * 2) = rating.codes
          Exit Sub
        End If
        j = j + 1
      Loop Until shData.Cells(ROW_INPUT_START + j, COL_DEPARTMENT) = ""
     End If
    i = i + 1
  Loop Until shData.Cells(ROW_INPUT_START + i, COL_DEPARTMENT) = ""
End Sub

If you want to subsequently use the ARC data for methods such as Schmigalla or CORELAP (Computerized Relationship Layout Planning), you need the list form for point evaluations of the relationships in order to create a sequence.

Summary

Activity Relationship Charts are useful tools for planning the layout of factories. They make it possible to take into account other criteria in addition to the material flow. Activity Relationship Charts visualize the relationships between workstations using triangular Mileage charts.

Collecting the relationship data often requires the collaboration of several people involved. This is best achieved through guided workshops.

A self-developed Activity Relationship Chart Generator in Microsoft Excel helps with this, and also conveniently supports joint evaluations in workshops.

Download

Try out download the code and adapt to your needs, it’s free.

Click here to download

Links

Factory Planning by James A. Tompkins, John A. White, Yavuz A. Bozer, J. M. A. Tanchoco
https://books.google.de/books/about/Facilities_Planning.html?id=-xBIq6Qm2SQC&redir_esc=y

Activity Relationship Chart by Wikipedia
https://en.wikipedia.org/wiki/Activity_relationship_chart

Method to Generate Activity Relationship Chart in Facility Layout Problems by Monika Sharma, Ashwani Mor
https://www.ijspr.com/citations/v13n3/IJSPR_1303_144.pdf