Tool for the Emulation

of a Supply-Chain
Manual

This document serves as manual for the operation of a tool

that is capable to simulate, to analyse and to emulate
supply-chains and their contents.

Wolfgang Bartl, Wolfgang Burgholzer

11.10.2012
Content
1. General facts and assumptions ....................................................................................................... 4
1.1. Description of the emulated company .................................................................................... 4
1.2. Technical preparations and notes ........................................................................................... 4
1.3. How to work with the tool? .................................................................................................... 5
1.4. How to use the tools via the TAVR-server............................................................................... 6
1.5. Overview of the company’s divisions ...................................................................................... 7
2. INPUT file ......................................................................................................................................... 8
2.1. Settings .................................................................................................................................... 8
2.2. Activities ................................................................................................................................ 11
2.3. Products................................................................................................................................. 13
2.4. Materials................................................................................................................................ 14
2.5. Employees ............................................................................................................................. 14
2.6. Production process ................................................................................................................ 15
2.7. Bill of materials ...................................................................................................................... 16
2.8. Production orders.................................................................................................................. 16
2.9. Layout .................................................................................................................................... 16
2.10. Warehouse ........................................................................................................................ 17
2.11. Order processing ............................................................................................................... 18
2.12. Suppliers ............................................................................................................................ 19
2.13. Procurement...................................................................................................................... 19
2.14. Sourcing ............................................................................................................................. 19
2.15. Customers.......................................................................................................................... 20
2.16. Distribution ........................................................................................................................ 20
3. Output-File .................................................................................................................................... 22
3.1. Production flow ..................................................................................................................... 22
3.2. Supply – Demand Match ....................................................................................................... 23
3.3. Key Performance Indicators (KPIs) ........................................................................................ 23
3.3.1. General definition of terms ............................................................................................... 24
Time figures ............................................................................................................................... 24
Quantity figures ......................................................................................................................... 26
3.3.2. KPIs for the Analysis of Employees .................................................................................... 27
3.3.3. Key Production Figures .................................................................................................. 27
3.3.4. Key logistics and process figures ................................................................................... 32

2
 3.3.5. Key corporate income statement figures ...................................................................... 33
3.3.6. KPIs at a glance .............................................................................................................. 34
Bibliography........................................................................................................................................... 36

3
 1. General facts and assumptions

1.1. Description of the emulated company

The intention of this tool is to emulate the relevant areas of a company’s value-added chain.
This should serve the possibility of displaying the internal creation of value as well as the
external relations with customers and suppliers. Therefore, different scenarios or strategies -
based on individually designable, real companies - can be emulated and analysed. This
manual gives an in-depth description of the usage and the possibilities of this tool.

The tool was modelled after a company that processes raw materials into finished products,
but it is also able to being applied to companies in the service sector. To emphasize the
general range of the application, for example, the productive stations have been denoted
"activities". As terminology should be simplified, however, in many cases terms from the
industrial sector ("production", "manufacturing", "assembly", “workman”, etc.) are used.

The tool combines the information entered and constitutes the entire supply chain of the
company, starting with the customer's order until the actual delivery of the final products to.
To analyse the process, a selection of relevant key figures is calculated. Based on that, the
opportunity to show the effects of changes in the parameters to the value-added chain is given
as well as the central part of the tool.

1.2. Technical preparations and notes

The extensive calculations are performed by means of the program "R". "R" has been
developed specifically for statistical computing and is perfectly suited for the complex
calculation procedures which the evaluation of the data of the INPUT file requires. The
current version of the program (version 2.15.1) can be downloaded from the website www.r-
project.org for free and should be installed on the hard disk of the computer, in the directory
"programs". To use the INPUT and the OUTPUT file, Microsoft Office Excel 2007 or a
higher version should be available.

The INPUT and the OUTPUT file as well as the folder “Calculation” are forming a unit and
may never be copied separately but only in conjunction with each other. Additionally, if there
are made entries in the INPUT file input, the OUTPUT file always has to be opened too, so
that changes and references are updated automatically and simultaneously. After opening the
Excel files it has to be ensured that Excel macros are not disabled. This can be made by

4
choosing „settings for macros” in the trust center (menu tools, macros security) by selecting
the option “enable all macros”.

In addition, the separators of Excel have to be adjusted so that the figures that are exported to
the calculation program already have the correct format. The adaptation is done in the Excel
options under "advanced" in the first section. Here the decimal separator has to be set to
"point" (.) and thousands separators to "comma" (,).

At last, the column separator for CSV-text files has to be set to “semicolon” (;). This can be
done by following the instructions of Figure 1.

Figure 1: Instructions to set column separator for CSV-files to a “semicolon”.

1.3. How to work with the tool?

Handling

For the user, the relevant tools are the INPUT file (INPUT.xlsm) and the OUTPUT file
(OUTPUT.xlsx). As the name suggests, in particular the INPUT file is intended to represent
the individual supply chain of the user.

The INPUT file is divided into the various areas of the supply chain. All cells with blue,
purple or green shading are input fields and have to be completed by the user. Blue cells
require manual input, while purple fields demand a selection from a drop-down menu. Fields
marked with green shading are either on the right side or below a blue cell and have primarily
the same value as the blue cell. Due to this support the user hasn’t to enter equal contents for
several times, but nevertheless he might overwrite the figures individually.

5
After the entries in the INPUT file have been made (detailed information can be found in the
following chapters), the user should press the left button to allocate the unplanned disruptions
to the production orders at random. By operating the middle button "Load inputs and
start”R”", the input data is uploaded to "R", where the optimisation of the value creation
process is executed. To start the process of emulation, the required file path (cell C13) and the
source code itself (cell C14) have to be copied from the worksheet "Settings" to "R".

After this, "R" starts the emulation process automatically and loads the results into stored
files. After pressing the button “Load results from “R” ”, the results are forwarded to the
OUTPUT file. The OUTPUT file contains all necessary information for the user to analyse
and compare different scenarios. Finally, "R" has to be closed manually.

