Increasing manufacturing capabilities in a valve plant

Faculteit Ingenieurswetenschappen Vakgroep Technische bedrijfsvoering Voorzitter: Prof. Dr. Ir. Hendrik Van Landeghem

Increasing manufacturing capabilities in a valve plant door Nicolas Baert

Promotor: Prof. Dr. Ir. Hendrik Van Landeghem `re Begeleiders: Marc Baert (Magnetrol), Veronique Lime

Masterproef ingediend tot het behalen van de academische graad van Master in de ingenieurswetenschappen Bedrijfskundige systeemtechnieken en operationeel onderzoek Academiejaar 2008–2009

Faculteit Ingenieurswetenschappen Vakgroep Technische bedrijfsvoering Voorzitter: Prof. Dr. Ir. Hendrik Van Landeghem

Increasing manufacturing capabilities in a valve plant door Nicolas Baert

Promotor: Prof. Dr. Ir. Hendrik Van Landeghem `re Begeleiders: Marc Baert (Magnetrol), Veronique Lime

Masterproef ingediend tot het behalen van de academische graad van Master in de ingenieurswetenschappen Bedrijfskundige systeemtechnieken en operationeel onderzoek Academiejaar 2008–2009

ACKNOWLEDGEMENTS

i

Acknowledgements Hereby I would like to show my great gratitude to everyone who contributed to the realization of this thesis. First of all I thank my promoter, Prof. Dr. Ir. H. Van Landeghem, who gave me the required academic input and support for this study. My thanks to the management of Magnetrol International NV and also its personnel that helped me to obtain the huge amount of information and provided me with their expertise. Special thanks to Mr. Jeffrey Swallow, Owner of Magnetrol International Incorporated, who gave the opportunity to work in his company.

Nicolas Baert, juni 2009

PERMISSION TO LOAN

ii

Permission to Loan

“De auteur geeft de toelating deze masterproef voor consultatie beschikbaar te stellen en delen van de masterproef te kopiëren voor persoonlijk gebruik. Elk ander gebruik valt onder de beperkingen van het auteursrecht, in het bijzonder met betrekking tot de verplichting de bron uitdrukkelijk te vermelden bij het aanhalen van resultaten uit deze masterproef.”

Nicolas Baert, juni 2009

