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.
![Basic structure of an Activity Relationship Chart with departments and their relationships](https://i0.wp.com/techpluscode.de/wp-content/uploads/2024/06/arc-structure-of-chart.png?resize=1024%2C524&ssl=1)
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](https://i0.wp.com/techpluscode.de/wp-content/uploads/2024/06/arc-relationship-in-detail.png?resize=1024%2C406&ssl=1)
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](https://i0.wp.com/techpluscode.de/wp-content/uploads/2024/06/arc-reasonable-number-of-departments.png?resize=1024%2C382&ssl=1)
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.
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](https://i0.wp.com/techpluscode.de/wp-content/uploads/2024/06/arc-excel-aufbau-0.png?resize=1024%2C622&ssl=1)
![Possible visual implementation with frames, but with an unsightly text alignment problem](https://i0.wp.com/techpluscode.de/wp-content/uploads/2024/06/arc-excel-aufbau-1.png?resize=1024%2C770&ssl=1)
![Introducing an extremely narrow column for text alignment](https://i0.wp.com/techpluscode.de/wp-content/uploads/2024/06/arc-excel-aufbau-2.png?resize=1024%2C513&ssl=1)
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](https://i0.wp.com/techpluscode.de/wp-content/uploads/2024/06/arc-flexible-input-lists.png?resize=1024%2C772&ssl=1)
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](https://i0.wp.com/techpluscode.de/wp-content/uploads/2024/06/arc-vba-dialog-form-for-workshops.png?resize=1024%2C711&ssl=1)
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](https://i0.wp.com/techpluscode.de/wp-content/uploads/2024/06/arc-automatic-generated-statistics.png?resize=1024%2C772&ssl=1)
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