File structure

The file structure for every scenario has to look as in Figure 2. Each scenario has to contain of
an INPUT as well as an OUTPUT File and the folder “Calculation”. The folder “Calculation”
contains the R-code, which is triggered by the INPUT-file (see above). In order to develop
new scenarios, you simply copy both files as well as the folder “Calculation” (see Figure 1)
into a new directory. Then you can insert the specifics of this new scenario into the INPUT
file.

Figure 2: The INPUT and OUTPUT-File as well as the folder “Calculation”

1.4. How to use the tools via the TAVR-server

To allow access to the tool via the Internet, the package has been installed on the server of the
"Vienna Region Academy of Engineering (TAVR)". On Windows 2007 or a higher version,
the access is received by clicking the Windows Start button (bottom right), "Programs" -
"Accessories" - ("System Tools") - "Remote Desktop Connection". Alternatively, the user can
find the access by using the search function of the Windows Start menu.

6
 Figure 3: Enabling Remote Desktop Connection

To establish the connection to the TAVR-server it has to be entered: Computer

"tavr.homedns.org:52139" and username "V-LLU-PC-R \LLU-User”.

Now the login window for the TAVR-server appears, by which the access can be established
by entering the user name "LLU-user" and the password "llu".

Figure 4: Login window for the TAVR-server

The tool is saved on the hard disk (C :) of the TAVR-server in the directory "Emulation Tool"
and can be started from there or from the shortcut on the desktop.

1.5. Overview of the company’s divisions

The considerations start with the sourcing division. The user might enter detailed information
about the suppliers (worksheet “Suppliers”), the procured materials (sheet “Materials”), the

7
conditions in the purchasing department (worksheet “Procurement”) as well as – on the base
of all of this processes - the individual orders themselves (worksheet “Sourcing”).

Based on parts lists and production plans (worksheets “Production process”, “Products” and
“Bill of materials”), the materials procured are fed into the value creation process. There, the
individually identifiable production orders (worksheet “Production orders”) are supplied with
the necessary materials through the integration of existing resources (worksheets
“Employees” and “Activities”), in consideration of the specific definable layout of the
company (worksheet “Layout”) and the warehouse management (sheet “Warehouse”). Further
conditions, such as the IT-support or some specifications of the value-added process, can also
be adjusted in the worksheets “Settings” and “Order processing”.

The sales of manufactured products is, similar to the sourcing process, emulated by the
connection of the customers (worksheet “Customers”) with the final products (worksheet
“Products”) to individual customer orders (worksheet “Distribution”).

2. INPUT file

2.1. Settings

In the table "Settings" general information can be stored. At the top of the sheet the user can
select via a drop-down menu which IT-system (see "IT-models") the pictured company uses
(cell B3). Cell B4 then tells the user whether the employees are pre-informed (which goes
along with IT-models 2-4) about the needed materials. In case of being pre-informed, the
employees can prepare the needed materials in advance. The rate in cell B6 is used to
calculate the opportunity costs of the capital invested in inventory.

Using a drop-down menu, in the following information block can be selected,

 whether intermediate products should be stored in the production storage or directly at

the activities (cell B8),
 who should perform the transport activities between the activities (cell B9), and
 who should carry out transport activities between the receiving-, the production- and
the outbound-warehouse (cell B10).

The bottom block of information contains the location where the INPUT and the OUTPUT
file are stored (the working path, cell C12). The two cells below (C13 and C14) provide the

8
commands that have to be copied into “R” for the start of the calculations. In cell C15 the user
has to enter the location, where the executable file of "R" is stored on the computer. The file
path is received by pressing the “Windows Start button” (bottom left) and entering the string
“R x64” (64 bit version) or "R i386" (32 bit version) in the window "search programs / files".
Among the results has to be a program called “R x64” ("R i386") including its version
number. With right-click on the search result and selecting "properties", the user can go
directly to the destination of the program that has to be copied and pasted into cell B15. For
example, the valid file path on the TAVR-server is: “C:\Programme\R\R-
2.15.1\bin\i386\Rgui.exe”. It should be noted that the file path has always to be in quotation
marks.

IT-models

Companies may use very different levels of IT-support for the handling of their administrative
needs. The spectrum ranges from the use of an Enterprise Resource Planning (ERP) - system
(e.g.: SAP) in several departments up to full IT-support with the additional integration of a
Management Execution System (MES).

Simultaneously to the IT-support, the technical infrastructure and the structure of the
warehouse management are increasing. Order processing, for example, can be carried out
paper-based or by using a scanner. There can be used bin locations, the material flow can be
based on production orders or be determined by the needs of planned and customer orders.

Of the four models described below, for the emulation the model should be chosen that
corresponds best with the characteristics of the IT-support of the company displayed.

IT-model 1: ERP used for order processing and warehouse management

Under model 1, IT-support is used at the lowest level. Only the order processing
department and the warehouse have access to the ERP-system. In the first-named
station, the recording of customer orders, the availability check of stocks and the
confirmation of the delivery dates requested by the customers, as well as the release
and the deregistration of the production orders are handled electronically. The
warehouse records the inflows and outflows of raw materials, intermediates and final
products also in the ERP-system. All other processes are carried out paper-based by
using printed production orders, control cards, goods requisition slips and completion
confirmation slips. Also the operations management is working without IT-support on

