Assembling station modelling in Excel VBA

Ihor Savchenko1, Peter Novotny1 and Herbert Fleck2

1
FH Joanneum Kapfenberg, Werk-VI-Strasse 46, 8605 Kapfenberg, Austria
[email protected]

2
HITEL GmbH, Redtenbachergasse 25/1/12, 1160 Vienna, Austria
[email protected]

Abstract. The paper touches the subject of assembling station modelling as a

part of a much more complex discipline of enterprise modelling. A simple
model of a single assembling station is described. The model is based on Excel
VBA capabilities and is able to simulate the consequent fulfillment of
production orders. An order can be produced only when a set of certain
conditions (the station is free, the worker is ready, there are enough materials,
the working shift is not over) is met. Various production and logistical delays
influence the outcome. A formula to calculate and depict the proper working
time depending on the shift start, shift end and lunch breaks is introduced. The
resulting model can be used as a basic unit for a full-scale production process
modeling. An example of practical use of the model is given, as well as the
ways to expand and enhance the model.

Keywords: enterprise modelling, assembling station, Excel VBA, working

shift, materials, products, formula, algorithm, discrete event simulation.

1 Assembling station in the context of enterprise modelling

A significant branch of information technology and computer science is enterprise

modelling (EM). There has been a number of definitions for this term, one of the most
comprehensive given by Bubenko and al.: it is the process of creating an integrated
enterprise model, which captures the aspects of the enterprise required for the
modeling purpose at hand. An enterprise in this context can be a private company,
government department, academic institution, other kind of organization, or part
thereof. An enterprise model consists of a number of related sub-models, each
focusing on a particular aspect of the enterprise, e.g., processes, business rules,
concepts/information, vision/goals, and actors. An enterprise model describes the
current or future state of an enterprise and contains the commonly shared enterprise
knowledge of the stakeholders involved in the modeling process [3]. According to
Vernadat, EM is dedicated to representing and describing the structure, the
organization and the behavior of a business entity to evaluate its performances,
reengineer its various internal and external flows or optimize them in order to make
the enterprise more efficient and effective [9]. Sandkuhl and Stirna present a list of
2

typical EM tasks; searching for needs for change, understanding organization

dependencies and improving business processes are in this list among others [7].
Additionally, Horkoff and Jeusfeld name EM as the important connecting link
between proper business activities and IT support at the enterprise. «EM addresses
business-IT alignment in a holistic manner by providing the techniques, languages,
tools and best practices for using models to represent organizational knowledge and
information systems from different perspectives» [5].
For decades, EM remains one of the most important and quick-developing
branches of the modern IT science. As Vallespir notes, since the 1970s, EM has
developed into an effective methodological source for improving business
performance [8]. The more competitive and complicated the business world becomes,
especially in developed countries, the higher the demand for accurate and easily
understandable enterprise models. They are our means for explicating, agreeing and
managing information in a well-defined and structured manner. Without a model, it
would become very difficult to manage vast amounts of information in a coherent
manner and come to any form of common understanding [1]. As the modern
enterprise includes many centers of activity and can be presented from different
points of view, the models themselves can differ greatly, depending on their purpose.
Still, main production activity, or an activity creating actual value, remains the
integral component of a business.
The whole idea of business process modelling grew out of Frederick Taylor’s
then-innovative techniques and methods to evaluate manual labor and production
activities [4]. Nowadays, manufacturing systems are among the most complex and
difficult to model and analyze, requiring many different methods. Computer
simulation methods, especially discrete event simulation (DES), are the most
universal and are widely used. Although there are many DES software tools dedicated
to production process simulations, for example ARENA, Enterprise Dynamics,
PlantSim and FlexSim [2], at the start of a university course dedicated to enterprise
modelling, it is logical to begin with the simplest objects (like an assembling station)
and easily understandable presentations (like Excel tables). Despite its assumed
simplicity when compared to specific simulation software, Excel proved to be a
powerful tool for DES, as Matson and Elizandro show [6].
In this paper, authors describe a sub-model of an assembling station created in
Excel VBA environment, with a purpose to use it as an introductory tool for technical
university students (freshman course). The model was based on following
assumptions:
• the assembling station is a single independent manufacturing unit, existing in a
complex enterprise environment;
• the main goal of the station is to create added value for the enterprise, taking
«cheap» raw materials and creating «expensive» final products;
• the production process is presented in the form of consequent production
orders, different in product type and amount;
• the production process for a specific order can be divided into a number of
typical operations like loading the materials, station setup, taking away the
finished products, etc;
 3

• the station is semi-automatic, being served by a person (worker) performing

necessary operations;
• the worker operates within a specific range of working hours (working shift),
set by the simulation user;
• the materials arrive at specific moments, set by the simulation user, either
manually or regularly by a formula;
• the process cannot start while the necessary materials are absent;
• the process cannot start while the station is busy with another order;
• the process cannot start outside the working shift.

2 Assembling station modelling

2.1 Schedule table and delay factors