Increasing manufacturing capabilities in a valve plant door Nicolas Baert Masterproef ingediend tot het behalen van de academische graad van Master in de ingenieurswetenschappen: bedrijfskundige systeemtechnieken en operationeel onderzoek Academiejaar 2008–2009 Promotor: Prof. Dr. Ir. Hendrik Van Landeghem `re Begeleiders: Marc Baert (Magnetrol), Veronique Lime Faculteit Ingenieurswetenschappen Universiteit Gent Vakgroep Technische bedrijfsvoering Voorzitter: Prof. Dr. Ir. Hendrik Van Landeghem

Summary This thesis includes simulations of profit and loss over a period of 10 years and is constructed within the scope of a production overload at the Belgium facilities of Magnetrol International. A forecast of the MINV sales is made to estimate the future product mix and that product mix is projected on a 5%, 10% and 15% sales growth. Along with an analysis of production times these sales numbers where used to estimate the future needed production capacity expressed in FTEs. Three cases were evaluated: no investment, investment and shift work. All calculations are incorporated in an Excel assessment tool. This tool is also used to calculate the influence of the US dollar/ euro exchange rate on MINV’s P&L. In order to compare the investment case with the shift work case a Monte Carlo simulation is performed. As a result the NPV of the cashflows after taxation of the investment option is higher in 60% of the cases. As a consequence this option is preferable to the shift work option.

Key words forecast, production capacity, profit & loss, option analysis, Monte Carlo simulation

Increasing manufacturing capabilities in a valve plant Baert Nicolas Supervisor: Prof. dr. ir. Hendrik Van Landeghem

Abstract This article describes the methodology and the results of the study concerning the increase of manufacturing capabilities at Magnetrol International N.V. Keywords: forecast, production capacity, profit & loss, option analysis, Monte Carlo simulation

I. INTRODUCTION Magnetrol International N.V. is experiencing a production overload at its plant in Zele. The overload causes the backlog and lead times to grow. This work has to convince the American owner to invest in increasing the production capabilities either by investing in new production facilities or by implementing a 2-shift system. II. QUALITATIVE INDICATORS The increasing backlog is a direct consequence of the overload in production, as the rate at which orders are reaching the company is larger than the rate at which they can be processed by production. Because of the shortage of production floor space the high WIP levels cause the blocking of hallways. This makes inefficiency percentages rise and adds waste to the system. There is no room in the current setting to separate carbon steel material production from stainless steel material production which can lead to carbon contamination of stainless steel and thus quality issues.

percentages, give us the total of production hours requirement. That number is translated in the required number of full-time equivalents and in extra equipment in departments welding and assembly. The results of this section are used in the profit and loss analysis. IV. PROFIT&LOSS ANALYSIS No investment, investment and a 2-shift system are the three possibilities that the company wants to evaluate. To understand the P&L statement of Magnetrol International a thorough research is performed of all the statement sections and their influencing input variables. Variables like for example sales growth, inflation percentage, dollar/euro exchange rate, cost of subcontracting, cost of a new production hall and office building and so on, are considered in the analysis. Because of the current saturation in production, making no investment does not leave any room for sales expansion. Machining and welding operations can be subcontracted but assembly forms the constraint. The cash flows in the option analysis are calculated with the no investment case as a reference. The second possibility researched is investing in a new production and office facilities. As a third possibility a 2-shift work system has to be considered in the evaluation. Based on the P&L analysis the investment case is preferable over the shift system because the 2-shift system is already saturated after 2014 as estimated in the 10% growth case. The P&L analysis is a very visual method but does not include the time value of money nor does it take uncertainty of input variables into account.

III. DATA ANALYSIS A. Data collection The number of required direct production hours per product family are acquired from the database system that keeps loggings from scanning production orders. Scanning errors were deleted by using realistic cut-off rules. Other important data sets are the MINV accountancy system and the MINV sales reports. . B. Forecasting A mathematical forecast [1] is performed accompanied by a qualitative sales survey. The survey serves to adjust those mathematical forecasts where accuracy is low because of limited historical data series. The forecasted numbers are projected on 5%, 10% and 15% growth as requested by management. The unit sales forecast, combined with the direct production hours per product family per department and with inefficiency

N. Baert is a master of science in industrial engineering and operational research student at the University of Ghent, [email protected].

Figure 1. Profit after taxation To complete the analysis an option analysis is performed.

V. OPTION ANALYSIS A. Sensitivity analysis Multiple scenarios are imaginable with the variables set to different values. Apart from a nominal value variables get also a high and low value to express variable variance. Through a sensitivity analysis the 4 variables with the greatest impact are selected based on the decision criterion – NPV of cash flows after taxation - and they are used to make a table with 3x3x3x3 combinations, resulting in 81 scenarios per option. The scenario probabilities are calculated from the individual probabilities of the variables’ values and a cumulative probability column is added.

B. Monte Carlo simulation By generating 10.000 random numbers - between 0 and 1 scenarios are selected by searching for the random numbers in the cumulative probability column. A cumulative graph is constructed with the results of this simulation.

VI. RESULTS AND CONCLUSION From Figure 2. follows that in 40% of the cases shift work (red line on Figure 2. Monte Carlo simulation) returns a better NPV of cash flows after taxation and in 60% of the cases the investment option (blue line on Figure 2. Monte Carlo simulation). A comparison of the means also results in the investment option (€10.093.171) being preferable to the 2shift system (€7.824.048). In case of low sales growth it shows that the shift system is preferable. That is because there were no large investments as in the investment case that influence the NPV. Moreover the same turnover as in the investment case can be achieved in our study period of 10 years.

Figure 2. Monte Carlo simulation ACKNOWLEDGEMENTS The author would like to acknowledge the support of Prof. Dr. ir. H. Van Landeghem and the management of Magnetrol International N.V. to this master dissertation. REFERENCES [1] J. Holton Wilson and Barry Keating, Business Forecasting, McGraw-Hill Higher Education, 2002.

CONTENTS

vi

Contents Acknowledgements

i

Permission to Loan

ii

Overview

iii

Extended abstract

iv

Inhoudsopgave

vi

List of Abbreviations

ix

1 Magnetrol International N.V. 1.1 Company information . . . . . . . . . . . . . . . . . 1.2 Current situation MINV . . . . . . . . . . . . . . . 1.3 Need for extra capacity and floor space: indicators . 1.3.1 Increasing backlog . . . . . . . . . . . . . . 1.3.2 Increase in subcontracting hours . . . . . . . 1.3.3 Indicators on the shop floor . . . . . . . . . 1.3.4 Safety on the shop floor . . . . . . . . . . . 1.3.5 Quality issue . . . . . . . . . . . . . . . . . 1.4 From current to future production . . . . . . . . . . 1.5 Goal of thesis . . . . . . . . . . . . . . . . . . . . . 2 Data Analysis 2.1 Introduction . . . . . . . . . . . . . . . . . . 2.2 Direct production hours per product family 2.3 Current production capabilities . . . . . . . 2.4 Sales: forecasting . . . . . . . . . . . . . . . 2.4.1 Introduction . . . . . . . . . . . . . . 2.4.2 Qualitative Sales Survey . . . . . . . 2.4.3 Mathematical forecasting . . . . . . . 2.4.4 Results . . . . . . . . . . . . . . . . . 2.5 New production capabilities . . . . . . . . . 2.5.1 Calculations . . . . . . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

1 1 2 4 4 5 5 8 10 10 14

. . . . . . . . . .

15 15 15 19 21 21 23 25 29 37 37

CONTENTS 2.5.2

vii Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

3 Financial Evaluation 3.1 Profit & Loss: introduction . . . . 3.2 Parameters . . . . . . . . . . . . . 3.2.1 Economic Parameters . . . . 3.2.2 Profit & Loss Relations . . . 3.2.3 Labour Parameters . . . . . 3.2.4 Subcontracting Parameters . 3.2.5 Investment Parameters . . . 3.3 Assessment tool . . . . . . . . . . . 3.4 Case study ’No Investment’ . . . . 3.4.1 Analysis . . . . . . . . . . . 3.4.2 Results . . . . . . . . . . . . 3.5 Case study ’Investment’ . . . . . . 3.5.1 Analysis . . . . . . . . . . . 3.5.2 Results . . . . . . . . . . . . 3.6 Case study ’Shift Work’ . . . . . . 3.6.1 Analysis . . . . . . . . . . . 3.6.2 Results . . . . . . . . . . . . 3.7 US Dollar analysis . . . . . . . . . 3.7.1 Introduction . . . . . . . . . 3.7.2 Analysis . . . . . . . . . . . 3.7.3 Conclusion . . . . . . . . . . 4 Option analysis 4.1 Introduction . . . . . . . . . . . 4.2 The base case . . . . . . . . . . 4.3 Option analysis . . . . . . . . . 4.3.1 Sensitivity analysis . . . 4.3.2 Monte Carlo simulation 4.3.3 Results . . . . . . . . . .

. . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . . . . . . . . . . . . . . . . .

. . . . . .

44

. . . . . . . . . . . . . . . . . . . . .

50 50 51 51 52 53 54 55 56 56 56 64 64 64 73 74 74 79 79 79 80 82

. . . . . .

83 83 84 85 85 87 89

Bibliography

91

A Sales survey

92

B P&L structure

93

C Option analysis

96

D Nederlandse samenvatting 104 D.1 Inleiding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 D.2 Gegevensanalyse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 D.3 Financiële evaluatie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110

CONTENTS D.3.1 Inleiding . . . . . . . . D.3.2 Resultaten . . . . . . . D.4 Vergelijking van de cases . . . D.4.1 Monte Carlo simulatie D.4.2 Resultaten . . . . . . .

viii . . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

110 111 115 115 116

List of Figures

119

List of Tables

122

LIST OF ABBREVIATIONS

ix

List of Abbreviations BS5750

The British Standard on ’Quality Systems’.

EMEA

Europe, the Middle East and Africa.

ERP

Enterprise Resource Planning is a company-wide computer software system used to manage and coordinate all the resources, information, and functions of a business from shared data stores.

FOB

Freight On Board

FTE

Full Time Equivalent means a person that works full-time.

IC/PC

Department for Inventory Control and Production Control

ISO

International Standards Organization

MIG

Metal Inert Gas welding process

MII

Internal company abbreviation for Magnetrol International Inc. (USA)

MINV

Internal company abbreviation for Magnetrol International N.V. (Belgium)

MIS

Management Information System

MSE

Mean Squared Error

NPV

Net Present Value

NS5801

Precedent of ISO 9001

OK

State of Oklahoma, United States

P&L

Profit and Loss statement or Income statement

PAT

Profit After Taxation

PBT

Profit Before Taxation

PCB

Printed Circuit Board

RF

Radio Frequency

RMSE

Root Mean Squared Error

LIST OF ABBREVIATIONS ROI

Return On Investment

SSE

Sum of Squared Errors

TIG

Tungsten Inert Gas welding process

USD/US$

The United States dollar, currency of the US. All dollars in this thesis are US dollars.

WIP

Work In Process

WTI

West Texas Intermediate is a type of crude oil used as a benchmark in oil pricing

x

MAGNETROL INTERNATIONAL N.V.

1

Chapter 1 Magnetrol International N.V. 1.1

Company information

The history of Magnetrol (2009) goes back to the 1930’s, when a Chicago - USA based manufacturer of boiler systems, called Schaub Inc. was in need of level controllers for its systems. The level switches and controllers developed for this purpose, ultimately also became attractive instruments for installation in applications and process installations, other than the own Schaub systems, and gradually started to be marketed under the name Magnetrol. Driven by the many European projects for offshore and nuclear power plants, Magnetrol started looking at the overseas market as a major new investment to expand business. In 1971, Magnetrol went international, and in joint venture with the US company Daniel Industries, founded a company called Danmag, based in Zele - Belgium. Activities of this company were sales and marketing of Magnetrol products on the European, Middle-East and African continents, as well as manufacturing of the full range of products for these markets. In 1974, Daniel Industries drew back from this joint venture, and Magnetrol became wholly owner of Danmag. At the same time, the company was changed into Magnetrol International N.V. Since that date, Magnetrol’s product program has been subject to continued program and range extensions, introduction of new technologies and continued efforts for improvement of a full five year warranty on its electro-mechanical products. Magnetrol set an innovative step forward towards supplying highly performing and reliable instruments to the industry covering multiple technologies, e.g.

1.2 Current situation MINV

2

buoyancy, ultrasonic, vibrating, RF capacitance, thermal dispersion, magnetostrictive and contact / non-contact radar. An essential part of Magnetrol’s activities in the last decade furthermore has been the systematic and gradual implementation of Quality Control and Quality Assurance Procedures and Programs. Such has been an essential step not only to ensure Magnetrol’s participation in major nuclear power station projects, as well as in many offshore projects, but also to supply quality products and services to its overall customer base. The implemented Quality Programs resulted in Magnetrol being the first Belgian Company to have its Quality Assurance System certified in accordance with BS 5750 - part 1 and NS 5801. Certification was obtained in December, 1985 and ISO 9001 was obtained March 1989 for the first time. Today Magnetrol is a leading global manufacturer of industrial instrumentation, with main markets in the oil & gas, petrochemical, power and chemical industries. The company is headquartered in the USA and employs 600+ people worldwide.

1.2

Current situation MINV

The production environment of MINV has been developed over the years, starting at its foundation in 1971. It is configured as a process layout where machining operations are grouped together, as are welding and assembly operations. Today, 38 years later, it is time to have a global look at the existing facilities and find out where improvement in flow and layout is possible to optimize the current situation (research by Verstraete (2009)). The reason that there has never been a radical wave of change in the production environment of MINV, is that sales and administration are responsible for the largest part in MINV’s cost structure and therefore they had investment priority in the past. It is in the scope of this thesis to consider an increase of the production capacity to answer to the current sales boost and make a financial evaluation of it. A large part of MINV’s sales can be assigned to customers in the energy industry, more specific the oil companies. Due to the huge increase in oil prices over the last years as can be seen in figure 1.1 oil companies were given the opportunity to invest more in new projects around the world. Those investments had a direct impact on the sales of Magnetrol International. Over the months that this thesis was written the economic situation has changed. The crisis in the financial world has set sail to the business markets resulting in crude oil prices below US$ 50 / barrel according to the U.S. Energy Information

1.2 Current situation MINV

3

Administration (2009). In my research I look for a profitable way to answer to the increasing sales, and moreover give MINV the opportunity to increase its market share in the future.

Figure 1.1: US Dollars per barrel, OK WTI spot price FOB

(Source: U.S. Energy Information Administration (2009))

In section 2.4 sales forecasts are made to harbour the capacity study of MINV’s production facilities. A sales survey is performed in order to verify the results from the forecast of needed production hours for the following years. An important remark is that we don’t work with the absolute forecasted numbers, but with forecasted product mix projections on a 5%, 10% and 15% annual growth. Information is obtained by own research, combined with the experience and knowledge of MINV sales managers. A proper method in this context is to make a study of the evolution at each technology produced by MINV and make turnover forecasts. An incoming MINV order consists of 1 or more suborders. Planning receives that order from Factory Sales and places an order with Magnetrol International Inc. (Downers Grove, Illinois, USA) for the needed parts. About 80% of the required parts are procured from Magnetrol’s headquarters in Downers Grove, USA. Every Tuesday at 8 a.m. parts arrive at the production facilities of MINV in Zele, Belgium. As a consequence of ordering parts in the USA, the reader can understand the importance of the exchange rate study US dollar/euro. This subject attracted the attention of Magnetrol management and they requested an analysis of the influence of the exchange rate. For the moment it is very favourable for MINV to order parts in the USA considering the low exchange rate dollar/euro and the fact that special moulds require a very high

1.3 Need for extra capacity and floor space: indicators

4

investment costs that are now carried by two production facilities, in the USA and in Belgium. Can we give a good estimate for the influence of a changing dollar/euro exchange rate? An answer is provided in section 3.7. Next the delivered US parts and other ordered materials/parts are kept in labelled boxes (e.g. SM80001 5-11, Sales Magnetrol order number 80001 suborders 5 till 11) that are stored in the stockroom. The planning itself is done with a generic routing describing the sequential production steps that a product must go through and the estimated production hours per process group. Next the orders are placed in a production sequence that is discussed weekly in a production meeting. During those meetings the production staff can decide to favour certain orders, considering the urgency for the customer. Thereafter a labelled box goes through the subsequent process groups: machining, welding, assembly, quality control and shipping.

1.3

Need for extra capacity and floor space: indicators

Taking a look at the company data to determine, what information there is that indicates the need for production capacity expansion as well as increasing floor space.

1.3.1

Increasing backlog

Last year’s produced value was e 23,51 million. The total sales of MINV was e 26,1 million, which creates an excess of e 2,59 million. One can deduct from these numbers that production capacity is not sufficient to fulfil the market demand. Because of this overload the problem of long lead times arises. This is a disadvantage from a competitive point of view. We can also see this in the increasing backlog of MINV. If a system processes orders at a slower rate than they arrive it follows logically from queuing theory that the queues of waiting orders will explode. Lead times will become unacceptably high and as a consequence will negatively impact future sales as customers request shorter lead times.


1.3.2

5

Increase in subcontracting hours

Another indicator is the increase in subcontracting hours. Although some parts need special expertise and are preferable for subcontracting, the main part of these subcontracting hours are due to an overload in production. In other words, MINV needs to subcontract a large amount of hours because production does not have the capacity to produce those hours. They are on their limit. Of course this does not necessarily mean that expensive new machines have to be bought right away. The overload can be periodic. In this case the new machine will have its use in some periods, but then will be idle in others. On the other hand, in the long term this oscillation in production hours will still be there, but the mean level of needed production hours will be higher. In that case new machines or maybe a 2-shift system are justified.

1.3.3

Indicators on the shop floor

Have a look at figures 1.2 through 1.8 taken recently on the shop floor.

Figure 1.2: Shop floor




6


7


As can be seen on these pictures this situation brings along important safety risks. It also slows down transport through the production hall, which increases the amount of indirect hours and in this case can clearly be defined as waste.


I challenge you to manoeuvre a forklift through this hallway.


8

Apart from the fact that there is an increasing backlog there is also a flow problem on the shop floor. Clearly the shop cannot take extra workload as they have already a lack of floor space at this very moment. Even if we consider extra racks, we do not have the space to install them. Jobs that are awaiting their processing are already blocking passage for forklifts and operators cannot move around in a safe way. Even if an increase in production capacity was possible in the current setting, the increased throughput would make the WIP situation only worse if cycle times of processes remain unchanged. This follows from Little’s law: T hroughput =

W IP Cycletime

(1.1)

Because of the limited production floor space there is another effect that reduces productivity: inefficiency due to extra material handling. This effect increases the indirect production hours and therefore the efficiency of the production force. As you can see on figure 1.4 it is impossible to reach for the box at the back without moving the boxes in front of it. Workers confirmed that these situations often occur. Besides reducing work in process, racks would again be the solution. The problem is that there is no room for more racks in the production hall.

1.3.4

Safety on the shop floor

Also note the safety advantage of extra shop floor. Now the overload of the production facility creates possible risks for accidents as there is a lot of work in process on the shop floor and in certain situations it blocks the way for safe passage. The handling of longer probes in the current layout leads to inefficient material handling and could also lead to accidents. It has not be mentioned that work accidents are a very expensive, yet preventable, risk for the company.


9

Figure 1.7: Manoeuvring long probe

Figure 1.8: Probe blocking safe passage

Figures 1.7 and 1.8 show workers who are trying to manoeuvre a probe of approximately 5 meter onto an assembly table and they have to stand in the painting booth to do so. Once in place the probe doesn’t leave any room for safe passage and surely blocks any kind of forklift or hand pallet that tries to move to the other side. In this case this placement was only temporarily, however in many other cases with longer probes they are blocking passage for a longer time while an assembly worker is doing a configuration

1.4 From current to future production

10

on them. For example a couple of weeks before writing this part I came across a probe that was reaching into a welding cabin while being in configuration. Probes can go up to 6 meter.

1.3.5

Quality issue

Last but not least: Clean/Dust areas. First of all there are some valuable technical reasons to separate assembly from welding and machining operations. Normally it is expected that carbon steel material production is separated from stainless steel production, to avoid carbon contamination of stainless steel. It is also a critical requirement to separate PCB assembly from steel production, again to avoid steel contamination of the PCB’s. From a marketing point of view it is an advantage that a client who visits the production plant, perceives the quality he expects from Magnetrol products reflected in the appearance of the shop. Building a new production hall gives the opportunity to separate assembly from welding and machining.

1.4

From current to future production

To make a profit & loss analysis of the different cases as in chapter 3 we first have to estimate the required production capacity for the coming years. Production capacity will be expressed in available direct hours since we deal with a job shop way of working. This means that there are a lot of different jobs that all require a different amount of production hours in the different departments. Most of the time the duration of a job does not depend on the speed of a machine but on the time the worker is spending on processing a part. In other words the processes machining, welding and assembly within Magnetrol are all manual processes. Capacity is determined by the amount of workers in each department. Of course a welding station cannot be occupied by an infinite amount of people. In theory in a 1-shift system a welding station can be used 7,5 hours per day, depending on the number of hours a worker spends on it. At the moment all equipment (machining: machining equipment; welding: welding station; assembly: assembly tables with configuration equipment) are continuously in use. There is no extra capacity available. Another remark about scalabilty of resources in the constructed model of chapters 2 and 3 is that when the amount of required direct production hours increases, the amount of total required production hours increases with the same factor. As a consequence the number of indirect hours also increases with the same factor. A problem with this construction is the following: it does not necessarily imply that when we need 1000


11

indirect hours in the current production setting for maintenance, supervision and so on, that we will need double of indirect hours if we want to double our production capacity. It does not necessarily mean if we need 1 full time supervisor in the current setting that we require 2 of them in the situation where production has doubled. These are aspects of management control and are left out of this thesis. However, it is possible to account for this problem in the Excel assessment tool by changing the inefficiency reduction percentages in the parameter sheet. The same remark goes for machining and assembly. Other supporting functions grow along with increasing sales but of course not with the same factor (see section 3.2.3). Now we have determined the amount of direct production hours and thus the number of workers as a measure for the capacity, we start off with an analysis of the current production situation. Next through sales forecasting we estimate the future requirement of total production hours. These numbers are converted to a required number of workers (FTE’s) that are used in different investment options in chapter 3. Let me first identify more specific what Magnetrol means by direct and indirect production hours. Direct production hours are the hours that one works on a specific production order; indirect production hours contain the period of time spent on supervision, quality control, maintenance of machines, material handling and safety meetings. The direct production hours are measured by scanning and are logged in MINV’s ERP system. These data make it possible to give an estimate of future required number of FTEs (chapter 2): Data analysis of database information: download in a spreadsheet and use specialized filters (pivot table as in table 2.2) to structure the information; Calculate average cycle times per product family per department and standard deviation (the standard deviation can be used in case of a production simulation); Make a forecast of future sales numbers in units, not e values; Use the forecast to make a projection of the expected product mix on a 5%-10%15% annual sales growth; Use the future sales numbers in combination with the required direct hours per product family per department to estimate the total need of direct production hours per department over a period of 10 years.


12

Convert the direct production hours to total production hours by using the inefficiency percentages. From the total production hours the required number of FTEs can be calculated.

Although considering that a forecast of 10 years will generate errors of great importance, the results give us a good idea of how production will evolve in the coming years and will be checked by performing a sales survey to increase the accuracy. The absolute forecast is used to estimate the future product mix. As requested by management this product mix is projected on the annual sales target for MINV, being a 10% sales value growth, accompanied by a pessimistic case of 5% growth and an optimistic case of 15%. We also assumed that a 10% sales value growth is approximately the same as a 10% growth in unit sales. Now we have to estimate the annually required number of full time equivalents, FTEs. This can be done by combining the estimate of the direct production hours in combination with a most likely inefficiency percentage (share of indirect hours to the total production hours). As mentioned above the indirect hours consist of supervision, quality control, maintenance of machines, material handling, safety meetings and production meetings. Part of the material handling is due to inefficiency in the current methods. In many cases this can be assigned to the lack of space in the current production setting. Following situations occur: Sometimes the material waiting for further processing is allocated in the hallway and obstructs the passage of forklifts (space issue, inefficiency) and people (safety issue). Indirect hours increase due to these situations. The racks in the production hall are not sufficient to contain all the material in process. As a consequence of that boxes are placed on the floor, either on top of each other or in front and next to one another. The problem here is that when an operator has to reach for a box that is at the bottom of a pile or behind other boxes, and then extra handling is needed to get to the box by moving others. Again this increases inefficiency. Lately there is an increasing trend in probe lengths. This makes it difficult to transport through the factory as small hallways and corners obstruct fluent transportation of these probes. Also due to the current layout in assembly the probes obstruct the hallways when operators are configuring them on their work benches. Apart from increasing the inefficiency and therefore indirect hours, this situation bring along a great safety risk.


13

Increasing its shop floor and changing its layout will help Magnetrol to prevent these situations and decrease the inefficiency. In other words we cannot use the inefficiency percentages of 2008 but we have to estimate new inefficiency percentages for the new situation. The inefficiency percentages are used in the assessment model. The evolution of current inefficiency percentages in the three major departments is given in the bar chart of figure 1.9.

Figure 1.9: Inefficiency percentages in production

The combination of the future direct production hours and the inefficiency percentages gives us the total need of production hours and therefore the number of FTEs in each department. Two extra production considerations: The maximum probe (non flexible) length is 6m. This maximum dimension is due to transportation costs where parts longer than 6m result in extra high costs for air transport. In the layout study we have to bring this max length into account when considering material handling and workers’ safety. In the current situation electronics are assembled in the same space where steel parts are produced. This is not an ideal situation due to contamination risks but also customer perception. Building a new production hall would give MINV the opportunity to make a separate ’clean room’ for the handling/assembling of electronics.

AT MINV each year the sales targets of the previous year are multiplied by 1,1 (10% increase) to get the targets for the next year. In the profit&loss analysis multiple values for sales increase are considered.

1.5 Goal of thesis

1.5

14

Goal of thesis

The indicators, the large increase in sales and the overload in production show the need for extra production floor and capacity. Increasing the capacity can be achieved in more than one way: More direct production hours achieved by optimizing current facilities. By a rearrangement of the factory layout, a better flow through the factory can be achieved. The goal of this rearrangement is to have less transport and material handling time so that an order will have a shorter lead time and more hours are available to do value adding activities (e.g. more production time). For this analysis I refer to Verstraete (2009).

There is also an opportunity to make a study of working methods and change over times of machines, again to eliminate non-value adding activities and therefore to create extra production capacity. This is beyond the scope of this financial analysis. Shift work is another option to increase production capacity. The possibility of 2 shifts must be considered in this thesis. See also section 3.6. More work can be subcontracted to other companies. Since in Magnetrol’s case subcontracting is more expensive than producing it themselves it is not a preferable option. The production facilities can be expanded by building a new production hall next to the existing one (room for expansion is available on MINV grounds). This gives us also an opportunity to separate all electronic handling from steel parts production. See also section 3.5.

In the financial P&L analysis of chapter 3 we will consider a ’no investment’, an ’investment’ and a ’shift work’ case. In chapter 4 a NPV (of cash flows after taxation) simulation is made to compare the discounted value of the cashflows after taxation of the two main options:implementing shift work and investing in new production facilities.

DATA ANALYSIS

15

Chapter 2 Data Analysis 2.1

Introduction

The goal in the data analysis section is to estimate the future required production capacity in terms of FTEs. First we determine how many direct hours are required on the average per product family. Secondly sales numbers are forecasted to get an estimate of the future product mix. The combination of these two results in the future required capacity, expressed in FTEs. The outcome of this analysis is used in the financial evaluation of chapters 3 and 4.

2.2

Direct production hours per product family

The products of Magnetrol can be divided into 10 product families: Table 2.1: 10 Magnetrol product families

Brand name Mechanicals Modulevel Eclipse Pulsar Gap Sensors Thermatel Kotron Air Sonar Jupiter Solitel

Alternative name Mechanical products Displacer transmitters Guided wave radar Radar Ultrasonic Contact Thermal dispersion RF controls Ultrasonic non-contact Magnetostrictive Vibrating rod

2.2 Direct production hours per product family

16

The direct hours per product family were not directly available in the MINV database but after a little research it showed that there was a way to acquire them. MINV stores the start and end time of a suborder in departments machining, welding and assembly. One has to know that a Magnetrol suborder only contains products of one type. Therefore when we know how many hours are spent on a suborder in a certain department we can calculate how many hours are needed on the average for one product by dividing the total time in that department(end time - start time) by the number of products in the suborder (completed quantity). Of course it is possible that someone works on a suborder one day and completes it the other day. This results in two lines in the raw data list which each a start and end time. In order to make a pivot table we have to make sure that these lines are not summed up, as seen in following example: First data line Order number 79087 - 0012 Working time 2 hrs Completed Quantity 20 Department 13 (welding)

Second data line Order number 79087 - 0012 Working time 1,5 hrs Completed Quantity 20 Department 13 (welding)

These two lines denote the same suborder, so the correct conclusion is that there was 3,5 hrs of work on 20 items in department 13. How can we make sure that this is also interpreted this way when making a pivot table? There is an extra column in the raw data list that can be used, namely: CMPLT FLAG. If this column value is 0 this order is only partial completed, only if the column value is 1 the suborder is completed in a department and this row only specifies the number of items completed. The working times however must all be summed up within a suborder in a department. Figure 2.1 shows the format of the raw data list from the MINV database.


17

Figure 2.1: Raw data list

Only the relevant columns are displayed. With these data we can evaluate how many hours were spent on how many items of what type (first 3 characters of part number denote product type) in which department. The result of transforming the data list into a more manageable data format with the help of pivot tables, is shown in figure 2.2.

Figure 2.2: Pivot table

As seen in figure 2.2 information is structured by: Department - Order number -


18

Suborder number - Product type - Sum of Production time - Maximum of Completed Quantity. The next step is to connect the right product codes to the correct product family. This connection is found in the so called literature cross reference list of MINV (2009). The numbers are transferred from the pivot table to this new list and thereafter the total is calculated per product family in terms of total production time and completed quantities. Subsequently we calculate the average required direct production hours per product family and per department. Before finalizing these numbers there was a meeting with the production manager to correct and fine tune the data. He gave me guidelines for reasonable production times so that I could filter the higher ones out of the analysis. The scanning system is not yet made error proof and the situation occurs that a worker does not scan the object again at the end of his job, which leads to high production times in the database system. We have the sales numbers available per product family over the last 18 years and based on these numbers we perform a forecast for the next 10 years. Those numbers combined with the production hours per item results in the total needed production hours in each of the departments. Table 2.2 shows the results of the calculation described above and shows the required direct production hours per product family item and per department. Table 2.2: Direct production hours per product family and per department

Product family Mechanical products Displacer transmitters Guided wave radar Radar Ultrasonic Contact Thermal dispersion RF controls Ultrasonic non-contact Magnetostrictive Vibrating rod

Machining 1,07 1,67 1,73 0 0,80 1,42 0 0 0 0,08

Welding 1,39 2,43 1,67 0,31 0,37 0,33 0,48 0,12 0 0,02

Assembly 1,61 2,19 1,49 0,96 0,61 1,19 2,10 0,28 0,47 0,29

Some numbers in this table may seem odd, like why does a Solitel only need 0,02 hrs

2.3 Current production capabilities

19

of welding. Normally Solitel is machined and welded in the States and the Belgium factory assembles the products. Only in a few cases the parts coming from Downers Grove needed some adjustment by the machining or welding departments of Zele. That explains the low production times for this product. Other product families like Jupiter, Air Sonar or Kotron need no machining in Belgium at all. If Magnetrol ever decides to move the production of these items to the Belgium factory, these numbers will have to be adjusted in the assessment tool and new time measurements are required.

2.3

Current production capabilities

The current production capabilities are measured in production hours. The graphs in figures 2.3 through 2.5 show us the evolution of production hours in each of the departments from 2003 till 2008. The data to produce these graphs were deducted from production reports of this period.

Figure 2.3: Total production hours per department

In the graph of figure 2.3 we see that assembly requires the greatest amount of total production hours. One of the reasons assembly carries the most hours is that this process cannot be subcontracted while machining and welding can be subcontracted. For example in 2008 7.150 hours of welding and 2.220 hours of machining were subcontracted. As subcontracting is more expensive than producing the parts yourself we try to limit this for the future, however, some parts require the expertise of extern companies and cannot be processed at MINV.

2.3 Current production capabilities

20

Figure 2.4: Direct production hours per department

Figure 2.5: Direct production hours per department

The sudden rise of indirect production hours in 2007 is due to safety meetings and extra material handling because of the overload in production. Extra material handling appears in different situations, for example: Due to the overload in production there is a large amount of WIP. Because of the limited floor space boxes are sometimes stacked on top of each other. When an operator needs a box that is covered by other boxes he first has to remove the other boxes to reach the required one. This takes lots of extra time from operators which could be interesting to measure in the future. Work pieces of increasing length are also a problem on the shop floor. Recently I took a walk through the factory and two men were busy moving long work pieces

2.4 Sales: forecasting

21

from one department to another. Due to the limited space it took quite a while to manoeuvre them through the hallways.

2.4 2.4.1

Sales: forecasting Introduction

A data series with a maximum of 18 historical annual figures per product is available at MINV (2008b), which may not be sufficient to perform a forecast on with acceptable accuracy. To prove this I perform a forecast for a given data series and then look at the errors accompanied with this length of historical time series and the required forecasting period. In other words, it will show that having a time series on hand of 10 years (this is the case for the Eclipse product family), the uncertainty of the forecast for the next ten years will be quite large. This follows directly from a general wisdom in forecasting: the further in the future you predict, the less certain your forecast will be. A forecast is always accompanied by a forecast error, or, a forecast equals the model value plus a certain error. In figure 2.6 this error is graphically shown by two extra lines on a demand-time graph. One is the upper limit with x% reliability; the other is the lower limit with x% reliability.

Figure 2.6: Forecast illustration dr. ir. R. Van Landeghem (2008)

In the forecasts made for MINV we decided to work with the common used 90%


22

reliability limits, being the 95% confidence level and the 5% confidence level. These lines can be interpreted as: There is a 90% chance that, based on the historical time series, future demand will have a value between the 5% confidence level and the 95% confidence level. There is a 5% chance that, based on the historical time series, future demand will have a value that is lower than the 5% confidence level. There is a 5% chance that, based on the historical time series, future demand will have a value that is higher than the 95% confidence level.

Let me illustrate this for the Eclipse family, number of units. Running a 10-year forecast on the given time series from 1998 until 2008 results in the graph of figure 2.7 .

Figure 2.7: Forecast Eclipse Graph

In this picture the forecasted line, the upper and lower limit are displayed. Following table shows the exact values.


23 Table 2.3: Sales units forecast Eclipse

Date 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018

Annual Forecast 4988 5474 5959 6445 6931 7416 7902 8387 8873 9359

95% - upper 6329 7027 7731 8439 9150 9863 10578 11294 12010 12728

5% - lower 3647 3920 4188 4451 4711 4969 5226 5481 5736 5990

As you can see on figure 2.7 as well as in table 2.3, the further in the future you go, the more uncertainty there is, or, the wider the 90% reliability lines become. In 2018 the 90% confidence interval becomes [5990,12728] produced units, which gives an interval width of 6738 units. In 2009 the 90% confidence interval [3647, 6329] is only 2682 units. Another measure that is used to see how well a model fits the given data is R-squared in %. This percentage shows how well the fitted values compare to the actual values, how well it predicts the trend in the historical time series. For the Eclipse product family the R-squared value is 66,6%. A quick look at the graph above shows that the model gives a very good fit for the sales numbers of 2004 and before, but cannot fit or explain the peak seen in 2007, which leads to a lower R-squared value. For comparison, the forecast for the Thermatel Switch model has an R-squared value of 84,3%.

2.4.2

Qualitative Sales Survey

The forecasts produced by MINV data will have a considerable error due to the limited data. A sales survey is performed to verify the forecast results. The outcome of this research will give us a good estimate of what the future MINV product mix will be. The sales force can be a rich source of information about future trends and changes in buyer behaviour. These people have daily contact with buyers and are the closest contact the company has with its customers. It is the job of the forecaster to organize


24

and collect this information in an objective matter to obtain a considerable insight into future sales volumes. This survey is sent to people of the MINV sales force to give their expectation about the future sales of the product families in terms of five categories: highly increasing (score=5), increase (score=4), constant (score=3), decrease (score=2), highly decreasing/disappearing of the market (score=1). We will control our forecast results obtained from a mathematical model with the product sales expectations of the sales force and adapting the forecasted values where needed if there are large deviations between model and sales force expectations. Note that combining two or more forecasts is done frequently in forecasting. In our case we are not going to add the results from one model to the results of another one, but we are going to verify our initial forecasts with a sales survey. The e-mail that was sent to 7 sales managers is found in Appendix A. In reply they provided me with their sales expectations as shown in table 2.4. Table 2.4: Sales Survey: scores as defined in text

Product family Y.D. Mechanical products 3 Displacer transmitters 3 Guided wave radar 5 Radar 2 Ultrasonic Contact 3 Thermal dispersion 4 RF controls 1 Ultrasonic non-contact 3 Magnetostrictive 4 Vibrating rod 2

A.R. 2 2 5 3 3 4 1 2 4 1

M.B. 3 3 4 3 4 4 2 3 4 1

W.H. 4 3 5 3 3 4 2 3 3 2

P.S. 3 3 3 3 3 4 3 2 4 3

J.V. 3 3 5 5 2 5 1 2 4 3

F.A. 3 4 4 3 3 3 1 4 4 2


25 Table 2.5: Summary Sales Survey

Product family Mechanical products Displacer transmitters Guided wave radar Radar Ultrasonic Contact Thermal dispersion RF controls Ultrasonic non-contact Magnetostrictive Vibrating rod

2.4.3

Mean 3,00 3,00 4,43 3,14 3,00 4,00 1,57 2,71 3,86 2,00

Standard Deviation 0,58 0,58 0,79 0,90 0,58 0,58 0,79 0,76 0,38 0,82

Mathematical forecasting

In this section the sales numbers of the 10 Magnetrol product families are forecasted using the excel tool provided with the book Business Forecasting. Forecasting has to be seen as a process that contains certain key components. This process includes the selection of one or more forecasting techniques applicable to the data that needs to be forecasted, which depends on the type of data being used. First we have a look at the sequence one has to follow when starting a forecasting process. As proposed in Wilson & Keating (2002): 1. Specify objectives The objective of this forecasting is to use the future sales figures to estimate the need of direct production hours at the production plant of MINV in Zele 10 years from now. In this way we want to assess whether an expansion of production capabilities in Zele is the way to go to increase profits. Apart from that it can be interesting for Magnetrol management to see the results of an academic forecasting based on the past sales figures. The forecasted numbers themselves are not used to estimate the absolute quantities of products sold 10 years from now, but the relative percentages of the total. For example it is expected that Eclipse will gain a bigger share in total sales in the future. Because each product family hs specific required production hours


26

this future product mix will affect the required production hours per production department. These percentages are then projected on a 5%, 10% and 15% annual growth. Why do we use the forecasted numbers to estimate a product mix and then project it? Every year, Magnetrol management sets a new target for the following year, normally this is a sales value growth of 10%. It is this sales growth number that management is trying to achieve every year, that will mostly determine future sales. As worst case scenario we simulate a 5% sales growth, as best case a 15% growth, although this second figure is quite optimistic in the current economic setting of low economic activity. That is why the 15% growth case will get a low probability in the option analysis of chapter 4. 2. Determine what to forecast If one wants to know how many production hours will be needed, there has to be a knowledge of future production demands. We have to make a forecast of future sales and this has to be done in units, not in values, because the unit sales has a direct influence on the required capacity. Since forecasts become more accurate by aggregating the forecasted series, it seems a logical decision to aggregate the different Magnetrol products to a total 10 product families (e.g. Radar, Guided Wave Radar, Magnetostrictive, Ultrasonic Contact etc.). 3. Identify time dimensions The length of the forecast horizon must be determined. In the case of Magnetrol, a 10-year forecast horizon is requested to estimate the required capacity at the Zele production plant 10 years from now. A very important note in this section is that one has to be aware that forecasts beyond a few years are likely to be influenced by unforeseen events that are not incorporated into the model used. Also a forecast horizon of 10 years will generate a large error. Because of this uncertainty concerning the forecast a qualitative forecast is made as in section 2.4.2. We have to determine whether the forecast is required on an annual, quarterly or monthly base. Since our goal is to estimate what production capacity is needed over the next 10 years and expressed per year, neither quarterly nor monthly based forecast are of any use to us. It suffices to know which capacity will be needed from which year on. Of course this has nothing to do with the time dimensions of the time series on hand. The more data you have, the more accurate


27

the forecast will be, so monthly sales data would be preferable over annual data. However we decided to work with the annual data, because retrieving monthly data is very time consuming in the current data system. Because we only make a one time forecast, we don’t have to pay attention to the complexity of the forecasting methods. For example, if a forecast is meant to be made on a very small time base (e.g. when forecasting electricity demand) then one could prefer simple, easy to solve forecasts that not require too much time to calculate. 4. Data considerations In this topic one has to have a look at the quantity and type of data that are available. The current situation is that we only have annual data available and that we are going to try forecasting on that given time series. If it shows that this data series gives a too large error, we might consider obtaining more detailed information, as in monthly data. Considering the goal of forecasting in this case, it might not be necessary to go in this much detail. You might ask why not using the detailed information immediately? Well, this information has to be extracted from a database in the United States and is very time consuming, so before asking for people’s valuable time, we are going to evaluate the time series that is directly available at MINV. 5. Model selection After considering the objectives, what to forecast, time dimensions and data we can go on with selecting an appropriate model. The model that will be selected depends on several criteria:

(a) The pattern exhibited by the data (b) The quantity of historic data available (c) The length of the forecast horizon We have the advantage of having a software package named ForecastXdelivered with Wilson & Keating (2002), that has a function that through a tool called Procast, automatically chooses the best model to apply to the given time series. It does this by minimizing one or more error measurements such as MSE, SSE or RMSE.


28 Table 2.6: Selected forecasting models

Product family Forecast model R-square Mechanicals Holt-Winters 49,5 % Modulevel Exponential smoothing 10,9 % Eclipse Double exponential smoothing-Holt 66,6 % Pulsar Exponential smoothing / Gap Sensors Holt-Winters 83,5 % Thermatel Switch Holt-Winters 84,3 % Thermatel Transmitter Double exponential smoothing-Holt 84,9 % Kotron Holt-Winters 52,5 % Air Sonar Exponential smoothing 44,9 % Jupiter Double exponential smoothing-Holt / Solitel Holt-Winters 62 %

As the R-square values tell the models chosen forGap Sensors, Thermatel switch and Thermatel transmitter sales are a good fit for the historical data of these product families. Moduvel, Jupiter and Pulsar have a bad fit and have low or even no R-square values were returned by the program. Having a look at figures 2.4.4 and 2.4.4 explains why a no clear trend or proper model could be found. In the case of Pulsar there are only 6 historical sales numbers available which is too low to make a reasonable forecast on. Also the data exhibit an unpreditable pattern starting a low sales value, going to a very high value and then to a medium sales value. In the case of Jupiter there is a sufficient data series (13 numbers) but with no clear pattern (long period with almost no sales). A forecaster must be aware of the fact that using an automated forecasting method is acceptable if you understand the selected method well enough to evaluate whether it is truly a logical choice. This means that after using the automated Procast-function we have to check whether the chosen model seems an appropriate one to make a forecast on the given time series. We can check our results with the sales expectancies of the MINV sales force, section 2.4.2. 6. Model evaluation After selecting a workable model we have to evaluate whether it fulfils our expectancies. This can be achieved by using the sales survey and check whether the mathematical results are in line with what people expect for the future.


2.4.4

29

Results

Product forecasts Air Sonar (Ultrasonic Non-Contact) The graph in figure 2.8 below shows the forecast of the Air Sonar product family based on historical data (blue line). The pink line shows the forecast for the next 10 years with upper (light blue line) and lower (purple line) limits.

Figure 2.8: Forecast sales units Air Sonar

The sales survey of section 2.4.2 gave a mean score of 2,71 which indicates an expectation of constant or slightly decreasing sales. This score is in line with the forecast based on the historical data.


30

Eclipse (Guided Wave Radar)

Figure 2.9: Forecast sales units Eclipse

The sales survey gave a mean score of 4,43 which indicates an expectation of increasing or even highly increasing sales. This score is in line with the forecast based on the historical data and was expected as Eclipse is Magnetrol’s best selling product at this moment. Gap Sensors (Ultrasonic Contact)

Figure 2.10: Forecast sales units Gap Sensors

The sales survey gave a mean score of 3 which indicates an expectation of constant sales. This score is approximately in line with the forecast based on the historical data, however the forecast shows a slight increase in sales, but with large uncertainty levels as can be seen by the upper and lower limits.


31

Jupiter (Magnetostrictive)

Figure 2.11: Forecast sales units Jupiter

The sales survey gave a mean score of 3,86 which indicates an expectation of increasing sales. This score deviates some what from the forecast based on the historical data, the forecast shows a highly increasing sales. Having a look at the historical data it is clear that no clear trend can be distinguished, so a mathematical forecast may not be that reliable here. Considering the sales survey score let us take a Jupiter sales growth of 10% annually, parallel with the yearly sales target. Kotron (RF controls)

Figure 2.12: Forecast sales units Kotron

The sales survey gave a mean score of 1,57 which indicates an expectation of decreasing, maybe even highly decreasing sales. This score is not so much in line with the forecast based on the historical data, as this forecast shows constant sales. We could make an adaptation here. Let us take the Kotron sales evolution a 10% annual sales decrease.


32

Mechanicals

Figure 2.13: Forecast sales units Mechanicals

The sales survey gave a mean score of 3 which indicates an expectation of constant sales. This score is in line with the forecast based on the historical data, as this forecast shows approximately constant sales. The forecast shows cyclic sales because these cycles were also present in the historical data. Modulevel (Displacer Transmitters)

Figure 2.14: Forecast sales units Modulevel

Since the best fitted forecast model only gave an R-square of 10,9 % we cannot rely on the mathematical prediction of a constant sales. However, the sales survey gave a mean score of 3 (indication of a constant sales expectation) and based on this finding we assume a constant unit sales for Modulevel.


33

Pulsar (Radar)

Figure 2.15: Forecast sales units Pulsar

The sales survey gave a mean score of 3,14 which indicates an expectation of constant sales. As it happens, this score is in line with the forecast based on the historical data which shows constant sales. However the forecast itself is not reliable as indicated by the low R-square Procastreturned. The reason why Procastcould not forecast based on the given historical data is that there was an insufficient data series of only 6 years with no clear pattern. In this case we are relying on the sales survey that expects a constant sales for Pulsar.


34

Solitel (Vibrating Rod)

Figure 2.16: Forecast sales units Solitel

The sales survey gave a mean score of 2 which indicates an expectation of decreasing sales. This score is in line with the forecast based on the historical data, as this forecast shows a decreasing sales. Thermatel (Thermal Dispersion switch/transmitter)

Figure 2.17: Forecast sales units Thermatel Switch


35

Figure 2.18: Forecast sales units Thermatel Transmitter

The sales survey gave a mean score of 4 for the combination of switches and transmitters which indicates an expectation of increasing sales. This score is in line with the forecast based on the historical data, as this forecast shows an increasing sales trend. Summary The following pie charts show the estimated product mix in 2009 and 2018. I’ve made such product mix estimation for each year from 2009 to 2018. The exact numbers are used for a projection on a yearly 10% sales target growth, along with a worst case of 5% and a best case of 15%.


Figure 2.19: Pie chart of the forecasted product mix for 2009

Figure 2.20: Pie chart of the forecasted product mix for 2018

36

2.5 New production capabilities

2.5 2.5.1

37

New production capabilities Calculations

Now that we have the required direct production hours per product family per department and an estimate of the future sales, we can proceed to calculating the new required production capacity. First we make a table with the actual forecasted numbers and we project these numbers on a target growth of 10% per year. The same is done for a worst case of 5% and a best case of 15%. Table 2.7: Forecasted quantities 2009-2013

Forecast (qty.) Mechanicals Modulevel Eclipse Pulsar Gap sensors Thermatel sw. Thermatel Tr. Kotron Air sonar Jupiter Solitel Total

2008 5665 1058 3667 89 2311 1107 224 139 74 103 79 14515

2009 2010 5953 6188 968 968 4988 5474 89 89 2407 2503 999 1290 249 278 125 112 74 74 113 124 104 139 16069 17239

2011 5398 968 5959 89 2599 1293 307 101 74 136 97 17022

2012 2013 5848 6140 968 968 6445 6931 89 89 2695 2791 1180 1392 335 364 91 82 74 74 150 165 109 114 17985 19111


38

Table 2.8: Forecasted quantities 2014-2018

Forecast (qty.) Mechanicals Modulevel Eclipse Pulsar Gap sensors Thermatel sw. Thermatel Tr. Kotron Air sonar Jupiter Solitel Total

2014 6381 968 7416 89 2887 1497 393 74 74 182 113 20075

2015 2016 5566 6027 968 968 7902 8387 89 89 2984 3080 1511 1402 422 451 66 60 74 74 200 220 60 79 19842 20837

2017 6327 968 8873 89 3176 1693 479 54 74 242 104 22081

2018 6574 968 9359 89 3272 1696 508 48 74 266 72 22927

Tables 2.7 and 2.8 show the actual forecast made by mathematical methods. To project these numbers on the target growth (and worst and best case) we do the following. The relationship of the actual number of a certain year to the total of that year is calculated and multiplied by the total target sales of that year. For example take the Mechanical sales in 2013 when a 10% sales target growth is given.

Actual M echanicals F orecast 2013 . T otal T arget Sales 2013 = T otal F orecast 2013 6.140 . 14.515 . 1, 12013−2008 = 7.510 (2.1) 19.111 The results are given in tables 2.9 and 2.10.


39

Table 2.9: Forecast Projection on a 10% target growth 2009-2013

Mechanicals Modulevel Eclipse Pulsar Gap sensors Thermatel sw. Thermatel Transm. Kotron Air sonar Jupiter Solitel Total (units)

2009 5915 962 4956 89 2391 992 247 124 74 112 103 15966

2010 2011 2012 6304 6127 6909 987 1099 1144 5576 6763 7615 91 102 106 2550 2950 3184 1314 1467 1394 283 348 396 114 115 107 76 84 88 126 155 177 142 110 129 17563 19319 21251

2013 7510 1184 8477 109 3414 1702 445 100 91 202 139 23376

Table 2.10: Forecast Projection on a 10% target growth 2014-2018

Mechanicals Modulevel Eclipse Pulsar Gap sensors Thermatel sw. Thermatel Transm. Kotron Air sonar Jupiter Solitel Total (units)

2014 2015 2016 2017 2018 8173 7934 9000 9807 10795 1240 1380 1446 1501 1590 9499 11264 12524 13753 15367 115 128 134 139 147 3698 4253 4598 4922 5373 1918 2153 2093 2625 2785 503 601 673 743 834 94 94 89 83 79 95 106 111 115 122 233 285 328 375 437 145 86 118 161 118 25713 28285 31113 34225 37647

And the corresponding graph of the largest product families:


40

Figure 2.21: Forecast sales of largest product families

By multiplying the projected numbers by the required direct production hours per department the total required direct hours in each department are calculated. With a 10% sales growth each year the needed direct hours for each department are:

Figure 2.22: Total direct hours in the machining department


41

Figure 2.23: Total direct hours in the welding department

Figure 2.24: Total direct hours in the asembly department

From the bar charts in figures 2.22 through 2.24 an important conclusion to be made is that Eclipse is the fastest grower in all three departments. This could be a justification


42

to make a separate Eclipse area (machining, welding and assembly) in the new hall, such as production manager Paul D’Hoey proposed in his latest production hall lay out. By separating this area a lot of extra material handling can be eliminated which increases the efficiency of the total production hall. After all the Eclipse flow is a very important one on the shop floor and the Eclipse product family forms a great part of the total WIP, now blocking the hallways. The next step is to transform these direct hours back to total production hours and full time equivalents. There are two cases when calculating the total hours from the direct hours. The first one is the case where there is no investment in extra shop floor and the current inefficiencies cannot be resolved. This would be the case with the no investment and the shift work situation. In these two situations we use the inefficiency percentages of 2008 for further calculation. A second case is the investment in extra shop floor which makes it possible to reorganize production to eliminate certain inefficiencies. Also the inefficiencies of extra material handling due to a lack of space will be resolved in this way. We assume that the first year the new hall is taken into use the inefficiency percentage will drop by 10% and thereafter by 1% each year by performing gradual improvements. So the inefficiency of year X is calculated as: Inef f iciencyX = Inef f iciency2008 . (1 − 0, 1).(1 − 0, 01)X−2009

(2.2)

Of course it may be possible to reduce the inefficiency even more with a very beneficial effect, but then a detailed study of the current production system has to be made. The reasoning is illustrated by the graphs in figures 2.25 and 2.26 for inefficiency reduction of respectively 1%, 2% and 5% each year.


43

Figure 2.25: Inefficiency reduction

Figure 2.26: Influence of inefficiency reduction on FTEs

To state this case even more the cumulative reduction in men years over 10 years time between 1% reduction and 5% reduction (great inefficiency reduction effort) is calculated in graph 2.27.


44

Figure 2.27: Difference between 1% and 5% annual inefficiency reduction

The cumulative diffence between the 1% and 5% inefficiency reduction in the number of FTEs over 10 years is 14 men years. The total cost of these 14 mean years, corrected for the annual labour cost increase of 4%, is e 530.850 (not NPV). One can use the assessment tool is this way to compare the benefits gained from an efficiency improvement project against the costs of the project.

2.5.2

Results

The results of the future needed production capacity expressed in full time equivalents is put in two graphs for each case (5%, 10%, 15% yearly sales growth). The first graph displayed shows the future required full time equivalents in departments 12 (machining), 13 (welding) and 14 (assembly) in case Magnetrol does not invest in a new production hall (same inefficiency situation). The second graph displays the investment case where there is an efficiency improvement.


45

Annual sales growth: 5%

Figure 2.28: Full time equivalents

Figure 2.29: Full time equivalents with better efficiency

The graphs in figures 2.28 and 2.29 show that with an investment in a new production hall better efficiency can lead to a reduction of FTEs in machining, welding and assembly of respectively 1, 2 and 1 in 2018. The graph below shows the cumulative


46

reduction in men years in the three departments from 2009 until 2018 due to better efficiency.

Figure 2.30: Cumulative reduction in mean years

The cumulative graph of figure 2.30 shows that over ten years time a total reduction of 8 men years in machining, 9 in welding and 8 in assembly can be achieved with better efficiency. Annual sales growth: 10%



47


The graphs in figures 2.31 and 2.32 show that with an investment in a new production hall better efficiency can lead to a reduction of FTEs in machining, welding and assembly of respectively 3, 2 and 3 in 2018. The graph below shows the cumulative reduction in men years in the three departments from 2009 until 2018 due to better efficiency.



48

The cumulative graph of figure 2.33 shows that over ten years time a total reduction of 16 men years in machining, 9 in welding and 13 in assembly can be achieved with better efficiency. Annual sales growth: 15%




49

The graphs in figures 2.34 and 2.35 show that with an investment in a new production hall better efficiency can lead to a reduction of FTEs in machining, welding and assembly of respectively 4, 3 and 3 in 2018. The graph below shows the cumulative reduction in men years in the three departments from 2009 until 2018 due to better efficiency.


The cumulative graph of figure 2.36 shows that over ten years time a total reduction of 22 men years in machining, 15 in welding and 19 in assembly can be achieved with better efficiency.

FINANCIAL EVALUATION

50

Chapter 3 Financial Evaluation 3.1

Profit & Loss: introduction

After the operational calculation of the required number of fulltime equivalents required for future production, we are going to estimate the financial benefits from investing in increased production capabilities at MINV. Three case studies are considered: 1. no investment is made and MINV operates with its current production capacity 2. an investment is made in: (a) doubling of the production hall (b) extra capacity in welding and assembly, gradually (c) extra office capacity after 5 years (beginning of 2014) 3. a 2-shift production schedule is introduced Before calculating the future estimated P&L situation, we analyze the current structure of MINV’s P&L. By doing this we can easily see which factors are influenced by an increase of the manufacturing hall, the number of fulltime equivalents and production means (extra welding units, assembly tables...). The structure of the MINV P&L is derived from the statement Excel file available at MINV (2008a) and the result is given in appendix B. The P&L structure is also used in chapter 4 to compare the cash flows resulting from investing in new facilities and introducing a 2-shift system. The next section shows the parameters that will be used in the P&L analysis and thereafter we go to the actual P&L calculation of the ’no investment’, ’investment’ and ’shift work’ cases.

3.2 Parameters

51

The influence of the US dollar value separately on the P&L tables is shown at the end of chapter 3 under ’US Dollar Analysis’.

3.2

Parameters

In tables 3.1 through 3.5 the parameters used for the P&L analysis are given. Not all of the parameters are meant to be changed. Parameters like current headcount, P&L relations, labour parameters or investment parameters are likely to remain unchanged. Parameters like list price increase, sales growth, mean tax rate, exchange rate US dollar/euro, increase of purchased material/parts and so on, are adjustable.

3.2.1

Economic Parameters Table 3.1: Economic parameters

Sales growth Increase of list prices Estimated Future Dollar/Euro Rate Increase of raw material prices/GOODS MII Increase non-salary operating expenses Mean Tax Rate

10 % 4% 1,48 4% 4% 28 %

Raw material Purchase Increase Goods purchased from MII

10 % 10 %

Increase of discount Produced 2008 Exchange Rate 1e = US$ of 2008

0,50 % e 23.507.000 1,48

The sales growth is the main parameter in our analysis. As displayed in table 3.1 above is it set to 10%, but with a dropdown menu the values 5% and 15% can be chosen as well. All P&L tables are adjusted to react in a correct way to the chosen sales growth. For example, depending on the sales growth the limit in shift work is reached earlier or later than in the 10% case, which had to be implemented with if-clauses. Normally the increase of list prices depends on the Magnetrol policy. A 4% increase is

3.2 Parameters

52

now used to account for the 4% mean material price/expenses increase each year due to price inflation. The mean tax rate for 2008 was 28% and this value is used for later years. ˙ now this value The mean dollar/euro exchange rate for MINV in 2008 was 1,48 $/eFor is taken the same for the rest of the forecasted period, but can be adjusted if needed. The influence of the US dollar/euro exchange rate is calculated in the P&L tables as a multiplication by the 2008 exchange rate of 1,48 divided by the current rate. As the dollar becomes more expensive this fraction becomes larger and makes MII goods more expensive in the P&L. Each year the Magnetrol discount increases with 0,5 % for competitive reasons explained further in this text.

3.2.2

Profit & Loss Relations Table 3.2: Profit & Loss relations

Invoiced not shipped/List Magnetrol Discount Magnetrol/List Magnetrol Yearly Increase Discount Commission/List Magnetrol Total Other Sales/Total Invoiced Total Other Expenses/Total Invoiced Other Manufact Mat/Total Other Sales

0,01 0,27 0,005 0,01 0,06 0,05 0,22

The fractions or relations as calculated by dividing two 2008 P&L lines by each other. These relations are used to calculated the lines invoiced not shipped, discount Magnetrol, commissions and so on, for other years.

3.2 Parameters

3.2.3

53

Labour Parameters Table 3.3: Labour parameters

Increase services headcount Increase payroll cost Shift work salary increase Total annual working hours in production Mean labour cost (no shift work) Working days Holidays Hours per day

4,1 % 4% 15 % 1706,20 hrs 16,50 e / h 246 21,5 7,6 hrs

Salary share in total cost Engineering Field Sales Factory Sales Customer Satisfaction Marketing MIS Administration

0,8565 0,7877 0,9802 0,9954 0,3813 0,7737 0,3075

Table 3.3 shows the general parameters related to labour. Most of the data were collected from 2008 figures. To account for index adjustments of salaries there is a 4% payroll increase each year. The only figure here that needs some explanation is the increase in services headcount. Management target is as follows: If sales increases by 5% each year or 63% over a 10 year period, then services headcount may increase by 30% over 10 years to support the sales growth. This equals a yearly headcount increase of 2,66%. If sales increases by 10% each year or 159% over a 10 year period, then services headcount may increase by 50% over 10 years to support the sales growth. This equals a yearly headcount increase of 4,14%.

3.2 Parameters

54

If sales increases by 15% each year or 305% over a 10 year period, then services headcount may increase by 70% over 10 years to support the sales growth. This equals a yearly headcount increase of 5,45%.

3.2.4

Subcontracting Parameters Table 3.4: Subcontracting parameters

Price Subcontract Machining 2008 Annual price increase subcontracting Direct machining hours by MINV Zele Plant

40 e / h 4% 11541 hrs

Subcontracting price for machining is e 40/h and is calculated from MINV accounting numbers. The amount of direct machining hours currently available is used in further calculations to estimate to future need for subcontracting of machining. A 4% price inflation of subcontracting prices is incorporated.

3.2 Parameters

3.2.5

55

Investment Parameters Table 3.5: Investment parameters

Equipment Depreciation Period Machinery

10

Machining Current headcount

9,5

Welding Current numbers of welding stations Relationship #welders/station Cost of One Welding Station Exhausting device Current headcount

8 1,375 e 16.500 e 28.000 11

Assembly Current headcount Investment cost per head Maintenance cost per head per year

12 e 5.000 e 800

New Buildings Depreciation Period Building Basic cost Production Hall Basic cost Administration Building Safety Factor Financial Cost for Loan

20 e 1.000.000 e 600.000 15% 5%

Parameters like depreciation periods, current headcounts and investments with financial costs for building loans.

3.3 Assessment tool

3.3

56

Assessment tool

The results of section 3.4 through section 3.7 are obtained by using an Excel based assessment tool Baert (2009) that contains all the data and calculations for the three cases: no investment, investment and shift work. The file contains several sheets: a parameter sheet containing all the parameters discussed in section 3.2; a forecast sheet with the forecasted numbers as retrieved from ForecastXand the projection of these numbers on a 5%, 10% and 15% unit sales growth; separate sheets that calculate the required number of FTEs in case of a 5%, 10% and 15% growth. The influence of the (in)efficiency is incorporated in these sheets; an investment sheet with the most important depreciations and labour costs per year; separate sheets for each of the three cases:

– P&L No Investment used in section 3.4 – P&L Investment used in section 3.5 – P&L Shift Work used in section 3.6 calculations sheets where the data of the other sheets are used and summarized to draw conclusions concerning the dollar influence, case comparison or case analysis (with uncertainty).

The Excel file can be found on the cd-rom accompanied with this thesis.

3.4 3.4.1

Case study ’No Investment’ Analysis

If no investment is made and we know that the current production capacity is saturated, MINV sales can only increase if there is more work contracted out in the departments welding and machining. This is of course without considering the assembly department. Assembly is typically a process that cannot be subcontracted, so it remains the bottleneck of the plant. Apart from this we can see in the present a growth of MINV

3.4 Case study ’No Investment’

57

backlog, which will only keep on growing if no adjustments in production are made. In other words, sales may not increase or MINV won’t be able to keep the backlog under control. Meanwhile costs are growing due to inflation: raw material gets more expensive labour gets more expensive (estimated 4% each year) subcontracting gets more expensive

MINV has to increase its sales prices or decrease its discounts in order to keep up with these price increases and stay profitable. Of course this cannot be beneficial for competitive reasons. Let us begin our analysis with a definition of all the lines in the P&L of 2008 and their relation to the input variables. For each P&L line we will estimate the future values. In sections 3.4.1 to 3.6 all the lines are calculated using the basic values of the input variables. In the option analysis of section 4 two or more values (low-base-high) will be estimated for the input variables to account for uncertainty. Low-base-high values for the input variables are agreed upon with management. Total Net Sales Total Invoiced Magnetrol : sales of Magnetrol products.

– In the no-investment case List Magnetrol will be held constant, perhaps corrected for list price changes. To be realistic we add a annual 4% price increase which is normally taken to account for the increase in product costs, especially payroll cost (typical increase of 4% annually). Note that this total increase of Total Invoiced is not due to an increase in unit sales but due to an increase of prices. Unit sales is limited by production when no investment is made so it cannot increase. – The production limitation has a consequence for the sales in 2009 in the analysis. In 2008 sales was e 2,1 million too high as sales summed up to e 26,1 million and produced was only e 24 million. This became clear when backlog rose to e 8 million where normally e 5 million is the maximum backlog MINV can process in order to maintain a good customer service concerning lead times. In the analysis to eliminate this excessive backlog MINV can only sell for:


58

e 23, 507million . 1, 04 − e 3million = e 21, 447million = T otal Invoiced M agnetrol allowed f or 2009

Because we need to keep the partition of Total Invoiced Magnetrol into List Magnetrol (LM, positive), Discount Magnetrol (DM, negative) and Invoiced Not Shipped (INS, negative), we calculate them from Total Invoiced Magnetrol as follows: LM 2009 =

21.447.000 .1, 04 = e 30.020.027 S2008 1− + IN LM 2008 DM 2008 LM 2008

(3.1)

As you can see, the relationship DM/LM and INS/LM are taken from 2008. The LM is calculated with values from 2008 and has to be corrected for a 5% price increase. Then Total Invoiced Magnetrol is calculated again with these new figures. Because at the end of 2009 Magnetrol will have made up arrears, 2010 values can again be calculated with the producible 2008 amount of EUR 23,507 million. LM 2010 =

23.507.000 .1, 042 = e 34.219.611 IN S2008 + LM 2008 1− DM 2008 LM 2008

(3.2)

The following years are calculated with the 4% price increase as before. – Another important remark here when analyzing Total Invoiced Magnetrol are the discounts. In general there are two kinds of discounts. One is the discount given by direct sales people of Magnetrol to the customer. Second is the discount that Magnetrol gives to a distributor in order to allow them to make a profit and be competitive. When for example the distributors ought to sell a product for 100, but cannot sell it for more then 90, they need extra discount on the purchase price to ensure their profit margin. This discount can go up to 32,5%. The preceding phenomenon is due to competitors that are selling in the same countries as the distributors, but without a distributor network and with a direct sales force. In this way their prices can be lower than Magnetrol prices because there isn’t a distributor in between who also needs a profit margin.


59

The distributor of Magnetrol is now forced to lower its prices if he wants to sell his products, decreasing his margin. To help the distributors Magnetrol grants them a standard 32,5% discount on list price. This phenomenon was increasing in the past few years and since more than half of Magnetrol sales is made by distributors, this effect has to be incorporated in the analysis. A 0,5% annual growth of discounts could be a realistic figure, along with 0% and 1% as low and high value in the option analysis of chapter 4. So in the case study we will include a constant discount and a discount increase over 10 years from 26,6% to 31,6%. This effect of uncertainty will be incorporated in the option analysis at the end by using high - medium - low values.

Figure 3.1: The influence of discount on list prices of Magnetrol

Total Invoiced Other : products of other companies that are distributed by Magnetrol. According to Magnetrol personnel, this will become zero in the future. That is because the margins are much lower than Magnetrol products and Magnetrol started to produce similar products. By assumption let us decrease this number by 1/4th of the 2008 value each year. Also correct for price increase (4%). Total Commissions: commissions paid to distributors or agents. Take the 2008 percentage of List Magnetrol, being 0,8%. Total Other Sales: this includes the sales of extra services such as packaging, shipping, X-ray... These other sales will grow along with Total Invoiced Magnetrol. In 2008 Total Other Sales was 6,5% and in 2007 it was 7,44%. The mean


60

from 1993 to 2008 was 4,87%. One could conclude that there is an increasing trend as seen in the past few years for this relationship or the mean can be used for further calculations. In the financial analysis the mean of 4,87% is used.

Figure 3.2: Total other sales divided by total invoiced

Total Cost of Goods Total goods Magnetrol

– Raw Material : value of raw material purchased by the production plant in Zele. Include 4% price increase for future years to account for inflation and increasing prices due to world economic situation (scarcity of raw material). – Goods MII : the dollar value of the goods bought from MII will grow along with Total Invoiced Magnetrol. The important factor or input variable to consider here is the exchange rate dollar/euro. The analysis in section 3.7 shows the influence of the dollar value on MINV profits. In the assessment tool I made it is possible to change this rate in order to estimate the influence on MINV profit. In the option analysis we account for more than


61

one possible value of this rate, namely 0,9 $/e as the low value (expensive dollar) and 1,6 $/e as the high value (cheap dollar). The value of the dollar variable is set to 1,48 $/e in the base case. – Inventory: inventory is counted at the end of each year. In the no-investment case we keep this value constant, corrected for a 4% inflation. The real future value of the inventory can differ from these numbers because of two reasons. One is that Inventory is a snapshot of the moment and can vary depending on the production situation at that time. Apart from this snapshot, MINV can reduce its total inventory (WIP, stock) by introducing new techniques into its production system such as Lean techniques. Total Goods Other : prices paid for products from other companies that Magnetrol sells as a distributor (Total Invoiced Other). This value will go to 0 in a couple of years. By assumption let us decrease this number by 1/4th of the 2008 value each year. Also correct for inflation. Total Cost of Sales

– Other Manufacturing Material : this includes the costs of extra services such as packaging, shipping, X-ray... These costs will be set equal to the 2008 percentage of Total Other Sales. Other M anuf acturing M aterial = 22% (3.3) T otal Other Sales – Stock Room: depreciations of the new stock room hall, stock room forklift... Stock room line will be held constant because the stock room hall has just been constructed and has a depreciation period of 20 years. Apart from that, stock room material has to be replaced and repaired from time to time, therefore this cost too will be held constant. – Machining: depreciations of the different machines. This value will be held constant in the next years to account for future replacement and repair of machines. – Labour Machining: labour cost in Machining, increased with 4% per year. Headcount stays the same under the no-investment decision. – Welding: depreciations of the different machines. This value will be held constant in the next years to account for future replacement and maintenance of welding equipment. – Labour Welding: Labour cost in Welding, increased with 4% per year. Headcount stays the same under the no-investment decision.


62

– Assembly: depreciations of the different machines. This value will be held constant in the next years to account for future replacement and repair of assembly equipment. – Labour Assembly: Labour cost in Assembly, increased with 4% per year. Headcount stays the same under the no-investment decision. – Quality Control : increases along with Total Invoiced Magnetrol and is in general a payroll cost. – Shop-General : depreciations of equipment that is not used in only one specific department, such as forklifts and production hall depreciation. The depreciation period of the current production hall however is already completed and the only depreciations left are those of equipment that is usually replaced after its depreciation period. All other costs in this line stay the same under the no-investment case. – IC/PC : this line contains only payroll costs and will be accounted for a 4% increase in salary. Even an increase in production will not change the value of this line. In other words, the headcount of this department will suffice even if there is an increase in production. – Quality Approval : for the same reason as IC/PC this line will not change, except to account for a salary increase of 4%. – Direct Labour Subcontracting: includes the cost of subcontracting in machining and welding. Will be held constant, except to account for an increase in subcontracting prices of 4%. In case of no investment, will this amount remain unchanged? If we decide not to invest, sales cannot increase because of the limiting capacity in assembly (no subcontracting possible in assembly) and with current sales subcontracting is necessary in machining and welding to keep up with sales.

Operating Expenses - the lines written below contain not only payroll costs but also costs like certificates (engineering), travel expenses (field sales), the mainframe depreciation (MIS) or the marketing budget. We separate salaries from other expenses because salaries depend on the factor ’payroll increase’ which is 4% in the base case and other expenses (travel, certificates, clothing, meals ...) depend on the inflation percentage, which has also 4% as base value. In the option analysis it is possible that both percentages differ from one another. The amount of salaries in each of the following service departments is calculated. These calculations are based on the numbers found in the 2008 consolidated balance sheet and profit&loss statement of Magnetrol Europe.


63

Engineering

Salaries 382.742, 79 = = 0, 8565 T otal Engineering 446.851, 42

(3.4)

Field Sales: from adding Sales Administration to Product Support.

Salaries 2.180.426, 31 + 5.987, 24 = = 0, 7877 T otal F ield Sales 2.758.961, 16 + 16.817, 28

(3.5)

Factory Sales: from adding Export Compliance to Inside Sales.

Salaries 116.576, 65 + 1.226.823, 05 = = 0, 9802 T otal F actory Sales 125.199, 56 + 1.245.338, 48

(3.6)

Customer Satisfaction

Salaries 322.805, 47 = = 0, 9954 T otal Customer Satisf action 324.282, 29

(3.7)

280.352, 48 Salaries = = 0, 3813 T otal M arketing 735.173, 00

(3.8)

Salaries 185.595, 33 = = 0, 7737 T otal M IS 239.890, 91

(3.9)

Marketing

MIS

Administration: from adding Accounting to General Administration and shipping.

Salaries 300.857, 24 + 215.284, 5 + 150.035, 28 = = 0, 3075 T otal Administration 367.030, 95 + 1.643.184, 96 + 156.443, 68 (3.10)

Total Other Expenses - management fees for MII, lawyer expenses, financial expenses... This value can be obtained as 5% of Total Invoiced.

3.5 Case study ’Investment’

3.4.2

64

Results

As you are probably interested in the results of the analysis of the ’no investment’ case, I summarized it in the graph of figure 3.3 of the estimated profit over a period of 10 years.

Figure 3.3: Financial evaluation of ’no investment’ case

3.5 3.5.1

Case study ’Investment’ Analysis

Considering the current overload in production it is clear that if management sets increasing sales targets for the future, Magnetrol has to invest in new infrastructure to cope with this increasing sales. We want to investigate the effect of the investments on the P&L of MINV. In chapter 4 the effect of uncertainty of input variables is taken into account in an option analysis. The investments mentioned below are only a guideline and can be different from management decisions, for example the purchase of new forklifts, the expansion of certain service departments or the installation of rolling bridges to facilitate movement of large work pieces through the departments. Magnetrol uses a linear depreciation system, which means the cost of a piece of equipment is equally spread over the depreciation period.


65

First we have to define what is understood by increasing production capabilities. First 5 years: Start building a new hall in 2010 (production floor, facilities for workers) and bring into use in 2011. Hiring new workers according to FTE-numbers in the assessment tool that I have made. Buying new production equipment according to FTEs and reorganizing production floor lay out. No expansion in machining since this is the easiest process to subcontract and floor space is limited. Maybe installing rolling bridges in new production floor configuration to facilitate movement of large and heavy work pieces through the factory.

Next 5 years: Take into use a new office building in 2015 along with larger demonstration room, conference room etc. The new office building has two purposes. First of all offices are installed in the new building when expanding manpower of the supporting services. Secondly is the customer perception when he visits the plant. Hiring new workers and buying new material according to FTE-numbers in the assessment tool Baert (2009). Hiring new employees to support the increase in sales.

The advantage of doing all this in phases is that management can adapt its expansion decision whenever they believe it is appropriate. The only condition is that the lay out is organized in such a way that it is possible to gradually install for example new welding stations. Meanwhile the free room can be used as storage for WIP in expectation of a production revision to shorten lead times and reduce intermediate stocks. These two matters can evolve together: placing new equipment and finding ways to reduce intermediate stock. The reduction in intermediate stock will be a project for the future. Magnetrol is considering the further elaboration of a ERP system in its production plant in Zele. Because of the better visibility of WIP when using an ERP system compared to the


66

current system, Magnetrol Zele can start working on projects to reduce WIP as well as lead time. Of course in the long term if their is a saturation of the new production floor shift work can be still implemented to enable a further sales growth. Discussion of each of the P&L lines. Total Net Sales Total Invoiced Magnetrol.

In the investment case List Magnetrol will increase with a mean of 10% annually (in the end analysis, low: 5% - high: 15%) and corrected for list price changes. To be realistic we add a annual 4% price increase. Note that this total increase of Total Invoiced is now due to an increase in unit sales and to an increase of prices. Another important remark is that the expansion can be set to work until 2011, so in 2009 and 2010 production is still limited as discussed in section 3.4.1. After 2011 sales is not limited anymore to the capacity of production as we let the production capacity evolve in phases along with the increase in sales, as explained before. Thus: – 2009 and 2010: Price adaptation, but no sales growth – From 2011 - ...: Price adaptation and sales growth

Total Invoiced Other : products of other companies that are distributed by Magnetrol. According to Magnetrol personnel, this value will evolve to zero in the near future. That is because the margins are much lower than Magnetrol products and Magnetrol started to produce similar products. By assumption let’s decrease this number by 1/4th of the 2008 value each year. Also correct for annual price increase (4%). Total Commissions: commissions paid to distributors or agents. Take the 2008 percentage of List Magnetrol. Total Other Sales: this includes the sales of extra services such as packaging, shipping, X-ray... These other sales will grow along with Total Invoiced Magnetrol. As in the no-investment case, let us work with a mean of 4,87%.


67

Total Cost of Goods Total Goods Magnetrol

– Raw Material : value of raw material purchased by the production plant in Zele. Include 4% price increase every year and a 10% annual unit growth after 2011. – Goods MII : the dollar value of the goods bought with MII will grow along with Total Invoiced Magnetrol. The important factor here is the exchange rate euro-dollar that will influence MINV profits. In our analysis we will account for several possible values of this rate. – Inventory: increases in value with an estimated 4% per year and in amount by 10% like the sales target growth of MINV. Total Goods Other : prices paid for products from other companies that Magnetrol sells as a distributor (Total Invoiced Other ). This value will evolve to 0 in a couple of years. By assumption let us decrease this number by 1/4th of the 2008 value each year. Also correct for price increase. Total Cost of Sales

– Other Manufacturing Material: this includes the costs of extra services such as packaging, shipping, X-ray... These costs will be set equal to the 2008 percentage of Total Other Sales, being 22%. – Stock Room: depreciations of the new stock room hall, stock room forklift... Stock room line will be held constant because the stock room hall has just been constructed and has a depreciation period of 20 years. Apart from that, stock room material has to be replaced and repaired from time to time, therefore this cost too will be held constant. Increasing the MINV production can of course lead to more material handling in the stockroom and therefore more associated costs. However, in line with future investments the implementation of an Inventory Management system is planned. Such a system consists of processes concerning tracking, handling and managing of goods and materials that are held in the stockroom. After successful implementation an effective inventory management system can not only reduce operational costs, but reduce lead times as stock-outs are greatly reduced. The latter will increase customer satisfaction and therefore have a beneficial effect on sales.


68

– Machining: depreciations of the different machines. Since there will be no expansion in machining this value will be held constant in the next years to account for future replacement and repair of machines. – Labour Machining: Labour cost in machining, increased with 4% per year. Headcount stays the same even under the investment decision because there will be no expansion in machining. – Welding: depreciations of the different machines. Currently there are 11 workers (incl. foreman) in assembly for 8 welding stations. In the MINV production presentation of 2009 we see that 6700 production hours in welding are subcontracted. This equals approximately 4 welders. This total of 15 workers very well approximates the FTEs number I calculated in the Excel tool, which was 14. The reason these 4 extra men are contracted out is that there is not enough room to install more welding stations. By assumption let us use the relationship

11 workers 8 welding stations

= 1, 375

We have a mean of 1,375 workers per welding station. For the future number of welding stations we divide the forecasted number of FTEs by 1,375 and round the result upwards. Note that we work with the 11 workers, including foreman, because the FTEs are calculated with inefficiency percentages including foreman. In other words, in a future FTE number there are foreman included. New investments include: * New exhausting device: e 28.000 (depending on lay out) * New welding station costs e 16.500 per station and this cost is depreciated over a period of 10 years linearly. Table 3.6: Costs welding station

1 MIG device 1 TIG device 1 weld manipulator Total cost Depreciation period

e 6.000 e 6.000 e 4.500 e 16.500 10 yrs

The 2008 welding cost is held constant to account for future maintenance, repair and replacement of the current equipment.


69

– Labour Welding: Labour cost in welding, increased with 4% per year. Headcount in 2009-2010 will be held equal to the 2008 headcount because there is no room for more workstations. From 2011-... we use the headcount calculated in the assessment tool. So actually we are using the numbers of the mentioned file with a 2-year lag. – Assembly: depreciations of the different machines and tools. This value will be held constant in the next years to account for future replacement and repair of assembly equipment. From 2011 on there is room for more equipment. Because it is not a trivial case to forecast which equipment will be needed in the future, further research must be made. Meanwhile we will work with estimates based on the forecasted FTEs. We calculate the total value of the current machines and tools used by current assembly workers and forecast these according to the number of forecasted FTEs. Current headcount in assembly is approximately 12 according to the production hours report of IC/PC. Current depreciations + costs = e 20.652,64 An estimate for the investment cost per head in assembly is about e 5.000 and an extra e 800 per head per year for maintenance of equipment. The investments can be considered per head because of the work structure within assembly. Every blue collar has his work table with equipment, so practically every extra worker in assembly requires a work table and specific equipment. In other words, if there are 18 people required tomorrow in stead of 12 today than the investment will grow by half the value it is today. – Labour Assembly: Labour cost in the assembly department, increased with 4% per year. Headcount in 2009-2010 will be held equal to the 2008 headcount because there is no room for more workstations. From 2011-... we use the headcount calculated in the assessment tool. Again we are using the numbers of the mentioned file with a 2-year lag.


70

Figure 3.4: Labour costs

– Quality Control : increases along with Total Invoiced Magnetrol from 2011 on with the management target that if sales increases with 2,6 (10% annual growth) then service personnel costs will increase with 1,5 (annual service growth of 4,14%). This relationship is justified since further automation will make sure that less personnel is needed for the same job. Of course the annual payroll increase of 4% must be taken into account for all years. – Shop-General : depreciations of material that is not used in only one specific department, such as forklifts, production hall depreciation. In the first 2 years the same number of 2008 is used and from 2011 on following depreciations are added. * New production hall, facilities (e.g. dressing room, toilets, office): Table 3.7: Facility costs

Price Safety factor New floor surface Financial loan cost Depreciation on 20 yrs

e 500/m2 15% 2.000m2 5% e 60.375 / year


71

* Expansion logistic infrastructure: · Purchase 1 new forklift of e 11.000. Depreciation on 10 years. These forklifts are used to transport heavy parts. · For lighter parts, 1 more electric pallet truck is bought for e 5.000. * Rolling bridge (optional): 4 units across the production hall to facilitate the movement of big, heavy parts. This investment is left out for now because the production lay-out are still under construction at this moment. At the time of writing an alternative lay-out was introduced that could eliminate the need for rolling bridges. * Cost for rearranging shop floor.

– IC/PC : this line contains only payroll costs and will be accounted for a 4% increase in salary. Even an increase in production will not change the value of this line. In other words, the headcount of this department will suffice even if there is an increase in production, according to management. – Quality Approval : for the same reason as Quality Control this line will increase from 2011 on with 4,14% annually. Because this is mainly a payroll cost, we have to add 4% cost each year. – Direct Labour Subcontracting: includes the cost of subcontracting in machining and welding, corrected for inflation. From 2011 on, since we assume machining is not expanding, all additional hours (calculated hours - available hours) will be subcontracted. The price of a subcontracting hour is not directly available, but is estimated to be e 40/hr. Subsequently we multiply this cost per hour each year by the number of hours in that year that could not be processed by the machining department of the MINV plant. This increase will take effect from 2011 on, when the new capacity in other departments is installed. We are going to use the numbers forecasted in the assessment tool - 10% growth. To calculate how many hours we have to subcontract in the future we take the forecasted direct hours in machining and we subtract the amount of direct hours that is currently available. These numbers are available through production reports of 2008 that give us the total direct and indirect hours achieved in each of the departments. Because it was obvious from experience that in 2008 each of the departments


72

worked under full capacity usage, the number obtained from these reports is approximately the maximum amount of direct hours that can be achieved in production at this moment, being 11.541,17 hours. In 2009 and 2010, where the investment has not yet been completed, these hours are held constant and the same amount as in 2008 will be subcontracted because there is no growth possible due to the assembly restriction. Because even after 2011 we assume machining department will not expand, this department will have the same amount of direct hours as in 2008 and every hour (as forecasted in tool) that is needed on top of this figure will be subcontracted. An important remark about the needed direct hours for 2008 is the following. We see from the tool that we estimate a requirement of 17.883,8 direct production hours in 2008 within machining. From the production reports we can see that only 11541 hours have been achieved in Magnetrol itself plus an extra 2.220 hours were subcontracted. The sum of these two is 13.761 hours, which does not come close to the estimated need of 17.883,8 direct hours. Of course this phenomenon is due to the overload in production and can be seen in the increase of the MINV backlog in 2008. In other words in 2008 Magnetrol Zele production plant could keep up with the huge increase in sales, so, only speaking for machining, could not deliver the 17883,8 hours needed to produce the amount that was demanded by the market.

Operating Expenses: this is a service payroll cost as well as a budget cost and a depreciation cost and by assumption the salaries will increase with a yearly 4% and from 2011 on an increase of service costs of 4,14%. In contrast with the no-investment case we have to make a division of each of the lines, if relevant, because for example in marketing the advertising budget will increase more than the payroll cost in marketing.


73

The salary percentages (SP) are the same as for the no-investment case. The formulas are: 2009-2010:

V alueY earX = V alueY ear2008.(SP.1, 04X−2008 + (1 − SP ).1, 04X−2008 ) (3.11) 2011-...:

V alueY earX = V alueY ear2008.(SP.1, 04X−2008 + (1 − SP ).1, 04X−2008 ).1, 0414X−2010 (3.12) We apply these percentages for the lines Engineering, Field Sales, Factory Sales, Customer Satisfaction, Marketing, MIS and Administration. Administration: For a good price estimate for the new administrative building in 2015, consider the total cost of the administrative infrastructure built in 1998. This value will be a good estimate of the cost of a new building of the same size. The total cost is estimated to be e 600.000, plus 15% safety and 5% financial loan cost, so e 724.500.

Total Other Expenses: management fees for MII, lawyer expenses, financial expenses... This value can be obtained as 5% of Total Invoiced.

3.5.2

Results

The results of the analysis above are summarized in figure 3.5 of the estimated profit over a period of 10 years. All input variables are set to their basic values. Uncertainty of the input variables is taken into account in the option analysis of chapter 4.

3.6 Case study ’Shift Work’

74

Figure 3.5: Financial evaluation of ’investment’ case if 10% growth occurs

3.6 3.6.1

Case study ’Shift Work’ Analysis

In case of shift work only part of the investments made in the investment case are made such as new service personnel and a new administrative building in 2015 to handle the increasing sales. In contrast with the no investment case shift work allows Magnetrol to expand sales. However sales can only increase until the moment the maximum capacity in production is reached, which will be in 2014 as mentioned below. In this respect shift work can be considered an intermediate situation between no investment and investment cases. If Magnetrol plans a long term growth, shift work can only be considered as an intermediate solution, but investments have to be made to achieve long term growth. As a certain preparation period is necessary to set the framework for a 2-shift system, changes will apply from 2011. Now what costs are there in a shift work environment: Headcount Salary costs increase with 15% for the workers in a shift system.


75

Headcount will increase in each of the production departments according to the FTEs calculated in our tool with that difference that the numbers used in the investment case have to be corrected again. In the investment case we introduced improvements in inefficiency because of the larger floor space. With no investment in a larger floor space, current inefficiencies such as needless material handling, transport and safety issues will be much harder to resolve. In other words we keep the current inefficiency instead of correcting it for improvements such as in the investment case. Also we could correct the FTE numbers in production for a productivity loss due to shift work, but that is left out in this study.

Figure 3.6: Headcount in the production departments

Shift work is also applied in machining department. Therefore less subcontracting is necessary. An important remark here is that the maximum number of FTEs that can be employed is limited. This limitation on FTEs is twice the current headcount if there is no extra floor space created.

Current headcount is approximately (from production hours report of 2008): – M achining : 9, 5 =⇒ M AX = 19 – W elding : 11 =⇒ M AX = 22


76

– Assembly : 12 =⇒ M AX = 24 A consequence is that shift work headcount can only increase until 2014 for the 10% sales growth case and therefore sales cannot increase anymore from 2015 on. If estimated headcount in machining or welding exceed the maximum we can always subcontract this surplus at e 40/hr.

Figure 3.7: Corrected headcount in the production departments

Also, if sales does not increase then an increase in services (engineering, field sales, factory sales, customer satisfaction, marketing, MIS, administration) is not necessary from 2015 on.

Up to 2014:

V alueY earX P P = ServiceV alue2008.( .1, 04X−2008 + (1 − ).1, 04X−2008 ).1, 0414X−2010 T T (3.13) Explanation: the service value is taken of the 2008 P&L and separated into a payroll and non-payroll part, according to the P/T relationships. The payroll


77

part is increased with 4% each year, the non-salary related costs are corrected for inflation. Subsequently this value is increased from 2011 on, the year where the shift work framework is completely set up, with 4,14% each year. From 2015 on:

V alueY earX P P = ServiceV alue2008.( .1, 04X−2008 + (1 − ).1, 04X−2008 ).1, 04142014−2010 T T (3.14)

Explanation: same as before, but now the service department growth is limited to the year 2014. This is the year where the maximum capacity of assembly is achieved and there is no production growth possible anymore. Graph in figure 3.8 shows the P/T values of above mentioned formulas.

Figure 3.8: Operating expenses - payroll cost/total cost

The cost evolution of the different service departments is summarized in graph 3.9.


78

Figure 3.9: Cost evolution of service departments

To give support to each of the shifts, lead men have to be hired as well. However we do not have to add extra costs for this as lead men are already incorporated in the FTEs.

Subcontracting - Depending on the sales growth (5% - 10% - 15%) other situations occur. Let’s have a look at the 10% case. Sales cannot increase from 2015 on, so the gap in machining has to be filled up by subcontracting. To cope with the sales of 2013 and 2014 there are respectively 21 and 23 workers needed in machining and only 19 can be set to work there. So the equivalent of respectively 2 and 4 workers has to be subcontracted, with associated costs of: 2 workers: 2 . 1706,2 hrs . e 40/hr . 1, 042013−2008 = e 166.068

3.7 US Dollar analysis

79

4 workers: 4 . 1706,2 hrs . e 40/hr . 1, 042014−2008 = e 345.422

Inventory 2009-2010: value increase of an estimated 4% per year 2011-2014: value increase of 4% plus amount increase of 10% per year 2015-...: value increase of 4% each year.

3.6.2

Results

The results of the analysis above are summarized in figure 3.6.2 of the estimated profit over a period of 10 years. All input variables are set to their basic values. Uncertainty of the input variables is taken into account in the option analysis of chapter 4.

Figure 3.10: Financial evaluation of the ’shift work’ case

3.7 3.7.1

US Dollar analysis Introduction

The US dollar/euro exchange rate has known a great evolution from the beginning of the 21st century. When the exchange rate was still under 1 before mid 2002, it is now


80

around 1,3. In other words the dollar became cheaper over the years with regard to the euro. Now if we zoom in on the last months there is a drop from 1,6 to 1,3 or a change of 18,75%. One can imagine that a change of that size can have a influence of importance on the profit of Magnetrol International N.V. which purchases a large number of parts from its mother company in Downers Grove. Of course this influence becomes larger as MINV purchases more parts from MII as in the ’investment’ and ’shift work’ case.

Figure 3.11: Dollar/Euro exchange rate from 2002 to 2009 BC investments (2009)

3.7.2

Analysis

Let us investigate the financial implications of the US dollar/euro exchange rate for MINV in the three cases we’ve been studying in the beginning of this chapter. As studied rate values let us consider a rate of 1,28 around the time this analysis was written, 1,48 as the mean rate of 2008 that was established in the MINV accounts and a rate of 1,6 as was the peak in the summer of 2008. To be complete a dollar/euro rate of 0,9 is added to the analysis. 0,9 $/e was the exchange rate only seven years ago. The impact of these changes are easily calculated with the Excel assessment tool. The analysis below goes out from the assumption that the chosen rate is the mean rate throughout the total period studied, which is 10 years in this thesis. For the case where no investment is made the influence on the profit (after tax) of the US dollar/euro exchange rates is shown graphically in figure 3.12.


81

Figure 3.12: Influence of dollar/euro exchange rate on the profit in the ’no investment’ case

The difference in profit between the 0,9 rate and the 1,6 rate in 2009 is e 2.573.489 and in 2018 e 3.662.878. For the case where the investment is made the influence of the US dollar/euro exchange rates is shown graphically below.

Figure 3.13: Influence of dollar/euro exchange rate on the profit in the ’investment’ case

The difference in profit between the 0,9 rate and the 1,6 rate in 2009 is e 2.573.489 and in 2018 e 7.851.704. For the case where a shift work system is being introduced the influence of the US dollar/euro exchange rates is shown graphically in figure 3.14.


82

Figure 3.14: Influence of dollar/euro exchange rate on the profit in the ’shift work’ case

The difference in profit between the 1,28 rate and the 1,6 rate in 2009 is e 2.573.489 and in 2018 e 5.362.820.

3.7.3

Conclusion

Apparently the US dollar/euro rate is a factor that cannot be neglected. In all three cases a shift in long term US dollar/euro exchange rate can have a large influence on the profit of MINV since a large amount of parts is purchased in the USA. Having a look at the P&L tables in the assessment tool shows clearly the relative importance of the GOODS MII line.

OPTION ANALYSIS

83

Chapter 4 Option analysis 4.1

Introduction

A rational way to compare 2-shift system with the investment case is to make an option analysis of the situation. In an option analysis different options or cases are analyzed using simulation techniques in order to make allowance for uncertainty. A decision has to be made which option to implement based on a decision criterion. Each option can consist of multiple decisions, but these underlying decisions are bundled in two options: investment (investing in larger production hall, new office building, new production equipment) and shift work (introducing a 2-shift system, investing in new office building).

In this thesis there are in fact three cases, options: no investment, investment and shift work. The cashflows of the investment case and shift work case are calculated relative to the no investment option. In this way the direct gain from the option can be seen. Next determine which categories are deterministic and are calculated from other categories (appendix B). These categories form the deterministic model and will be calculated from the ones that are not deterministic. Examples of these categories are Total Invoiced, Total Net Sales, Commissions... All other fields containing for example labour costs, material costs, subcontracting costs and cost of goods from the USA (due to uncertain dollar value), are probabilistic. There is uncertainty about their values because they depend on input variables that are hard to predict. In this case we will make assumptions and give a range of 2 or 3 values for the input variables. For example, for the dollar/euro exchange rate 3 possible ˙ values are included: 0,9 $/e - 1,48 $/e - 1,6 $/eThe exchange rate affects the Euro price of the goods bought from the production unit in Downers Grove. Apart from a

4.2 The base case

84

nominal value, we have a high and a low value to represent the variation of the variable due to uncertainty. The uncertainties as mentioned above are used in the Monte Carlo simulation of section 4.3.2 in which only the input variables with the most impact on the NPV of the cash flows, can vary. The impact of the input variables can be calculated by performing a sensitivity analysis where for each variable the decision criterion is calculated for the different variable values and the other variables are kept at their base value. The variables that cause the greatest change in the NPV are used in the Monte Carlo simulation.

4.2

The base case

Graph 4.2 shows a comparison of the P&L profits after taxation for the three cases where the input variables are set to their basic values as in section 3.2.

Figure 4.1: PAT over a period of 10 years for the 3 cases

There are two reasons why this analysis is too superficial. One is that all the input variables’ values are set to their basic values which excludes the effect of uncertainty. For example, the investment cost of a new production facility is set to e 1.000.000, but depending on the degree of finishing it could easily go up to e 1.500.000. Another example is that the dollar/euro exchange rate is set to 1,48 $/e but the past showed rates below 1 $/e or even up to 1,6 $/e .

4.3 Option analysis

85

The second reason why this analysis does not suffice is that the true impact of the investments is not correctly implemented in the P&L calculations. First of all the time value of money is neglected. There is a difference in spending e 1.000.000 in 2010 or spending it in 2014. Therefore we should work with discounted values as calculated later on. Also the P&L analysis shows no direct influence from the money invested, only from the depreciations resulting from the investments. For these reasons we proceed to a so called option analysis where we use the net present value of the cash flows as a reliable measure for the profitability of investment and shift work case.

4.3

Option analysis

Uncertainty makes it unreliable to compare investment and shift work case with input variables set to their mean values. A more profound study can be found in the option analysis. The goal of such an analysis is to compare to possible options in case of uncertainty. First an influence diagram is made such as in appendix B to understand the structure and be able to perform the calculations. All of these relations are incorporated in the Excel assessment tool resulting from the detailed study in the beginning of this chapter.

4.3.1

Sensitivity analysis

The variation of some input variables in the assessment tool are subjected to uncertainty and those variables are tested in a sensitivity analysis where a variable is given 2 or 3 possible values: a nominal or basic value, a low and high value. The variables that give the largest deviation in the decision criterion, NPV of cash flows after taxation, are held for further analysis. The following variables are considered as being uncertain and are used for a sensitivity analysis.

4.3 Option analysis

86

Table 4.1: Chosen variables for a sensitivity analysis

Investment Sales growth x Dollar value x Inflation percentage x Cost production hall x Cost administrative building x Payroll increase x Discount increase x Initial ineff. decrease x Yearly ineff. decrease x Subcontracting hour x

Shift work Low - Base - High x 5% - 10% - 15% x 0,9 - 1,48 - 1,6 $/e x 3% - 4% - 5% e 1.000.000 - e 1.500.000 x e 600.000 - e 900.000 x 2% - 4% x 0% - 0,5% - 1% 5% - 10% - 15% 0% - 1% - 2% x e 35 - e 40 - e 50

The timing of the building investments is fixed (2010 and 2014). The timing of the investments in welding and assembly depend on the sales growth that is experienced at that time and is incorporated in the calculations of the assessment tool. Net present value of cash flows after taxation is chosen as the decision criterion and is calculated as 9 X (1 − T ax rate).savingsi + T ax rate.depreciationi − capital expenditurei (4.1) (1 + discount rate)i i=1

Tables C.1 and C.2 of appendix C show the cash flows resulting from each option (investment or shift work) for every year, starting with 2009 (year 0) till 2018 (year 9). This can be achieved by subtracting the cash flows of the no investment case from the option under analysis resulting in the cash flows only depending on the either the investment decision or the shift work decision. The cash flows are deducted from the P&L tables. The tax savings from the new depreciations are calculated separately and are also displayed in the tables. The value of the capital investments are displayed in the upper line. At the bottom of each table the NPV of the cash flows after tax are displayed. Note that in the cash flow table for the investment option 2009 is left out and in the shift work case 2009 as well as 2010 because all values are 0. Next we determine which input variables are to be used in the Monte Carlo simulation. To determine this a sensitivity analysis is performed for the variables of table 4.3.1.

4.3 Option analysis

87

The results of this analysis can be found in tables C.3 and C.4 of appendix C. From each table we select the 4 input variables that give the largest deviation on the NPV, being: sales growth, dollar value, inflation percentage and yearly discount increase. These variables are selected for the Monte Carlo simulation. The results of the sensitivity analysis are as follows. The base value for option 1 is e 13,855 million and e 10,155 million for option 2. The value ranges are e 4,522 million - e 25,831 million for option 1 and e 5,575 million - e 14,161 million for option 2. As the reader can see both intervals overlap and no clear conclusion can be drawn.

4.3.2

Monte Carlo simulation

The next step is performing a Monte Carlo simulation. First a table is constructed with all possible combinations of the 4 selected input variables with 3 values each, resulting in 81 possible scenarios per option. The combinations are displayed in tables on pages 96 and 98 of appendix C. The probabilities of each of the variables’ values are given in table 4.3.2. By multiplying the correct probabilities (see table 4.3.2) for each scenario the probability is calculated that a certain scenario occurs. As a preparation for the Monte Carlo simulation an extra column is added with the cumulative probabilities. Table 4.2: Probabilities of variables’ values

Variable Low Sales growth 0,45 Dollar value 0,33.. Inflation percentage 0,4 Yearly discount increase 0,25

Base 0,45 0,33.. 0,4 0,5

High 0,1 0,33.. 0,2 0,25

Before proceding take a quick look at the 3x3x3x3 combination tables on pages 96 and 98. Clearly the shift work option is better than the investment option if there is a low sales growth (5%). In case of a large sales growth (10% and 15%) the investment option is preferable. After completing the scenario tables a new Excel table is constructed with 10.000 random numbers generated for each option. The random numbers are generated by the Excel function Rand() between 0 and 1, and are meant to make a random selection from the scenario/combinations list. For each random number Excel searches its position in the cumulative probability column of the scenario list. If the exact number is not in the column then the greatest number smaller than our number is selected. As output the

4.3 Option analysis

88

search function gives the NPV value in the row below the number that was found. That is because Excel finds the lower limit of the interval that contains the random number and we need the upper limit of the interval. An easy way to understand this way of simulating is to see it as a darts game. Each scenario gets a part of the disc according to its probability where the sum of all the slices adds up to the whole disc (100 %). A blindfolded person throws a dart at the disc of figure 4.3.2 and hits randomly one of the slices. This will be the selected scenario. The larger a slice is (or the larger the probability of that scenario is), the more chance it has to get hit by the dart.

Figure 4.2: Darts game illustration of Monte Carlo simulation

This is done for all 10.000 random numbers and for each option, and a mean is calculated per option. In other words Excel simulates 10.000 times a possible scenario where scenarios with a higher probability are more likely to occur. The mean of all 10.000 NPV values is calculated for each option. Each time Excel simulates 10.000 random numbers this mean can differ a bit. To conclude the analysis an extra table is made with 200 of such means for both the investment as the shift work option. Finally the mean of the means is calculated. As can be easily understood these two numbers are more reliable to compare with each other than taking the NPV values for the base case and comparing those values. By performing a Monte Carlo simulation uncertainty is taken into account and more reliable conclusions can be drawn.

4.3 Option analysis

4.3.3

89

Results

The Monte Carlo simulations themselves are too long to include on paper but are supplied on the cd-rom delivered with this book. The results of the simulation are displayed in the cumulative graph of figure 4.3.3.

Figure 4.3: Cumulative probabilities as perceived in Monte Carlo simulation

Based on graph 4.3.3 we can draw the conclusion that in 40% of the cases the shift work option is preferable to the investment option. In 60% of the cases the investment option is better. The horizontal distance between the graphs denote the difference in NPV. Of course this analysis cannot predict what will really happen, but it gives a good idea of the difference between the shift work and investment option. In 30% of the cases the NPV of the cash flows resulting from investing is below e 5.000.000 and in less than 20% of the cases the NPV resulting from introducing a 2-shift system is less than e 5.000.000. The scenarios where a shift system performs better are in the lower end tail of the distribution and have typically a low sales growth of 5 %. If Magnetrol is expecting a low sales growth then implementing a 2-shift system might be the better solution (when not considering the limited floor space issues). In 10% of the cases the NPV of the shift work option is above e 11.000.000 and in 45% the NPV of the investment option is above this value. In the higher end tail the investment option shows to be the better solution. That is because the 2-shift option is quickly saturated which puts a stop to further sales growth.

4.3 Option analysis

90

Taking the mean of the simulation of 10.000 numbers and simulating this mean 200 times gives a mean of means of e 10.093.171 for the investment option and e 7.824.048 for the shift work option, in favour of the investment option. Based on the cumulative graph and the calculated means, we conclude that investing in new production facilities is the better option of the two. Besides the financial conclusion, one must also consider the social impact of the 2-shift option. First management has to make the current workers agree to work in shifts, and secondly the immense difficulties have to be considered to find additional qualified workers that are willing to work in shift. One has to consider that in the industrial park of Zele only few companies work in shift, so that finding workers in the area to do so may be very difficult if not impossible.

BIBLIOGRAPHY

91

Bibliography N. Baert (2009). Assessment tool. Assessment tool.xls. BC investments (2009). Dollar/Euro exchange rate. http://www.bcinvestments. net/chart.php. P. dr. ir. R. Van Landeghem (2008). LOG forecasting 2008 X.pdf.

Advprolog forecasting 2008 x.

ADVPRO-

Magnetrol (2009). Magnetrol International - About Us. http://www.magnetrol.com/ uk/html/about_us.asp. MINV (2008a). Balance 12-07 auditor. BALANCE 12-07 AUDITOR.xls. MINV (2008b). Statistics MINV November 2008 YTD. Statistics MINV2008NOV.xls. MINV (2009). Literature cross reference list. Literature cross reference list.xls. U.S. Energy Information Administration (2009). Cushing, OK WTI Spot Price FOB (Dollars per Barrel). http://tonto.eia.doe.gov/dnav/pet/hist/rwtcd.htm. C. Verstraete (2009). Analysis and redesign of the production of measurement instruments. Master’s thesis, University of Ghent. J. H. Wilson & B. Keating (2002). Business Forecasting. McGraw-Hill Higher Education.

SALES SURVEY

Appendix A Sales survey

92

P&L STRUCTURE

Appendix B P&L structure

93

P&L STRUCTURE

94

P&L STRUCTURE

95

OPTION ANALYSIS

96

Appendix C Option analysis Below you can find the scenarios table with the 3x3x3x3 combinations for the investment option. Value 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

3.076 2.353 1.629 3.244 2.479 1.714 3.410 2.601 1.792 4.981 4.258 3.535 5.287 4.522 3.757 5.601 4.791 3.982 5.212 4.489 3.766 5.535

Prob 0 0,0133 0,0267 0,0133 0,0133 0,0267 0,0133 0,0067 0,0133 0,0067 0,0133 0,0267 0,0133 0,0133 0,0267 0,0133 0,0067 0,0133 0,0067 0,0133 0,0267 0,0133 0,0133

Cum Prob 0 0,0133 0,0400 0,0533 0,0667 0,0933 0,1067 0,1133 0,1267 0,1333 0,1467 0,1733 0,1867 0,2000 0,2267 0,2400 0,2467 0,2600 0,2667 0,2800 0,3067 0,3200 0,3333

Growth

Dollar

Inflation

Discount

5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5

0,90 0,90 0,90 0,90 0,90 0,90 0,90 0,90 0,90 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,60 1,60 1,60 1,60

3 3 3 4 4 4 5 5 5 3 3 3 4 4 4 5 5 5 3 3 3 4

0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00

OPTION ANALYSIS 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61

4.770 4.004 5.866 5.057 4.247 10.538 8.902 7.265 11.031 9.298 7.566 11.526 9.692 7.858 14.783 13.146 11.510 15.588 13.855 12.122 16.417 14.583 12.749 15.297 13.661 12.025 16.140 14.407 12.675 17.010 15.175 13.341 20.211 17.426 14.642 21.135 18.184 15.233 22.069

0,0267 0,0133 0,0067 0,0133 0,0067 0,0167 0,0333 0,0167 0,0167 0,0333 0,0167 0,0083 0,0167 0,0083 0,0167 0,0333 0,0167 0,0167 0,0333 0,0167 0,0083 0,0167 0,0083 0,0167 0,0333 0,0167 0,0167 0,0333 0,0167 0,0083 0,0167 0,0083 0,0033 0,0067 0,0033 0,0033 0,0067 0,0033 0,0017

97 0,3600 0,3733 0,3800 0,3933 0,4000 0,4167 0,4500 0,4667 0,4833 0,5167 0,5333 0,5417 0,5583 0,5667 0,5833 0,6167 0,6333 0,6500 0,6833 0,7000 0,7083 0,7250 0,7333 0,7500 0,7833 0,8000 0,8167 0,8500 0,8667 0,8750 0,8917 0,9000 0,9033 0,9100 0,9133 0,9167 0,9233 0,9267 0,9283

5 5 5 5 5 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 15 15 15 15 15 15 15

1,60 1,60 1,60 1,60 1,60 0,90 0,90 0,90 0,90 0,90 0,90 0,90 0,90 0,90 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,60 1,60 1,60 1,60 1,60 1,60 1,60 1,60 1,60 0,90 0,90 0,90 0,90 0,90 0,90 0,90

4 4 5 5 5 3 3 3 4 4 4 5 5 5 3 3 3 4 4 4 5 5 5 3 3 3 4 4 4 5 5 5 3 3 3 4 4 4 5

0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00

OPTION ANALYSIS 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81

18.943 15.816 27.327 24.542 21.757 28.782 25.831 22.880 30.285 27.158 24.032 28.189 25.404 22.620 29.709 26.758 23.807 31.280 28.154 25.027

0,0033 0,0017 0,0033 0,0067 0,0033 0,0033 0,0067 0,0033 0,0017 0,0033 0,0017 0,0033 0,0067 0,0033 0,0033 0,0067 0,0033 0,0017 0,0033 0,0017 100%

98 0,9317 0,9333 0,9367 0,9433 0,9467 0,9500 0,9567 0,9600 0,9617 0,9650 0,9667 0,9700 0,9767 0,9800 0,9833 0,9900 0,9933 0,9950 0,9983 1,0000

15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15

0,90 0,90 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,60 1,60 1,60 1,60 1,60 1,60 1,60 1,60 1,60

5 5 3 3 3 4 4 4 5 5 5 3 3 3 4 4 4 5 5 5

0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00

Below you can find the scenarios table with the 3x3x3x3 combinations for the shift work option. Value 1 2 3 4 5 6 7 8 9 10 11 12

4.064 3.340 2.617 4.298 3.532 2.767 4.533 3.724 2.914 5.969 5.246 4.522

Prob 0 0,0133 0,0267 0,0133 0,0133 0,0267 0,0133 0,0067 0,0133 0,0067 0,0133 0,0267 0,0133

Cum Prob 0 0,0133 0,0400 0,0533 0,0667 0,0933 0,1067 0,1133 0,1267 0,1333 0,1467 0,1733 0,1867

Growth

Dollar

Inflation

Discount

5 5 5 5 5 5 5 5 5 5 5 5

0,90 0,90 0,90 0,90 0,90 0,90 0,90 0,90 0,90 1,48 1,48 1,48

3 3 3 4 4 4 5 5 5 3 3 3

0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00

OPTION ANALYSIS 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

6.341 5.575 4.810 6.724 5.914 5.105 6.200 5.477 4.753 6.588 5.823 5.058 6.989 6.180 5.370 7.742 6.689 5.636 8.061 6.952 5.844 8.380 7.214 6.047 10.744 9.691 8.638 11.263 10.155 9.046 11.795 10.629 9.462 11.108 10.055 9.001 11.651 10.543 9.434

0,0133 0,0267 0,0133 0,0067 0,0133 0,0067 0,0133 0,0267 0,0133 0,0133 0,0267 0,0133 0,0067 0,0133 0,0067 0,0167 0,0333 0,0167 0,0167 0,0333 0,0167 0,0083 0,0167 0,0083 0,0167 0,0333 0,0167 0,0167 0,0333 0,0167 0,0083 0,0167 0,0083 0,0167 0,0333 0,0167 0,0167 0,0333 0,0167

99 0,2000 0,2267 0,2400 0,2467 0,2600 0,2667 0,2800 0,3067 0,3200 0,3333 0,3600 0,3733 0,3800 0,3933 0,4000 0,4167 0,4500 0,4667 0,4833 0,5167 0,5333 0,5417 0,5583 0,5667 0,5833 0,6167 0,6333 0,6500 0,6833 0,7000 0,7083 0,7250 0,7333 0,7500 0,7833 0,8000 0,8167 0,8500 0,8667

5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10

1,48 1,48 1,48 1,48 1,48 1,48 1,60 1,60 1,60 1,60 1,60 1,60 1,60 1,60 1,60 0,90 0,90 0,90 0,90 0,90 0,90 0,90 0,90 0,90 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,60 1,60 1,60 1,60 1,60 1,60

4 4 4 5 5 5 3 3 3 4 4 4 5 5 5 3 3 3 4 4 4 5 5 5 3 3 3 4 4 4 5 5 5 3 3 3 4 4 4

0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00

OPTION ANALYSIS 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81

12.209 11.043 9.876 11.108 9.836 8.564 11.492 10.156 8.820 11.878 10.475 9.071 14.871 13.599 12.327 15.497 14.161 12.824 16.139 14.736 13.332 15.327 14.055 12.783 15.982 14.646 13.310 16.656 15.252 13.849

0,0083 0,0167 0,0083 0,0033 0,0067 0,0033 0,0033 0,0067 0,0033 0,0017 0,0033 0,0017 0,0033 0,0067 0,0033 0,0033 0,0067 0,0033 0,0017 0,0033 0,0017 0,0033 0,0067 0,0033 0,0033 0,0067 0,0033 0,0017 0,0033 0,0017 100,00%

100 0,8750 0,8917 0,9000 0,9033 0,9100 0,9133 0,9167 0,9233 0,9267 0,9283 0,9317 0,9333 0,9367 0,9433 0,9467 0,9500 0,9567 0,9600 0,9617 0,9650 0,9667 0,9700 0,9767 0,9800 0,9833 0,9900 0,9933 0,9950 0,9983 1,0000

10 10 10 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15 15

1,60 1,60 1,60 0,90 0,90 0,90 0,90 0,90 0,90 0,90 0,90 0,90 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,48 1,60 1,60 1,60 1,60 1,60 1,60 1,60 1,60 1,60

5 5 5 3 3 3 4 4 4 5 5 5 3 3 3 4 4 4 5 5 5 3 3 3 4 4 4 5 5 5

0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00 0,00 0,50 1,00

OPTION ANALYSIS

101

Table C.1: Cash flows for investment option in x e 1000

2010

2011

2012

2013

2014

2015

2016

2017

2018

1

2

3

4

5

6

7

8

9

Capital Investment

1.000

96

22

27

627

27

43

48

48

Total Goods Total Cost of Sales Total Operating Expenses Total Other Expenses

0 0 0

784 460 375

1.713 701 796

2.808 1.058 1.268

4.094 1.406 1.796

5.602 1.840 2.383

7.362 2.330 3.038

9.415 2.987 3.765

11.803 3.687 4.571

0

130

281

458

663

900

1.175

1.491

1.856

Total Cash out

0

1.749

3.491

5.592

7.958

10.726

13.905

17.658

21.918

Total Net Sales

0

2.730

5.921

9.639

13.956

18.958

24.739

31.409

39.090

Depreciation Tax Reduction

-14

-17

-17

-18

-27

-28

-29

-30

-32

-986

627

1.746

2.905

3.719

5.929

7.787

9.883

12.348

450

1.062

1.498

1.626

2.196

2.444

2.629

2.784

Yearly costs

Cash flow after tax Discount rate

18 %

Discounted

-836

NPV cash flows after tax

13.855

Table C.2: Cash flows for shift work option in x e 1000

Yearly costs

2011

2012

2013

2014

2015

2016

2017

2018

2

3

4

5

6

7

8

9

Capital Investment Total Total Total Total

Goods Cost of Sales Operating Exp. Other Expenses

600 784 332 375 130

1.713 591 796 281

2.808 1.002 1.268 458

4.094 1.449 1.796 663

4.258 1.506 1.867 684

4.429 1.565 1.942 707

4.606 1.626 2.020 730

4.790 1.689 2.101 753

Total Cash out

1.621

3.382

5.535

8.002

8.316

8.642

8.981

9.333

Total Net Sales

2.730

5.921

9.639

13.956

14.411

14.881

15.365

15.864

-8

-8

-8

-8

-8

Depr. Tax Reduction Cash flow after tax Discount rate Discounted NPV cash flows after tax

799 18 %

1.829

2.954

3.695

4.397

4.501

4.605

4.711

574

1.113

1.524

1.615

1.629

1.413

1.225

1.062

10.155

OPTION ANALYSIS

102

Table C.3: Sensitivity analysis investment option (NPV values in x e 1000)

Description

Base

Low

High

Difference H-L

Sales growth NPV values

10% 13.855

5% 4.522

15% 25.831

21.309

Dollar value NPV values

1,48 13.855

0,9 9.298

1,6 14.407

5.109

Inflation percentage NPV values

4% 13.855

3% 13.146

5% 14.583

1.436

e 1.000.000 13.855

e 1.000.000 13.855

e 1.500.000 13.386

469

e 600.000 13.855

e 600.000 13.855

e 900.000 13.731

124

Payroll cost increase NPV values

4% 13.855

2% 14.363

4% 13.855

508

Yearly discount increase NPV values

0,50% 13.855

0% 15.588

1% 12.122

3.465

Initial inefficiency decrease NPV values

10% 13.855

5% 13.814

15% 13.959

145

Yearly inefficiency decrease NPV values

1% 13.855

0% 13.814

2% 13.899

85

Price subcontracting hour NPV values

e 40 13.855

e 35 14.108

e 50 13.349

760

Cost production hall NPV values Cost administrative building NPV values

OPTION ANALYSIS

103

Table C.4: Sensitivity analysis shift work option (NPV values in x e 1000) Description

Base

Low

High

Difference H-L

Sales growth NPV values

10% 10.155

5% 5.575

15% 14.161

8.585

Dollar value NPV values

1,48 10.155

0,9 6.952

1,6 10.543

3.590

Inflation percentage NPV values

4% 10.155

3% 9.691

5% 10.629

938

e 600000 10.155

e 600000 10.155

e 900000 10.030

125

Payroll cost increase NPV values

4% 10.155

2% 10.623

4% 10.155

468

Yearly discount increase NPV values

0,50% 10.155

0% 11.263

1% 9.046

2.217

Price subcontracting hour NPV values

e 40 10.155

e 35 10.216

e 50 10.032

184

Cost administrative building NPV values

NEDERLANDSE SAMENVATTING

104

Appendix D Nederlandse samenvatting D.1

Inleiding

Magnetrol International N.V. (MINV) is een dochterbedrijf van het Amerikaanse Magnetrol International Incorporated. Het vervaardigt elektro-mechanische vlotter- en verdringerniveauschakelaars en -meters, capacitieve niveauschakelaars, continue niveaumeetapparatuur met behulp van golfgeleide radar en vrijstralende radar enz. De afzetmarkt situeert zich vooral in industrieën zoals de olie- en gas, petrochemische, energieproducerende en chemische industrieën. Wereldwijd stelt Magnetrol meer dan 600 mensen te werk. De productieomgeving van MINV is geleidelijk aan over de jaren heen ontwikkeld, vanaf zijn oprichting in 1971. 38 jaar later zit men op een punt waar de huidige productievloer niet meer voldoet om antwoord te bieden aan de groei die Magnetrol in de laatste jaren gekend heeft. Deze masterproef heeft als doel de Amerikaanse eigenaar op een kwalitatieve en kwantitatieve manier te overtuigen van de noodzaak tot capaciteitsuitbreiding. Door een profit and loss studie uit te voeren, kan er nagegaan worden wat de invloed van investeringsbeslissingen zijn op de winst na belastingen, alsook wordt er gekeken naar de NPV van de cashflows na belastingen. Uiteraard zal een capaciteitsvergroting alleen niet volstaan om klaar te staan voor de toekomst. Er is ook een flow en layout studie nodig om de efficiëntie van het systeem te bewaken. Een aantal problemen vallen onmiddellijk op wanneer we de productieomgeving bekijken. Hierna ziet u enkele indicatoren voor de nood aan extra capaciteit en ruimte: Vergrotende backlog: De geproduceerde waarde van vorig jaar bedroeg e 23,51 miljoen. MINV heeft in die periode voor e 26,1 miljoen verkocht. Het verschil

D.1 Inleiding

105

tussen beide bedragen kon dus niet geproduceerd worden, nl. e 2,59 miljoen. De vraag van de markt is groter dan hetgeen geproduceerd kan worden, waardoor de backlog aangroeit. De backlog is de waarde van de goederen die in behandeling zijn door het systeem, zowel door administratie als productie. Zie het als een wachtlijn waarbij de aankomstrate groter is dan de verwerkingsrate. Het effect hiervan is nu al zichtbaar aan de langer wordende lead times, wat dan weer negatief is vanuit een competitief standpunt. Meer uitbestedingsuren: Door de overbelasting in productie wordt er ook steeds meer uitbesteed wat kan leiden tot kleinere winstmarges. Indicatoren op de productievloer : Het gebrek aan ruimte op de productievloer brengt enerzijds extra material handling met zich mee en anderzijds is er ook een verhoogd veiligheidsrisico. Zoals op foto’s 1.2 tot en met 1.6 te zien is, belemmert WIP de transportgangen op de vloer. Door de trend van steeds langer wordende probes is er plaatsgebrek aan de assembly tafels. Lange probes reiken bij hun configuratie op de assembly tafels tot in de laskabines of spuitkabine. Los van het capaciteitsprobleem kan de herschikking van de tafels hier al een oplossing bieden. Kwaliteit van productie: In de huidige situatie zitten de ’vuile’ en ’propere’ operaties in dezelfde ruimte waar zij in feite gesplitst zouden moeten zijn. De reden hiervoor is dat de PCB’s vervuild kunnen worden door staaldeeltjes vanuit machining. Anderzijds moet koolstofstaalproductie gescheiden zijn van roestvrij staalproductie. Momenteel is er geen ruimte om deze splitsingen te maken.

De werkwijze van de thesis zit als volgt in elkaar: Gegevensanalyse van databank informatie: gegevens downloaden in een spreadsheet en gespecialiseerde filters gebruiken zoals in figuur 2.2 om de informatie overzichtelijk en werkbaar te maken. Op basis van deze gegevens de gemiddelde bewerkingstijden per product familie en per departement berekenen, vooraf gegaan door het controleren van de gegevens uit het eerste puntje. Een voorspelling maken van de toekomstige verkoop in eenheden van MINV en deze gebruiken om de toekomstige product mix in te schatten. Deze mix wordt dan geprojecteerd op een vooropgestelde groei van 5%, 10% en 15%.

D.2 Gegevensanalyse

106

Deze voorspelling in combinatie met de directe productie uren per product familie en per departement geeft een schatting van de benodigde productie uren over een periode van 10 jaar in elk van de departementen. Vertaling van het aantal benodigde uren in VTE’s (voltijdse equivalenten) - rekening houdend met inefficiënties - en investeringen. Monte Carlo simulatie maken om de twee investeringen met elkaar te vergelijken:

– investering maken in nieuwe productiehal met bijhorend nieuw productiemiddelen – een 2-shiftensysteem invoeren – niet uitbreiden wordt als referentie gebruikt om de cashflows te berekenen die resulteren uit de investeringen

D.2

Gegevensanalyse

Zoals in de inleiding reeds vermeld werd, gaan we vooreerst de benodigde directe productie uren per productfamilie per departement bepalen omdat ze niet rechtstreeks beschikbaar zijn binnen de organisatie. In productie was men ter voorbereiding van de implementatie van een nieuw ERP-systeem begonnen met het opmeten van productietijden van een suborder in elk departement. Een suborder is een deel van een bestelling bestaande uit een aantal identieke toestellen. Deze suborderboxen worden gescand bij het starten en stoppen van de bewerking in een departement. Per dergelijke starten stoptijd wordt er een lijn in een data lijst bijgehouden. Eén suborder kan binnen één departement over meerdere lijnen beschikken. Dat gebeurt als er verschillende bewerkingen moeten uitgevoerd worden of als de bewerking om een andere reden gestopt dient te worden. Door gebruik te maken van pivot tables in Excel is het mogelijk om deze ruwe informatie op een gestructureerde manier per producttype weer te geven. Vervolgens worden de resultaten gegroepeerd in 10 product families en worden de gemiddelde productietijden per productfamilie bepaald. De koppeling tussen de productcodes en de productfamilies gebeurt door gebruik te maken van de zogenaamde literature cross reference list MINV (2009).

D.2 Gegevensanalyse

107 Table D.1: 10 Magnetrol productfamilies

Brand name Mechanicals Modulevel Eclipse Pulsar Gap Sensors Thermatel Kotron Air Sonar Jupiter Solitel

Alternative name Mechanical products Displacer transmitters Guided wave radar Radar Ultrasonic Contact Thermal dispersion RF controls Ultrasonic non-contact Magnetostrictive Vibrating rod

Vooraleer we de bekomen data gebruiken, moet ze gecontroleerd worden. Hiertoe werd er een meeting gepland met de productie manager van MINV om de data te corrigeren en te fine tunen waar nodig. Hij maakte een voorstel van bepaalde afkappingsregels om niet-correcte productie uren uit de analyse te filteren. Er zaten namelijk onrealistische cijfers in de data lijst door bijvoorbeeld het vergeten uitscannen van suborders. Door realistische afkappingsregels op te stellen werden foutieve data uit de gegevenslijst verwijderd. Het resultaat van deze analyse wordt: Table D.2: Directe productie uren per product familie per departement

Product familie Machine afdeling Mechanical products 1,07 Displacer transmitters 1,67 Guided wave radar 1,73 Radar 0 Ultrasonic Contact 0,80 Thermal dispersion 1,42 RF controls 0 Ultrasonic non-contact 0 Magnetostrictive 0 Vibrating rod 0,08

Lasafdeling 1,39 2,43 1,67 0,31 0,37 0,33 0,48 0,12 0 0,02

Assemblage 1,61 2,19 1,49 0,96 0,61 1,19 2,10 0,28 0,47 0,29

Als een product in een bepaald departement weinig uren vergt, komt dit doordat deze operatie hoofdzakelijk in de productie afdeling in Downers Grove (USA) gebeurt met

D.2 Gegevensanalyse

108

enkele kleine aanpassingen in België. Alvorens over te gaan naar de schatting van de toekomstige noden wordt er een korte analyse gemaakt van de huidige productie situatie met betrekking tot eigen productie en subcontracting. Er wordt bijvoorbeeld opgemerkt dat er een stijging is in de inefficiëntie vanaf 2007 omdat er vanaf dat jaar veiligheidsvergaderingen moesten ingepland worden, alsook door de overbelasting in productie met veel extra material handling als gevolg. De verkoopsvoorspellingen van de producten is het belangrijkste gedeelte van de gegevensanalyse. De basis voor deze analyse is een lijst met historische verkoopscijfers van MINV op jaarbasis gegeven. Eclipse is een product dat vanaf 1998 in productie werd genomen, waardoor er maar 11 elementen in de data serie zitten. Deze beperkte gegevensbeschikbaarheid zal een grotere onzekerheid met zich meebrengen omtrent de voorspellingen, zeker omdat er 10 jaar in de toekomst geschat dient te worden. Hoe verder men in de toekomst probeert te voorspellen, hoe groter de onzekerheid. Daarom ook werd de kwantitatieve forecast gestaafd met een kwalitatief marktonderzoek bij de verkopers. De resultaten van de sales survey zullen ons een goed idee geven van de toekomstige product mix van MINV. Het verkoopskanaal is immers een rijke bron van informatie omdat zij dag in dag uit in contact komen met klanten en ze zijn het dichtste contact dat een bedrijf met zijn klanten heeft. De resultaten van het marktonderzoek zijn te zien in tabel 2.4. De cijfers 1 t.e.m. 5 stellen respectievelijk sterk dalende en sterk stijgende verkoop voor. De kwantitatieve voorspelling wordt uitgevoerd met behulp van de ForecastX software, geleverd bij het boek Wilson & Keating (2002). Voor elk product wordt de tijdserie ingegeven en wordt er gekozen op basis van welke foutmetingen het voorspellingsmodel geselecteerd moet worden. Zo kan men kiezen voor bijvoorbeeld MSE, SSE en RMSE. Afhankelijk van de gekozen meting zal het programma het model kiezen waarvoor de meting minimaal is. De gekozen modellen vindt u in tabel 2.6. Door de jonge leeftijd van sommige product families waren er weinig historische gegevens om handen, wat de voorspelling dan enigszins bemoeilijkte. De resultaten van de verkoopsvoorspelling worden in alle geval gecontroleerd aan de hand van de resultaten van een marktonderzoek bij de verkoopsmensen van MINV. Vooral bij gebrek aan historische gegevens van jonge producten hebben we ons hierop moeten baseren. De resultaten van de verkoopsvoorspelling worden gebruikt om voor de komende jaren een geschatte product mix op te stellen. De product mix van 2009 en 2018 ziet u respectievelijk in figuur 2.19 en figuur 2.20 in sectie 2.4.4. De getallen achter de productnamen zijn in % weergegeven, om op te tellen tot 100%. Deze product mix wordt

D.2 Gegevensanalyse

109

geprojecteerd op de voorafbepaalde jaarlijkse groei van 10%, met als worst case scenario een groei van 5% en 15% groei als best case scenario. Merk op dat Eclipse de sterkste groeier is en 41% van de geproduceerde eenheden in 2018 zou uitmaken, wat eventueel justifieert om een aparte Eclipse area te voorzien, een product layout als u wil, waar alle processen die benodigd zijn om het product te maken in lijn zijn opgesteld. Nu we de benodigde directe productie uren per product familie en per departement hebben, gecombineerd met een voorspelling van de toekomstige verkoop, kunnen we overgaan tot de berekening van de toekomstige nood aan productie capaciteit, uitgedrukt in VTE’s. Om te beginnen projecteren we de product mix op verschillende groeipercentages zoals eerder gezegd. In verdere tekst wordt er steeds gewerkt met een groei van 10%, zijnde de jaarlijkse groeitarget van Magnetrol. De twee overige scenario’s worden verwerkt in de Excel assessment tool die wordt bijgevoegd bij deze masterproef. De scenario’s worden ook verwerkt in de investeringsanalyse van sectie 4. De projectie levert de toekomstig benodigde aantallen en, in combinatie met de benodigde productieuren, de nodige directe uren per product voor de komende jaren op. Bij de berekening van de VTE’s, en dus de incorporatie van indirecte uren, werd er rekening gehouden met eventueel een verbeterde efficiëntie. Er kan met een extra vloeroppervlakte immers gewerkt worden aan de reductie van indirecte uren door overbodig transport of blokkeren van transport te vermijden. Ook zal de material handling binnen assemblage vlotter kunnen verlopen. Om het voordeel van verbeterde efficiëntie aan te tonen wordt er berekend hoeveel manjaren uitgespaard kunnen worden en welk bedrag hiermee overeenstemt. De resultaten van deze analyse worden samengevat in figuren 2.28 tot en met 2.30. Bij een stijgende verkoop heeft Magnetrol voorzien om de ondersteunende manpower met volgende percentages te verhogen: Als de verkoop jaarlijks met 5% stijgt, dan mag de headcount van de ondersteunende functies jaarlijks stijgen met 2,66%. Als de verkoop jaarlijks met 10% stijgt, dan mag de headcount van de ondersteunende functies jaarlijks stijgen met 4,14%. Als de verkoop jaarlijks met 15% stijgt, dan mag de headcount van de ondersteunende functies jaarlijks stijgen met 5,45%.

Een andere opmerking in verband met schaalbaarheid is dat wanneer de benodigde directe uren verdubbelen (verkoop verdubbelt), dan zullen in het geconstrueerde model de totale indirecte uren met dezelfde factor stijgen. Dat wil in feite zeggen dat ook de

D.3 Financiële evaluatie

110

supervisie met die factor vermenigvuldigd wordt, wat in praktijk natuurlijk niet persé opgaat. Het kan zijn dat Magnetrol bijvoorbeeld de helft meer laskabines kan zetten en lassers kan tewerkstellen zonder dat het daarvoor de supervisie door een leadman moet verhogen.

D.3 D.3.1

Financi¨ ele evaluatie Inleiding

Na de berekening van de benodigde VTE’s voor de komende jaren kan er overgegaan worden tot de financiële evaluatie. We maken gebruik van de profit & loss statement van Magnetrol International N.V. om drie gevallen te bekijken: Er wordt beslist om niet verder te investeren in de Belgische productiefaciliteiten van Magnetrol International. Een investering wordt gemaakt in:

– Het verdubbelen van de bestaande productievloer met herschikking van het bestaande productie apparaat en het installeren van extra productieuitrusting. – Uiteraard moet verdere verkoops- en productiegroei ondersteund worden door het uitbreiden van de dienstenactiviteiten. Het bestaande administratieve gebouw heeft echter weinig ruimte om extra personeel te plaatsen, zodus moet er overwogen worden om het bestaande gebouw uit te breiden. Deze uitbreiding zou pas in gebruik genomen worden vanaf 2014. Vanuit het moederbedrijf MII wordt er gevraagd om ook een 2-shiftensysteem te overwegen.

Alvorens de berekeningen aan te vatten wordt er een analyse gemaakt van de P&Lstructuur van MINV. Alle rubrieken worden bekeken en er wordt een onderscheid gemaakt tussen deterministische en stochastische rubrieken. Stochastische rubrieken zijn rubrieken waarvan de waarde afhangt van input variabelen die zich moeilijk laten voorspellen en waarvan de waarde dus op voorhand niet geweten is. Deterministische rubrieken worden vaak berekend als som van andere, dikwijls stochastische rubrieken. We gaan na welke rubrieken be¨ınvloed worden door beslissingen als extra blue collars, white collars, productie-uitrusting, nieuwe productiehal en een nieuw bureaugebouw. Zoals eerder vermeld werd, zullen de toekomstmogelijkheden in drie gevallen onderverdeeld worden.


111

Op het einde van de ’investerings-’ en ’shift werk’ case wordt er een cumulatieve distributie opgesteld. Hierbij worden er voor elk van de meest onzekere beslissingen van een bepaalde case enkele waarden meegegeven en worden de verschillende mogelijke winsten berekend en uitgezet in een grafiek. Een dergelijke grafiek laat zich bijvoorbeeld lezen als volgt: er is 85% kans dat onze winst meer dan e 5 miljoen zal bedragen. De P&L analyse wordt voorafgegaan door een keuze aan parameters die we in de assessment tool zullen incorporeren. Zo zijn er: economische parameters als de aangroei van lijstprijzen, de dollar/euro koers, het duurder worden van materialen en de sales target van MINV; P&L relaties die de verhouding uitdrukken van een bepaalde P&L rubriek op een grote. Deze relaties worden gebruikt omdat bepaalde rubrieken zich heel moeilijk vooraf laten bepalen en het aanvaardbaar is om deze als percentage van een andere, welgekozen rubriek te nemen. Zo zal de verhouding genomen worden van Other manufacturing material op Total Other Sales; parameters met betrekking tot arbeid zoals de toename van dienstenheadcount (afhankelijk van de groei in sales), de aangroei van de lonen (in feite ook economische parameter, bevat loonsopslag alsook indexering), een toeslag voor shift werk, gemiddelde loonskost in productie ...; uitbestedingsparameters zoals de uitbestedingskost per machine-uur en prijsstijging van uitbestedingen; investeringsparameters zoals de duur van afschrijvingsperioden, aantal laskabines, kost van een laskabine en afzuiginstallatie, kost van een nieuwe productiehal...

Na de individuele analyses komt er een vergelijk tussen de verschillende cases alsook een analyse van de invloed van de dollar/euro koers op de winst van MINV. Deze topic vormt al jaren een aandachtspunt en er werd verwacht deze invloed te kwantificeren aan de hand van de assessment tool.

D.3.2

Resultaten

Huidige productie situatie behouden Als er niet wordt ge¨ınvesteerd in nieuwe productiecapaciteit en we weten dat de huidige situatie verzadigd is, zou er enkel meer geproduceerd kunnen worden mits uit te besteden. Hier zitten we echter met de beperking van de capaciteit van de assemblage die


112

sowieso in house gebeurt. Assemblage blijft de bottleneck in het productieproces zodat uitbreiding van de verkoop vrijwel onmogelijk is zonder een explosie van de backlog. Dat heeft lange lead times tot gevolg wat de klantenservice sterk vermindert. Deze situatie wordt gedetailleerd uitgewerkt in sectie 3.4.1. De resultaten van de analyse zijn voorgesteld in grafiek D.1.

Figure D.1: Financiële evaluatie in geval van geen bijkomende investeringen

Investeringsoptie De investeringen zouden ingevoerd worden in 2 fasen om de investeringslast minder zwaar te laten doorwegen op de balans. Fase 1

– Bouwen van nieuwe productiehal en in gebruik nemen tegen 2011. – Nieuwe blue collars aannemen volgens de berekende VTE aantallen of volgens de noden. Ook nieuwe white collars om de toegenomen verkoop te ondersteunen, vooral na 2015. – Nieuwe productie uitrusting ter uitbreiding van de bestaande capaciteit volgens het aantal VTE’s (machining wordt verder aanvullend aan huidige capaciteit uitbesteed) met herschikking van de bestaande layout. Fase 2

– Nieuwe kantoren worden in 2015 in gebruik genomen. In dit nieuw kantoorgebouw zal er ook een nieuwe demonstratieruimte en ruimere vergaderzaal voorzien worden.


113

Er wordt ook gewezen op de noodzaak om projecten te organiseren om tussenstocks te verminderen en dit om optimaal van de verkregen ruimte gebruik te kunnen maken. Een verandering dringt zich op in de manier van productie inplannen en de processen moeten op elkaar afgestemd worden in de mate van het mogelijke. De resultaten van deze P&L analyse worden vertaald in grafiek D.2.

Figure D.2: Financiële evaluatie in geval van investeringen

2-shiftensysteem Ook hier wordt dezelfde P&L analyse toegepast met de relevante parameters van hiervoor. Bij het implementeren van een 2-shiftensysteem wordt een surplus van 15% op de loonkost berekend om de kosten i.v.m. shiftwerk te dekken. De resultaten worden gegeven in de grafiek van figuur D.3.


114

Figure D.3: Financiële evaluatie in geval van een 2-shiftensysteem

Dollar analyse De dollar/euro koers kende een grote evolutie sinds het begin van de 21e eeuw. Voor midden 2002 kreeg men minder dan 1 dollar per euro, momenteel bedraagt dat 1,3 dollar per euro. De laatste maanden kende de dollarkoers een verloop van 1,6 naar 1,3 dollar per euro. Gezien de grote hoeveelheid goederen die MINV aankoopt bij het moederbedrijf in Amerika heeft deze koers een aanzienlijke invloed op de balans van MINV, welke niet gecompenseerd wordt door het moederbedrijf. Naarmate MINV meer goederen aankoopt, wat het geval is voor de shiftwerk- en investeringscase, zal deze invloed nog groter zijn. De resultaten van de analyse worden in drie grafieken uitgezet, één voor elke case. Hierbij wordt er gekeken naar 4 koersen $/e 0,9 - 1,28 1,48 - 1,6. De koers van 1,28 $/e is de koers op het ogenblik van schrijven, 1,48 $/e is de gemiddelde gewogen koers voor MINV uit 2008. De resultaten kan de lezer vinden in figuren 3.12 tot en met 3.14. De dollar koers is duidelijk een factor die niet mag genegeerd worden. In elk van de drie gevallen heeft een verschuiving van de lange termijn dollar koers een grote impact op de winst van MINV. Als de lezer even een kijkje neemt in de P&L tabellen uit de assessment tool is het relatieve belang van de goederen uit de USA, Goods MII, al snel duidelijk.

D.4 Vergelijking van de cases

D.4 D.4.1

115

Vergelijking van de cases Monte Carlo simulatie

De vergelijking tussen de resultaten van de 3 cases worden voor u samengevat in figuur D.4. In onderstaande figuur worden alle input variabelen op hun basisch dwaarde ingesteld en wordt er gewerkt met de PAT uit de P&L tabellen (geen NPV).

Figure D.4: PAT voor een periode van 10 jaar en voor de 3 cases

Het bovenstaande volstaat echter niet om een volwaardige conclusie te trekken van welke case beter is, 2-shift systeem of investeringscase. Er wordt namelijk geen rekening gehouden met de tijdswaarde van geld, noch met de absolute investeringsbedragen. Met dat laatste wordt bedoeld dat in de bovenstaande P&L analyse enkel gekeken wordt naar de afschrijvingen die resulteren uit de investeringen, terwijl de reële invloed in feite het investeringsbedrag op een zeker tijdstip is. De tijdswaarde van e 1.000.000 ge¨ınvesteerd in 2010 is groter dan de som van gelijke afschrijvingen over 20 jaar van de investering. Langs de andere kant wordt er geen rekening gehouden met de variantie die kan zitten op de input variabelen. We gaan in plaats hiervan gebruik maken van een Monte Carlo simulatie waarbij we de NPV van de cashflows na belastingen gaan gebruiken als beslissingscriterium. De input variabelen krijgen hier in plaats van enkel een gemiddelde waarde ook nog twee uiterste waarden mee om het effect van variantie in rekening te brengen.


116

De werkwijze voor dergelijke analyse zit als volgt in elkaar: De variabelen die het grootste invloed hebben op de NPV van de cashflows na belasting selecteren via een gevoeligheidsanalyse (zie tabel C.3 and C.4). Gebruik de vier geselecteerde variabelen met hun 3 waarden elk. Stel een tabel op met alle mogelijke combinaties om vervolgens de probabiliteit van elk scenario te berekenen. De scenario-probabiliteiten worden berekend door de deelprobabiliteiten uit tabel 4.3.2 van elke variabele met elkaar vermenigvuldigd. Neem nu bijvoorbeeld het scenario dat er 5% verkoopsaangroei is, dat 1 euro 1,48 dollar waard is, er 4% inflatie is en dat de kortingen jaarlijks met 1% verhoogd worden. Dit scenario heeft dan een probabiliteit om op te treden van 0, 45.0, 33.0, 4.0, 25 = 0, 0150 of 1,5 % kans. Naast een kolom met individuele probabiliteiten van de scenario’s wordt er ook een kolom met de cumulatieve probabiliteit voorzien. Vervolgens worden er door Excel 10.000 random getallen (tussen 0 en 1) gegenereerd voor de twee opties. Elk van die random getallen R wordt gebruikt om een scenario te selecteren. Dat gebeurt door in Excel een zoekopdracht te vervullen waarbij er in de kolom met cumulatieve probabiliteiten gezocht wordt naar R. Gezien Excel steeds de ondergrens selecteert van het interval waar R inligt, laten we als output de NPV van de volgende rij weergeven. Scenario’s met een grotere probabiliteit hebben zo meer kans om voor te komen in de random reeks. Vervolgens wordt er met de 10.000 gegenereerde NPV’s een cumulatieve distributie opgesteld zowel voor het 2-shiftensysteem als voor de investeringscase. De verkregen grafiek D.4.2 laat toe een beter vergelijk te maken tussen beide opties.

D.4.2

Resultaten

De resultaten van de simulatie vindt u in onderstaande grafiek.


117

Figure D.5: Cumulatieve probabiliteiten uit Monte Carlo simulatie

Rekening houdend met de cumulatieve distributies kan er besloten worden dat in 40% van de gevallen het shiftensysteem beter is dan de investeringsoptie (de rode lijn ligt rechts van de blauwe lijn). In 60% van de gevallen is de investeringsoptie beter. De horizontale afstand tussen de grafieken geeft het verschil in NPV weer. Natuurlijk kan deze analyse niet voorstellen wat er in werkelijkheid zal gebeuren, maar het geeft een goed idee van het verschil tussen het shiftensysteem en de investeringsoptie. In 30% van de gevallen is de NPV van de cashflows afkomstig uit de investeringsoptie beneden e 5.000.000 en in minder dan 20 % van de gevallen is de NPV van de cashflows uit het shiftensysteem beneden e 5.000.000. In 10 % van de gevallen is de NPV van de shiftwerkoptie boven e 11.000.000, terwijl dat bij de investeringsoptie tot 45 % gaat. We laten de simulatie van 10000 random gevallen 200 keer uitvoeren en van elk van de simulaties wordt het gemiddelde bijgehouden. Het gemiddelde van die 200 gemiddeldes berekenen geeft dan e 10.093.171 voor de investeringsoptie en e 7.824.048 voor de shiftoptie, in het voordeel van de investeringsoptie. Als we ons baseren zowel op de cumulatieve distributie als op de gemiddeldes, blijkt de investeringsoptie in beide gevallen de betere oplossing. Voor de lage waarden van NPV in de linkerstaart van de distributie blijkt de shiftoptie beter te zijn. Zoals verwacht, gebeurt dat in geval van een lage verkoopsaangroei (5 %). Een verklaring hiervoor is te vinden in het feit dat bij lage verkoopsaangroei de productie in het shiftensysteem even lang kan blijven aangroeien als in de investeringsoptie zonder gesatureerd te worden (in


118

onze studieperiode van 10 jaar). In de investeringscase zijn er echter grote investeringen gemaakt om de verkoopsgroei bij te benen, investeringen die niet gemaakt werden in de shiftcase en zwaar doorwegen op de NPV van de investeringsoptie. Naast de financiële vergelijking moet er ook gekeken worden naar de sociale impact van shiftwerk. Eerst en vooral moet het management de huidige werkkrachten laten instemmen om in shiften te werken en daarnaast moet het nog extra werkkrachten vinden die bereid zijn om in shift te werken. In het industriepark van Zele zijn er slechts weinige bedrijven die in shiften werken wat de zoektocht in deze area nog bemoeilijkt.

LIST OF FIGURES

119

List of Figures 1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 1.9

US Dollars per barrel, OK WTI spot price FOB Shop floor . . . . . . . . . . . . . . . . . . . . . Shop floor . . . . . . . . . . . . . . . . . . . . . Shop floor . . . . . . . . . . . . . . . . . . . . . Shop floor . . . . . . . . . . . . . . . . . . . . . Shop floor . . . . . . . . . . . . . . . . . . . . . Manoeuvring long probe . . . . . . . . . . . . . Probe blocking safe passage . . . . . . . . . . . Inefficiency percentages in production . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

3 5 6 6 7 7 9 9 13

2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 2.10 2.11 2.12 2.13 2.14 2.15 2.16 2.17 2.18 2.19 2.20

Raw data list . . . . . . . . . . . . . . . . . . . . . . Pivot table . . . . . . . . . . . . . . . . . . . . . . . . Total production hours per department . . . . . . . . Direct production hours per department . . . . . . . Direct production hours per department . . . . . . . Forecast illustration dr. ir. R. Van Landeghem (2008) Forecast Eclipse Graph . . . . . . . . . . . . . . . . . Forecast sales units Air Sonar . . . . . . . . . . . . . Forecast sales units Eclipse . . . . . . . . . . . . . . . Forecast sales units Gap Sensors . . . . . . . . . . . . Forecast sales units Jupiter . . . . . . . . . . . . . . . Forecast sales units Kotron . . . . . . . . . . . . . . . Forecast sales units Mechanicals . . . . . . . . . . . . Forecast sales units Modulevel . . . . . . . . . . . . . Forecast sales units Pulsar . . . . . . . . . . . . . . . Forecast sales units Solitel . . . . . . . . . . . . . . . Forecast sales units Thermatel Switch . . . . . . . . . Forecast sales units Thermatel Transmitter . . . . . . Pie chart of the forecasted product mix for 2009 . . . Pie chart of the forecasted product mix for 2018 . . .

. . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . .

17 17 19 20 20 21 22 29 30 30 31 31 32 32 33 34 34 35 36 36

LIST OF FIGURES 2.21 2.22 2.23 2.24 2.25 2.26 2.27 2.28 2.29 2.30 2.31 2.32 2.33 2.34 2.35 2.36

Forecast sales of largest product families . . . . . . . . . . . Total direct hours in the machining department . . . . . . . Total direct hours in the welding department . . . . . . . . . Total direct hours in the asembly department . . . . . . . . Inefficiency reduction . . . . . . . . . . . . . . . . . . . . . . Influence of inefficiency reduction on FTEs . . . . . . . . . . Difference between 1% and 5% annual inefficiency reduction Full time equivalents . . . . . . . . . . . . . . . . . . . . . . Full time equivalents with better efficiency . . . . . . . . . . Cumulative reduction in mean years . . . . . . . . . . . . . . Full time equivalents . . . . . . . . . . . . . . . . . . . . . . Full time equivalents with better efficiency . . . . . . . . . . Cumulative reduction in mean years . . . . . . . . . . . . . . Full time equivalents . . . . . . . . . . . . . . . . . . . . . . Full time equivalents with better efficiency . . . . . . . . . . Cumulative reduction in mean years . . . . . . . . . . . . . .

120 . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

3.1 3.2 3.3 3.4 3.5 3.6 3.7 3.8 3.9 3.10 3.11 3.12

40 40 41 41 43 43 44 45 45 46 46 47 47 48 48 49

The influence of discount on list prices of Magnetrol . . . . . . . . . . . Total other sales divided by total invoiced . . . . . . . . . . . . . . . . Financial evaluation of ’no investment’ case . . . . . . . . . . . . . . . Labour costs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Financial evaluation of ’investment’ case if 10% growth occurs . . . . . Headcount in the production departments . . . . . . . . . . . . . . . . Corrected headcount in the production departments . . . . . . . . . . . Operating expenses - payroll cost/total cost . . . . . . . . . . . . . . . . Cost evolution of service departments . . . . . . . . . . . . . . . . . . . Financial evaluation of the ’shift work’ case . . . . . . . . . . . . . . . Dollar/Euro exchange rate from 2002 to 2009 BC investments (2009) . Influence of dollar/euro exchange rate on the profit in the ’no investment’ case . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3.13 Influence of dollar/euro exchange rate on the profit in the ’investment’ case . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3.14 Influence of dollar/euro exchange rate on the profit in the ’shift work’ case

59 60 64 70 74 75 76 77 78 79 80

4.1 4.2 4.3

84 88 89

PAT over a period of 10 years for the 3 cases . . . . . . . . . . . . . . . Darts game illustration of Monte Carlo simulation . . . . . . . . . . . . Cumulative probabilities as perceived in Monte Carlo simulation . . . .

81 81 82

D.1 Financiële evaluatie in geval van geen bijkomende investeringen . . . . 112 D.2 Financiële evaluatie in geval van investeringen . . . . . . . . . . . . . . 113

LIST OF FIGURES

121

D.3 Financiële evaluatie in geval van een 2-shiftensysteem . . . . . . . . . . 114 D.4 PAT voor een periode van 10 jaar en voor de 3 cases . . . . . . . . . . 115 D.5 Cumulatieve probabiliteiten uit Monte Carlo simulatie . . . . . . . . . 117

LIST OF TABLES

122

List of Tables 2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 2.10

10 Magnetrol product families . . . . . . . . . . . . . . . . . . . Direct production hours per product family and per department Sales units forecast Eclipse . . . . . . . . . . . . . . . . . . . . . Sales Survey: scores as defined in text . . . . . . . . . . . . . . . Summary Sales Survey . . . . . . . . . . . . . . . . . . . . . . . Selected forecasting models . . . . . . . . . . . . . . . . . . . . . Forecasted quantities 2009-2013 . . . . . . . . . . . . . . . . . . Forecasted quantities 2014-2018 . . . . . . . . . . . . . . . . . . Forecast Projection on a 10% target growth 2009-2013 . . . . . Forecast Projection on a 10% target growth 2014-2018 . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

15 18 23 24 25 28 37 38 39 39

3.1 3.2 3.3 3.4 3.5 3.6 3.7

Economic parameters . . . Profit & Loss relations . . Labour parameters . . . . Subcontracting parameters Investment parameters . . Costs welding station . . . Facility costs . . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

51 52 53 54 55 68 70

4.1 4.2

Chosen variables for a sensitivity analysis . . . . . . . . . . . . . . . . . Probabilities of variables’ values . . . . . . . . . . . . . . . . . . . . . .

86 87

C.1 C.2 C.3 C.4

Cash flows for investment option in x e 1000 . . . . . . . . . . . Cash flows for shift work option in x e 1000 . . . . . . . . . . . Sensitivity analysis investment option (NPV values in x e 1000) Sensitivity analysis shift work option (NPV values in x e 1000)

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . .

. . . .

. . . .

. . . .

101 101 102 103

D.1 10 Magnetrol productfamilies . . . . . . . . . . . . . . . . . . . . . . . 107 D.2 Directe productie uren per product familie per departement . . . . . . 107

Increasing manufacturing capabilities in a valve plant

Recommend Documents