9
checking capacities/sequencing, checking the documents and analysing production
activities.
Regarded as a whole, the physical transmission and especially the balance of the
processed orders with the operations management in terms of available capacities
turns out to be extremely costly in terms of time. In addition, possible delays at the
goods provision of the warehouse and the only paper-based manufacturing analysis
extend the time required for processing.
IT-model 2: ERP used for order processing, operations management and warehouse
management
Under IT-model 2, the order processing department, the operations management and
the warehouse are integrated into the ERP-system. Now checking
capacities/sequencing and releasing the production orders can be executed
electronically by the operations management. Additionally, delays at the goods
provision of the warehouse should be minimized as the warehouse gets early
information from goods provision slips about the upcoming needs. After the
manufacturing process, the operations management checks the documents and
deregisters the orders in the ERP-system. Finally, the analysis of the production
process is performed with IT-support too.
IT-model 3: ERP used for order processing, operations management, warehouse
management and assembly
At this level, the order processing department, the operations management and the
warehouse, but also the acivities, have access to the IT-network. Registration,
deregistration and documentation of the production orders via ERP-system are made
directly in the assemblies. The warehouse will be informed automatically from the IT-
system, therefore the required materials can be picked in advance by using goods
provision slips. This should grant the timely supply of the required materials at the
respective activities.
IT-model 4: Additional integration of MES in operations management und assembly
Under IT-model 4, order processing and warehouse are working with an ERP-system,
while for the operations management and the assemblies an MES is available. The
MES is interfaced with the ERP-system and allows the management, the controlling
and/or the monitoring of production in real time. Now the order processing department
checks the availability of stocks in the ERP-system, confers with the operations
management, creates the production orders and uploads them into MES. At the same

10
 time the warehouse will be informed automatically from the ERP-system. The
production orders are registered, deregistered and documented in the MES directly in
the assemblies. Then the orders are uploaded into the ERP-system. Finally the analysis
of the production activities is carried out by using the information from the MES.

The buttons

Button “Allocate unplanned disruptions randomly”

By clicking the button "Allocate unplanned disruptions randomly", unplanned
disruptions at each activity are determined randomly. In order to make different
scenarios of the same supply chain comparable, this setting should not be not be
changed. Therefore the button should be pressed only once and that is after the initial
input of the data in the INPUT file.
Button “Load inputs and start “R””
This process converges the input data into the format required for "R" and starts the
program, which then performs the calculations.
Button “Load results from “R”“
This button should be operated after the execution of the calculations. Therewith the
data is converted back from “R” and uploaded into the OUTPUT file, which finally
displays the final results and key figures.

2.2. Activities

This sheet contains a listing of all relevant time figures and factors for up to 20 activities.
Object of contemplation doesn’t necessarily have to be mechanical assembly necessarily but
can also be purely manual work or services. In column C the activities can be named by the
user according to individual preferences.

Activity time (cells G4 – Z4)

The activity time, measured in seconds, represents the time it takes to perform the value-
adding activity. To receive the most accurate representation of the activity process, this time
has to be entered for all products individually. If this value is fed only for the first product, for
example if the activity times of all products are equal or negligibly different, or if just a
simplified handling of the input is desired, this information is automatically taken from the
first to all other products.

11
Transfer time (cells G5 – Z5)

During the transfer time (in seconds), the manufactured product is forwarded to the measuring
station and is prepared for the measurement process. Since a workman can’t process any other
product during this process, the transfer time is part of the needed time for the activity
process. Like the activity time, the user can fill in the data for all products at once or for any
or all products individually.

Measuring time (cells G6 – Z6)

The measuring time (in seconds) describes the time which is required for checking the
manufactured product to meet predetermined parameters or quality standards. Errors or
deviations can occur either through damaged products (see "Irreparable errors"), or due to
incorrect assembly (see "Repairable errors").

If the test is performed by a machine, the workman can already produce the next product
during this process. If the workman carries out the test manually, the measuring time has to be
included by the user into the transfer time. The value of the measuring time (cell G6) then has
to be set to 0. Also the measuring time can be entered for all products at once or for any or all
products individually.

Set-up time of activity (cell F7) und Set-up time of measuring station (cell F8)

If the production of different products demands adaptations at the assembly and/or at the
measuring station, the additional time requirements are taken into account by the input of set-
up times (in seconds) each per order. It is assumed that set-up times for different products
don’t differ within an activity.

Figure 5 shows the relationship between the activity time (ACT), transfer time (TRT),
measuring time (MET) and set-up time (SUT) of two production orders, each with two units
to be produced.

Figure 5: Activity time (ACT), transfer (TRT), measuring (MET) and set-up (SUT)

12
Irreparable errors (cells G9 – Z9)

Products that are damaged during the activity process will be identified by the measuring
station and removed from the manufacturing process as "damaged parts". They increase the
quantity of required materials. Their share in each order is specified with a probability of
occurrence.

Repairable errors (cells G10 – Z10)

Intermediate or final products, that have been assembled incorrectly, are either identified by
visual check of the workman or by the measuring station. They are not removed from the
process, but they have to be disassembled and re-manufactured. This doesn‘t increase the
quantity of the required materials, but extends the overall processing time. The occurrence of
incorrectly assembled products is rated by a probability too.

Unplanned disruptions (cell F11)

During the process, unpredictable interruptions caused by power failures, damage to

machinery, the short-term failure of a workman, etc., can occur. Since the time of occurrence
of disruptions is uncertain, they will be allocated by random to any production order of each
assembly (see also “1.3 How to work with the tool?”). Adding the duration of unplanned
disruptions to the production time, the occupancy time of an activity is obtained.

Planned shutdowns (cell F12)

This input considers the time a production unit for a specific activity is, in addition to the
unplanned disruptions, not available for the process. Planned shutdowns can be used for
maintenance. The operating time of a production unit, less the length of the planned
shutdowns, is the planned occupancy time.

2.3. Products

This table contains a list of up to 20 final products which the emulated company may produce
(cells B4 - B23). Furthermore, the basic selling price (cells C4 - C23) and the internal
transport quantity (cells D4 - D23) of each product are to be filled in. The internal transport
quantity indicates how many units of a product can be transported simultaneously.

13
 2.4. Materials

In this sheet, up to 40 raw materials, which the company requires for the manufacturing
process, can be entered in cells C4 – C43. In column D the user can feed the initial inventory
of each raw material, intermediate and final product. Using this data and a sequential number,
an inventory-ID is created in column I.