The basic timetable for the assembling station lists production orders vertically, while
the elements of the production process — horizontally. These elements can be either
points in time or durations. The following elements are present:
• production start (point);
• transport of materials (duration);
• logistical delays (duration);
• predecessor element delivered (point);
• setup time for an assembling station (duration);
• setup finished (point)
• processing (duration);
• technical disruptions (duration);
• processing finished (point);
• transport of a product/finished element (duration);
• workman is ready for the next task (point);
• product delivered to a customer/semi-finished product transported to the
next production stage (point).
There are two types of explicit delays: logistical and technical. To define their fre-
quency, an additional column calculating the cumulative sum of materials delivered
(products manufactured) can be introduced. Every time this sum reaches a number
divisible by a specified quantity, a delay occurs. Practically, this means a disruption at
the warehouse or a machine break after every X units delivered/produced, on average.
Additional implicit delay is connected with a human factor/machine amortization.
These two parameters combined influence the speed of the worker’s and the station’s
actions, accordingly. Used in the calculations as the coefficient of 1 or higher, human
factor and amortization prolong the processing time depending on the level of em-
ployee training (for human factor) and machine condition (amortization).
The main goal of the calculations is to define starting points for every production
order. Other elements of the schedule table are calculated based on that value.
4

2.2 Working shift adjustment

Initially, during the simulation, times in the model are displayed in a [hh]:mm:ss
format, i. e. using over-24-hour «endless» calculation. It is necessary as the model
usually simulates greater periods than 1 day, but at the same time is not tied to the
concrete year and month. Table 1 shows an example of this time format, presenting
production start times of 5 consecutive orders.

Table 1. Production start times

Production order Start time

1 8:00:00
2 22:40:00
3 37:20:00
4 52:00:00
5 66:40:00

The format is not user-friendly. It is hard to guess which moments in real time
correspond with the table data. There, the time starts at 00:00:00 and goes without
interruptions. Basically, the table shows the total number of passed working hours (as
if the enterprise worked 24/7 uninterrupted) plus 8 hours before the start of the first
day’s shift. However, the model requires that the working shift at the enterprise last
from 8:00 to 16:30, with a 30-minute lunch break from 12:00 to 12:30. Moreover, the
enterprise is planned to operate 5 days per week. Outside of this range, no working
operations are possible. The shift parameters are presented in the table 2.

Table 2. Working shift parameters

Event Value
Shift start 8:00:00
Shift end 16:30:00
Lunch break start 12:00:00
Lunch break duration 0:30:00
Working days per week 5
Correction 0:00:00

In a decimal format, correction is displayed as 0,0000001. In the Excel time

format, it is invisible (less than a second).
To adjust the internal Excel time data to the user’s needs, a specific formula is
implemented, with corresponding cells shown in <angle brackets>:
 5

=IF(<order number>="","",CONCATENATE
("Week ",TRUNC((TRUNC((<internal time>-<shift start>)/
(<shift end>-<shift start>-<break duration>)+<correction>))/
<working days per week>)+1,
", Day ",MOD((TRUNC((<internal time>-<shift start>)/
(<shift end>-<shift start>-<break duration>)+<correction>)),<working days
per week>)+1,", ",
TEXT(MOD((<internal time>-<shift start>+<correction>),<shift end>-<shift
start>-<break duration>)+<shift start>+IF(MOD((<internal time>-<shift
start>+<correction>),<shift end>-<shift start>-<break duration>)+<shift
start>><break start>,<break duration>,0),"hh:mm:ss")))

The formula is applied only to the non-empty order lines. It uses CONCATENATE
to create a final version consisting of three elements: week number, day number and
time. The number of full passed weeks is calculated by dividing the internal time by
the daily shift duration, then by the number or working days per week. To exclude the
current week data from the calculation, TRUNC is used. At the end, 1 is added to the
number of full passed weeks to display the current week. The number of full passed
days of the current week is calculated as the remainder (MOD) of the division
described above. At the end, 1 is added to the number of full passed days to display
the current day. Finally, the current time is calculated as the remainder of the division
of internal time by the daily shift duration. If the result is higher than 12:00:00 (start
of the break), then it is automatically prolonged by the duration of the break (i. e. the
event calculated to happen at 12:15 actually happens at 12:45 because of the 30-
minute break). During the calculation of all three elements, a correction is added to
avoid the ambiguity of «borderline» time moments like 08:00:00 and 16:30:00.
The same production start data as in the table 2, but recalculated for users and
presented in the real-time format, are shown in the table 3.

Table 3. Production start times (real time)

Production order Start time

1 Week 1, Day 1, 08:00:00
2 Week 1, Day 2, 15:10:00
3 Week 1, Day 4, 13:50:00
4 Week 2, Day 1, 12:30:00
5 Week 2, Day 3, 10:40:00

2.3 VBA algorithm

The VBA algorithm used in the model addresses the production process as the
sequence of discrete events. The model calculates starting time points for every order,
6

from top to bottom. For every order, the model establishes a connection with the list
of materials delivered in the simulation period. (An order may require more than one
delivery). In general case, the starting time point is calculated as the maximum of the
following variables:
• shift start;
• time of the latest material delivery;
• end time of the previous order.
When the first order is simulated, third condition is excluded. When there are
enough materials present at the start of the simulated period, delivery is unnecessary
and the second condition is excluded. The algorithm in pseudocode is presented
below.