Column AM (»Intermediates«) automatically shows all possible intermediates. Also for them,
the initial inventory (column AP) and internal transport quantity (column AQ) can be inserted.
Using this data and a sequential number, an inventory-ID is created in column AR.

From the table "Procurement", the average cost per unit in stock is calculated and displayed in
column E. These values serve as references and can be modified by the user, for example if
prices change significantly. Columns F and G show the average cost of direct procurement
and procurement by a central warehouse

Finally, in column H the user has to quote the internal transport quantity of each product.

2.5. Employees

This directory contains a listing of all employees that are involved in the supply chain. These
include warehousemen, workmen, carriers (for internal transport activities), operations
managers and employees of the order processing department. If certain activities remain
vacant (number = 0 or blank), it has to be assumed that their responsibilities have to be carried
out by other departments. For each area of activity, the user has to specify the number of the
employees involved (cells C4 – G4), their scheduled daily working time (cells C5 – G5) as
well as the scheduled start of work (cells C6 – G6). In cells C7 – G7 and C8 – G8, the length
and the start time of the break have to be completed. The sum of working hours and breaks
results in the total attendance time. The labour costs (cells C9 – G9) and overtime premium
(cells C10 – G10) are required for the final calculation of the total production costs. The speed
(cells C11 – G11), in this case the walking pace of the employees, affects not only all
transfers of information that cannot be done by phone or computer, but also the totality of all
internal transportation activities. It should be considered that transportation activities are
mostly carried out one half loaded and the other unloaded. For this reason here should be
chosen an average speed. In this block of information, the data from column C, except the
number of employees and their labour cost, is copied in columns D through G. Alternatively
these cells can be completed individually.

14
At the bottom of the table (cells C15 to G34) there can be found the employee list which
contains the names of all employees. Originally they are named by the denomination of their
employee-type and a sequential number, but they can also be designated by the user
individually.

2.6. Production process

The overview of the production process displays all operating activities (column B) and
combines them with their order within the process as well as the needed resources in order to
fulfil the activities (needed material in cells E4 - X4; needed employees in column D).

Process step (cells C6 – C25)

In this area the user can select the sequence of the processes from a drop-down menu.
Processes that need no intermediates from each other can also run in parallel and therefore
may have the same rank.

Required employees (cells D6 – D25)

Column D requires the selection from a drop-down menu, in order to determine which
employee should execute the respective activity.

Required materials (cells E6 – X25)

In this table all raw materials and intermediates are quoted, which are required in the
respective assembly for the production of up to 20 final products. Basically, within each
activity this information is copied from the first product (column E) to all others. If desired,
though, these entries can be made individually for some or all products.

These entries have to be made in the format: „raw material_quantity“ respectively

„intermediate_quantity“. If several different materials are used, they have to be separated by
comas (,) from each other. Intermediates are denominated with the abbreviation "ZW" and the
name of the activity where they have been manufactured. For example, „A_100“ would mean
that for the processing of one unit of a product, 100 units of the raw material „A“ are required.
With „B_50,ZWRotating_10“, 50 units of the raw material „B“ and 10 units of the
intermediate manufactured in activity “Rotating” are needed. When inputs have been made in
a wrong format or contain invalid raw materials or intermediates, a red-deposited note
appears in cell E5.

15
Final product (cells E26 – X26) Kommentar [WB1]: Bin mir da noch
nicht sicher!

These cells display the activity, in which the final product is finally completed. The figures
may be modifyed by the user just as well, for example if a product hasn’t to pass the entire
manufacturing process on a customer's request.

2.7. Bill of materials

This matrix contains an accurate overview for the user of the raw materials needed to
manufacture the respective final products.

2.8. Production orders

This spreadsheet allows the user to determine the planning of the value-adding process, that is
to state the sequence and type of the production orders. In this table for each activity up to 20
production orders can be stored. First, the product to be processed has to be selected from a
drop-down menu (column D), then the production quantity (column E) and the share of the
manufactured products that should be measured (column F) have to be completed. With this
data and a sequential number, every production order is automatically denominated with an
ID as shown in column C. Column F („%measuring“) quotes the percentage of the produced
quantity per order that should be checked for errors and defects. If only the first cell (F4) is
filled, this value will be copied to all other production orders. Nevertheless, the portion that
should be measured can also – if desired - be entered individually for some or all orders.

2.9. Layout

This matrix displays the distances (in meters) between the various departments, warehouses
and activities to each other (cells C5 - AA29). This data is necessary for the calculation of the
internal transportation times. To simplify the input, the user may only complete the cells with
blue shading. In this case the values are automatically copied to the dark green cells right
below the dark blue cells. This would call for the assumption that the distance between all
activities is equal. These values are copied to the bright green cells to right, which follows the
assumption, that the distance from a particular station to all other stations is equal. Of course,
several or all cells can be completed individually just as well.

16
 2.10. Warehouse

This worksheet contains all information that is relevant for the warehouse management. This
data is strongly dependent on the specification of the IT-support with which the emulated
company operates (see also Section 2.1, IT-models). In order to simplify the input, in column
F is proposed empirical data which is based on the selected IT-model is proposed in column
F. This data can be taken as reference values for the fields to be filled. Additionally, cells C6 -
C9 display the information, via which system - depending on the underlying IT-model – the
respective activities from column B are usually executed.

In cells D6 – D8 and D13 - D18, the user can select from a drop-down menu, which
employee(s) should carry out the activities described in column B. As an example, under IT-
model 1, a workman comes with a goods provision slip to the warehouse and then has to wait
until the warehouse employee has read the document and picked the required parts. Therefore
both, the warehousman as well as the worker participate in and, thus, are occupied by this
process.

„Coverage of information“ (cell E6) describes the process, in which the warehouse will be
informed about how many and what kind of raw materials and intermediates it has to supply
for the respective production order. This may be performed by goods provision slips, via the
ERP-system or via MES. By "picking" (cell E7), then, the time is entered, which is needed to
collect the necessary parts from the warehouse. Then the parts are withdrawn from the stock
via the ERP-system (cell E8). After obtaining goods, the raw-, intermediate- or final products
are put back into the warehouse, where they will be booked and then stored (cell E9). At this,
two kinds of errors can occur: first, the delivered parts may be booked incorrectly, and
second, they may be placed at the wrong bin location. Both errors are assessed with a
probability of occurrence (cells E10 and E11).

In the provision process of the materials, several circumstances can cause additional delays.
These circumstances can either be that no warehouseman is available due to absence, or that
the warehousemen are engaged in another task or that the organization of the warehouse is
suboptimal. These three circumstances are also interpreted with their probabilities (cells E13-
E15) as well as their average duration when occurring (cells E16-18). The more IT-support,
the less probable and time consuming are those circumstances.

17
With the final rows of this table, the output quality of the material from the warehouse is
interpreted. The warehousemen might hand out wrong, faulty, not enough or too much
material (cells E20 - E22). Also here, the error rate strongly decreases with increasing IT-
support.

2.11. Order processing

In this table, all periods that arise from the management of internal orders are entered. Like in
the warehouse management spreadsheet, all figures are directly dependent on the IT-model
used (see also Section 2.1, IT-models). In order to simplify the input, empirical data which is
based on the selected IT-model is proposed in column G. This data can be taken as reference
values for the fields to be filled. Additionally, columns C and D display the information, by
which means and via which system - depending on the underlying IT-model – the respective
activities from column B are usually executed. Then, in column E the user can select which
employee or department should actually carry out these activities by using the drop-down
menu.

In the emulated company, the principal or the sales department forward the customer orders to
the order processing, where these are recorded in the ERP-system (cell F6). After physical or
telephonical consultation with the operations management (cell F7), the order processing
department creates the production orders in the ERP-system (cell F8).

With using IT-model 1 or 2, these orders require an additional release by the order processing
or by the operations management (cell F9). The subsequent capacity check by the operations
management (cell F10) as well as the forwarding of the forms to the workmen (cell F11) is
carried out - depending on the IT-support - physically, by the ERP-system or via MES.

Under IT-model 3 and 4, the workmen execute the registration (cell F13) of the orders and,
after having completed the production process, the deregistration (cell F14) directly in the
ERP-system or in the MES. Depending on the IT-model in use, the workmen perform the
documentation of the orders paper-based, in the ERP-system or via MES. If the emulated
company works with IT-model 1 or 2, the workman has to hand over the documents to the
operations management (cell F16) and discuss possible errors (cell F17). The latter process is
also required under IT-model 3.

18
After that, the documents are checked by the operations management either physically, in the
ERP-system or via MES (cell F19). Under IT-model 2, the orders are deregistered directly in
the ERP-system (cell F21), whereas under IT-model 1 the documents first have to be handed
over to the order processing (cell F20) and then are deregistered in the ERP-system by the
latter (cell F21).

The weekly preparation of statistics (cell F22) as well as the evalation of errors (cell F23) are
carried out by the operations management. These processes are, again depending on the IT-
support, effected paper-based, in the ERP-system or via MES. The evalation of errors requires
less time with increasing IT-support and will be totally eliminated by using an MES

2.12. Suppliers

The list of suppliers can include up to 40 entries (column B). In columns C and D the user
should specify the delivery time for direct shipment and the time for delivery through a
central warehouse. When entering the delivery time it has to be considered that this is a
theoretical time, as the emulation breaks down the operating time of an entire year to one day.
For this reason, the delivery time is calculated from the actual delivery time divided by the
number of operating days per year.

2.13. Procurement

In this spreadsheet, up to 40 combinations of raw materials (column B) and suppliers (column

C) can be entered by using a drop down menu. To the right (columns D and E), for each
delivery type (direct or indirect), the delivery times are provided from the table "Suppliers".
These values can be overwritten individually as certain products may cause different delivery
periods. In Columns F and G, the unit costs for direct and for indirect delivery should be filled
in.

2.14. Sourcing

"Sourcing" is the overview of the purchasing orders, in which up to 100 orders can be created.
By entering a rate in cell E3, an additional discount per unit can be considered. In columns B
and C, the user can select the ordered material and the supplier from a drop-down menu. After
the input of the order size in column E, an order-ID is created, which involves a sequential
number and is displayed in column D.

19
Furthermore, in column F the time of ordering has to be filled in. Column G requires the
selection, whether direct delivery or delivery via central warehouse is desired. Based on this
entry, column H shows the basic price per unit that comes from the worksheet „Procurement“.
The purchase price per unit in column I is calculated from the base price less the additional
discount from cell E3. These values can be adjusted manually, which may become important
if special prices have been agreed upon. The price from column I is, multiplied by the
quantity ordered, the total purchase price of the order (column J).

Depending on the chosen way of delivery, the delivery time is taken from the worksheet
„Procurement“ and displayed in column K. Subsequently it is calculated, when the ordered
goods arrive at the receiving warehouse (column L).

2.15. Customers

This worksheet includes the listing of up to 40 costumers (column B). In column C, the
delivery time for direct shipment and in column D the time for delivery via central warehouse
should be entered. When entering the delivery time it has to be considered that this is a
theoretical time, as the emulation breaks down the operating time of an entire year to one day.
For this reason, the delivery time is calculated from the actual delivery time divided by the
number of operating days per year. In addition, columns E and F present the opportunity to
insert fixed delivery costs to certain customers via direct and indirect shipping.

2.16. Distribution

In the sheet „Distribution“ up to 100 customer orders can be entered. An entry in cell E3
allows the granting of an additional discount per unit. In columns B and C, the user can select
the desired final product and the customer by using the drop-down menu. Together with the
order size from column E and a sequential number, an order-ID is created and displayed in
column D.