For all elements in a list

LatestReadyTimePoint = 0
If ElementName = «Product» Then
DeliveryTimePoint = time of the latest delivery
StartTimePoint = shift start
For all elements in the list
If Material (element in the list) = actual element
If LatestReadyTimePoint (Material) > LatestReadyTimePoint Then
LatestReadyTimePoint = LatestReadyTimePoint(Material)
LatestReadyLine = line with latest ready time point
End If
End If
Next element in the list
If LatestReadyTimePoint > 0 And LatestReadyTimePoint < DeliveryTimePoint Then
' All materials already present, delivery times are not important
' ----------------------------------------------------------------------------------
DeliveryTimePoint = StartTimePoint
End If
If SequenceNumber(actual element) = 1 Then
' If this is the first production order, then use regular starting point instead of latest ready
time point
' ----------------------------------------------------------------------------------
ActualStartingPoint = maximum(DeliveryTimePoint, LatestReadyTimePoint,
StartTimePoint)
Else
' If not the first production order, then
ActualStartingPoint = maximum (DeliveryTimePoint, LatestReadyTimePoint,
StartTimePoint, PreviousProductionCompleteTimePoint)
End If
 7

3 Practical use and further development

The assembling station model allows to accurately simulate a simple production

process under different conditions (such as parameters of a working shift and
availability of workers and materials). The model can be used both separately and as a
part of a more complex system. For example, the current variant can be enhanced by
adding another module simulating the corresponding warehouse activities: delivery of
materials, collection of finished products, etc. The assembling station model is
currently used in the experimental UKoSim software (Unternehmen-Kosten-
Simulation, “Simulation of enterprise costs”) developed by the team of researchers in
FH Joanneum (Kapfenberg, Austria). Within UKoSim, different combinations of
interdependent stations is already created, in which one station’s products become the
materials for the next station’s processing. UKoSim’s current main function is
educational, the software being used for training students in the branches “Business
process management” and “Supply chains”. Based on the multiple input data,
including assembling station elements, UKoSim calculates the production costs and
predicts financial results. For example, delays in assembling result in late deliveries
and penalties or reclamations, thus impairing the overall results (see Appendix).
At the next step, the developers plan to introduce UKoSim to the real industrial
process, in the branches of mechanical engineering, robotics and automation. In the
model itself, the most urgent improvement discussed is the implementation of the
“time stamp” function. According to the developers’ plan, after entering the time and
date in <week X, day Y, hh:mm:ss> format, the model will present a short summary
of the assembling process at the entered moment: the number of fulfilled orders, the
order in production, the amount of materials used/left, etc. This is achieved by
comparing the entered time to the starting and ending times of each activity for each
order. Another significant enhancement would be a calculation and comparison of
«machine involvement» and «human involvement» in the manufacturing process,
based on time spent by the worker and the station itself. It allows to assess the
technical development level of the enterprise and to take necessary actions for
production process optimization.
First results of UKoSim usage, along with the assembling station model, were
presented at the 7th International Conference on Computer Technology Applications
(Vienna, 2021).
8

Appendix

Fig. 1. A fragment of UKoSim results interface (in German). Marked is the line
«Pönalekosten» (penalty costs). These costs are based directly on delivery delays,
caused among others by production delays at the assembling stations.
 9

References
1. Aldin, L., de Cesare, S. A literature review on business process modelling: new frontiers
of reusability. In: Enterprise Information Systems, pp. 1–25. Taylor & Francis, Uxbridge,
UK (2011).
2. Barosz, P., Golda, G., Kampa, A. Efficiency Analysis of Manufacturing Line with
Industrial Robots and Human Operators. Applied Science, 10(8):2862 (2020)
3. Bubenko, J., Persson, A., Stirna, J.: User guide of the Knowledge Management approach
using Enterprise Knowledge Patterns. Royal Institute of Technology (KTH) and
Stockholm University, Stockholm, Sweden, (2001).
4. Jeston, J.: Business process management: practical guidelines to successful
implementations. Elsevier, Oxford, UK (1993).
5. Horkoff, J., Jeusfeld, M. Enterprise Modeling for Business Agility. Business &
Information Systems Engineering, 60(1), 1-9 (2018).
6. Matson, J., Elizandro, D. Discrete Event Simulation Using Excel/Vba. In: 2005 ASEE
Annual Conference proceedings, pp. 1-11. American Society for Engineering Education ,
Portland, USA (2005).
7. Sandkuhl, K., Stirna, J.: Enterprise Modeling: Tackling Business Challenges with the 4EM
Method. Springer-Verlag, Berlin, Germany, (2014). DOI: 10.1007/978-3-662-43725-4
8. Vallespir, B. Model-based enterprise continuous improvement. In: Lean Manufacturing,
pp. 1-19. DOI: 10.5772/intechopen.96856
9. Vernadat, F. Enterprise modeling in the context of enterprise engineering: State of the art
and outlook. International Journal of Production Management and Engineering, 2(2), 57-
73 (2014).