In addition, it has to be filled in at which point of time the ordered products should arrive at
the customers site (column F) and - using the drop-down menu - whether the customer will be
supplied directly or through a central warehouse (column G). Again, it is important to keep in
mind that these values are only theoretical periods, since they have to be broken down from
one year to one day.

20
Column H receives the basic sales price of each product from the table "Products", from
which the actual sales price per unit (column I) is calculated with consideration of the
additional discount from cell E3. This value can be overwritten individually just as well,
which becomes important for customers which receive special prices. In column J, the actual
sales price is multiplied with the corresponding order size, which results in the overall sales
price of each order.

The delivery time in column K is automatically entered from the worksheet „Customers“ and
is used for the calculation of that point of time, at which the final products have to be
available at the outbound-warehouse (column L). Similar to the delivery time in column F,
these periods have to be aliquoted from an annual perspective to one day. Finally, in column
M, the transport costs – as inserted in “Customers” – are given. They can be individually
changed as well. Information about additional costs due possible transport delays can be seen
in column N. These costs decrease – in the case of a delay – the revenue and they are based on
the information inserted in cell E4.

21
 3. Output-File

The OUTPUT file provides detailed insights into the analysis of the emulated company’s
business processes. Based on the company-specific framework, conditions and specifics
entered in the INPUT file, the value creation process is emulated by using „R“ and afterwards
analysed in the OUTPUT file.

The OUTPUT file offers the possibility to understand the executed production orders in detail
(see section 3.1), as well as to check the status/fulfillment of your customer orders (section
3.2). Furthermore, key performance indicators (KPIs) enable a detailed analysis of your
processes (section 3.3). KPIs regarding your employees (section 3.3.2), the value-
adding/production process (section 3.3.3) and the logistics process (section 3.3.4) make a
deep analysis possible. In addition, monetary figures (section 3.3.5) regarding your corporate
income statement are calculated. These figures give a detailed outlook onto the yearly cost,
revenue and profit margin figures of the emulated company and, thus, they are excellent for
comparing different scenarios. In order to get a quick overview of the performance of your
business, the spreadsheet “KPIs at a glance” (section 3.3.6) outlines the most important
productivity and monetary KPIs at a glance.

3.1. Production flow

In the worksheet „Production flow“, all production orders are compiled in an overview, where
the most important figures like the operating activities (column C), the production order-IDs
(column D), the final products (column E), the quantities (column F), the percentage of
measurement (column G) and the operating workmen (column H) are collected from the
respective tables of the INPUT file.

Column I, then, displays the planned start time of the orders, which is calculated from the
scheduled start of work of the workmen and the duration of the preliminary work necessary,
(e.g. handing over of the required information and forms to the workmen by the operations
management, transport of the raw materials or intermediates needed to the activities). The
actual start time takes possible deviations from the planned start time into account and is
calculated via "R". The difference between the actual and the planned start time is that the
actual start time considers the actual availability of all necessary resources for the specific
orders (material and workmen) as well as unplanned disruptions and delays caused by
preliminary processes.

22
The planned end time in column K is calculated from the planned start time and the planned
lead time (assembly-, transfer-, measuring- and set-up-time including the necessary
administrative activities) from column U, which, as it is a planned time, does not take the
occurrence of errors in the manufacturing process into account. Delays due to these errors are
included in the actual lead time (column V), which adds up together with the actual start time
to the actual end time of the production order (column L). Column R then outlies the
readiness of the workman for the next order. This time includes the possible transport time for
the transport of the produced intermediates to the production warehouse.

As you can see, the spreadsheet “production flow” compares the planned time of production
with the actual time of production. Based on the actual time, the delivery date and
subsequently the timely satisfiability of customer requirements can be calculated.

3.2. Supply – Demand Match

This spreadsheet gives a great overview for the user about the fulfilment situation of his
customer orders. With the customer orders and their specifics listed from columns B to G,
columns H to K give an overview about the delivery status. Column H lists the time the final
products for each customer order are ready at the outbound warehouse. Adding the delivery
time (column I), the arrival time at the customer is calculated (column J). This actual arrival
time, then, is compared to the due time (column G). As a result, column K shows the delivery
status for each customer order (see Figure 6).

Figure 6: The spreadsheet Supply-Demand Match

3.3. Key Performance Indicators (KPIs)

The following key performance indicators are based on Gröpper (2009, p. 8ff). They have
already been formulated in the diploma thesis of Burgholzer and Lindorfer (2010, p. 78ff),
which serves as a basis for this manual. They should provide better understanding and clear

23
comprehensibility of the terms used and the key indicators elaborated. In this regard, at first
variables used for the calculation of the KPIs are defined (section 3.3.1). These variables are
divided into time and quantity variables. Thereafter, beginning with section 3.3.2, the
calculated KPIs are described.

3.3.1. General definition of terms

Time figures
Planned period of use (PPU)

The planned period of use is received by deducting the planned down time from the operating
time. Planned down time can be used for example for carrying out maintenance work. The
planned period of use is available for the detailed planning of the allocation of production
orders to the production units.

Time used for value-adding duties/Main period of use (TVA)

The working time is the time used by workmen for the execution of value-adding processes. It
can also be called main period of use or main productive time as it also is the period in which
a production unit/station/activity produces. It only includes value-adding processes.

Processing time (PT)

The processing time is the period actually used for set-up activities plus the main value-
adding time.

Occupancy time (OT)

During the occupancy time, a production unit is occupied with the execution of a production
order.

Operating time (OPT)

The operating time is the period during which a production unit can be used by a workman for
operational purposes for production and maintenance. The operating time is a planned time.

Lead time (LT)

The lead time is the difference between start time and end time of the order. It includes
occupancy time, waiting time and transfer time.

24
Total attendance time (TAT)

The total attendance time is the period a workman is present in the company. It is the
difference between "arrive" and "leave," minus the break.

Calendar day (CD)

A calendar day (24 h) is the maximum time available for daily production.

Waiting time (WAT)

The waiting time is the period during which the material in the manufacturing process is not
in process and not in transport.

Plan allocation time (PAT)

The plan allocation time is the operating time minus the planned shutdowns (planned
downtime). The planned shutdowns can be used for scheduled maintenance activities. The
planned occupancy time is available for the detailled planning of the allocation of production
orders to the production units.

Production time per unit (PUT)

The production time per unit is the planned time for the production of one unit.

Down time (DT)

The down time is the period during which a production unit is not occupied with orders,
although it is available.

Disruptions caused by failure (DCF)

Disruptions caused by failure occur during the production process. They have not been
planned and threfore they extend the occupany time.

Actual set-up time (AST)

The actual set-up time is the time that is spent to prepare a production unit for a production
order.

25
Transport time (TT)

The transport time is the time used for materials handling between production units or from
and to the warehouse.

Working time (WT)

The working time is the time of the day that a workman is actually working. This can include
value-adding-, transport-, measuring- and administrative duties.

Kommentar [WB2]: Bitte neu

angepaßt machen! Ich hab einige
Bezeichnungen geändert (z.B.
manufacturing time ist jetzt processing
time)

Figure 7: Time model for production units (source: Gröpper, 2009, p. 10)

Quantity figures
Order quantity (OQ)

The order quantity is the planned quantity of a production order (lot size, production order
size).

Quantity of rejects (QR)

The quantity of rejects is the amount produced which did not meet the qualitative
requirements and has to be either scrapped or recycled.

Measured quantity (MQ)

The measured quantity is the portion of products of each batch, that is tested for the
compliance with predefined parameters.

26
Yield (Y)

The yield is the quantity produced, that meets the qualitative requirements.

Rework quantity (RQ)

The reworking quota is the amount produced, which did not meet the qualitative
requirements. These requirements can be achieved by reworking.

Produced quantity (PQ)

The quantity produced is the amount that a production unit has produced with reference to a
production order.

3.3.2. KPIs for the Analysis of Employees

Employee productivity

The employee productivity is calculated from dividing the time, which is used for the value-
adding process, by the total attendance time.

The Employee productivity provides information on the ratio of the order-related working
time of an employee in comparison with the total attendance time of the respective employee.

Employee utility

The employee utility is calculated from dividing the working time by the total attendance time
minus the scheduled breaks.

The employee utility provides information on the amount a workman is utilized.

3.3.3. Key Production Figures

Occupancy rate

The occupancy rate indicates the relationship between the occupancy time of an assembly
station per production order and the total lead time of the respective order.

27
According to Gröpper (2009, p 15) the occupancy rate is used as an index for the amount of
work in process (WIP) in manufacturing. This key indicator involves waiting periods too.
Excessive WIP results in low liquidity, high inventory costs and an extended processing time
for the customer order. (cf. Little, 1961).

Throughput

The throughput is received from dividing the quantity produced by the lead time.

Throughput is an indicator for the performance of a process and is therefore an important

index for the efficiency of manufacturing (cf. Gröpper, 2009, p. 16).

Occupancy utilisation rate

The occupancy utilisation rate is the ratio of the occupancy time of an assembly station to the
plan allocation time.

The occupancy utilisation rate provides information on the utilisation and by implication also
on the available capacity of a production unit (cf. Gröpper, 2009, p. 17).

Utilisation rate

The rate of utilisation is received from the share of the main productive time of an activity in
the total occupancy time.

The utilisation rate is a measure of the productivity of a production unit or station. The
purpose should be to keep the value-adding time as high as possible (cf. Gröpper, 2009, p.
18).

28
Availability

The availability is calculated by dividing the main productive time of an activity by the plan
allocation time.

Availability indicates the ratio of the value-added productive time of a production unit or
station to the planned time provided (cf. Gröpper, 2009, p. 21).

Efficiency/performance

The efficiency is calculated from the time that is used for the production of one unit,
multiplied by the quantity that is produced, divided by the main productive time.

Efficiency is a measure for the performance of a process and is often referred to as operating
ratio or performance. It provides the ratio between the planned and the actual performance of
a process (cf. Gröpper, 2009, p. 22).

Quality rate

The quality rate is obtained by the division of the yield by the total quantity produced.

The quality rate is the ratio between the yield and the quantity produced, and serves as an
indicator of the quality of a production station (cf. Gröpper, 2009, p. 23).

OEE-Index

The Overall Equipment Efficiency (OEE) is determined by the product of availability,

efficiency and quality rate.

29
The OEE Index provides information on the efficiency of a production unit, a production area
or even the entire production of a company. It offers the opportunity to identify improvements
in a company by optimised processes (cf. Gröpper, 2009, p. 19).

NEE-Index

The Net Equipment Effectiveness (NEE) is calculated from dividing the processing time by
the planned occupancy time and multiplying the received ratio by efficiency and quality rate.

The NEE-Index is very similar to the OEE-Index. The only difference is that, compared to the
OEE, the NEE doesn’t consider set-up- and adjustment-processes as losses. (cf. Gröpper,
2009, p. 20).

Set-up rate

The set-up rate is the ratio of the actual set-up time to the manufacturing time of an order.

The set-up rate is a measure that describes the share of set-up activities related to the
manufacturing time of a production unit. A high portion of set-up time means that potential
manufacturing time cannot be used in a value-adding way (cf. Gröpper, 2009, p. 24).

Technical efficiency

The technical efficiency is the ratio of the main productive time to the main productive time
including the disruptions caused by failure.

The technical efficiency indicates how much of the resource “production unit” is still
available after reduction of the disruptions caused by failure. Therefore it is a measure for the
efficiency of a production unit. In contrast to the utilisation rate, set-up periods are not
considered (cf. Gröpper, 2009, p. 25).

30
Process level

The process level is obtained from the division of the value-adding time by the lead time of an
order.

The process level is an index for the profitability and the efficiency of the production. The
index provides information about the presence of substantial, non-value-adding periods during
the execution of an order. The process level is very important for value stream mapping (Lean
Management) too (cf. Gröpper, 2009, p. 26).

Rejects ratio

The rejects rate is the share of the quantity of rejects in the planned quantity of rejects.

The rejects rate primarily serves the monitoring of target specifications. The difficulty lies on
the one hand, usually already in the ERP-system, in the reasonable planning of the process-
related rejects, on the other hand the portion of the rejects should still be below 100% (cf.
Gröpper, 2009, p. 27).

First Pass Yield (FPY)

The First Pass Yield is calculated by dividing the quantity of positively tested goods by the
quantity of all measured parts.

The FPY describes the percentage of products that leave the first process run faultlessly
without rework, and therefore fully meet the quality requirements. This key indicator
measures the direct process quality in terms of workstations and products. The better the first
pass yield, that is the closer it is to 100%, the lower the defect costs as well as the waste of
material, which implies an increase of the output quantity (cf. Gröpper, 2009, p. 28).

31
Rejects ratio

The rejects ratio results from dividing the quantity of rejects by the produced quantity.

The rejects ratio reflects the proportion of the rejects of the overall production and is used to
evaluate the quality of production (cf. Gröpper, 2009, p. 29).

Rework rate

The rework rate is received from the quantity of products that had to be reworked, divided by
the total amount produced minus the quantity of rejects.

The rework rate displays the percentage of rework in the total production and is also used to
evaluate the quality of production (cf. Gröpper, 2009, p. 30).

3.3.4. Key logistics and process figures

Average inventory in the company [units]

The average inventory in the company specifies how many units of material are in the
company on average. This figure includes both the unprocessed units in the warehouses as
well as the material that has already been processed to intermediate or final products.

Average tied-up capital (in material) [€]

The average tied-up capital indicates how much capital is tied up in the total inventory of raw
materials, intermediate and final products, and therefore can not be used elsewhere.

Average opportunity costs (due to capital tied-up in material)

The average opportunity costs refer to costs that are incurred indirectly by funds tied up in
material since these funds could earn interest (with a so-called opportunity cost rate) by being
used elsewhere.

32
Average lead time

The average lead time indicates how long a material is tied up in the company on average.
Here, the average time is measured, in which a material passes through the company, from the
arrival at the warehouse to the exit (either as part of a final product or as a reject).

Average range of inventory

The average range of inventory provides information on the internal security of supply
through its own resources within a given period. It describes the period for which the
inventory is sufficient under a certain (average or planned) material usage.

3.3.5. Key corporate income statement figures

All key figures, which are used to analyse the company's performance, can be seen from the
overview in the spreadsheet " KPIs_Corporate income statement" in the OUTPUT file. For
this purpose, the cost factors as well as the sales and the contribution margin are extrapolated
to one year. This starts from the assumption that the displayed process day can be considered
as a typical day for the emulated company.

Overall costs

An important element for the analysis of the company's performance are of course the costs
involved. Especially those costs are considered, which are of interest for the value-added
process. This includes the costs of the employees participating in the value-added process
(labour costs) as well as the costs of the materials needed and of the future lost sales. The
latter may be caused by deliveries that have been executed lately or not at all, but also by the
delivery of defective products to the customer. The future lost sales are calculated in this tool
following the „10-rule“, which allows an approximate quantification of future lost sales.
These are due to loss of image and the subsequent loss of customers, based on customer
inquiries that have been met insufficiently.

Labour costs

Labor costs are calculated here for all employees emulated. Included are warehousemen,
carriers of internal transport, order processers, operations managers as well as workmen. In
this context, the fixed labour costs of an ordinary working day without overtime are added to
the costs caused by overtime. In particular, the labour costs of the workmen are split - as a

33
kind of analysis facility – into the costs of each activity (manufacturing, testing/measuring,
transportation, etc.).

Material costs

At their calculation, the cost of production materials can be divided into three types of costs,
which can be differentiated by the utilisation of the purchased material. On the one hand, this
concerns cost factors for materials that have been processed into qualitatively appropriate
final products in a value-adding way. On the other hand this relates to materials which had to
be “utilised” as rejects within the company, but also to materials that have been processed into
qualitatively inappropriate final products, which have been delivered to the customer and have
been returned for rework. Additionally, the opportunity costs caused by the purchase of
materials and the resulting capital commitment, are specified.

Future lost sales as cost factor

Future lost sales are a cost factor, which states that sales will decrease in future due to today's
insufficiencies (late deliveries to the customer, delivery of defective products to the customer,
etc.).

Revenue

The revenue is displayed - again extrapolated to a year - for each product and in total.

Profit margin

The profit margin – as prioritised key performane indicator for corporate success – is
displayed for each product and in total too.

3.3.6. KPIs at a glance

The key performance indicators at a glance should provide a summary of the results of a
scenario. This table includes all key performance indicators that enable a quick overview.
These are:

The total revenues, costs and profit margins as key performance indicators for the
corporate performance.
The average tied-up capital (in material), the average cycle time and the average
inventory in the company of the process- and logistics-area.

34
The employee productivity as well as the Overall Equipment Efficiency (OEE)
additionally provide information about the productivity of the employees and the
activities. Additionally, the employee utility figures for workmen and warehousemen
also give information about the use of your employees.

35
Bibliography

Burgholzer and Lindorfer (2010). Manufacturing Execution Systems in Klein- und

Mittelbetrieben

Gröpper M (2009) VDMA-Einheitsblatt 66412-1. Manufacturing Execution System (MES) -

Kennzahlen. Tech. Rep., Verband Deutscher Maschinen- und Anlagenbau e.V. (VDMA).

36