Pricing tool for transportation at Bpost International …lib.ugent.be/fulltxt/RUG01/001/887/294/RUG01-001887294...Pricing tool for transportation at Bpost International Yue Wan Promotor:

Pricing tool for transportation at Bpost International

Yue Wan Promotor: Prof. dr. ir. Pieter Vansteenwegen

Begeleiders: Ir. Luiza De Lima Gabriel Zeltzer, ir. Ihsan Arkan

Masterproef ingediend tot het behalen van de academische graad van

Inkomende Gast- en Exchangestudenten

Vakgroep Technische Bedrijfsvoering

Voorzitter: Prof. dr. ir. El-Houssaine Aghezzaf

Faculteit Ingenieurswetenschappen en Architectuur

Academiejaar 2011-2012

Pricing tool for transportation at Bpost International

Yue Wan Promotor: Prof. dr. ir. Pieter Vansteenwegen

Begeleiders: Ir. Luiza De Lima Gabriel Zeltzer, ir. Ihsan Arkan

Masterproef ingediend tot het behalen van de academische graad van

Inkomende Gast- en Exchangestudenten

Vakgroep Technische Bedrijfsvoering

Voorzitter: Prof. dr. ir. El-Houssaine Aghezzaf

Faculteit Ingenieurswetenschappen en Architectuur

Academiejaar 2011-2012

II

Preface – Word of Gratitude

This master thesis is part of the final project of the program of Industrial Engineering I am

following at the Polytechnic University of Madrid, and I have had the great opportunity to

perform it in the frame of the Erasmus Exchange program in the department of industrial

management in the faculty of Engineering of Ghent University which has been a very enriching

experience for me.

The thesis has been supervised by Prof. Dr. Pieter Vansteenwegen, Ihsan Arkan and Luiza Zeltzer.

I would like to thank them for the assistance and the help provided at any time during the work

on the thesis. I also would like to thank my promoter at my home university Álvaro García for his

enthusiasm, motivation and for having taught me the basics of Operations Research years ago.

I thank Lieselot Ceenaeme, Peter Smet and Bart Kennes from Bpost International for their

guidance and time.

I am grateful to my Belgian friends for sharing and caring and to my Erasmus friends for

discovering Ghent’s nicest corners with me. I also have to thank my friends at home and all over

the world, especially to Pablo Lammers, Katharina Viet, Natalia Pozhilova, Geraldine Lagonell,

Clara Casas and Ming Qi for going with me through good times and bad times.

Most of all I would like to thank my parents for their unconditional support and trust throughout

my whole academic career.

III

Permission to Loan

The author gives permission to make this master dissertation available for consultation and to

copy parts of this master dissertation for personal use.

In the case of any other use, the limitations of the copyright have to be respected, in particular

with regard to the obligation to state expressly the source when quoting results from this

master dissertation.

6th of June, 2012 Yue Wan

De auteur geeft de toelating deze masterproef voor consultatie beschikbaar te stellen en delen

van de masterproef te kopieren 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.

6 juni 2012 Yue Wan

IV

Pricing tool for transportation at

Bpost International

Yue WAN

Master thesis in the frame of Erasmus Exchange

Universidad Politécnica de Madrid – Ghent University

Academic year 2011 – 2012

Promoter: Prof. Dr. Ir. P. VANSTEENWEGEN

Co-promoters: Ir. I. Arkan, ir. L. Zeltzer

Faculty of Architecture and Engineering

Ghent University

Department of Industrial Management

Chairman: Prof. dr. ir.. E-H. AGHEZZAF

Outline:

Bpost International is one of the largest postal operators for international deliveries and

belongs to BPost, the third biggest civil employer in Belgium. Currently, Bpost International

is in a process of modernization in many fields of action. The object of this paper is to

propose a tool that optimizes the delivery routing and the usage of means to facilitate the

decision making for the company taking into account all relevant characteristics of

international delivery.

Keywords:

Cost optimization, Bpost International, Decision Making, Integer Programming, Simplex Method

V

Pricing tool for transportation at Bpost

International Yue Wan

Supervisors: Prof. Dr. Ir. P. Vansteenwegen, Ir. I. Arkan, Ir. Luiza Zeltzer

Abstract – Bpost International is one of the largest

postal operators for international deliveries and

belongs to BPost, the third biggest civil employer in

Belgium. Currently, Bpost International is in a

process of modernization in many fields of action.

The object of this paper is to propose a tool that

optimizes the delivery routing and the usage of

means to facilitate the decision making for the

company taking into account all relevant

characteristics of international delivery.

Keywords – Cost optimization, Bpost International,

Decision Making, Integer Programming, Simplex

Method

I. INTRODUCTION

The postal service sector has recently

experienced great changes due to the modification

of customer’s behavior and the privatization and

globalization of the sector.

One of the top five international postal operators,

Bpost International requires a dynamic database

application with incorporated cost optimization

function that selects the options that allow an

optimal usage of resources.

II. PROBLEM DESCRIPTION

Bpost International offers tailored solutions to

companies that wish to send daily and

administrative mail, parcels and direct marketing

mail. The European Union is the main market of

the company and the headquarters is located in

Belgium. Thus, about 80% of the postal items are

directed through the European Mail Center in

Zaventem.

However, the company is in a process to expand

to the US and the Asian market. In regard to this

development, it seems obvious that great part of

the handling process must be subcontracted, in line

with the inevitable outsourcing, also alternatives at

European level could be considered in order to

increase efficiency.

The goal of this paper is to demonstrate the

feasibility and efficiency of a pricing tool that is

able to obtain information daily from dynamic

databases and to return the optimal combination of

resources based on operations research techniques.

III. MODELING

A. Assumptions

The main subject of this paper is a sub-set of the

complete problem, which owns all the

characteristics of the logistic process in order to

facilitate a future expansion.

As a result, a relatively small assignation

problem is obtained, with a broad inherent set of

input data.

Two feasible means of transport for oversea

transactions are assumed, the air priority and the

alternative transport option in regard to future

developments of the transport sector.

The outcome of the optimization process at first

instant is the chosen supplier which grants the

optimal use of means. At a second level, the

economic attribute of each service, each route and

even each set of items with the same weight step

can be observed.

B. Input data

The input data of the problem discussed in this

paper are: the estimated cost for a specific service

carried out by a specific supplier for an item in a

certain weight step and format for a determined

route, the estimated time for a supplier to fulfill a

service on a certain route, the volume discount

applied under specific conditions and the average

weight of an item belonging to a determined

weight step and format.

C. Decision variables

The main decision variables are those that decide

if a supplier is chosen for a service on a

determined route.

There is also the decision variable that refers to

the application of volume discounts depending on

the compliance of pre-fixed conditions.

Both types of variables have binary character.

The amount of these variables are respectively

45 and 12 for the here considered problem.

D. Objective function

In the Objective function the input data and

decision variables are brought together resembling

the pricing process. The solver will attempt to

VI

minimize the final value of this function by

keeping the factors of the positive decision

variables as low as possible, opposite to the

behavior towards the volume discount variables.

E. Constraints

There are four types of constraints: the binary

constraints for each variable, the constraints that

grant to obtain one and only one supplier for one

logistic step per route, the constraint for the

volume discount and the time constraints.

The numbers of these constraints are respectively

57, 15, 24 and 45, which gives a total amount of

141 constraints.

IV. IMPLEMENTATION

The model is implemented in an open version of

the solver application of MS Office Excel® using

the Simplex method.

The advantages of the use of Excel are its

relatively intuitive interface, the possibility to

generate the database in Excel worksheets and

interconnect them with each other and to the

solver.

The different set of data distributed among

functional units and the results are displayed

according to the supply chain stage.

The requirements of flexibility and user-friendly

environment are successfully accomplished.

V. TEST RESULTS

During and after the construction of the

application, its effectiveness and efficiency are

extensively tested with various sets of data,

whereby guidelines for quality assurance are

followed.

With the finished application 10 tests with

different sets of input data are run in order to

obtain pertinent economic results.

In a first study, A for further considerations, the

difference between the optimum cost given by the

solver and the maximum cost using the most

expensive suppliers are calculated, mean relative

values and their standard deviations are obtained.

The final results state that the average maximum

savings are around 54,48% in the interval between

29,88% and 79,08% with the security of one

sigma.

Since this test computes only the maximum

savings, it was decided to carry out a second study,

B for further considerations, where the difference

is calculated with respect to the cost generated by

choosing arbitrary suppliers. Also in this case

mean values and their standard deviation are

calculated.

For this study an average saving of around

18,72% (with one sigma security between 6,9%

and 30,54%) is obtained.

An approximate economic study about costs and

benefits based on both A and B test studies is

carried out to prove the utility of a more potent

pricing tool that could facilitate and optimize

20.000 deliveries per day. The test results of A are

modified by dividing by 2 to obtain average costs

and the test results of B are not modified for this

purpose.

The economic study is based on three scenarios:

optimistic, pessimistic and realistic, taking

respectively the one sigma and mean values. For

both the optimistic and realistic scenarios, the

investment and the maintenance cost can be

recovered in the first year of effective use of the

tool. For the pessimistic scenario the recovery of

the investment is possible in the second year of

effective use according to the values of study A or

in the third year of use according to the values of

study B, whereby constant profit can be made from

the third and fourth year on, respectively.

VI. CONCLUSIONS

On the one hand the successful modeling,

implementation and verification of the required

tool have been carried out. On the other hand this

paper gives a solid basis for the further

development of a tool that involves around 100

applicable delivery routes of bpi and includes more

details regarding to the volume discounts.

The economic studies have proven the

profitability of such a tool even in the worst case

scenario.

VII

Table of Contents

Preface – Word of Gratitude ......................................................................................................... II

Permission to Loan ..................................................................................................................... III

Summary ...................................................................................................................................... IV

Extended Abstract ......................................................................................................................... V

Glossary ........................................................................................................................................ XI

List of Figures ............................................................................................................................. XIII

List of Tables .............................................................................................................................. XIV

List of Equations .......................................................................................................................... XV

1. Introduction .......................................................................................................................... 1

1.1 Motivation ....................................................................................................................... 1

1.1.1. Postal Service in the European Union ......................................................................... 1

1.1.2. Bpost ........................................................................................................................... 3

1.1.3. Bpost International ..................................................................................................... 3

1.1.4. Postal Service in the USA ............................................................................................ 5

1.2. Problem Description ....................................................................................................... 6

2. Goal ....................................................................................................................................... 8

2.1. Proposal .......................................................................................................................... 8

2.2. Scope ............................................................................................................................... 9

2.3. Objectives ........................................................................................................................ 9

2.4. Methodology ................................................................................................................. 10

2.5. Document Structure ...................................................................................................... 12

3. The State of Art. Theoretical Background ........................................................................... 14

3.1. Historical Background/ Trends ...................................................................................... 14

3.1.1. The Evolution of Postal Service in the Recent Decades ............................................ 14

3.1.2. The Characteristics of the International Postal System ............................................ 15

3.1.3. Digitalization, Electronic Substitution ....................................................................... 16

VIII

3.1.4. Tracking, Radio Frequency Identification ................................................................. 17

3.1.5. Competition and Universal Service Obligations ........................................................ 20

3.2. Introduction to Terminology ......................................................................................... 20

3.2.1. Supply Chain .............................................................................................................. 21

3.2.2. Formats ..................................................................................................................... 21

3.2.3. Weight Steps ............................................................................................................. 22

3.2.4. Categories ................................................................................................................. 22

3.2.5. Classes ....................................................................................................................... 23

3.3. Methods and Processes ................................................................................................ 23

3.3.1. Modeling ................................................................................................................... 24

3.3.2. Optimization Problems and Methods ....................................................................... 24

3.3.3. Lineal Programming, Simplex Method ...................................................................... 25

3.3.4. Applications to Postal Service ................................................................................... 26

4. Problem Statement ............................................................................................................. 28

4.1. Description .................................................................................................................... 28

4.2. Assumptions .................................................................................................................. 29

4.2.1. General ...................................................................................................................... 29

4.2.2. Software .................................................................................................................... 29

4.2.3. Transport Options ..................................................................................................... 30

4.2.4. Suppliers .................................................................................................................... 30

4.2.5. Fixed Input Data ........................................................................................................ 31

4.2.6. Variables .................................................................................................................... 31

4.2.7. Outcome ................................................................................................................... 31

4.3. Reduced Problem .......................................................................................................... 32

5. Modeling ............................................................................................................................. 34

5.1. Definitions ..................................................................................................................... 34

5.1.1. Fixed Input Data ........................................................................................................ 34

5.1.1.1. Estimated Costs ..................................................................................................... 34

5.1.1.2. Estimated Times .................................................................................................... 34

5.1.1.3. Volume Discounts ................................................................................................. 35

5.1.1.4. Average Weights and Number of Items ................................................................ 35

5.1.1.5. Sub-indexes ........................................................................................................... 35

5.1.1.6. Declarations .......................................................................................................... 36

5.1.2. Decision Variables ..................................................................................................... 36

IX

5.1.2.1. Sub – indexes ........................................................................................................ 36

5.1.2.2. Declarations .......................................................................................................... 37

5.2. Objective Function ........................................................................................................ 37

5.2.1. Costs .......................................................................................................................... 37

5.2.2. Volume Discounts ..................................................................................................... 38

5.3. Constraints .................................................................................................................... 38

5.3.1. Binary Constraints ..................................................................................................... 38

5.3.2. Constraints Relative to Choice of Suppliers .............................................................. 38

5.3.3. Constraints Relative to Volume Discounts ................................................................ 39

5.3.4. Time Constraints ....................................................................................................... 40

6. Implementation ................................................................................................................... 41

6.1. Database ....................................................................................................................... 41

6.1.1. Structure ................................................................................................................... 41

6.1.2. Pick up Cost Worksheet ............................................................................................ 42

6.1.3. Handling Cost Worksheet ......................................................................................... 43

6.1.4. Transport Cost Worksheet ........................................................................................ 45

6.1.5. Delivery Cost Worksheet ........................................................................................... 47

6.1.6. Solver Worksheet ...................................................................................................... 49

6.1.6.1. Functional Unit: Input Data per Route and per Logistic Stage.............................. 51

6.1.6.2. Functional Unit: Results per Route and per Logistic Stage ................................... 53

6.1.6.3. Functional Unit: Volume Discounts....................................................................... 55

6.1.6.4. Functional Unit: Time Constraints ........................................................................ 56

6.1.6.5. Functional Unit: Objective Function Cell .............................................................. 57

6.2. Solver Application ......................................................................................................... 57

6.2.1. Structure ................................................................................................................... 58

6.2.2. Adapting Model to Solver ......................................................................................... 61

6.3. Daily Use and Manipulation .......................................................................................... 62

7. Test Results ......................................................................................................................... 63

7.1. Test Phase ..................................................................................................................... 63

7.2. Economic Results .......................................................................................................... 64

7.2.1. Economic Results of Study A: .................................................................................... 67

7.2.2. Economic Results of Study B ..................................................................................... 68

7.3. Budget for the Further Development ........................................................................... 69

7.4. Economic Return ........................................................................................................... 70

X

7.4.1. Assumptions .............................................................................................................. 70

7.4.2. Optimistic Scenario ................................................................................................... 71

7.4.3. Realistic Scenario ...................................................................................................... 71

7.4.4. Pessimistic Scenario .................................................................................................. 72

7.5. General Outcomes ........................................................................................................ 72

8. Conclusions ......................................................................................................................... 75

8.1. Achievements ................................................................................................................ 75

8.2. Future Challenges ......................................................................................................... 75

Appendices .................................................................................................................................. 77

Appendix A

Manual for pricing tool version 0.1 ........................................................................................... 78

Appendix B

Manual for pricing tool version 1.0 ........................................................................................... 83

Appendix C

Codes for Excel Macros ............................................................................................................. 89

Bibliography ................................................................................................................................ 93

XI

Glossary

GDP Gross Domestic Product

B2B Business to Business

NPO National postal operator

Bpi Bpost International

USPS United States Postal Service

EMC European Mail Centre

UPS United Parcel Service

IPC International Post Corporation

REIMS Remuneration of International Mails

PSO Postal Service Operator

VoIO Voice over IP

RFID Radio Frequency Identification

CPG Consumer Packaged Goods

USO Universal Service Obligation

EPPML Extensible Postal Product Model and Language

EMS Express Mail Service

LP Linear Programming

IP Integer Programming

VBL Visual Basic Language

XII

MILP Mixed Integer Linear Programming

EU European Union

GB Great Britain

USA United States of America

CR Czech Republic

CH China

BEL Belgium

Crlsfw Cost of service per route, logistic step, supplier, format and weight

Trls Time required by supplier to carry out a service from a specific route and

logistic stage

Tmaxrl Time requirement by customers for a service per logistic stage and route

AWfw Average Weight ot the items per weight step and format

Nrlfw Number of items per route, logistic step, format and weight

Drls Binary decision variable :

{

Vi Binary decision variable:

{

VDi Volume discount applied for condition i

Sigma, standard deviation

XIII

List of Figures

Figure 1.1. Revenues of the postal sector to GDP [2] ......................................................................... 1

Figure 1.2 Employment in postal sector to total employment [2] ..................................................... 2

Figure 1.3 Profit Margin of postal operators in 2009 [2] .................................................................... 2

Figure 1.4 European Mail Centre, Zaventem, Brussels – Headquarters of bpi ................................... 4

Figure 1.5 Sorting line in EMC, Zaventem Brussels ............................................................................. 5

Figure 1.6 Logo MSI Worldwide Mail .................................................................................................. 6

Figure 2.1 Objectives Scheme ........................................................................................................... 10

Figure 2.2 Scientific approach to Decision-Making [9] ..................................................................... 11

Figure 2.3 Methodology Scheme ...................................................................................................... 12

Figure 3.1 Evolution of cost- effective applications of RFID [19] ...................................................... 18

Figure 3.2 Simplified Supply Chain of bpi ......................................................................................... 21

Figure 3.3 Steps of Simplex algorithm .............................................................................................. 26

Figure 4.1 Structure of bpi assignment problem .............................................................................. 29

Figure 6.1. Left part of the “solver” worksheet with hidden rows and columns.............................. 50

Figure 6.2. Right part of the “solver” sheet with hidden rows and columns ................................... 50

Figure 6.3. Functional unit input data per route and per logistic stage in the “solver” worksheet. 51

Figure 6.4. Input data of route GB-USA and Pick Up* ...................................................................... 51

Figure 6.5. Functional unit Results per route and per logistic stage in the “solver” worksheet. ..... 53

Figure 6.6 Results of route GB-USA and pick up step ....................................................................... 53

Figure 6.7. Functional unit: Volume discounts on the “solver” sheet .............................................. 55

Figure 6.8. Functional unit: Time constraints on the “solver” sheet ................................................ 56

Figure 6.9. Solver Add in from MS Office Excel® .............................................................................. 58

Figure 6.10. Maximize, minimize the objective function or making it converge to a certain value . 58

Figure 6.11. Unconstrained Variables Non-negative, Simplex LP ..................................................... 59

Figure 6.12. Options Window from Excel solver ............................................................................... 60

Figure 6.13. Objective function cell in Excel solver........................................................................... 60

Figure 6.14. Selection of decision variable cells ............................................................................... 60

Figure 6.15. Constraints in the solver ............................................................................................... 61

Figure 6.16. Add Constraint .............................................................................................................. 61

Figure 7.1. Graphic maximum values vs. optimum values, study A................................................. 67

Figure 7.2. Graphic costs with random suppliers vs. optimum values, study B .............................. 68

Figure 7.3. Positive profit after subtracting investment and maintenance costs on three scenarios,

study A .............................................................................................................................................. 73

Figure 7.4. Positive profit after subtracting investment and maintenance costs on three scenarios,

study B .............................................................................................................................................. 74

XIV

List of Tables

Table 1 . Approximate composition of letter mail operational costs and scale economies [16] ..... 16

Table 2. Potential global market in billions of RFID tags yearly [19] ................................................ 18

Table 3. The areas, scales and results of RFID in post authorities and express companies [19] ...... 19

Table 4. Specifications of bpi formats ............................................................................................... 22

Table 5. Weight steps division .......................................................................................................... 22

Table 6. Bpi categories ...................................................................................................................... 22

Table 7. Bpi classes............................................................................................................................ 23

Table 8. Assignment table for reduced problem .............................................................................. 32

Table 9. Example for estimated costs representation* .................................................................... 34

Table 10. Example for time requirement representation ................................................................. 35

Table 11. Pick Up worksheet ............................................................................................................. 43

Table 12. Part of “Handling” worksheet ........................................................................................... 45

Table 13. Transport cost table from “Transport” worksheet ........................................................... 46

Table 14. Customs table from “Transport” worksheet ..................................................................... 47

Table 15. Part of a table from “Delivery” worksheet ........................................................................ 49

Table 16. Volume discounts for contracting both Pick Ups and Handling service ........................... 55

Table 17. Volume discounts for contracting one air prior transport supplier for all 3 transport

routes ................................................................................................................................................ 56

Table 18. Time constraints for route GB – USA ................................................................................ 56

Table 19. Objective function cell....................................................................................................... 57

Table 20. Test run results 1-7............................................................................................................ 65

Table 21. Test run results 8-10 ......................................................................................................... 66

Table 22. Comparison between the maximum value and optimum total costs, study A ................. 67

Table 23. Comparison between the total cost taking random suppliers and optimum total costs,

study B .............................................................................................................................................. 68

Table 24. Budget for the future software development................................................................... 69

Table 25. Investment, profit and positive differences on 3 scenarios .............................................. 73

XV

List of Equations

Equation 1. Total potential assignments according to number of countries and suppliers ............. 29

Equation 2. Minimization function, costs part .................................................................................. 37

Equation 3. Minimization function, volume discount ....................................................................... 38

Equation 4. Constraint one and only one supplier per service ......................................................... 39

Equation 5. Set of constraints for volume discounts of supplier 1 for delivering both pick-ups and

handling on routes 1,2 and 3 ............................................................................................................ 39


handling on routes 1, 2 and 3 ........................................................................................................... 39


handling on routes 1,2 and 3 ............................................................................................................ 40

Equation 8. Set of constraints for determining the application of volume discount in case an air

transport company obtains all three routes. .................................................................................... 40

Equation 9. Equations from “Pick up Cost” worksheet .................................................................... 42

Equation 10. Equations from “Handling” worksheet ........................................................................ 43

Equation 11. Estimated transport costs ............................................................................................ 46

Equation 12. Customs ....................................................................................................................... 47

Equation 13. Estimated costs in “delivery” worksheet ..................................................................... 48

Equation 14. Difference in “delivery” worksheet ............................................................................. 48

Equation 15. Sum of cost per format PGEP in “Delivery” worksheet, grouping together the weight

steps .................................................................................................................................................. 48

Equation 16. Average weight ............................................................................................................ 52

Equation 17. Prices by format for one logistic stage and route....................................................... 54

Equation 18. Effective prices by format for one route and logistic stage ........................................ 54

Equation 19. Formula to copy the first non-zero value. ................................................................... 54

Equation 20. Objective function from “solver” sheet ....................................................................... 57

Equation 21. Standard deviation ...................................................................................................... 64

Equation 22. Yearly costs for running daily the optimization tool ................................................... 70

Equation 23. Economic Results, optimistic scenario, study A .......................................................... 71

Equation 24. Economic Results, optimistic scenario, study B ........................................................... 71

Equation 25. Economic Results, realistic scenario, study A .............................................................. 71

Equation 26. Economic Results, realistic scenario, study B .............................................................. 72

Equation 27. Economic Results, pessimistic scenario, study A ......................................................... 72

Equation 28. Economic results, pessimistic scenario, study B .......................................................... 72

1

1. Introduction

1.1 Motivation

Postal Services are fundamental for economic development, since they have an important

support function for many other economic sectors and as such contribute to generating wealth

and employment. Additionally, in most of the countries, the law recognizes the right of any citizen

to have access to postal communications at affordable prices. [1] Moreover, the postal sector is

submitted to some recent changes that happened both in the society and the economic frame

and therefore is an interesting field of study.

1.1.1. Postal Service in the European Union

Effectively, in most countries belonging to the European Union, the postal sector takes an

important part in the national economy, whereby the Belgian situation in percentage of gross

domestic product (GDP) and employment is rather in the European average, whereas the profit

margin is the highest of the European Union as can be observed in the graphics below. [Fig. 1.1,

1.2, 1.3]

Figure 1.1. Revenues of the postal sector to GDP [2]

Chapter 1. Introduction

2

Figure 1.2 Employment in postal sector to total employment [2]

Figure 1.3 Profit Margin of postal operators in 2009 [2]

In a world that is becoming more and more dynamic and global, the postal and logistic services

are more required than ever. Nevertheless the postal operators are facing big challenges. This can

be seen through declining volumes in many segments of the market. [2]

Since the breakthrough of the new technologies, the electronic information flow has substituted a

formerly big branch of the traditional postal services. But the same technologies also encourage

the electronic purchase of goods. The fact is that the demand for global material flow is increasing


3

due to the internationalization of companies and an intensified trade. [3] This evolution should be

beneficial to both B2C and B2B branches in a long run.

Nevertheless, the national postal operators (NPOs) have to face the emergence and the growth of

new private or foreign operators due to the liberalization of the sector that took place in the

recent decades. [4] This development means for most of the former NPOs that changes to

structure, procedures and technologies and even to the culture of the company must be made in

order to be competitive in the sector and to maintain the market share in addition to

modernizations that are made in order to switch from letter mail transport to predominant

parcels transport. [5]

1.1.2. Bpost

Bpost, formerly known as De Post in the Flemish community, La Post in the Francophone

community and Die Post in the German speaking community, is the Belgian NPO. It is one of

Belgium’s largest civil employers with over 35.000 employees. It handles about 3.9 billion postal

items yealy. In 2008 Bpost achieved a turnover of 2,6 million Euros.

In 1830 the Belgian Postal services became a public enterprise under the name of “Direction

Générale des Postes”. On 17th of March 2000, the enterprise became independent from the

Belgian government under the condition of compromising necessary services to the Belgian state.

In January 2006, the Post Invest Europe Sarl Group (consisting of a union from the Danish Post

and CVC Funds) bought 50% minus one share of Bpost. The Belgian government owns 24,14% of

the company, the federal society of participation (Federale Participatiemaatschappij) owns 25,87%

of the shares and the staff-organization of Bpost owns 0,01% of Bpost.

The Danish Post was the first choice of the Belgian government to buy Bpost, because it could

help Bpost with its know-how and technologies to modernize the Belgian postal system.

1.1.3. Bpost International

Bpost International is a business unit of the NPO of Belgium and provides exclusively international

daily mail, direct mail and parcel service in the Business to Business (B2B) sector in Belgium and

worldwide. The daily mail service includes the tailored and all-in-one mailing solutions for large

volume customers, the direct mail refers to the service around direct marketing items such as

letters, brochures and catalogues and parcel services are customized solutions for packages.


4

Bpost International has about 400 employees and handles over 340 million items per year. The

turnover of bpi rises to 394 million Euros, which puts it in the top 5 international postal operators.

The mission of bpi is to ensure a relentless passion for developing and delivering recognized

superior customer result-driven solutions and peace of mind. Their vision is to deliver the best

international postal solutions enabling mutual sustainable growth by applying 7 fundamental

company values, that is to earn customer confidence, to be close to customers, to respect

commitments, to successfully meet challenges, to empower, to take the initiative, to share

success, to simplify the approach. [6]

One of the most important strategies of bpi is to focus on deliveries with big volumes, this way

the marginal costs are reduced. Furthermore, the number of customers can be reduced and a

more customized service can be offered to every customer. That is the main reason, why bpi

operates exclusively in the B2B sector.

The headquarters of bpi, the European Mail Centre (EMC) is located in Zaventem, Brussels. It has

a total freight capacity of 10 000 m2 and 3000 m2 of office space. The pictures of the outside and

the inside of the EMC can be seen on figures 1.4 and 1.5. The EMC is situated in the very heart of

Europe, directly accessible through motor and railways and also close to Brussels National Airport

which is an important transit center with daily flights to and from European and worldwide

destinations. As a consequence, any big European city can be reached theoretically within 4 hours.

[7]

Figure 1.4 European Mail Centre, Zaventem, Brussels – Headquarters of bpi


5

Figure 1.5 Sorting line in EMC, Zaventem Brussels

In the most important markets worldwide bpi works with exchange offices to guarantee the most

efficient solutions and the smoothest handling. Additionally, bpi is enclosing large, strategic

partnerships, for example with MSI Worldwide Mail (see logo at figure 1.6), an American Postal

operator for international mail and parcel service. Through these partnerships, the partner

companies can outsource their activities with fewer formalities and without unnecessary delays.

1.1.4. Postal Service in the USA

The United States Postal Service (USPS) is one of the most developed National Postal Operators at

the edge of technology. It sustained a practical monopole for domestic mail, though direct

competitors such as FedEx and United Parcel Service (UPS) compete in the segment of urgent mail

and parcels.

The reason for such implementation of technology and especially the use of operations research

is related with the big extension of the country and the high population rates in very distant urban

regions. The high industrial development and challenging service sector require a very well

organized and optimized delivery system. [8]

Bpost International sees a feasible entrance possibility in the international mail segment in the US

market. The realization of this challenging task is one of the current concerns of bpi. In this master

thesis a focus on the US market is kept in regard to the future application of the pricing tool.


6

1.2. Problem Description

Bpost International, as one of the top companies for international postal services is concerned

about the optimal usage of resources in order to improve its performance and be more

competitive in the sector.

As recent studies have proven, national postal operators tend to fusion and co-operate with each

other, in order to use common resources more efficiently. [2] Aligned with this trend, in 2010,

Bpost bought a high percentage of shares of MSI Worldwide Mail (see logo below), a back then 3

and a half year old, uprising American postal operator with headquarters in Sterling, Virginia.

Within this partnership MSI continues to operate with full autonomy allowing Bpost International

to reinforce its future growth in the American market.

Figure 1.6 Logo MSI Worldwide Mail

The activities of bpi are very centralized; most of the sorting and handling is currently being done

at the sorting center EMC in Zaventem, but taking into account the recent and future partnerships,

the expansion of the market and the optimization of resources, it is inevitable to think of

outsourcing certain activities.

Bpost International has to deal with the collection, the handling, the transport and the delivery of

postal items. In the collection phase mail and parcels are gathered in different parts of the world

to be moved from collecting points to the corresponding sorting center where the handling

process takes place. Different alternatives are offered for the transport. The alternatives depend

basically on the time constraints and the means of transport used and their speed. The orders are

normally transported to a distribution center that carries out the distribution to the end customer.

The Supply Chain of bpi is similar to supply chains of other companies that offer international

courier services; with the only difference that bpi operates mainly in the B2B field with rather high

volumes.


7

Each of the steps can be carried out by a different supplier, partner or by bpi itself, the tool that

will be developed for this master thesis will make quantitative studies in order to choose the

optimal combination of suppliers.

8

2. Goal

2.1. Proposal

The main goal of this master thesis is to define a dynamic application that determines and

optimizes the delivery routing and the usage of means according to diverse constraints taking into

account various suppliers and partners. It should calculate the optimal costs and determine the

optimal supplier to carry out a whole logistic step in order to facilitate the pricing of the services.

The improvement proposal consists of 4 parts:

1) Develop a mathematical model that describes the economic relationships between bpi

and its suppliers and partners, and the time constraints towards customers.

2) Implement the mathematical model to a solver, with the objective to choose adequate

partners and suppliers at lowest cost and respecting the minimum times.

3) Include the solver application into a dynamic user friendly database system that can be

updated daily.

4) Prove the efficiency and the economic relevance of the tool.

Since the goal aimed in this master thesis is to design a feasible mathematical model that depicts

the situation of Bpost International at a basic level, we decided to use freeware in order not to

cause additional costs to the company. Therefore, solutions such as AIMMS®, Gurobi® and CPLEX®

are not yet considered. They will become interesting after this first approach to cost optimization

for bpi prove to be useful and a future powerful optimization tool can be developed.

Lpsolve® is a potent freeware solver that can solve mixed integer problems with up to 1000

variables. On the other hand it is not as commonly known as the MS Excel® solver for which more

literature and help features for the solver function of Excel exist. Furthermore, its compatibility

with the Excel database is granted. The Excel solver’s limits of 100 variables and about 200

constraints are not a hinder to the basic problem that is going to be considered in this paper.

Finally, all involved parties agreed to the use of Microsoft Excel® because of its sufficient

efficiency and ease of use. The change to a solver with higher computational power will be

Chapter 2. Goal

9

pondered for the next development step, outside of the scope of this master thesis, if the results

of the current studies are satisfactory.

2.2. Scope

In this master thesis a subsystem of bpi’s supplier network is going to be optimized in regard to

supplier costs assuming that the real system consists of many subsystems that exhibit the same

behavior. The benefits of the development of a big model for the complete system will be

evaluated, but its design is outside of the scope of this master thesis.

The focus will be kept on suppliers and partners in regard to the quantitative values, mainly on

cost, but also on time. Qualitative values, such as extra-services, reliability and sympathy cannot

be included in this study. The intrinsic costs of each supplier for a certain logistic step (collection,

handling, transport, delivery) are considered for the study. Fixed costs such as customs and air

cargo are not taken into account for the optimization. Other costs such as communications,

expenses etc. are regarded as negligible.

2.3. Objectives

The compliance of the overall goal of this master thesis implies the fulfillment of each and all of a

set of objectives.

First of all, it is categorical to understand the company we are working with, the business to which

it is dedicated and its need for improvement. This preliminary study has been discussed already in

the first chapter and concluded in the need for improvement in choosing the optimal resources,

i.e. suppliers and partners in order to save costs.

In this case, the understanding of the supply chain of bpi’s network is necessary to be able to

crystallize the different cost factors involved in the delivery of letter mails or parcels. It is also

important to carry out a thorough literature study about the technology in use in the Postal

Sector and the techniques that can be applied in our case.

After understanding the whole process, our next objective is to formulate a mathematical model

that should be as simple as possible but sufficiently detailed to depict the reality. It should

represent the supply chain of bpi from collection to the delivery and include all necessary

information to make the decision of outsourcing or keeping a determined step and entrust the

service to the adequate supplier or partner if it applies.

Chapter 2. Goal

10

The next conceptual step, but in practice a parallel step would be to implement this model to the

solver. Parallel, because the feedback and the solutions of the solver give an insight to which

propositions are right and which are wrong. The implementation of the model goes hand in hand

with the creation of the basic database in order to feed the solver with data for the continuous

validation process. Finally, the secondary database frame has to be built and modifications to the

model have to be done for the user friendliness and an optimal performance of the pricing tool.

In conclusion, the objectives consist in literature, company and process studies, formulate the

mathematic model, its implementation and building a user friendly application as can be seen in

the scheme below (fig. 2.1).

Figure 2.1 Objectives Scheme

2.4. Methodology

Operations Research will be extensively used to analyze and resolve the problem. Operations

Research is a scientific approach to decision making that seeks to best design and operate a

system, usually under conditions requiring the allocation of scarce resources.

The scientific approach to decision making can be depicted as follows:

Literature Study

Worldwide State of Art in technology

Bpost International

Supply Chain

Cost management

Modelling

Mathematical Model

Implementation to solver

Testing

Application

Integration

Database

Clear

Userfriendly

Chapter 2. Goal

11

Figure 2.2 Scientific approach to Decision-Making [9]

All the steps of this approach are going to be followed and exposed in this paper.

More precisely, the Linear Programming method has been chosen to model the problem, because

it is suitable for problems with limited amount of variables and constraints as in this case and can

give a global optimal solution as result.

Linear Programming is a mathematical technique that can be used to find the global minimum or

maximum of a constrained region defined by inequalities. [10] It is very important that all

relations that are defined in this model are linear. No multiplications of variables of any kind are

allowed.

The input data are going to be the costs, the times of delivery and the times required by

customers.

The decision variables are going to be binary, indicating if a specific supplier will be chosen.

Our objective function will pursue the global minimum cost and will return the elected suppliers

as solutions by means of the values of the previously defined binary variables. The main

constraints are related to time and other constraints are of a practical character such as to restrict

the employed supplier to one. Finally, some volume discounts can be added to the objective

function, with the corresponding new binary variables and constraints.

For building the model, extensive knowledge about Linear Programming (LP), the Simplex method

and more specifically Integer Programming (IP) is required, since almost all variables are going to

be binary.

To implement the model in the Excel Solver, basic user knowledge about the functionalities of the

tool must be acquired. As explained in the previous paragraph, the model building and the

Chapter 2. Goal

12

implementation in the Solver should be parallel, because mutual feedback is needed. So for

example, what in the formal modeling is a mere declaration of binary variables, in the Excel Solver

becomes a variable declaration plus a constraint. Sometimes the linearity of the model is not very

clear in the Excel Solver because of the highly interlaced fields and it is very likely to obtain such

errors at the beginning. These and many other issues have to be disentangled.

Finally, in order to include the solver in a database, many formulas have to be written and macros

have to be created in order to provide the necessary facilities to show the results. The knowledge

about MS Excel® and Visual Basic Language (VBL) are very useful for this task.

The abstract processes are represented in the figure 2.3.

Figure 2.3 Methodology Scheme

2.5. Document Structure

This document is divided into chapters, whose content will be explained below:

Chapter 1: Introduction

In this chapter, the motivation and the frame in which the thesis is developed, are briefly

described. Moreover an insight to the postal sector and the company will be obtained and the

reader will be familiarized with the treated problem.

Chapter 2: Goal

In this part, the formal thesis proposal and objectives pursued in this project are mentioned, the

scope of the thesis is defined and the methodology to achieve the objectives is explained.

Implementation MS Excel Data

Base

MS Excel Solver

LP, IP

Chapter 2. Goal

13

Chapter 3: State of Art. Theoretical Background

The history of postal service and the recent developments in this sector are discussed in this

chapter. Furthermore, the postal terminology is explained and finally, a study of feasible

techniques concerning the stated problem is conducted.

Chapter 4: Problem Statement

In this part, an extensive examination of the problem and possible solutions are performed. First

of all, the problem is described in all its details. For the lacking data, assumptions are established.

Possibilities of resolution are considered and finally, a reduced problem is issued in order to be

solved with the available resources.

Chapter 5: Modeling

The mathematical model corresponding to our problem is constructed and declared in all detail,

whereby the aim for this model is to be as general as possible, although references to our limited

problems are made.

Chapter 6: Implementation

In this chapter, the detailed process of integrating the model to a solver and to a dynamic and

user friendly database is described, followed by the instructions of manipulation of the final

application.

Chapter 7: Test Results

This part of the document deals with the validation of the application after its construction and

with its potential economic impact. In addition, the budget and the economic return of a the

sophisticated tool basic on the current application are calculated

Chapter 8: Conclusions

This chapter states the aims achieved during the completion of this master thesis. Moreover,

recommendations to the further development on the treated topic are made.

14

3. The State of Art.

Theoretical Background

3.1. Historical Background/ Trends

The postal service has existed since the ancient history, being the only conditions for this kind of

information exchange the invention of the writing and a reliable mean of transport. All

sophisticated cultures of the antique had highly efficient networks. However, this service was

indicated exclusively for political and military aims and fell with the deterioration of the empires.

[11]

The modern postal service in Europe begun with the establishment of an extended postal network

that connected all important cities, which made it available for the leading classes of the society.

Thanks to the first and second Industrial Revolution, new means of transport were invented and

the transportation became much more efficient. The post became eventually a service that was

extensively provided by the states to the citizens to regulated fares. [12]

Since then, the postal service has been exposed to fast developments and changes due to the

Digital Revolution, also called sometimes Third Industrial Revolution. It was mandatory for all of

the NPOs to undertake steps towards the modernization.

3.1.1. The Evolution of Postal Service in the Recent Decades

In recent decades the future of postal activity has been viewed with pessimism, justified by the

development of new information and communication technologies. In line with that pessimistic

view of the postal sector, it was thought that a process of substitution of the postal sector by e-

mail would take place as was the case of the new technologies replacing the now-old and

obsolete ones. [13]

However, despite such pessimistic forecasts, the postal sector has continued growing. Postal

shipments have continued on an upward path, though at a much lower rate than electronic

Chapter 3. The State of Art. Theoretical Background

15

communications (e-mails). Only since 2000, the figures of the public agencies have shown some

stagnation in the growth of the postal sector. This hold back, however, conceals the growth that is

taking place in postal deliveries through private companies after the process of liberalization of

the sector in many countries. Moreover, the shipments related to unaddressed mail, linked to

advertising and marketing campaigns are growing considerably. Probably, a process of

substitution has indeed taken place in certain services offered by the postal sector, as in the case

of financial transactions or personal letters. At the same time, this process may not necessarily be

one of pure substitution, but complementary to the postal service. Certainly the postal sector is

trying to diversify. [14]

The European postal sector has faced two big challenges over recent decades. On the one hand, it

had to face the challenge unleashed by new information and communication technologies. On the

other hand, their activities were reorganized in an environment of competition and deregulation.

Studies have proven a growth of productivity accompanied by a decrease of efficiency, for which

new procedures must be established to fight against. [15]

3.1.2. The Characteristics of the International Postal System

Due to the liberalization of the postal sector, new companies or established companies in other

countries, so called entrants, are permitted to access the infrastructures and services from

incumbent companies for a corresponding charge. This leads to the question of access pricing.

The key characteristics of the postal system that need to be borne in mind when considering

access pricing are as follows:

- Commitments to universal delivery and a geographically uniform tariff structure for mail

are constraints imposed by the government.

- The sort and delivery functions account for the vast majority of costs (see Table 1).

- In parcel service the collection and transports take in higher percentages both for cost

and scale economies to the detriment of the percentage for delivery. Besides, the pricing scheme

is more customized for each individual delivery.


16

% Cost Scale Economies

Collection 10 Moderate

Outward

sorting 18 Low

Transport 2 Low

Inward sorting 5 Low

Delivery 65 High

Table 1 . Approximate composition of letter mail operational costs and scale economies [16]

- There are significant economies of scale in the delivery function (which is the function

that most closely approximates to having natural monopoly characteristics).

- Economies of scale in other functions are rather low (Table 1).

- There is significant averaging of costs. In addition to averaging of costs between urban

and rural users, there is also cost-averaging between regular and casual mailers.

- If the monopolies of the postal sector have been based on price, i.e. state institutions

fixed the maximum prices in exchange of granting the monopole, questions as to the basis on

which competition might be allowed to occur are raised. [16] One option would be to call on an

organism to fix the end price and compete solely on service and quality, or to compete both on

price and on quality, which would be possible thanks to the relatively low fixed costs for the

postal operators. The International Post Corporation (IPC) is such an international institution that

attempts to group postal operators from different countries in order to optimize the cross border

interactions. Therefore, the remuneration of international mail services (REIMS) is established in

order to price systematically intercompany postal services.

3.1.3. Digitalization, Electronic Substitution

Over the period from 1986 to 2004, a steady decline in the share of postal service operators (PSOs)

coming from the household sector has been identified. The high increase of percentages of

households with access to the Internet and a constant decrease in the price of national and

international telephone services and in addition, the emergence of voice over IP (VoIP) have had a

negative effect on the household-level demand for postal delivery services. [17] The volume of

single piece First-Class mail, for example, has declined in the United States at an average annual

rate of close to 4% from 2000 to 2006.


17

To maintain the PSO’s revenues, other sectors must be reinforced. Currently, the PSOs have a

considerable cost advantage in providing saturation advertising for a given geographic area

through its local delivery network. This is unlikely to change in the foreseeable future. Significant

electronic substitution, nevertheless, seems plausible in the provision of bulk advertising, as the

fraction of households with computers increases steadily. In the developed countries, the

presence of computers and internet access across various channels is almost 100%. Package

delivery services are another growing source of revenue for the PSOs.

The electronic alternatives will constitute further competition and a thread to the PSOs. However,

there are plenty of alternatives of intensification in the growing sectors electronic service cannot

substitute. [17]

3.1.4. Tracking, Radio Frequency Identification

The product tracking became widely used in the postal sector. Also bpi uses product tracking.

Most of the tracking is done by bar codes. However, there is a new emerging technology, the

radio frequency identification (RFID) that can substitute the bar codes in the next decades.

The RFID is an automatic identification technology, which identifies items and collects the item

related information without human intervention. It is a wireless technology based on

radiofrequencies, which allows transmission of data without physical contact. This is its main

advantage to optical technologies such as bar codes. By replacing bar codes with RFID tracking

system many manual processes could be automated. [18]

RFID technology applications grow rapidly and have received considerable worldwide attention as

the costs of RFID tag continually falls and leading retailers strictly instruct their suppliers to use

RFID. The application of RFID changes gradually from people identification to product

identification. The application fields increasingly expand to retail supply chain logistics, postal and

courier service, and asset management. The evolution of cost-effective applications of RFID is

shown in the figure below.


18

Figure 3.1 Evolution of cost- effective applications of RFID [19]

RFID systems for postal and courier service including tags will reach a market share of 3 billion US

dollars by the year 2016, about 25% in the US, 25% in Europe, and 50% in Eastern Asia. The

demand will be higher if RFID tags for individual items gain general acceptance. In due course,

over one trillion postal items will be tagged yearly. That may occur by the year 2020 [19]. The

potential global market is shown in the table below.

Table 2. Potential global market in billions of RFID tags yearly [19]

The RFID technology is for sure a very important tool to increase the efficiency of the PSOs. Some

of them already employ this new technology as shown in table 3.


19

Table 3. The areas, scales and results of RFID in post authorities and express companies [19]

The application of RFID technology can assist the optimization of postal and courier services in

various aspects and it can be concluded as follows:

1) It effectively supervises and manages reusable assets, decreases consumption, increases the

availability and the usage of assets, and reduces the operation costs.

2) Its operation is automated, and this decreases the manual mistakes, and thereby enhances the

efficiency of operation and optimizes the production processing.

3) It effectively realizes the tracking for valuable items, and prevents the loss of items, and

thereby improves the service.

4) It provides sufficient information regarding the feedbacks of items on time for the business

partner.

5) It increases the operation efficiency of the supply chain and enhances the cooperation between

the enterprises.


20

The study and proposal of this technology is beyond the scope of this master thesis, although it

would be an interesting parallel solution to increase efficiency. Extensive information technology

infrastructure and restructuration of the sorting center would have to be provided. Thus, RFID

should be considered for the middle term planning. [19]

3.1.5. Competition and Universal Service Obligations

Postal services were the latest network industry to be liberalized and privatized given the natural

monopoly characteristics of the delivery function.

In a liberalized postal market, new PSOs are allowed to enter the market with the patronage of

the governments in the first years. Hence, the customers will be able to choose between different

operators for the same service, especially for international mails. Consequently new competence

in quality and prices are created, in which postal operators have to focus now to maintain

customer satisfaction.

The Universal Service Obligation (USO) guarantees that all costumers have access to a basic

package of services at affordable uniform prices and that the services have a minimum level of

quality. However the compliance with the USO may be threatened when determined companies

limit services to profitable customers, opt for niches or choose product differentiation strategies.

[20]

Studies have contributed to the knowledge that firms are able to determine both the quality and

the coverage they would like to offer, whereby a relationship between both exists. It is proven

that modern postal operators first choose their coverage and then determine the quality of the

service in function of the coverage in opposition to the traditional model of product

differentiation which was applicable in the monopoly. [21]

3.2. Introduction to Terminology

The postal sector uses its own extensive terminology to name the products and services offered

and also the tools and practical objects used in the postal environment. Different Postal Operators

may have different denominations for the same object, though the Extensible Postal Product

Model and Language (EPPML) is widely used because of its practical denomination of a varied

range of innovative postal products and its integration in the activity of the United States Postal

Service (USPS). [22] In this section the relevant terminology of bpi for the objective of this paper

will be mentioned.


21

3.2.1. Supply Chain

Bpi is as an international deliverer of mail and parcel service and owns a worldwide logistic

network. However, for our purpose a very simplified description with the basic steps that match

with the cost steps is sufficient.

Figure 3.2 Simplified Supply Chain of bpi

The first element of bpi’s supply chain is “Pick Ups”. It is the activity of collecting mail and parcels

from collecting points to a sorting center. The handling refers to the activity of identifying the ZIP

codes and grouping the items for the same regions together. Currently, bpi only outsources about

20% of the sorting activity; all other items are brought to the EMC. Bpi offers international

transport with different alternative services. These alternatives depend basically on the time

constraints and the means of transport used which coincide with the categories (priority Air, SAL,

Surface and Priority Surface) that are going to be explained below. Not all categories can be

offered for all routes, because of the geographical and volume issues. The customs coincide with

the transport in time, but must be taken into account separately as a fixed costs independent

from the postal company and its suppliers. The orders are normally transported to a distribution

center that carries out the distribution to the end customer.

Each of the steps of this simplified supply chain has associated costs, the sum of these costs allow

the pricing of the end service by comparing it to the actual postal rates.

3.2.2. Formats

Bpi defines three different formats of contents according to REIMS (Remuneration of

International Mails), a system for international intercompany pricing.

These formats have French denominations for small, big and voluminous: Petit, Grand,

Encombrant. The specifications are given in the table below.

Pick-ups Handling Transport Delivery

Customs


22

Table 4. Specifications of bpi formats

These three formats include single letters, flats, parcels and packages with grouped letters for the

same destination. There exists an additional format called parcel. However conventional parcels

are already included in the above mentioned formats, consequently this denomination will be

used for parcels that don’t fulfill the sizes for the standard format or include various items with

different formats.

3.2.3. Weight Steps

Bpi and its partners and suppliers define weight steps in each format in order to price the services

differently depending on size and weight of each postal item.

In our pricing application, the items will be priced basically according to format and weight.

Therefore, a very generic scale is defined in order to be able to work with all suppliers. The weight

range will reach from 0 grams to 3000 grams for each of the formats.

The arrangement of the weight steps can be deduced from the following table:

Table 5. Weight steps division

3.2.4. Categories

The categories in bpi refer to the means of transport, the associated speed of transport and the

transport cost. They are visualized in the table below.

Table 6. Bpi categories

LengthxWidth (mm) thickness max weight gr

Petit min 90x140 - max 165x245mm max thi =< 5 mm 100

Grand min165x254 - max 305x381 max thi =< 20mm 500

Encombrant L+W+D: max 900mm, Rollform: L+2xdiam. max 1040 greatest dim =< 900mm 2000 (books, pamphlets 5000)

Weight (grams) Division Example

0 - 100 Every 10 grams 0 - 10

100 - 500 Every 50 grams 100 - 150

500 - 2000 Every 100 grams 500 - 600

2000 - 3000 Every 500 grams 2000 - 2500

Categories Denomination Means of transport Priority/Economy

A Priority Air Airplane Priority

B SAL (surface airlift) Truck --> Airplane Economy

C Surface Truck, Ship Economy

D Priority Surface Truck Priority


23

However, in the study performed in this master thesis only the Priority Air category is taken into

account from this table and a new category, Alternative Transport mean is added, because the

focus on oversea transportation in the future.

3.2.5. Classes

Bpi differentiates four classes of deliverables based on their intrinsic characteristics: letter mail,

parcels, EMS (express mail service) and empty receptacles. These classes are subdivided in

subclasses. Details are given in the table below.

Table 7. Bpi classes

*Letter Mail is delivered in an M-bag (Direct sack of printed matter to a single foreign address).

3.3. Methods and Processes

In this paper, the problems of optimization are defined as those whose solution consists in the

verification of its existence and the identification of the best possible solution from the feasible

solutions to a problem. More precisely, our goal will be to find the value of the decision variables,

in this case, the choice of suppliers that maximize or minimize the objective function or functions

respecting a series of constraints.

It is assumed that at each logistic step, one and only one supplier will take over the whole of a

bulk of mails and parcels of all weight steps from one route. Evidently, a cost for each of the

postal items will be incurred for the service. The objective function, on the other hand, will be the

sum of all estimated costs of the service applied to each item on each supply chain step. The

constraints will refer to the maximum times it should take a supplier to deliver a service, so that

suppliers that exceed the maximum duration of delivering a service cannot be chosen from the

solver.

Classes Denomination Description Mail Subclasses

U Letter Mail* Paper documents in Envelops UN, UR, UM, UA, UL, UT, etc.

C Parcels Packages CN, CE,CV, CT, etc.

E EMS Mail and packages for express mail service EN, ED, EM, ET, etc.

T Empty receptacles Empty receptacles TT


24

3.3.1. Modeling

A mathematical model is a careful abstraction of a real-world system into a mathematical

representation to clarify its operations and improve its related decision-making process.

A model is supposed to balance the necessity of gazing at all the details with the feasibility of

finding the suitable solutions. The use of mathematical models and their related quantitative

methods is made extensively by the scientific approach to decision making. The principal objective

of a model is to reproduce the reality as accurate as possible, trying to understand the behavior of

the real world and obtaining the responses that can be expected of determined actions. The

selection of an accurate model is of crucial importance for obtaining a solution for a real problem.

The input data affect the functioning of the model, but are not under the control of the decision

makers. Variables whose value can be controlled by the decision maker and affect the system are

called decision variables. [10]

3.3.2. Optimization Problems and Methods

The optimization problems have the aim of finding the best solution from all feasible solutions

and its statement is defined through the model and its three elements: the objective function or

objective functions, the decision variable or variables and the constraints of the model.

- The objective function is the quantitative measurement of the operation of the system

that needs to be optimized.

- The decision variables represent the decisions that can affect the objective function.

- The constraints represent a set of relations (equations or inequalities) that certain

variables are obliged to satisfy.

The optimization problems can be divided into two big categories:

- Non – linear programming: either the objective function or the constraint or both of them

are not linear in respect to the decision variables. This condition makes the computation of the

optimal value more complicated. This doesn’t apply to the problem of this thesis and no focus

must be given on this kind of problems.

- Linear Programming: all constraints and the objective function take a linear form in

respect to the decision variables.

The integer programming is an optimization model in which some or all of the decision variables

of a linear problem take integer values.


25

The binary programming is an extension of the integer programming where the variables can only

take two values, 0 or 1. The majority of allocation problems are developed within this approach.

Depending on the characteristics and the size of the problem there are three big approaches to

solve optimization problems.

- Exact techniques: the most commonly used optimization algorithm for exact techniques is

the Simplex method and its derivatives. It has been proven to be efficient to solve problems with

certain characteristics and normally reduced size.

- Metaheuristics and heuristics: there is a set of iterative methods that are used to solve

non-linear problems that fulfills the conditions of each method. However, not all non-linear

problems fulfill all conditions of an iterative method and therefore cannot be solved this way. The

heuristics are very powerful methods that gained importance with the growing capacity of the

computers. They basically consist in trying variables and keeping the best solution until finding a

better one. They do not grant a global optimum, but they can handle big problems.

- Simulation techniques: with these techniques a system is replicated with a mathematical

model that is analyzed with the computer. Very complex systems that cannot be solved with the

techniques above can be covered with this technique. Common areas of application are queuing

systems, inventory control, production and manufacturing etc.

3.3.3. Lineal Programming, Simplex Method

The problem of this master thesis fulfills the conditions under which the Simplex method can be

applied. These conditions are: all decision variables are non-negative and both objective function

and constraints are linear.

The Simplex method was developed by George Dantzig in 1947 and is an effective and efficient

procedure for solving linear programming problems. Although its concepts are geometric, it is an

algebraic algorithm.

It is based on three fundamental theorems:

Theorem 1:

Given a basis B that verifies ∀j ∈ Jzj – cj ≤ , than, the associated basic solution XB = X*B and XN

= 0 are optimal solutions.


26

Theorem 2:

Given a basis B, so that X*B > 0, and for which there exists k ∈ J that verifies zk - ck > 0 and yk

≤ , then the linear programming problem has a not confined solution.

Theorem 3:

Given a basis B, so that X*B > 0, and for which there exists k ∈ J that verifies zk - ck > 0 and yk >

0, then, there exists a basis B’ for which the associate feasible basic solution reaches a higher or

equal value than the starting basis.

The steps of a linear maximization problem using the simplex algorithm are the following:

Figure 3.3 Steps of Simplex algorithm

The Simplex Method is widely used because of its big advantage of finding efficiently the global

optimum. [23]

3.3.4. Applications to Postal Service

The collection, transport and delivery of mail and parcels belong to a large, worldwide service

sector. Applications of Operations Research are widely used in leading postal operators such as

the USPS. Since the seventies USPS has used operational research in the areas of forecasting, cost

and revenue analysis, postal network design, manpower planning and scheduling and

maintenance to maximize the outcomes. [24] Since the nineties the postal sector has used

heuristic methods for the layout planning of the sorting centers in order to increase the efficiency

of mail processing. A mixed-integer linear program (MILP) has been developed for configuring

Step 1 •Convert the Linear Problem to canonical form

Step 2 •Obtain a basic feasible solution

Step 3

•Optimal?

•Yes => End, No => Step 4

Step 4 •Determine which variable(s) should leave and which should enter the basis

Step 5 •Find new basic feasible solution => Go to step 3


27

general mail facilities and determining machine, floor space and man-hour requirements, being

USPS again the leader in technology. [25]

In 1996 an application of a combined producing/routing model has been developed for a specific

USPS division in order to balance the daily sorting and transportation. Specific routes and

production schedules detailed by the model indicate that the division could save up to 5% of the

combined production and routing cost. A MILP model is therefore applied to a trucking schedule.

It resulted in a large problem with 800 000 variables and 200 000 constrains, which was solved

daily to near optimality. [8]

In 1999 a decision support system has been designed for the Deutsche Post AG. It improved the

planning of two delivering networks, the Global Area Transportation Network (GATN) and the

Local Area Transportation Network (LATN). Furthermore, it took the means of transportation, the

vehicle routing and scheduling and the staff scheduling into account. The network of Deutsche

Post was divided in two sub-networks to facilitate the computation. Regardless, the optimization

problems could not be solved by classic exact methods. Intelligent Techniques such as Tabu

Search, Metaheuristics and Evolutionary Strategies had to be employed. [10]

28

4. Problem Statement

4.1. Description

Currently, bpi delivers mails and parcels to all countries of the world that are in regular political

situation. Bpi offers tailored solutions to companies that wish to send daily and administrative

mail, parcels and direct marketing mail. The main market of bpi is located in Belgium and the

European Union. Hence, the most intense traffic is also situated in Belgium and the European

Union. However, bpi is in a process of expanding to the US market and also the Eastern Asian

market.

Until now, bpi has had an extremely centralized system of sorting. Almost 80% of the deliveries

pass through the EMC situated in Zaventem. However, bpi has closed new partnerships and will

be able to use their installations and infrastructures. The whole planning has to be more flexible,

so that every day, with given input data and constraints, the routing can be re-planned.

The input data for the problem are the route (origin and destination), the supply chain step (pick-

ups, handling, transport and delivery), the format (petit, grand, encombrant and parcel), the

weight, the number of items and the times. The combination of suppliers should be optimized

according to regular costs, volume discounts and times, whereby regular costs and discounts are

reflected in the objective function and the times in the constraints. All input data, decision

variables and constraints are explained in the next paragraphs at length.

The solution should also be flexible to be incorporated to a database with trace and track system,

and take future developments in the transport and logistics sector, such as new means of

transport and sources of energy into account.

Chapter 4. Problem Statement

29

4.2. Assumptions

4.2.1. General

Bpi offers services to 183 countries, with intense traffic among the European Union (27 countries)

and increasing intensity in the USA and Asia.

In the EU and the US bpi has up to three alternative partners or suppliers (in this paper the term

of supplier will be used for both of them) for the handling, sorting, transport and delivering

service. In the rest of the countries bpi has on average up to two alternative suppliers.

The problem will be four times a classic assignment table with supplier as origin and country as

destiny with corresponding costs (cost can be infinite if a supplier does not offer a particular

service to a particular country). It is four times because there will be one table for pick-ups, one

for handling, one for transport and one for delivery. See figure 4.1.

Figure 4.1 Structure of bpi assignment problem

Taking the number of countries and their respective potential suppliers into account, 1206

potential assignments are obtained according to the following calculation:

4 x [((183 – 27) x 1.5) + (27 x 2.5)] = 1206

Equation 1. Total potential assignments according to number of countries and suppliers

4.2.2. Software

As already mentioned above, our case is an IP problem with linear relations and can be solved

with the Simplex method.

Supplier/Country Count1 Count2 Count3 Count4 … Supplier/CountryCount1 Count2 Count3 Count4 …

Sup1 Sup1

Sup2 Sup2

Sup3 Sup3

Sup4 Sup4

… …

Supplier/Country Count1 Count2 Count3 Count4 … Supplier/CountryCount1 Count2 Count3 Count4 …

Sup1 Sup1

Sup2 Sup2

Sup3 Sup3

Sup4 Sup4

… …

Pick Ups Handling

Transport Delivery


30

The Microsoft Office Excel Solver® is used because it is a commonly used and handy software that

has a free limited version which can be installed through an add-in, without any cost for the user.

The free edition of the MS Office Excel® Solver may be used to solve problems with up to 200

decision variables, 100 explicit constraints and 400 simple constraints (lower and upper bounds

and/or integer constraints on the decision variables).

It was decided to build a small model with restricted routes that represents subunits of the

complete system to test first the validity of the idea and leave the construction of the complete

model for a future stage. Concretely there will be 3 routes and 3 suppliers for each of the supply

chain stages. In order to take the recent and future expansion of bpi and its interest in

outsourcing services into account, the routes Great Britain – USA, Czech Republic – China and USA

– Belgium were chosen.

4.2.3. Transport Options

Our focus is on oversea and long distance transportation in the future. Thus, the transport step is

subdivided in 2 categories only: Priority Air (A) and Alternative Transport (B). The number of items

corresponding to each category is determined a priori. For testing our model, the total number to

be transported will be distributed randomly between both transport options.

For the transport costs, an additional surcharge for airline use and customs are incurred if

applicable. Average costs are taken for the computation.

4.2.4. Suppliers

The names and the exact quantity of the potential suppliers of bpi are strictly confidential. Hence,

no company names will be mentioned in this thesis and their number will be arbitrarily chosen.

Three local suppliers are supposed to provide both collection and handling services in two

locations and in the third there will be two feasible suppliers.

Three global companies are respectively assigned to priority and alternative transport. All routes

will have the same suppliers and all three suppliers will operate in all routes except one that will

not operate on one route.

Again up to three local suppliers that provide the delivery services to the respective countries are

supposed.


31

4.2.5. Fixed Input Data

The input data of the problem on hand are inter alia the estimated costs incurred by the suppliers

for a provided service regarding to one item depending on route, supply chain step, format and

weight step. Since these estimated costs are strictly confidential, the values have to be

substituted with a set of numbers obtained through random numbers with a realistic behavior

towards its relative development to increasing weight and volume, and also with realistic relative

differences to competitors.

The costs for each supply chain step: collection, handling, transport and delivery are a priori

indicated as $/item* kg. A multiplication by the average weight and number of items can yield the

cost for the group of items in the same weight step.

The time data refer to approximate values both for customer requirement and supplier needs.

The numbers of items to be transported at the levels of route, format and weight steps are also

reasonable random values for a high volume delivery for a company with the dimensions of bpi.

The numbers of each weight step are supposed to be random values between 0 and 7, 0 and 15,

and 0 and 9 respectively for the routes GB-USA, CR-CH, USA-BE.

4.2.6. Variables

The variables will be the choice of engaging the service of a specific supplier. For reasons of

economy of scales it is supposed that only one supplier can be chosen for one service given to the

whole load of packages on one route. For example only one supplier can be hired to manage the

handling of all mail items and parcels belonging to the route USA – Belgium. However, the costs

incur individually to each item.

4.2.7. Outcome

The outcome of the optimization process of the pricing tool will be in first instance the supplier

chosen for each route and supply chain stage and their respective total estimated costs. At lower

level, the estimated cost of each item of each route and supply chain stage can be viewed. For

practical reasons, since postal rates are grouped by formats on the bills, the costs for each format

are also displayed separately in order to allow a comparison of estimated cost and actual postal

rate in regard to cost efficiency.

The manners to analyze the results are manifold and flexible.


32

In this study, since no real costs can be used because of confidentiality reasons, the focus will be

set upon the correct running of the application and the relative efficiency and savings achieved by

the use of this tool with dummy data that simulate the behavior of the real data.

4.3. Reduced Problem

All parties involved in this project agreed on developing a small and simple-to-use pricing tool

based on the service on 3 routes with 3 respective feasible suppliers for each logistic step.

The three chosen routes are Great Britain – USA, Czech Republic – China, USA – Belgium to take

the recent and aimed partnerships and the outsourcing options into account.

Taking the quantity of suppliers and routes in the reduced problem into account, the assignment

table will look as follows:

Table 8. Assignment table for reduced problem

As a result there are 45 decision variables for the choice of suppliers. Additional decision variables

will be defined for the volume discounts. There exist 15 inherent constraints plus the constraints

related to the volume discounts and time constraints according to the constraint of one supplier

GB CR US GB CR US

Pick Ups & Handling Supplier GB 1



Pick Ups & Handling Supplier CR 1



Pick Ups & Handling Supplier US 1



GB-USA CR-CH US-BEL GB-USA CR-CH US-BEL

Priority Air Transport Supplier 1



Alternative Transport Supplier 1



US CH BEL

Delivery Supplier USA 1



Delivery Supplier CH 1



Delivery Supplier BEL 1



Handling

Priority Air Transport Alternative Air Transport

Delivery

Pick Ups


33

per service. The time data and cost data will determine the objective function in order to obtain

the most suitable suppliers.

The extended explanation of the details of the problem is foreseen in the following chapter.

34

5. Modeling

5.1. Definitions

For the mathematical model, we have the customer time requirement and the costs per item

depending on routes, logistic steps, formats and weight steps as fixed input data. The decision

variables are those elements we can influence in regard to the results, i.e. the choice of supplier

for a determined service.

5.1.1. Fixed Input Data

5.1.1.1. Estimated Costs

The estimated costs per item are represented for each supplier and each process step per weight

step, format and category following the scheme of the example of Pick Ups for the route Great

Britain - USA:

Table 9. Example for estimated costs representation*

*The weight steps are collapsed in the table. The extended weight steps are used, which are

explained in the paragraph of “Introduction” to terminology.

5.1.1.2. Estimated Times

Each supplier has fixed times for delivering a determined service for each route. On the other

hand, each of the services must respect the maximum duration required by customers. They will

0 - 10 …2500 - 3000 0 - 10 … 2500-3000 0 - 10 … 2500-3000 0 - 10 … 2500-3000

SupplierPHGB1 ($/item*kg) 0.134792595 … 13.4169 0.123746 … 4.389611 0.256085 … 1.355159 0.305226 … 1.079844



Parcels (gr)

Great Britain - USA

PICK UPS

P (petit) (gr) G (grand) (gr) E (encombrant) (gr)

Chapter 5. Modelling

35

constitute the basis for the time requirement of the model. An example of the representation of

time requirements is given on table 10.

Table 10. Example for time requirement representation

5.1.1.3. Volume Discounts

The volume discounts represent the values of the discount in $ that bpi will get, if contracting a

certain combination of services.

5.1.1.4. Average Weights and Number of Items

Since we assume the given cost units as $/item*kg, the number of items and the weight of each

item to the cost have to be multiplied.

To simplify the matter we will suppose the weight of the items uniformly as the average weight of

a weight step. For example, for the weight step 0 – 10 gr, we will work with the average weight of

5gr.

The number of items will be an input data given to the company periodically, normally every day.

5.1.1.5. Sub-indexes

To facilitate the use of the input data we will define sub – indexes:

∈ { , , } Routes (GB-USA, CR-CH, USA-BEL),

∈ { , , } Logistic Chain Step (Pick Ups, Handling, Transport, Delivery), = 4

∈ { , , } Supplier for specific route and supply chain step (Supplier 1, Supplier 2, Supplier 3), = 3

∈ { , , } Formats (Petit, Grand, Encombrant, Parcel),

∈ { , , } Weight steps (0-10, 10-20,…),

∈ { , , } Discount situation (supplier x gets PickUps+Handling for country y, supplier z gets transport for all routes, etc), = 12

t(pickup) effective t(handling) effective t(transpA) effective t(transpB) effective delivery effective

Respective Supplier1 5 1 1 2 2

Respective Supplier2 6 2 1 3 1

Respective Supplier3 5 2.5 1 2.5 3

Customer requirement 5 2 1 3.5 2

Great Britain - USA


36

5.1.1.6. Declarations

Crlsfw Cost for a specific weight step w and format f for a service l through supplier s on the

route r.

Trls Time the supplier s needs to deliver the service l on route r.

Tmaxrl Maximum time the customer requires for the service l on route r.

AWfw Average weight of the deliverable of format f and weight step w.

Nrlfw Number of items on route r, of format f and weight steps w.

Where Crlsfw, Trls, Tmaxrl, AWfw, Nrlfw ∈

VDi Volume discount for a certain combination of services i.

5.1.2. Decision Variables

The decision variables are exclusively binary in this model. There are two types:

- The variables to decide which supplier to choose.

- And the variables to decide where volume discounts are to be applied.

5.1.2.1. Sub – indexes

The same basic sub – indexes as in the input data are going to be used for decision variables.

∈ { , , } Routes 1 = GB-USA, 2 = CR-CH, 3 = USA-BEL, for our example.

∈ { , , } Logistic Chain Step 1 = Pick Ups, 2 = Handling, 3 = Transport A, 4=

Transport B, 5 = Delivery, = 5, for our example

∈ { , , } Supplier for specific route and supply chain step 1, 2, 3 = for our

example.

∈ { , , } Discount situation (supplier x gets PickUps+Handling for country y,

supplier z gets transport for all routes, etc.), = 12 for our example.

R, L, S, I ∈ ℕ


37

5.1.2.2. Declarations

Drls { ∈ ∈ ∈

Vi { ∈

There exist 45 decision variables regarding to the assignment problem (Drls) and 12 decision

variables belonging to the volume discount option (Vi) in this example.

5.2. Objective Function

The objective function will minimize the global cost for the delivery of all supply chain steps to all

items of the three routes. It will take into account the separate processing costs of each item in

each stage and also the volume discounts from the suppliers.

Here, the general objective function will be indicated, not the one subjected to the specific

example, since the problem can be amplified or modified easily.

5.2.1. Costs

MIN (z) =

∑ {∑ ∑ ∑ [ ]

}

Hypothetical costs of respective Supplier 1

∑ {∑ ∑ ∑ [ ]

}


∑ {∑ ∑ ∑ [ ]

}


Equation 2. Minimization function, costs part


38

5.2.2. Volume Discounts

∑ { }

Equation 3. Minimization function, volume discount

5.3. Constraints

There are basically four kinds of constraints in this model: the binary constraints which oblige the

decision variables to take the values of 0 or 1, the constraint that assures that we will have one

and only one supplier for a determined service and finally, we have the constraints that apply to

the volume discounts and the maximum duration.

Summing up all constraints for the specific model, a total of 141 constraints are registered.

5.3.1. Binary Constraints

Drls ∈ , ∀ , , ∈ , ,

Vi ∈ , ∀ ∈

For the specific example, 57 constraints can be counted. In general, this number will always match

up with the number of suppliers involved in the problem plus the number of possible volume

discounts. In a real case there can be up to 1500 binary constrains considering the number of

European and non-European suppliers (see equation 2) and assuming about 300 volume discount

options.

5.3.2. Constraints Relative to Choice of Suppliers

Drls + Drls + Drls = 1 (general equation with r, l = and s )

For our example:


39

D111 + D112 + D113 = 1 D211 + D212 + D213 = 1 D311 + D312 + D313 = 1

D121 + D122 + D123 = 1 D221 + D222 + D223 = 1 D321 + D322 + D323 = 1

D131 + D132 + D133 = 1 D231 + D232 + D233 = 1 D331 + D332 + D333 = 1

D141 + D142 + D143 = 1 D241 + D242 + D243 = 1 D341 + D342 + D343 = 1

D151 + D152 + D153 = 1 D251 + D252 + D253 = 1 D351 + D352 + D353 = 1

Equation 4. Constraint one and only one supplier per service

These are 15 constraints for this problem, being for the real problem number of routes times

number of logistic steps = 183* 5 = 915 constraints.

5.3.3. Constraints Relative to Volume Discounts

There are two kinds of volume discounts in the example of this work.

The first volume discount applies if one local supplier for delivery and handling services obtains

both services.

≤ ≤

≤ ≤

≤ ≤

Equation 5. Set of constraints for volume discounts of supplier 1 for delivering both pick-ups and handling on routes 1,2 and 3

≤ ≤

≤ ≤

≤ ≤

Equation 6. Set of constraints for volume discounts of supplier 2 for delivering both pick-ups and handling on routes 1, 2 and 3


40

≤ ≤

≤ ≤

≤ ≤

Equation 7. Set of constraints for volume discounts of supplier 3 for delivering both pick-ups and handling on routes 1,2 and 3

The second volume discount applies if any global supplier for priority air transport obtains all

routes.

≤ ≤

≤ ≤

≤ ≤

Equation 8. Set of constraints for determining the application of volume discount in case an air transport company obtains all three routes.

In total, there are 24 volume discount constraints for this example.

5.3.4. Time Constraints

The duration of an activity cannot exceed the maximum time requirement of the customers. Both

durations are fixed input data.

≤

In total, 45 constraints are registered, taking into account that we have three routes, five different

logistic steps and 3 suppliers for each logistic step. For a realistic problem this number would

amount to approximately 1500.

41

6. Implementation

6.1. Database

The mathematical model from the previous chapter needs to be integrated in a database system

to be able to operate.

For our example the solver application of MS Office Excel® was chosen, which is automatically

linked to an Excel book where the database will be located.

The solver will be linked to an Excel worksheet that contains all the information it has to access

directly. To visualize the results and to facilitate the entrance of input data and configure a

dynamic interface, auxiliary worksheets will be created and interconnected between each other

and to the basic solver worksheet.

Two different versions of the application were designed during the project. The first version

embraces the optimizing tool for one route and four different suppliers and includes four

different categories of transport. For the second version various routes were required. To limit

the number of variables three routes were chosen, also only three suppliers per service and route

and two transport categories.

6.1.1. Structure

The workbook will consist of five worksheets that represent the main working frame. Apart from

the “solver” sheet where all input data and variables, the objective function and the constraints

are located, we will have four worksheets with organized information according to the logistic

steps:

- “Pick-up”

- “Handling”

- “Transport”

- “Delivery”

Chapter 6. Implementation

42

In “Pick Up” and “Transport”, the results are going to be grouped, whereas “Handling” and

“Delivery” are going to report all details.

Each of these worksheets is designed to give specific information for both characteristics of the

service (route, product, service level, format, weight steps, number of items, arrival, departure)

and the results given by the solver after optimizing costs (supplier and total estimated cost).

Furthermore, it groups the detailed costs per format as on bills and can compare the estimated

costs with the actual postal rate to determine the efficiency of the delivery.

There will be a “result” sheet that summarizes the economic values of each route and logistic step.

The “Answer report” gives useful and organized information about the outcomes of the

mathematical model.

Finally, there is an “Auxiliary” sheet for e.g. the creation of drop downs and the storage of solver

data.

6.1.2. Pick up Cost Worksheet

This database consists of 14 columns and 12 rows for this example. There will be two

predetermined columns: route and Format which are filled in beforehand. Both are built upon

dropdown fields from which the routes (GB-USA, CR-CHI, USA-BEL) and the formats (Petit, Grand,

Encombrant, and Parcel) can be chosen.

The columns Number of items, Kgs, Supplier, Price in original currency, Total estimated costs in €

and the difference are populated directly from the data and results of the “solver” sheet or

populated via formulas.

Equation 9. Equations from “Pick up Cost” worksheet

The column for Exchange Rate has to be changed according to the Dollar-Euro exchange rates.

The Actual Postal rate, Arrival and Departure are data that have to be filled in manually, whereas

the pallets and collection columns are optional to be filled in.


43

Table 11. Pick Up worksheet

6.1.3. Handling Cost Worksheet

As mentioned above, in this part, a detailed report about the costs of the items of each weight

step and their respective suppliers will be generated. Additionally, the number of items will be

able to be updated for the delivery on the next day.

On this worksheet there will be three different parts corresponding to the routes. Each route

encompasses respectively 17 columns according to the company requirements: Route, Product,

Service Level, Format, Weight steps, Nb of items, Warehouse/fulfillment cost, Additional

execution step, Supplier, Total estimated cost, Exchange Rate, Estimated cost in €, Actual postal

rate, Difference, Sum PGEG, Arrival and Departure.

The Route, Format and Weight steps are cells that might be changed on long or middle term but

they are not supposed to be changed on short term, whereby the cells are based on drop down

lists and only predetermined options from the “Auxiliary” sheet might be chosen.

There are four columns that have to be filled in manually: Nb of items, Actual postal rate, Arrival

and Departure.

The columns that are updated automatically are Supplier, Total estimated cost, Estimated cost in

€, Difference and Sum PGEP, whereby the supplier, the estimated cost in €, the difference and the

sum PGEP are populated directly either via link with the “solver” sheet or mathematical formulas

as explained below.

∑

Equation 10. Equations from “Handling” worksheet

Route Format Items Kgs Pallets Collection Supplier Price in original currency ExchangeRate Total Estimated Costs in € Actual Cost (€) Difference Arrival Departure

GB-USA Petit 133 102.315 SUPPHGB1 647.3498437 0.769 497.8120298 -497.8120298

GB-USA Grand 127 122.535 SUPPHGB1 203.8020437 0.769 156.7237716 -156.7237716

GB-USA Encombrant 277 99.34 SUPPHGB1 81.58533729 0.769 62.73912438 -62.73912438

GB-USA Parcel 150 127.835 SUPPHGB1 93.5493896 0.769 71.93948061 -71.93948061

CR-CHI Petit 262 195.405 SUPPHCR3 2009.926888 0.769 1545.633777 -1545.633777

CR-CHI Grand 281 216.52 SUPPHCR3 3478.653366 0.769 2675.084438 -2675.084438

CR-CHI Encombrant 451 176.975 SUPPHCR3 4260.531544 0.769 3276.348757 -3276.348757

CR-CHI Parcel 222 169.715 SUPPHCR3 1068.390779 0.769 821.5925088 -821.5925088

USA-BEL Petit 150 125.26 SUPPHUS1 627.308442 0.769 482.4001919 -482.4001919

USA-BEL Grand 115 101.005 SUPPHUS1 801.7963636 0.769 616.5814036 -616.5814036

USA-BEL Encombrant 284 127.755 SUPPHUS1 1606.294373 0.769 1235.240373 -1235.240373

USA-BEL Parcel 141 116.81 SUPPHUS1 2126.454153 0.769 1635.243243 -1635.243243

Pick Up Cost


44

To obtain the total estimated cost, however, it is necessary to run a macro. This is possible by

pushing the button “update cost”. Three of these buttons are foreseen on the worksheet, one for

each route.

There are also three “solver” buttons that activate the solver application in order to obtain a new

solution. All solver buttons have the same effect; to push one of them is enough to update the

supplier fields. However, the “update costs” buttons are linked respectively to the estimated cost

in € column of one route, consequently, all buttons have to be activated to update the worksheet.

Under normal circumstances, the buttons are used after updating the Nb of items column, with

the particularity that after changing each column, the results have to be translated to other

worksheets (“delivery” and “solver”). Therefore, there is an “Update Nb of items” button foreseen.

As in the other worksheets, the exchange rate has to be changed frequently according to the

Dollar-Euro courses.

The Product, Service level, Warehouse/fulfillment cost and Additional execution step are columns

that can be filled in with pertinent information, whereby Product and Service level can only take

predetermined values from a drop-down list.

The part of the table for one route looks as in the table 12 below:


45

Table 12. Part of “Handling” worksheet

6.1.4. Transport Cost Worksheet

Based on the company requirement, the intrinsic transport costs were separated from the

customs and divided the sheet in two tables. The costs in this case are functionally collapsed for

the formats, so that the table is not as extensive as in the “handling” sheet.

On the table for transport cost there are 21 columns: Route, Traffic days, Arrival, Departure, Days

ongoing, Service, Category, Product tracking, ITL, Items, Kg (PEGP), Air cargo €/kg, Fuel charges,

Ground handling, Volume weight requirements, Supplier, Estimated cost in original currency,

Exchange rate, Estimated Cost in €, Actual postal rate and Difference.

Here, the route, the service level and the format are predetermined values that can be changed

on middle or long run. Service levels are especially important here, since they determine the

transport via air priority or alternative means.

Route Product Service Level Format Weight Steps Nb of items Warehouse/fullfillment cost Additional execution step Supplier Total Estimated Cost Exchange Rate Estimated Cost in € Actual Cost Difference Sum PGEP Arrival Departure

GB-USA U:Letter Mail Priority Petit 0 - 10 8 SUPPHGB2 0.01244061 0.762 0.009479745 -0.0094797 502.7868

GB-USA Priority Petit 11 - 20 3 SUPPHGB2 0.015395255 0.762 0.011731184 -0.0117312 502.7868

GB-USA Priority Petit 21 - 30 6 SUPPHGB2 0.056449268 0.762 0.043014343 -0.0430143 502.7868

GB-USA Petit 30 - 40 6 SUPPHGB2 0.086931873 0.762 0.066242088 -0.0662421 502.7868






GB-USA Petit 90 - 100 0 SUPPHGB2 0 0.762 0 0 502.7868

GB-USA Petit 100-150 0 SUPPHGB2 0 0.762 0 0 502.7868

GB-USA Petit 150-200 5 SUPPHGB2 0.776442463 0.762 0.591649157 -0.5916492 502.7868










GB-USA Petit 800 -900 3 SUPPHGB2 5.86905606 0.762 4.472220718 -4.4722207 502.7868














GB-USA Grand 0 - 10 4 SUPPHGB2 0.009330458 0.762 0.007109809 -0.0071098 901.45805





GB-USA Grand 50-60 6 SUPPHGB2 0.247942122 0.762 0.188931897 -0.1889319 901.45805








GB-USA Grand 250-300 0 SUPPHGB2 0 0.762 0 0 901.45805






















GB-USA T: Empty Receptables Encombrant 0 - 10 6 SUPPHGB2 0.02099353 0.762 0.01599707 -0.0159971 952.88775





GB-USA T: Empty Receptables Encombrant 50-60 3 SUPPHGB2 0.185956592 0.762 0.141698923 -0.1416989 952.88775

GB-USA T: Empty Receptables Encombrant 60-70 4 SUPPHGB2 0.322324759 0.762 0.245611466 -0.2456115 952.88775

GB-USA C: Parcels Encombrant 70-80 5 SUPPHGB2 0.511380627 0.762 0.389672037 -0.389672 952.88775


GB-USA C: Parcels Encombrant 90-100 0 SUPPHGB2 0 0.762 0 0 952.88775


























GB-USA C: Parcels Parcel 0 - 10 7 SUPPHGB2 0.036738677 0.762 0.027994872 -0.0279949 2219.8478





GB-USA C: Parcels Parcel 50-60 3 SUPPHGB2 0.278934887 0.762 0.212548384 -0.2125484 2219.8478




















GB-USA C: Parcels Parcel 1200-1300 0 SUPPHGB2 0 0.762 0 0 2219.8478

GB-USA C: Parcels Parcel 1300-1400 0 SUPPHGB2 0 0.762 0 0 2219.8478









Great Britain - USA

Update Nb of items Update CostsSolver


46

Product tracking and ITL can be filled in case it applies. Traffic days, Arrival, Departure, Days

ongoing, Fuel-charges, Ground handling, Volume weight requirements and Actual postal rate are

data to be filled in manually. The exchange rate again is subjected to changes on the market.

The rest of columns are related to the “solver” sheet or with an equation to other cells:

- The number of items of each format, service level and route are taken from the “solver”

sheet.

- The Kg (PEGP) column collects the weight of each delivery per service level and route,

whereby all formats are grouped together.

- The air cargo of the chosen supplier is stated for the air priority service. Since the air cargo

is already stated per kilogram of weight, the format, in principal, is not important.

- The suppliers and the estimated cost in original currency are directly transcript from the

“solver” sheet.

- The estimated cost in € is calculated with the formula below:

Equation 11. Estimated transport costs

In the following screen shots (table 13 and 14), the “transport cost” table and the “customs” table

can be viewed:

Table 13. Transport cost table from “Transport” worksheet

Route Traffic Days Arrival Departure Days ongoing Service Category Product tracking ITL Items Kg(PEGP) Aircargo €/kg Fuelcharges Ground Handling Volume weight requirements Supplier Estimated cost in orginal currency Exchange rate Estimates Cost in € Actual Cost Difference

GB-USA Priority Petit 57 199.925 5.68385535 SUPT1 870.1625496 0.762 663.0638628 -663.06386

GB-USA Priority Grand 53 5.68385535 SUPT1 1444.94424 0.762 1101.047511 -1101.0475

GB-USA Priority Encombrant 122 5.68385535 SUPT1 1383.887711 0.762 1054.522436 -1054.5224

GB-USA Priority Parcel 68 5.68385535 SUPT1 3469.106065 0.762 2643.458821 -2643.4588

GB-USA Economy Petit 76 252.1 0 SUPAT3 195.6354692 0.762 149.0742275 -149.07423

GB-USA Economy Grand 74 0 SUPAT3 344.4126714 0.762 262.4424556 -262.44246

GB-USA Economy Encombrant 155 0 SUPAT3 378.2915572 0.762 288.2581666 -288.25817

GB-USA Economy Parcel 82 0 SUPAT3 773.6528249 0.762 589.5234526 -589.52345

CR-CHI Priority Petit 96 260.97 5.068226785 SUPT1 655.3506904 0.762 499.3772261 -499.37723

CR-CHI Priority Grand 100 SUPT1 912.8933521 0.762 695.6247343 -695.62473

CR-CHI Priority Encombrant 154 SUPT1 900.6583448 0.762 686.3016587 -686.30166

CR-CHI Priority Parcel 76 SUPT1 1154.570903 0.762 879.7830281 -879.78303

CR-CHI Economy Petit 166 497.645 0 SUPAT1 631.8261672 0.762 481.4515394 -481.45154

CR-CHI Economy Grand 181 0 SUPAT1 1076.685792 0.762 820.4345738 -820.43457

CR-CHI Economy Encombrant 297 0 SUPAT1 1349.901699 0.762 1028.625094 -1028.6251

CR-CHI Economy Parcel 146 0 SUPAT1 1968.618372 0.762 1500.0872 -1500.0872

USA-BEL Priority Petit 45 144.61 3.295805926 SUPT1 754.2801335 0.762 574.7614617 -574.76146

USA-BEL Priority Grand 32 SUPT1 789.2350263 0.762 601.3970901 -601.39709

USA-BEL Priority Encombrant 86 SUPT1 1663.565414 0.762 1267.636846 -1267.6368

USA-BEL Priority Parcel 40 SUPT1 2034.46175 0.762 1550.259854 -1550.2599

USA-BEL Economy Petit 105 326.22 0 SUPAT2 488.9550291 0.762 372.5837321 -372.58373

USA-BEL Economy Grand 83 0 SUPAT2 647.9977497 0.762 493.7742853 -493.77429

USA-BEL Economy Encombrant 198 0 SUPAT2 1227.55349 0.762 935.3957596 -935.39576

USA-BEL Economy Parcel 101 0 SUPAT2 1648.139695 0.762 1255.882448 -1255.8824

Transport Cost


47

Table 14. Customs table from “Transport” worksheet

On the table for customs, the cost/kg, the weight of the whole delivery in kg (all formats together),

the exchange rate, the final cost and the time delay due to the customs are displayed.

Both Cost and Exchange rate depend on external circumstances and have to be changed

according to that.

The weights in kg of all PEGP formats are extracted directly from the “solver” sheet.

The cost in € is calculated applying the exchange rate to the original cost and multiplied by the

weight:

k

Equation 12. Customs

6.1.5. Delivery Cost Worksheet

Similar to the “transport” sheet, the “delivery” sheet has one table for each route. Each table

encompasses 14 columns: Route, Format, Volume, Weight steps, Nb of items, Supplier, Total

estimated cost in €, Actual postal rate, Difference, Sum PGEP, Arrival and Departure.

The costs in this worksheet are also declared per weight steps. Thus, it will be an extensive

worksheet with macros.

Cost/kg kgs(PGEP) Exchange Rate Cost (€) Delays

70 199.925 0.762 152.343

70 0 0.762 0

70 0 0.762 0

70 0 0.762 0

70 252.1 0.762 192.1

70 0 0.762 0

70 0 0.762 0

70 0 0.762 0

70 260.97 0.762 198.859

70 0 0.762 0

70 0 0.762 0

70 0 0.762 0

70 497.645 0.762 379.205

70 0 0.762 0

70 0 0.762 0

70 0 0.762 0

70 144.61 0.762 110.193

70 0 0.762 0

70 0 0.762 0

70 0 0.762 0

70 326.22 0.762 248.58

70 0 0.762 0

70 0 0.762 0

70 0 0.762 0

Customs


48

The cells to be manipulated at middle or long term are those included in the columns route,

format and weight steps. Their respective values are predetermined and eligible from a drop

down list. The volume (high or low from drop down) and the exchange rate are cells that need to

be updated in given circumstances.

The numbers of items, actual postal rate, arrival and departure columns are to be filled in

manually, whereby after filling in the new number of items for the next day, the button “Update

Nb of items” have to be pushed in order to populate the field in the “transport” sheet and the

“solver” sheet with the new data.

Consecutively the “solver” buttons should be activated (as in the case of “transport” sheet, these

buttons have to be activated only once, after changing and updating all numbers of items) in

order to obtain the new solution suggested by the mathematical model.

Then, the supplier fields are going to be updated automatically because of their link to the “solver”

sheet. However, the Total estimated cost has to be updated via a macro by pushing the button of

update cost of each of the routes.

The estimated cost in €, the difference and the sum PGEG are calculated according to the

following formulas:

Equation 13. Estimated costs in “delivery” worksheet

Equation 14. Difference in “delivery” worksheet

∑

Equation 15. Sum of cost per format PGEP in “Delivery” worksheet, grouping together the weight steps

Part of one of the tables of the “Delivery” worksheet would look as follows:


49

Table 15. Part of a table from “Delivery” worksheet

6.1.6. Solver Worksheet

As already mentioned above, the “solver” sheet is the main work environment of the application.

The reason for that is that the solver application of Excel can only access data on one single

worksheet. Hence, all important information must be stored in one single worksheet. According

to the same principle the results of the optimization, i.e. the values of the decision variables are

also displayed on the same worksheet.

On this account, the “solver” sheet contains a big amount of information, which has to be

organized.

The application is designed in this way to modify the “solver” sheet as little as possible in the daily

use, in order not to change important values by chance. Nevertheless, regarding to middle and

long term decisions and changes, the “solver” sheet has to be modified accordingly.

However, the final version of the application includes a very flexible “solver” sheet. So, for

example the number of suppliers for any route and any logistic can be modified without necessity

to change the structure of the worksheet, though all modifications have to be reflected in the

solver application for the smooth operation of the program.

The worksheet with hidden non-value adding cells for the current analysis would look as in figure

6.1 and 6.2:

Route Format Volume Weight Steps Nb of items Supplier Total Estimated Cost Exchange Rate Estimated Cost in € Actual Cost Difference Sum PGEP Arrival Departure

GB-USA Petit High 0 - 10 8 SUPDUS1 0.024983734 0.769 0.019212491 -0.0192125 776.47213









GB-USA Petit High 90 - 100 0 SUPDUS1 0 0.769 0 0 776.47213

GB-USA Petit High 100-150 0 SUPDUS1 0 0.769 0 0 776.47213

GB-USA Petit High 150-200 5 SUPDUS1 1.559282996 0.769 1.199088624 -1.1990886 776.47213







GB-USA Petit High 500 - 600 0 SUPDUS1 0 0.769 0 0 776.47213



GB-USA Petit High 800 -900 3 SUPDUS1 11.78647452 0.769 9.063798909 -9.0637989 776.47213

Great Britain - USA

Update Nb of items Update CostsSolver


50

Figure 6.1. Left part of the “solver” worksheet with hidden rows and columns

Figure 6.2. Right part of the “solver” sheet with hidden rows and columns

Optimal Cost

99924.54195

0 - 10 2500-3000 Dec Var Price Price(P) Price(Parcel) Supplier 0 - 10 2500-3000 Dec Var Price Price(P) Price(Parcel) Supplier

SupplierPHGB1 ($/item*kg) 0.134792595 1.079844339 DecisionVarS1 1 1026.286614 647.3498437 93.5493896 SUPPHGB1 SupplierPHCR1 ($/item*kg) 0.635252126 10.8366626 DecisionVarS1 0 0 0 0 0

SupplierPHGB2 ($/item*kg) 0.954691868 7.648184295 DecisionVarS2 0 0 0 0 0 SupplierPHCR2 ($/item*kg) 1.00E+07 1.00E+07 DecisionVarS2 0 0 0 0 0

SupplierPHGB3 ($/item*kg) 0.33184141 2.658432888 DecisionVarS3 0 0 0 0 0 SupplierPHCR3 ($/item*kg) 0.74610542 12.7276909 DecisionVarS3 1 10817.50258 2009.926888 1068.390779 SUPPHCR3

Avg kgs 0.01 2.75 452.025 Restriction = 1 1026.286614 647.3498437 93.5493896 SUPPHGB1 Avg kgs 0.01 2.75 758.615 Restriction = 1 10817.50258 2009.926888 1068.390779 SUPPHCR3

Number of items 8 9 537 Price(P) Price(G) Price(Enc) Price(Parcel) Number of items 12 10 994 Price(P) Price(G) Price(Enc) Price(Parcel)

Supplier1 (€) 0.005391704 26.72614739 1026.286614 647.3498437 203.80204 81.58533729 93.5493896 Supplier1 (€) 0.038115128 298.0082215 9121.373967 1711.300165 2961.809265 3627.519179 820.7453579

Supplier2 (€) 0.038187675 189.2925613 2795.870508 967.2152542 605.16037 562.555099 660.9397876 Supplier2 (€) 600000 275000000 7586150000 1954050000 2165200000 1769750000 1697150000


Number of items/format 133 Number of items/format 262


SupplierPHGB1 ($/item*kg) 0.370613514 31.95555202 DecisionVarS1 0 0 0 0 0 SupplierPHCR1 ($/item*kg) 0.370613514 6.322235609 DecisionVarS1 0 0 0 0 0

SupplierPHGB2 ($/item*kg) 0.311015253 26.81678818 DecisionVarS2 1 4576.980358 502.7868019 2219.847751 SUPPHGB2 SupplierPHCR2 ($/item*kg) 1.00E+07 1.00E+07 DecisionVarS2 0 0 0 0 0

SupplierPHGB3 ($/item*kg) 0.211979815 18.27761741 DecisionVarS3 0 0 0 0 0 SupplierPHCR3 ($/item*kg) 0.211979815 3.616129165 DecisionVarS3 1 3225.819424 571.0505775 455.9495569 SUPPHCR3

Avg kgs 0.01 2.75 452.025 Restriction = 1 4576.980358 502.7868019 2219.847751 SUPPHGB2 Avg kgs 0.01 2.75 758.615 Restriction = 1 3225.819424 571.0505775 455.9495569 SUPPHCR3



Supplier2 (€) 0.01244061 663.7155074 4576.980358 502.7868019 901.45805 952.8877524 2219.847751 Supplier2 (€) 600000 2.75E+08 7586150000 1954050000 2165200000 1769750000 1697150000



Cost of Selected Supplier 0.01244061 663.7155074 Cost of Selected Supplier 0.012718789 99.44355203

Surcharge Airlines Surcharge Airlines

per kilo, sum per kilo, sum


SupplierT1 ($/item*kg) 0.895663413 77.22713197 5.68385535 DecisionVarS1 1 7168.100566 870.1625496 3469.106065 SUPT1 SupplierT1 ($/item*kg) 0.294298911 25.37544856 5.068226785 DecisionVarS1 1 3623.47329 655.3506904 1154.570903 SUPT1

SupplierT2 ($/item*kg) 0.306420628 26.42062398 7.149389307 DecisionVarS2 0 0 0 0 0 SupplierT2 ($/item*kg) 1.00E+07 1.00E+07 0 DecisionVarS2 0 0 0 0 0

SupplierT3 ($/item*kg) 0.245041514 21.12830901 6.992369965 DecisionVarS3 0 0 0 0 0 SupplierT3 ($/item*kg) 0.964974498 83.20336843 7.64324444 DecisionVarS3 0 0 0 0 0

Avg kgs 0.01 2.75 199.925 Restriction = 1 7168.100566 870.1625496 3469.106065 SUPT1 Avg kgs 0.01 2.75 260.97 Restriction = 1 3623.47329 655.3506904 1154.570903 SUPT1

Number of items 4 5 0.432696413 Price(P) Price(G) Price(Enc) Price(Parcel) Number of items 5 4 0.580404639 Price(P) Price(G) Price(Enc) Price(Parcel)


Supplier2 (€) 0.149116199 461.5876827 3492.900765 525.5648187 772.84995 691.5071793 1502.978821 Supplier2 (€) 250000.1787 110000078.6 2609701866 679350485.7 761750544.6 595950426.1 572650409.4


Number of items/format 57 232 Number of items/format 96 350


SupplierAT1 ($/item*kg) 0.370613514 31.95555202 DecisionVarS1 0 0 0 0 0 SupplierAT1 ($/item*kg) 0.355145742 30.62186834 DecisionVarS1 1 5027.032031 631.8261672 1968.618372 SUPAT1

SupplierAT2 ($/item*kg) 0.311015253 26.81678818 DecisionVarS2 0 0 0 0 0 SupplierAT2 ($/item*kg) 1.00E+07 1.00E+07 DecisionVarS2 0 0 0 0 0

SupplierAT3 ($/item*kg) 0.211979815 18.27761741 DecisionVarS3 1 1691.992523 195.6354692 773.6528249 SUPAT3 SupplierAT3 ($/item*kg) 0.826337783 71.24964148 DecisionVarS3 0 0 0 0 0

Avg kgs 0.01 2.75 252.1 Restriction = 1 1691.992523 195.6354692 773.6528249 SUPAT3 Avg kgs 0.01 2.75 497.645 Restriction = 1 5027.032031 631.8261672 1968.618372 SUPAT1





Number of items/format 76 OK Number of items/format 166 OK


SupplierDUS1 ($/item*kg) 0.624593346 53.85455301 DecisionVarS1 1 9191.676114 1009.716686 4457.98757 SUPDUS1 SupplierDCH1 ($/item*kg) 0.747184968 64.42481771 DecisionVarS1 0 0 0 0 0

SupplierDUS2 ($/item*kg) 0.794497512 68.5042655 DecisionVarS2 0 0 0 0 0 SupplierDCH2 ($/item*kg) 1.00E+07 1.00E+07 DecisionVarS2 0 0 0 0 0

SupplierDUS3 ($/item*kg) 0.478904276 41.29274814 DecisionVarS3 0 0 0 0 0 SupplierDCH3 ($/item*kg) 0.664511026 57.29639054 DecisionVarS3 1 14238.44366 1790.120461 5555.498721 SUPDCH3

Avg kgs 0.01 2.75 452.025 Restriction = 1 9191.676114 1009.716686 4457.98757 SUPDUS1 Avg kgs 0.01 2.75 758.615 Restriction = 1 14238.44366 1790.120461 5555.498721 SUPDCH3






Total kgs 102.315 195.405

Cost of Selected Supplier 0.024983734 1332.900187 Cost of Selected Supplier 0.039870662 1575.65074

DELIVERY (gr)

P (petit) (gr) Parcels (gr)

Alternative Transport Option


Cost Priority Air (A)


Parcels (gr)

Handling Cost


Parcels (gr)

Parcels (gr)

Parcels (gr)

Great Britain - USA

PICK UP

Handling Cost



DELIVERY (gr)

Czech Republic - China

PICK UP

P (petit) (gr)

Parcels (gr)P (petit) (gr)

P (petit) (gr)

Parcels (gr)P (petit) (gr)

P (petit) (gr)

P (petit) (gr)

SOLVE

Extra Conditions (Scenarios) Discount Binary Variable Effective AuxMin AuxMax

If supplier 1 gets PickUp+HandlingGB, $400 discount 400 0 0 0 0.5

If supplier 1 gets PickUp+HandlingCR, $ 370 discount 370 0 0 -1 0

0 - 10 2500-3000 Dec Var Price Price(P) Price(Parcel) Supplier If supplier 1 gets PickUp+HandlingUSA, $800 discount 800 1 800 1 1 t(pickup) effective t(handling)effective t(transpA)effective t(transpB) effective delivery effective

SupplierPHUS1 ($/item*kg) 0.343592591 29.62571655 DecisionVarS1 1 5161.853 627.3084 2126.454153 SUPPHUS1 If supplier 2 gets PickUp+HandlingGB, $500 discount 500 0 0 0 0.5 Supplier1 5 5 1 0 1 1 2 0 2 2

SupplierPHUS2 ($/item*kg) 0.732848304 63.18866203 DecisionVarS2 0 0 0 0 0 If supplier 2gets PickUp+HandlingCR, $270 discount 270 0 0 -1 0 Supplier2 6 0 2 2 1 0 3 0 1 0

SupplierPHUS3 ($/item*kg) 0.827978483 71.39110814 DecisionVarS3 0 0 0 0 0 If supplier 2 gets PickUp+HandlingUSA, $450 discount 450 0 0 -1 0 Supplier3 5 0 2.5 0 1 0 2.5 2.5 3 0

Avg kgs 0.01 2.75 470.83 Restriction = 1 5161.853 627.3084 2126.454153 SUPPHUS1 If supplier 3gets PickUp+HandlingGB, $360 discount 360 0 0 -1 0 Customer req 5 2 1 3.5 2

Number of items 1 5 549 Price(P) Price(G) Price(Enc)Price(Parcel) If supplier 3 gets PickUp+HandlingCR, $950 discount 950 1 950 1 1

Supplier1 (€) 0.001717963 407.3536025 5161.853331 627.308442 801.7964 1606.294 2126.454 If supplier 3 gets PickUp+HandlingUSA, $150 discount 150 0 0 -1 0

Supplier2 (€) 0.003664242 868.8441029 11009.71195 1337.985566 1710.151 3426.064 4535.512

Supplier3 (€) 0.004139892 981.6277369 12438.86975 1511.667903 1932.143 3870.797 5124.261

Number of items/format 150

0 - 10 2500-3000 Dec Var Price Price(P) Price(Parcel) Supplier t(pickup) effective t(handling)effective t(transpA)effective t(transpB) effective delivery effective

SupplierPHUS1 ($/item*kg) 0.321018806 5.476207553 DecisionVarS1 1 3332.461 586.0947 496.4858063 SUPPHUS1 Supplier1 3 0 1 0 2 2 4 4 4 0

SupplierPHUS2 ($/item*kg) 0.457993188 7.81283124 DecisionVarS2 0 0 0 0 0 Supplier2 2 0 2 0 2 0 5 0 5.5 0

SupplierPHUS3 ($/item*kg) 0.921020659 15.71154148 DecisionVarS3 0 0 0 0 0 Supplier3 2.5 2.5 1.5 1.5 2 0 8 0 4 4

Avg kgs 0.01 2.75 470.83 Restriction = 1 3332.461 586.0947 496.4858063 SUPPHUS1 Customer req 2.5 1.5 2 6 5

Number of items 1 5 549 Price(P) Price(G) Price(Enc)Price(Parcel)

Supplier1 (€) 0.001605094 75.29785385 3332.461449 586.09473 749.1189 1500.762 496.4858

Supplier2 (€) 0.002289966 107.4264295 4754.377671 836.1734234 1068.758 2141.117 708.3296

Supplier3 (€) 0.004605103 107.4264295 8844.915735 1681.538104 2149.263 4305.769 708.3452


Cost of Selected Supplier 0.001605094 75.29785385

Surcharge Airlines

per kilo, sum

0 - 10 2500-3000 Dec Var Price Price(P) Price(Parcel) Supplier t(pickup) effective t(handling)effective t(transpA)effective t(transpB) effective delivery effective

SupplierT1 ($/item*kg) 0.927967925 80.01253636 3.295805926 DecisionVarS1 1 5241.542 754.2801 2034.46175 SUPT1 If SupplierT1 gets all 3 transport routes, $6000 discount 6000 1 6000 1 1 Supplier1 5 5 1 1 1 1 2 0 4 0

SupplierT2 ($/item*kg) 0.823754153 71.02687213 7.917793614 DecisionVarS2 0 0 0 0 0 If SupplierT2 gets all 3 transport routes, $5000 discount 5000 0 0 -2 0 Supplier2 6 0 2 0 1 0 2.5 2.5 3 3

SupplierT3 ($/item*kg) 0.915686539 78.95359369 5.110031103 DecisionVarS3 0 0 0 0 0 If SuppliertT3 gets all 3 transport routes, $0 discount 0 0 0 -2 0 Supplier3 5 0 2.5 0 1 0 3 0 4 0

Avg kgs 0.01 2.75 144.61 Restriction = 1 5241.542 754.2801 2034.46175 SUPT1 Customer req 5 2 1 2.5 3

Number of items 0 2 0.639608249 Price(P) Price(G) Price(Enc)Price(Parcel)

Supplier1 (€) 0 471.3301544 5241.542325 754.2801335 789.235 1663.565 2034.462

Supplier2 (€) 0 429.9694379 4957.137947 751.1067303 760.3297 1564.882 1880.819

Supplier3 (€) 0 472.7028001 5372.274501 797.9242518 818.0741 1699.52 2056.756

Number of items/format 45 163

0 - 10 2500-3000 Dec Var Price Price(P) Price(Parcel) Supplier

SupplierAT1 ($/item*kg) 0.803977578 69.32166891 DecisionVarS1 0 0 0 0 0

SupplierAT2 ($/item*kg) 0.391076455 33.71993604 DecisionVarS2 1 4012.646 488.955 1648.139695 SUPAT2

SupplierAT3 ($/item*kg) 0.962617806 83.00016644 DecisionVarS3 0 0 0 0 0

Avg kgs 0.01 2.75 326.22 Restriction = 1 4012.646 488.955 1648.139695 SUPAT2


Supplier1 (€) 0.004019888 571.9037685 8249.224276 1005.197003 1332.158 2523.613 3388.257

Supplier2 (€) 0.001955382 278.1894724 4012.645964 488.9550291 647.9977 1227.553 1648.14

Supplier3 (€) 0.004813089 278.1894724 7468.28906 1203.541689 1595.018 3021.57 1648.159

Number of items/format 105 OK

0 - 10 2500-3000 Dec Var Price Price(P) Price(Parcel) Supplier

SupplierDBE1 ($/item*kg) 0.819713275 70.67845392 DecisionVarS1 0 0 0 0 0

SupplierDBE2 ($/item*kg) 0.976448788 84.19272053 DecisionVarS2 1 14669.37 1782.735 6043.126751 SUPDBE2

SupplierDBE3 ($/item*kg) 0.346150923 29.8463046 DecisionVarS3 0 0 0 0 0

Avg kgs 0.01 2.75 470.83 Restriction = 1 14669.37 1782.735 6043.126751 SUPDBE2


Supplier1 (€) 0.004098566 971.8287414 12314.70004 1496.577838 1912.856 3832.157 5073.109

Supplier2 (€) 0.004882244 1157.649907 14669.36586 1782.735086 2278.609 4564.895 6043.127

Supplier3 (€) 0.001730755 410.3866883 5200.287614 631.9792741 807.7664 1618.255 2142.287


125.26

Cost of Selected Supplier 0.004882244 1157.649907

DELIVERY (gr)






Handling Cost


USA - Belgium

PICK UP

P (petit) (gr) Parcels (gr) Great Britain - USA

Czech Republic - China

USA - Belgium

Time Restrictions


51

To analyze the “solver” worksheet, the best option is to divide it into 5 functional units: input data,

results, volume discounts, time constraints and objective function.

6.1.6.1. Functional Unit: Input Data per Route and per Logistic Stage

Functional units of input data per route and per logistic stage are for example those indicated in

the screenshot below:

Figure 6.3. Functional unit input data per route and per logistic stage in the “solver” worksheet.

We will pick the unit for the route Great Britain – USA for the logistic stage of Pick Up and we look

into it more carefully.

Figure 6.4. Input data of route GB-USA and Pick Up*

*Weight steps between 10gr and 2500gr are hidden for the visibility of the screenshot.

In the first lines, the costs per item and kg for each of the weight steps and formats for the pick-

up service and the route are stated.

0 - 10 2500 - 3000 0 - 10 2500-3000 0 - 10 2500-3000 0 - 10 2500-3000

SupplierPHGB1 ($/item*kg) 0.134792595 13.41689804 0.123745613 4.389610965 0.256084985 1.355158699 0.305225617 1.079844339



Avg kgs 0.01 2.75 0.01 2.75 0.01 2.75 0.01 2.75 452.025

Number of items 8 5 4 7 6 0 7 9 537

Supplier1 ($) 0.005391704 184.4823481 0.002474912 84.50001109 0.00768255 0 0.010682897 26.72614739 1026.286614

Supplier2 ($) 0.038187675 210.1804064 0.017528994 140.8127082 0.004303835 0 0.026519582 189.2925613 2795.870508

Supplier3 ($) 0.013273656 159.8661927 0.006092904 82.49745922 0.007814579 0 0.026299868 65.79621397 1431.482278

Number of items/format 133 127 277 150

E (encombrant) (gr)G (grand) (gr)P (petit) (gr) Parcels (gr)

Great Britain - USA

PICK UP


52

In the next line the average weight of the items (in kg) are displayed. These average weights are

computed according to the following formula:

Equation 16. Average weight

The numbers of items are populated through a macro from either the transport or the delivery

sheet.

In the next three rows, the effective costs in $ for each supplier are displayed by multiplying the

cost per item and per weight by the number of items and the average weight of each item.

Finally, in the last row, we have the number of items to be processed per format (petit, grand,

encombrant and parcel).

The last column displays the sum of items, weight and costs.

The input-data functional units for other routes and logistic steps are from the same structure,

only for the air priority transport there is one added column for the respective airline surcharges.

There is also a random number that doesn’t belong to the database set, but it is used to generate

the separation between the number of items that go through priority air and the number of items

that go through alternative transport.

To limit the number of suppliers without changing the structure of the worksheet, very high

weight step costs Crlsfw can be applied as in the case of the route Czech Rep. – China. The decision

variable for supplier 2 still exists, but will be 0 because of the high associated costs.


53

6.1.6.2. Functional Unit: Results per Route and per Logistic Stage

Functional units of results per route and per logistic stage are for example those indicated in the

screenshot below:

Figure 6.5. Functional unit Results per route and per logistic stage in the “solver” worksheet.

Again, the table from the route Great Britain - USA and the pick-up stage (figure 6.6) is used to

explain the result unit:

Figure 6.6 Results of route GB-USA and pick up step

The first column represents the decision variables for the three suppliers to be chosen or not to

deliver the service. In the fourth row the constraint value 1 that restricts the sum of the binary

decision variables to be 1 can be viewed.

In the last three rows the prices per format are displayed, summing up all weight steps according

to the following equations:

Dec Var Price Price(P) Price(G) Price(Enc) Price(Parcel) Supplier

DecisionVarS1 1 1026.286614 647.3498437 203.8020437 81.58533729 93.5493896 SUPPHGB1

DecisionVarS2 0 0 0 0 0 0 0

DecisionVarS3 0 0 0 0 0 0 0

Restriction = 1 1026.286614 647.3498437 203.8020437 81.58533729 93.5493896 SUPPHGB1

Price(P) Price(G) Price(Enc) Price(Parcel)

647.3498437 203.80204 81.58533729 93.5493896

967.2152542 605.16037 562.555099 660.9397876

723.9333823 278.76366 198.4791026 230.306133


54

∑

∑

∑

∑

Equation 17. Prices by format for one logistic stage and route

After choosing the supplier, the effective prices per format are displayed next to the total

effective price for the service. These numbers are obtained by multiplying the potential prices to

the decision variables:

Equation 18. Effective prices by format for one route and logistic stage

The last column displays the name of the elected supplier or 0.

On the fourth row, the first non-negative value is displayed, so as to stabilize the location of the

values in order to forward them to other worksheets. For the numbers, the rows are summed up

and for the string value we apply the following formula:

, , ,

Equation 19. Formula to copy the first non-zero value.


55

6.1.6.3. Functional Unit: Volume Discounts

Functional units of volume discounts are those indicated in the screenshot below:

Figure 6.7. Functional unit: Volume discounts on the “solver” sheet

In our scenario there are two kinds of volume discounts (table 16 and table 17).

The first one is applied to the local suppliers to award the contracting of both pick up and

handling:

Table 16. Volume discounts for contracting both Pick Ups and Handling service

In the first column, the scenarios for discount are explained. The second column contains the

amount of discount. The third column is populated with the decision variables Vi from the solver

application. The constraints according to the equations from the chapter of Modeling and

Constraints paragraph are located in the last two columns. In the fourth column we see the

effective discount, which is obtained through multiplying the potential discount amount by the

corresponding decision variable.

Extra Conditions (Scenarios) Discount Binary Variable Effective AuxMin AuxMax


If supplier 1 gets PickUp+HandlingCR, $ 370 discount 370 0 0 -1 0

If supplier 1 gets PickUp+HandlingUSA, $800 discount 800 1 800 1 1


If supplier 2gets PickUp+HandlingCR, $270 discount 270 0 0 -1 0

If supplier 2 gets PickUp+HandlingUSA, $450 discount 450 0 0 -1 0

If supplier 3gets PickUp+HandlingGB, $360 discount 360 0 0 -1 0

If supplier 3 gets PickUp+HandlingCR, $950 discount 950 1 950 1 1

If supplier 3 gets PickUp+HandlingUSA, $150 discount 150 0 0 -1 0


56

The second kind of volume discount (table 17) applies to the global transport companies for the

air prior transport. The discount is applied if one supplier is engaged to do all routes for air prior

transport. The structure is the same as in the case above.

Table 17. Volume discounts for contracting one air prior transport supplier for all 3 transport routes

6.1.6.4. Functional Unit: Time Constraints

Functional units of volume discounts are those indicated in the screenshot below:

Figure 6.8. Functional unit: Time constraints on the “solver” sheet

The table 18 for the route: Great Britain – USA is taken for further explanations:

Table 18. Time constraints for route GB – USA

On the first, third, fifth, seventh and ninth column the times needed by the suppliers to execute

respectively the pick-ups, handling, transport A, transport B and delivery are stated. The customer

requirements, the maximum time limit for each service, are filled in the last row. The effective

time, i.e. the time used by the assigned supplier and zeros are calculated multiplying the

If SupplierT1 gets all 3 transport routes, $6000 discount 6000 1 6000 1 1

If SupplierT2 gets all 3 transport routes, $5000 discount 5000 0 0 -2 0

If SuppliertT3 gets all 3 transport routes, $0 discount 0 0 0 -2 0

t(pickup) effective t(handling)effective t(transpA)effective t(transpB) effective delivery effective

Supplier1 5 5 1 0 1 1 2 0 2 2

Supplier2 6 0 2 2 1 0 3 0 1 0

Supplier3 5 0 2.5 0 1 0 2.5 2.5 3 0

Customer req 5 2 1 3.5 2

Great Britain - USA


57

respective time for the weight steps with the decision variable Drls and displayed on the rest of the

cells.

To fulfill the constraints, the time for the service needed by a supplier has to be inferior to the

customer requirement.

By making this table it is important to keep in mind that no solution could be obtained if the time

requirement of a customer is inferior to all the needed supplier-time for a determined service.

Then the problem would be infeasible.

6.1.6.5. Functional Unit: Objective Function Cell

The last functional unit consists actually only of one cell (see table 19). It is the cell where the

objective function is written and which is connected to the solver, so it can be optimized by

determining the decision variables.

Table 19. Objective function cell

The objective function has the following expression:

, , , , , , , , , , , ,

, , , , , , , , , , , , , ,

, , , , , , , , , , , , ,

, , , , , ,

Equation 20. Objective function from “solver” sheet

The button below the objective function cell can be activated in order to run the solver via a

macro.

6.2. Solver Application

The solver is an add-in application from MS Office Excel® Software and can be accessed through

the data tab on the analysis section.

Optimal Cost

99924.54195

SOLVE


58

This application allows us to define an objective function, decision variables and write constraints.

By running the solver, it optimizes the objective function according to the constraints and gives us

the values of the decision variables and the optimal value of the objective function.

6.2.1. Structure

The environment which will link the solver application to the data stored on the “solver”

worksheet looks as in the picture below (figure 6.9):

Figure 6.9. Solver Add in from MS Office Excel®

In this environment, an objective function cell can be chosen, various decision variable cells can

be chosen and there is the possibility to add constraints defining cells on the left and right hand

side.

Furthermore, we can choose our goal to maximize or minimize the objective function or even to

make it converge to a certain value (see figure 6.10). In our case, the minimizing option is chosen.

Figure 6.10. Maximize, minimize the objective function or making it converge to a certain value

For this problem we also have to choose the option to make unconstrained variables non-

negative, because otherwise we could have problems to apply the simplex method (non-

negativity of variables is a condition to use this method).


59

Simplex LP is chosen, because it is a powerful method which can provide global optimality (see

figure 6.11).

Figure 6.11. Unconstrained Variables Non-negative, Simplex LP

By clicking on “Options” a new window with specific options pops out (see figure 6.12). For us,

only the first tab “All Methods” will be important. Here the constraint precision can be chosen.

The default value of 10-6 is more than admissible for our problem.

In poorly scaled models, i.e. models, where the values of the objective function and the constraint

functions differ in various orders of magnitude, automatic scaling can be used. For our problem it

is not yet necessary to check this box, though it would be recommended in regard to the future

when more routes or suppliers are added and the amount of the objective function higher.

When the “Show Iteration Result” box is checked, while running the solver, it stops after every

iteration to show the result. The next iteration is only performed if the user confirms the result.

It is possible to ignore integer constraints and consider almost integer values as integer. We will

not use any of these options for our model, since we are working with strictly integer values.

In the second part of the “Options” window, we can determine the solving limits, to restrict the

running time or iterations of the problem and sub-problems. The time or iterations don’t need to

be restricted because our problem can normally be solved relatively fast.


60

Figure 6.12. Options Window from Excel solver

To configure our problem, the cell where we will write the objective function can be selected as in

figure 6.13. In our case it will be cell A2. It is recommendable to place the objective function cell

to a not too remote place, since the application will always select this cell after running the solver.

Figure 6.13. Objective function cell in Excel solver

To choose the cells that will constitute the decision variables, we can both write the cells into the

field for variable cells or click the button on the right side and select the cells directly in our excel

worksheet (See figure 6.14).

Figure 6.14. Selection of decision variable cells


61

Regarding to the constraints, the Excel solver allows us to add, change and delete constraints one

by one (see figure 6.15). Moreover, we can reset the values, i.e. delete all constraints or load or

save a scenario. The scenario can be saved on a sufficient number of empty cells and loaded

selecting the cells where already some scenario is saved.

Figure 6.15. Constraints in the solver

By clicking the “Add” button, a new window pops out (figure 6.16), with which we can choose

both the variable and the left or right hand side by selecting corresponding cells and we can select

the kind of constraint (≤, , , , , A similar window appears when “Change”

button is activated.

Figure 6.16. Add Constraint

After understanding the structure of our solver tool, our model can be effectively adapted to the

Excel solver.

6.2.2. Adapting Model to Solver

The MS Office Excel® Solver tool offers an intuitive environment. As explained in the paragraph

above, the solver has to be linked to cells that will carry out specific functions.

First of all, we have to define our decision variables. We do this by selecting the empty cells which

we decided to employ for this problem and they will appear automatically in the Variable cells of

the solver tool.


62

Afterwards, we define the binary constraints of all the variable cells adding them as constraints in

the “Add constraint” window.

Next, we can add the functional constraint which admits only one supplier to the other

constraints.

At the same time, the formula for the objective function has to be written, including all elements

mentioned in the paragraph of “Declarations”.

Finally, the constraints for the volume discounts and the time constraints can be written.

Whereby we have to write the formulas for the right or left hand side into the cell and then refer

the cell to the solver via the “Add constraint” window.

It is recommendable to build first a small model (e.g. one route, one logistic step) and try the

model, adding successive variables and constraints to expand the model. This way it is possible to

figure out, where possible problems can come from and correct them.

6.3. Daily Use and Manipulation

The daily use and manipulation of the pricing tool for bpi should be carried out through the

support-sheets, in order not to change unintended basic configurations.

Macros have been configured to offer a user friendly environment. The VBL codes of the most

important macros are attached to the Appendix C.

Only time constraints (Trls), costs (Crlsfw) and basic configurations in the organization have to be

changed on the main page.

In the course of this project two manuals have been crafted: the first manual, version 0.1 was for

a preliminary application for only one route and four different suppliers for each logistic step with

4 different transport options. The second manual, version 1.0 has been adapted to the current

model and scenarios.

Both documents are attached in Appendix A and B.

63

7. Test Results

7.1. Test Phase

During the design of the application, many tests had to be carried out in order to verify the

behavior of the model.

The “solver” worksheet is built up by functional units (see chapter 6. Implementation: paragraph

solver worksheet). After the establishment of each functional unit, the new model has to be

tested. The Input data and Results units are implemented to the solver application at the same

time.

In order to assure the quality of the results, many factors have to be confirmed with their

corresponding test runs after building these units:

- First of all, the end-value of the objective function has to be in the correct order of

magnitude. This order of magnitude can be obtained by summing up all Crlsfw of random suppliers.

The objective function should give a value minor or equal than the sum of the Crlsfw of random

suppliers without applying volume discounts, with the exception of cases where economical

suppliers are not chosen for the optimal solution due to time restriction.

- We have to check that the constraint of only one supplier per service and route is kept.

This can be done via visual inspection.

- We have to verify that the costs and the names of the chosen suppliers are correctly

indicated.

After the implementation of the volume discount and time constraints unit we have to verify:

- The logic of the results, normally the highest discount should be chosen if the variations of

the regular costs are not too high.

- Overlaps: in the transport discount only one of the suppliers can be chosen.

- Suppliers are not obliged to give discounts. A supplier that has lower regular fees should

be preferred to another one with higher fees + discount.

- Effective times are lower than customer time requirement.

Chapter 7. Test Results

64

In the absence of concrete data, the finished model was tested with approximately 10 sets of

input data for both number of items and weight step costs generated with random values.

The number of items were generated with the function randbetween(), with the values (0,7);

(0,13); (0,9) for the respective routes (GB-USA; CR-CHI; USA-BEL).

The generation for the weight step costs follows the pattern of generating a random value

between 0,1 and 1 for the lowest fee and multiplying it per increasing weight step and increasing

format by 1,1; 1,11; 1,12; 1,15 or 1,2 via random selection.

If after 10 sets of different data, all checkpoints can be always fulfilled, the model can be assumed

to be acceptable and the application to work correctly.

7.2. Economic Results

The tables 20 and 21 show the results of 10 test runs with different input data from which the

economic outcomes of the model can be interpreted. It shows the separate costs of each service

and each route, displaying the name of the supplier. If volume discounts are applicable, they are

also displayed. Subtotals per route and per service are also indicated and finally the total amount

of costs.

The final value of the objective function is the amount of the total cost of all suppliers for all

services including volume discounts in US-Dollars and it ranges in the test runs between $78490

and $110967 for our assumptions.

The mean value is $92009.82 and the standard deviation according to the formula below is

$12086.5.

√∑

Equation 21. Standard deviation

Where xi is the final cost of each test run, m the mean value and n the number of test runs, which

in this case is 10.

This means that the results are on average $12086.5 deviated from the mean cost.


65

Table 20. Test run results 1-7

TEST 1 Disc. TranspS1 DiscTranspS2 DisTranspS3 Subtotal/activity

Pick - Up SUPPHCR3 10817.50258 SUPPHUS1 5161.853331 0 0 0 17005.64252

Handling Cost SUPPHCR3 3225.819424 SUPPHUS1 3332.461449 0 0 0 11135.26123

Cost Priority Air (A) SUPT1 3623.47329 SUPT1 5241.542325 6000 0 0 10033.11618

Alternative Transport Option (B) SUPAT1 5027.032031 SUPAT2 4012.645964 10731.67052

Delivery SUPDCH3 14238.44366 SUPDBE2 14669.36586 38099.48564

Discount S1 pick-ups&handling 0 800 800



Subtotal/route 35982.27098 31617.86893 6000 0 0 85255.17609

TEST 2 Discount TransportS1Discount TransportS2Discount TransportS3Subtotal/activity









Subtotal/route 47916.61209 25493.73054 6000 0 0 80018.59425










Subtotal/route 43469.36416 34125.18458 0 0 0 106049.8849










Subtotal/route 39545.81844 31520.28537 6000 0 0 82479.7631










Subtotal/route 46977.23473 28879.21795 6000 0 0 91837.44862










Subtotal/route 45662.85806 28790.55709 6000 0 0 82981.73223










Subtotal/route 64596.80608 31647.26215 6000 0 0 106094.0211

CR-CH USA-BEL

CR-CH USA-BEL

CR-CH USA-BEL

CR-CH USA-BEL

CR-CH USA-BEL

CR-CH USA-BEL

CR-CH USA-BEL


66

Table 21. Test run results 8-10

Since we didn’t work with real data, the point of interest in the economic result analysis lies in the

relative cost savings between the costs to be paid without using the pricing tool and the optimal

cost using the application.

Two different economic studies will be carried out: the first thing to keep in mind is the maximum

costs generated by the services without using the pricing tool. For this study we have to

remember that the differences obtained will constitute the maximum values. Mean difference

values will be obtained through the maximum differences. Since the choice of the least

convenient option is not likely to be made in all cases and the calculations to obtain a realistic

difference in costs parting from the worst case are not accurate enough, a second study on

economic result is going to be carried out. This study will consider the arbitrariness of choosing

suppliers if not relying on the tool and will be based on the difference of the total estimated cost

by choosing suppliers randomly or taking the solution suggested by the pricing tool. The

combination of both studies should prove the advantages of using the pricing tool for the decision

of suppliers.

We will compare the values from each of the 10 tests of both studies in order to obtain a more

reliable mean relative difference.










Subtotal/route 49022.8112 39924.80764 0 0 0 110967.839










Subtotal/route 34762.31857 32052.69208 6000 0 0 78490.68213










Subtotal/route 44320.38297 35362.01986 6000 0 0 95923.15809

CR-CH USA-BEL

CR-CH USA-BEL

CR-CH USA-BEL


67

7.2.1. Economic Results of Study A:

In the study A, maximum total costs are compared with the total costs obtained through the

optimizing tool in 10 test runs.

Table 22. Comparison between the maximum value and optimum total costs, study A

Figure 7.1. Graphic maximum values vs. optimum values, study A

As can be deduced from the table and the graphic, the difference between the maximum values

and the optimal values are high and can lie between 20% and 93% of the optimal value. These are

very high efficiencies in terms of savings.

A mean value of maximum saving of 54,48% has been obtained. This means that the real savings

will be on average lower that 54,48% and this value has to be modified for studies on average

Comparison Max Value Optimal Value Difference %

TEST 1 129715 85255.17609 44459.82391 52.14912

TEST 2 96686 80018.59425 16667.40575 20.82942

TEST 3 141746.35 106049.8849 35696.4651 33.66007

TEST 4 159853.4 82479.7631 77373.6369 93.80924

TEST 5 148063.5 91837.44862 56226.05138 61.22345

TEST 6 138684.7 82981.73223 55702.96777 67.12678

TEST 7 138362.9 106094.0211 32268.87888 30.41536

TEST 8 155537 110967.839 44569.16098 40.16403

TEST 9 149970.5 78490.68213 71479.81787 91.0679

TEST 10 145305 95923.15809 49381.84191 51.48063

MEAN 140392.44 92009.82996 48382.60504 54.1926

Standard Dev 17688.456 12086.4879 18093.71209 24.6179


68

savings. However the standard deviations are very high being a 24,6%, because of the use of

dummy data instead of real data.

We will consider the range of (54,48%-24,6;54,48%+24,6%) = (29,88;79,08) to make three

different scenarios for the economic return in further considerations.

7.2.2. Economic Results of Study B

In this study, to each logistic step of each route of each test version a random number is

generated with the excel function =randbetween(), in order to choose a random supplier for the

determined service. The sum of the costs, in disregard of volume discounts, is compared to the

optimal cost.

Table 23. Comparison between the total cost taking random suppliers and optimum total costs, study B

Figure 7.2. Graphic costs with random suppliers vs. optimum values, study B

Comparison 2 Value rd assignment Optimal ValueDifference%

TEST 1 88477.65 85255.18 3222.474 3.779799

TEST 2 108908.93 80018.59 28890.34 36.10453

TEST 3 129304 106049.9 23254.12 21.92753

TEST 4 108443.71 82479.76 25963.95 31.47917

TEST 5 99050.82 91837.45 7213.371 7.854499

TEST 6 104209.74 82981.73 21228.01 25.58154

TEST 7 129498.94 106094 23404.92 22.06054

TEST 8 116718.49 110967.8 5750.651 5.182268

TEST 9 99194.61 78490.68 20703.93 26.37756

TEST 10 102464.69 95923.16 6541.532 6.819554

MEAN 108627.158 92009.83 16617.33 18.7167

Standard Dev 13220.00697 12086.49 9738.388 11.82438

0

20000

40000

60000

80000

100000

120000

140000

1 2 3 4 5 6 7 8 9 10

Value rd assignment

Optimal Value


69

Study B gives savings from 3,78% to 36,1% as a result. The average savings with respect to

random suppliers are a 18,72% with a standard deviation of 11,82%. Using this information, we

are able to generate three scenarios with 18,72-11,82= 6.9%, 18,72% and 18,72+11,82= 30,54% of

average savings for further considerations.

7.3. Budget for the Further Development

In this paragraph we will consider the cost of materials and human resources required to develop

the final application.

The budget will be separated into two parts because we assume that the programming part will

be outsourced to an external institution and the monitoring and effective use is done at the

company.

In the table below we detail the approximate costs and its percentage over the whole budget for

the development of a complete software application for the facilitation of the decision making of

the management of bpi. We assume that the implantation will take 12month, whereby 6 month

are dedicated to the development of the program, where the project supervisor is in close contact

to the programmer to provide all necessary information. In the second half of the project, the

programmer will train the operators of bpi to use the application and at the same time test and

improve the software.

Table 24. Budget for the future software development

Item/service duration (month) if applies Total Cost(€) % over total

Project Supervisor 12 month (half time) 20000 35.391966

Software licence 12 month 6000 10.6175898

Computer 1500 2.65439745

Maintenance 12 month 360 0.63705539

Office material 50 0.08847992

bpi subtotal 27910 49.3894886

Programmer 6 month 18000 31.8527694

Training/Follow up 6 month (half time) 9000 15.9263847

Material 1000 1.7695983

Transport 600 1.06175898

outsource subtotal 28600 50.6105114

Total 56510

b

p

i

o

u

t

-

s

o

u

r

c

e


70

7.4. Economic Return

After analyzing the budget of implementing an extensive optimizing tool in bpi, we will study the

economic returns that could be obtained by using the tool in this paragraph. Hence, a set of

assumptions will be made and applied to three different scenarios.

7.4.1. Assumptions

Bpi delivers approximately 340 Million postal items per year and currently has a turnover of about

400 Million Euros [source: bpi]. We will assume that these numbers are not going to change in the

future.

Additionally, we consider that the pricing tool will encompass the delivery of 20.000 postal items

per day. Computed with a 300 days year, it would optimize the delivery of 6 Million of deliveries

per year. This makes approximately 0,0176% of the total delivery volume (340 Million of pieces).

We suggest to roughly calculate the savings due to the optimization tool over a 0,0176% of the

turnover of bpi. That is, the application will have an effect on a turnover of 7,04 Million of Euros.

We adopt the data of marginal profit of about 15% of the Belgian postal sector from 2010 [2] as

valid and applicable for our calculations.

Moreover, we roughly estimate that the supplier costs capture about 40% of the turnover, leading

to 2,816 Million € to which the future savings will be applicable.

On the other hand, we suppose that the total cost of implementation will be 56510€ in the first

year, but in following years current costs (license, computer, office material and maintenance) will

incur. We assume that the current costs would be:

Equation 22. Yearly costs for running daily the optimization tool

We will not consider the secondary benefits bpi might obtain through the application of the

pricing tool such as time savings at scheduling the deliveries and substitutions of conventional

techniques.

In the previous chapter the mean values between 30% and 80% for one sigma have been obtained

for the study A, with 54,5% as mean value. Since these are the maximum values, we will consider

the average saving respectively 0,5 times these values. According to this, we obtain 15% of cost


71

savings for the pessimistic scenario, 27,25% for the realistic scenario and 40% for the optimistic

scenario.

For the study B, the direct mean saving values and its one sigma values are going to be considered

for the scenarios: optimistic: 30,54% of savings, realistic: 18,72% and pessimistic 6,9%.

7.4.2. Optimistic Scenario

In the optimistic scenario for the study A, we apply the saving of 40% on 2,816M€ of supplier cost.

And over the results we can apply 15% of profit margin to see the real savings after operations

costs, customer discounts etc.

,

Equation 23. Economic Results, optimistic scenario, study A

Analogue for study B, the following equation is obtained:

, ,

Equation 24. Economic Results, optimistic scenario, study B

For the optimistic scenario we would obtain a yearly benefit of 168 960€ according to study A, or

129 001€ according to study B in exchange of an investment of 56 510€ in the first year and 7910€

of maintenance in the next years.

7.4.3. Realistic Scenario

In the realistic scenario we apply the saving of 27,25% to the study A and 18,72% for study B on

2,816M€ of supplier cost. And over the results we can apply 15% of profit margin to see the real

savings after operations costs, customer discounts etc.

, ,

Equation 25. Economic Results, realistic scenario, study A


72

, ,

Equation 26. Economic Results, realistic scenario, study B

For the realistic scenario we would obtain a yearly benefit of 115 104€ (according to study A) or

79 073€ (study B) in exchange of an investment of 56 510€ in the first year and 7910€ of

maintenance in the next years.

7.4.4. Pessimistic Scenario

In the pessimistic scenario we apply the savings of 15% (study A) and 6,9% (study B) on 2,816M€

of supplier cost. And over the results we can apply 15% of profit margin to see the real savings

after operation costs, customer discounts etc.

,

Equation 27. Economic Results, pessimistic scenario, study A

, ,

Equation 28. Economic results, pessimistic scenario, study B

For the pessimistic scenario we would obtain a yearly benefit of 63 360€ for study A or 29 145€

for study B in exchange of an investment of 56 510€ in the first year and 7910€ of maintenance in

the next years.

7.5. General Outcomes

A summary table and graphics (table 25, figures 7.3 and 7.4) are displayed below to visualize the

results of both studies.


73

Table 25. Investment, profit and positive differences on 3 scenarios

The first column corresponds to the investments made each year. The following columns state a

brute profit and the profit after subtracting the investments for each scenario. For the ease of

reading, we subtract costs only from positive values of the profit and transferred to the next year.

For the study A, net profit is made latest in the third year, therefore no data is given for the fourth

year. However for the study B, net profit is made latest the fourth year and a stable net profit the

fifth year.

As we can observe in the graphics 7.3 and 7.4, in the optimistic and realistic scenarios for both

studies, profit is made starting from year 2 (first year of employment of application). Only in the

pessimistic scenario the investment from the first year and the maintenance cost exceed the brut

profit of the first year. But even in the worst case scenario (study B, pessimistic scenario) a net

profit can be attained from the fourth year on.

Figure 7.3. Positive profit after subtracting investment and maintenance costs on three scenarios, study A

investm(€) profit (€) optimistic prof-invest(+), opt profit (€) realistic prof-invest(+),real. profit (€) pess. prof- invest(+),pess.

year 1 A 56510 0 0 0 0 0 0

year 2 A 7910 168969 104549 115104 50684 63360 0

year 3 A 7910 168969 161059 115104 107194 63360 54390

year 1 B 56510 0 0 0 0 0 0

year 2 B 7910 129001 64581 79073 14653 29145 0

year 3 B 7910 129001 121091 79073 71163 29145 0

year 4 B 7910 129001 121091 79073 71163 29145 7195

year 5 B 7910 129001 121091 79073 71163 29145 21235


74

Figure 7.4. Positive profit after subtracting investment and maintenance costs on three scenarios, study B

It is to comment on the test results that the ranges of reliable data are very wide, since no real

input data could be used. If the company decides to apply the pricing tool to the estimated costs

for significant suppliers, much more refined result range can be achieved and a more exact study

on economic returns can be performed.

75

8. Conclusions

8.1. Achievements

The major attainments of the work carried out are stated below:

- Understanding of the postal service sector through extensive literature study.

- Understanding of the business of bpi through interviews with the international transport

manager and the international transport coordinator.

- A mathematical model that resembles the supply chain process for a subunit of the

business of bpi with three routes has been constructed.

- A user-friendly database environment to save the data related to the logistical steps from

the collecting to the delivery of a postal element has been designed

- The mathematical model has been integrated to the solver application and linked to the

databases without affecting excessively to its flexibility.

- The application has been tested with experimental data both during and after its

construction so as to prove the effectiveness.

- Bpi obtains an application with which the postal rates from invoices and the estimated

costs can be compared and the cost-efficiency level of the suppliers can be observed.

- Furthermore, the investment value of developing an extensive tool that employs the

principle of the basis tool function on all international routes with multiple supplier options has

been calculated.

- The profitability of the investment in an extensive pricing tool has been proven.

In conclusion, we can affirm that the goal of defining a dynamic application that determines and

optimizes the use of means along the supply chain according to diverse constraints has been

achieved. The expansion of the tool should be considered, being technical and economic issues

discussed in the next paragraph.

8.2. Future Challenges

The object of this master thesis serves as a solid basis for the construction of an extensive price

defining software for bpi. The application is the visualization of the idea of optimizing the choice

Chapter 8. Conclusions

76

of the suppliers of bpi taking all relevant information into account. It embraces currently all the

necessary data classifications for facilitating the decision making to bpi. The application also

considers future developments in other sectors and is flexible to be adapted if needed.

The basic limitation of the current application is its restricted computational power and on

account of this, its small extension. As indicated in chapter 4.1 Problem Description, the complete

problem, only regarding the assignment would suppose 1206 variables without taking the volume

discounts and time constraints into account. To extend the model, it would be necessary to define

the information for more routes following the pattern of the existing ones, adding the appropriate

amount of suppliers and routes. More and more complex time constraints can be incorporated.

More volume discount options must be provided according to the offers of the suppliers. This part

might be more complicated, since the complexity is proportional to the complexity of the supplier

and route network, which will increase.

In addition, more currencies have to be introduced in the expanded version. The free version

excel solver admits merely one objective function, hence only one currency (the dollar) could be

used. For other software programs that allow the parallel solution of several objective functions,

it could be interesting to group objective functions by currency and transform the currency to

Euro at the database. Another option would be to use one objective function and transform the

data before entering to solver.

The Simplex Method will probably not be a suitable method to solve such an extent problem.

Metaheuristic optimization algorithms should be considered in the future.

As to the economic viability of the implementation of the complete tool, the economic study from

the previous chapter has proven that the investment will be profitable. The economic efficiency

has been tested on two studies to compensate the lack of real data and the results are in all cases

positive. Even the worst case scenario, choosing the lowest value of the one sigma scale of the

mean value of the relative savings by employing random suppliers, gives an acceptable profit in

the long run.

77

Appendices

78

Appendix A

Manual for pricing tool version 0.1

Appendix A Manual for pricing tool version 0.1

79

Cost optimization tool for bpost v0.1

Goal:

The goal is to choose the optimal supplier out of several feasible suppliers based on the

minimization of the total costs along the supply chain.

Assumptions:

- The costs are divided by 4 different processing steps: pick up, handling, transport, delivery,

whereby there are various transport options

- The transport step is subdivided in 4 categories (Air prior, SAL economy, surface economy

and surface prior)

- The number of items corresponding to each category is determined a priori. Currently it is

represented by dynamic random numbers. For this reason the results for the transport

cost will always change.

- There exist four different formats: Petit, Grand, Encombrant and Parcels

- Each format is subdivided by weight steps, for each of the weight steps there is a cost per

kilo in the original currency.

- The suppliers have to accomplish with certain time constraints for each processing step

Structure of the tool:

The tool consists of an Excel workbook with 5 active sheets:

- The main sheet is called “solver”. The mathematical model is allocated there and linked to

the excel solver that minimizes the overall cost.

- The “Pick Up Cost” sheet visualizes the optimal supplier, the overall cost of the collection,

the estimated weight of the whole collection and converts the cost from the original

currency to Euros

- In the “Handling Cost” sheet, the number of items of each weight step for each format

can be inserted. The estimated handling cost per each weight step and the assigned

suppliers will be extracted from the “Solver” sheet and visualized on the “Handling” Sheet.

It is necessary to click the “Transfer Nb” button, then run the solver on the solver sheet

and click the “Update Cost” button in order to obtain the new solution. Running the

solver can be omitted if changes are small and don’t affect the optimality of the solution.

- The “Transport Cost” sheet visualizes the optimal supplier, the total weight, the transport

costs, the air cargo and the customs per transport category.

- The “Delivery Cost” sheet reflects the total delivery cost required by the assigned supplier.


80

The Model:

Decision Variables:

PickUpSupplier i, i=1…4 => Binary Variable: 1 if pick up is realized by supplier i otherwise 0

HandlingSupplier i, i=1…4 => Binary Variable: 1 if handling is realized by supplier i otherwise 0

TransportAsupplier i, i=1…4 => Binary Variable: 1 if Priority Transport is realized by supplier i

otherwise 0

TransportBsupplier i, i=1…4 => Binary Variable: 1 if SAL Transport is realized by supplier i

otherwise 0

TransportCsupplier i, i=1…4 => Binary Variable: 1 if surface economy transport is realized by

supplier i otherwise 0

TransportDsupplier i, i=1…4 => Binary Variable: 1 if surface priority transport is realized by

supplier i otherwise 0

Deliverysupplier i, i=1…4 => Binary Variable: 1 if delivery is realized by supplier i otherwise 0

Parameters:

Cost of each process step per weight step, format and category

average weights

numbers of items

processing times of each process step

customer time requirements

Objective function:

Z = min (∑ (∑ (pick up cost per kg of each weightstep*average weight*number of

items)*PickUpSupplier i)

+ ∑ (∑(handling cost per kg of each weightstep* average weight*number of

items)*HandlingSupplier i )

+ ∑ (∑(transport A cost per kg* average weight* number of items) + aircargo)*

TransportASupplier i )

+ ∑ (∑(transport B cost per kg*average weight*number of items) + aircargo)*

TransportBSupplier i )

+ ∑ (∑(Transport C cost per kg of each weightstep* average weight* number of

items)*TransportCSupplier i)


81

+ ∑ (∑(Transport D cost per kg of each weightstep* average weight* number of

items)*∑TransportDSupplier i)

+ ∑ (∑(Delivery cost per kg of each weightstep* average weight* number of

items)*∑DeliverySupplier i))

Constraints:

∑PickUpSupplier i = 1

∑HandlingSupplier i = 1

∑TransportASupplier i = 1

∑TransportBSupplier i = 1

∑TransportCSupplier i = 1

∑TransportDSupplier i = 1

∑DeliverySupplier i = 1

∑(PickupTime*PickUpSupplier i) <= CustomerReqPickUpTime

∑(HandlingTime*PickUpSupplier i) <= CustomerReqHandlingTime

∑(TransportATime*PickUpSupplier i) <= CustomerReqTransportATime

∑(TransportBTime*PickUpSupplier i) <= CustomerReqTransportBTime

∑(TransportCTime*PickUpSupplier i) <= CustomerReqTransportCTime

∑(TransportDTime*PickUpSupplier i) <= CustomerReqTransportDTime

∑(DeliveryTime*PickUpSupplier i) <= CustomerReqDeliveryTime

How to use the solver sheet:

Each time any cost or quantity is changed the value of the objective function will be changed and

a new optimal solution might be possible. To calculate the new solution/ objective function we go

to the data menu and activate the solver add-in. The following window should appear:

Fig. 1

Click on solve and accept the new solution.


82

Normally costs can be changed directly on the solver sheet, please note that there are many

weight steps that are hidden. Later on a parameter sheet will be added in order to populate the

solver sheet with data without need to open it.

The easiest way to change the quantities is to fill them in the handling cost sheet and activate the

button “transfer Nb”

The estimated times and the required times by customers are also filled in manually, in the future

the fields will also be linked to the parameter sheet.

How to use the Cost sheets:

There are 5 different types of fields in these sheets, they are marked with different colours.

Fields that must be regularly updated manually and are linked to the results:

Field that can be filled in manually, having consequences on the results:

Field that can be filled in manually, but don’t have consequences on the results:

Fields that shows fixed information and shouldn’t be changed:

Field that is linked to a formula or to the results of the solver sheet and shouldn’t be changed:

Some fields are linked to the solver sheet with a macro. This macro has to be activated clicking on the corresponding buttons above the columns. Note that if parameters of the objective function of the model are changed, it’s necessary to run the solver on the solver sheet to obtain a new solution and new total costs.

83

Appendix B

Manual for pricing tool version 1.0

Appendix B Manual for pricing tool version 1.0

84

Cost optimization tool for bpost international v1.0

Goal:

The goal is to choose the optimal supplier out of several feasible suppliers that operate on several

routes and offer volume discounts on different stages of the supply chain.

The final choice will depend on the minimization of the total costs along the supply chain (Global

optimality).

Assumptions:

- The costs are divided by 4 different processing steps: pick up, handling, transport and

delivery.

- Our focus is on oversea transportation in the future. Therefore, the transport step is

subdivided only in 2 categories: Priority Air (A) and Alternative Transport (B). The number

of items corresponding to each category is determined a priori (randomly).

- There exist four different formats: Petit, Grand, Encombrant and Parcels

- Each format is subdivided by weight steps, for each of the weight steps there is a cost per

kilo in the original currency.

- The suppliers have to accomplish with certain time constraints for each processing step

- Each supplier can offer economic volume discounts, that are taken into account in the

objective function, for example:

o Pick Up and Handling Cost have to be done by the same supplier to apply some

volume discount

o Volume discount applies also to transport. If all 3 routes served by one supplier.

- Other discounts can be introduced by establishing the constraints and adding discount to

the objective function.

- All costs indicated in cost sheets are before volume discounts

Structure of the tool:

The tool consists of an Excel workbook with 5 active sheets:

Pick Up, Handling, Transport and Delivery: different parts of the supply chain.

Solver: Main sheet that allocates the model and is linked to the other sheets via formulas and

macros.

Color Legend:

The Pick Up, Handling, Transport and Delivery Sheets are designed for daily use. All cells are

marked with a specific color which indicates the type of information in it. It is important to keep in

mind the color legend to grant a smooth running of the application.

Cell that can be filled in with required information

Cell to be filled manually or through other database


85

Functional information, may be changed if needed

Parameters, information subject to alteration

Information given by the solver, directly or indirectly. DO NOT WRITE IN THESE CELLS!

Solver Sheet

The main sheet is called “solver”. The mathematical model is allocated there and linked to the

excel solver that minimizes the overall cost with the method of Simplex. Its results are

transported to the “xxxCost” sheets via formulas and macros.

Currently there are 3 routes that can be operated by 2 to 3 suppliers. More routes and suppliers

can be added to the hided rows and columns without disturbing existing formulas and macros.

Though the other sheets and the excel solver must be updated.

At the top left, there is the objective function.

In the center, there are 3 blocks with the unit and kg costs of each stage and weight step of the

different routes. If the costs of a supplier should rise, the new cost must be updated manually on

this sheet.

The average weight and the number of items are also visualized. The decision variables, the total

price per stage and the prices per format are also visualized.

At the right side extra conditions, such as volume discounts are displayed.

More at right, we have the time constraints per route, supplier and stage.

Pick Up Sheet

The “Pick Up” sheet visualizes the optimal supplier of pick up service, the overall cost of the

collection, the estimated weight of the whole collection and converts the estimated cost from the

original currency to Euros, and after filling in the actual postal rate (of the bills) will calculate the

difference from the estimated cost.

The columns “Route”, “Format” and “exchange rate” can be changed if necessary. “Actual Postal

Rate”, “Arrival” and “Departure” should be filled in, manually or be extracted from another

database. “Pallets” and “Collection” can also be filled in, but not necessarily. All other field are

filled in with formulas and give important information.

Handling Cost Sheet

In the “Handling” sheet, there are 3 big tables corresponding to each route.

The cost per weight step for this stage is displayed via macro. Also the total cost per format (PGE)

is indicated, since this is to be compared to the bills.

Furthermore, the number of items of each weight step for each format can be inserted.


86

For each country it is possible to run three different macros. The correct use of the macros via the

buttons places over the tables is to update first the numbers of items of all countries. By changing

the numbers and clicking on “update Nb of items”. (So, the numbers are transferred to both the

solver sheet and the delivery sheet). Then activate the solver by pushing any of the “solver”

buttons (one is enough). And finally, click on the “update costs” above each of the tables. Even if

the numbers of items of one or two particular routes didn’t change, it is still possible that the

global solution changed and the changes applied to one route, affects the others and new costs

were generated.

Transport Cost Sheet

The “Transport” Sheet includes two tables: One for the intrinsic transport cost and another for the

customs. It visualizes the optimal supplier, the number of items, the total weight, the transport

costs, the aircargo and the customs per transport category.

Some other details (traffic days, arrival, departure, fuel charges, ground handling, etc.) are to be

filled in manually.

Delivery Cost Sheet

The “Delivery” sheet, as the handling sheet, has three tables, one per route.

Over each of the tables, there are 3 buttons that activate macros to update the number of items,

to run the solver and to update the costs.

The correct use of these buttons is identic to the ones in the Handling sheet:

First all new number should be filled in. The buttons over the changed columns must be pressed

(the numbers in the solver sheet and in the handling sheet are updated). Then, one of the solver

buttons should be pressed. And all new cost must be calculated.

If the new numbers of items have already been filled in to the “Handling” Sheet, then it’s not

necessary to update them again. Then, only the “update cost” buttons has to be pressed.

Interaction between the Cost Sheets

It might be possible that changes to the handling sheet or delivery sheet affect the results on the

other cost sheets. Thus, it is advisable to first update the number of items, consecutively run the

solver and then look into the results on all the cost sheets.

Other Sheets

- Answer report, this is a sheet that can be generated at running the solver (manually, not

with macro or button). It gives information about the solution, the decision variables and

constraint.

- The auxiliary sheet is needed to create drop downs

The Model:

Decision Variables:


87

- PickUpSupplier i,j i=1…3, j= GB-USA, CR-CH, USA-BE

Binary Variable: 1 if pick up is realized by supplier i in route j, otherwise 0

- HandlingSupplier i,j i=1…3, j= GB-USA, CR-CH, USA-BE

Binary Variable: 1 if handling is realized by supplier i in route j, otherwise 0.

- TransportAsupplier i,j i=1…3, j= GB-USA, CR-CH, USA-BE

Binary Variable: 1 if Priority Transport is realized by supplier i in route j, otherwise 0.

- TransportBsupplier i,j i=1…3, j= GB-USA, CR-CH, USA-BE

Binary Variable: 1 if Alternative Transport is realized by supplier i in route j,

otherwise 0.

- Deliverysupplier i, j i=1…3, j= GB-USA, CR-CH, USA-BE

Binary Variable: 1 if delivery is realized by supplier i in route j, otherwise 0

- PickUp&HandlingDiscount i, k i=1…3, k=GB, CR, USA

Binary Variable: 1 if Pick Up and Handling are realized by supplier in in site k,

otherwise 0.

- TranportDiscount i, i=1…3

Binary Variable: 1 if all routes all operated by supplier i, otherwise 0.

Parameters:

- Cost of each process step per weight step, format and category

- average weights

- numbers of items

- processing times of each process step

- customer time requirements j, j=GB-USA, RC-CH, USA-BE

- volume discount for pick up & handling i,k i=1…3, k=GB,RC,USA

- volume discount for transport i, i=1…3

Objective function:

Z = min (∑ (∑ (pick up cost per kg of each weightstep*average weight*number of

items)*PickUpSupplier i)

+ ∑ (∑(handling cost per kg of each weightstep* average weight*number of

items)*HandlingSupplier i )

+ ∑ (∑(transport A cost per kg* average weight* number of items)

+ aircargo)* TransportASupplier i )

+ ∑ (∑(transport B cost per kg*average weight*number of items)

+ aircargo)* TransportBSupplier i )

+ ∑ (∑(Transport C cost per kg of each weightstep* average weight* number of

items)*TransportCSupplier i)

+ ∑ (∑(Transport D cost per kg of each weightstep* average weight* number of

items)*∑TransportDSupplier i)

+ ∑ (∑(Delivery cost per kg of each weightstep* average weight* number of

items)*∑DeliverySupplier i))

- ∑ volume discount for pick up & handling i* PickUp&HandlingDiscount i, k

- ∑ volume discount for transport i*TranportDiscount i


88

Constraints:

∑PickUpSupplier i,GB-USA = 1

∑PickUpSupplier i,RC-CH = 1

∑PickUpSupplier i,USA-BE = 1

∑HandlingSupplier i, GB-USA= 1

∑HandlingSupplier i, RC-CH= 1

∑HandlingSupplier i, USA-BE= 1

∑TransportASupplier i, GB-USA = 1

∑TransportASupplier i, RC-CH = 1

∑TransportASupplier i, USA-BE = 1

∑TransportBSupplier i, GB-USA = 1

∑TransportBSupplier i, RC-CH = 1

∑TransportBSupplier i, USA-BE = 1

∑DeliverySupplier i, GB-USA = 1

∑DeliverySupplier i, RC-CH = 1

∑DeliverySupplier i, USA-BE = 1

∑(PickupTime*PickUpSupplier i,j) <= CustomerReqPickUpTime j

∑(HandlingTime*PickUpSupplier i,j) <= CustomerReqHandlingTime j

∑(TransportATime*PickUpSupplier i,j) <= CustomerReqTransportATime j

∑(TransportBTime*PickUpSupplier i,j) <= CustomerReqTransportBTime j

∑(DeliveryTime*PickUpSupplier i,j) <= CustomerReqDeliveryTime j

PickUpSupplier i,j + HandlingSupplier i,j -1 <= PickUp&HandlingDiscount i, k <= (PickUpSupplier i,j

+ HandlingSupplier I,j)/2

∑i(TransportAsupplier i,j) - 2 <= TranportDiscount i <= (∑i(TransportAsupplier i,j))/3

89

Appendix C

Codes for Excel Macros

Appendix C Codes of Excel Macros

90

Code 1. Activation of solver from the “solver” sheet

Sub Solve()

'

' Solve Macro

' Activate solver

'

' AddIns("Solver Add-in").Installed = True

Sheets("Solver").Select

Application.Run "SolverSolve", True

Application.Run "SolverFinish", 1


Range("A2").Select

End Sub

Code 2. Activation of solver from the “handling” sheet

Sub SolverHandling()

AddIns("Solver Add-in").Installed = True


Application.Run "SolverSolve", True

Application.Run "SolverFinish", 1

Sheets("Handling").Select

Range("I1").Select

End Sub

Code 3. Copy the number of items of route GB-USA to “delivery” and “solver” sheet

Sub NbItemsHandlingGBUSA()

'

' NbItemsHandlingGBUSA Macro


91

' Takes the number of items transported from GB to the USA from Handling sheet and

populates the cells in Delivery and Solver sheet.

'

'


Range("G5:G144").Select

Selection.Copy

Sheets("Delivery").Select

Range("F5").Select

ActiveSheet.Paste


Range("C20").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=True

Range("A4").Select


Range("G2").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = " "

Range("G2").Select

End Sub

Code 4. Update the estimated costs from “solver” sheet on “handling” sheet

Sub CostsHandlingGBUSA()

'

' CostsHandlingGBUSA Macro


92

' Updates the cost given from solver on the Handling Sheet.

'

'


Range("C71:EL71").Select

Selection.Copy


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=True

End Sub

93

Bibliography

[1] A. Escribano, P. González, J. Lasheras, "Evolution and Analysis of the Market Structure of

Postal Services," Topics in Regulatory Economics and Policy series, vol. 46, pp. 287-309, 2004.

[2] CopenhagenEconomics, "Main developments in the postal sector (2008-2010)," DG Internal

Market, Copenhagen, 2010.

[3] J. Rodriguez, "The Internationalisation of the Small and Medium-sized Firm," Prometheus:

Critical Studies in Innovation, vol. 25, no. 3, pp. 305-317, 2007.

[4] M.A. Crew, P.R. Kleindorfer, Progress toward Liberalization of the Postal and Delivery Sector,

Springer, 2006.

[5] M.A. Crew, P.R. Kleindorfer, Current Directions in Postal Reform, Kluwer Academic, 2000.

[6] "Mission and vision," bpost international, 2012. [Online]. Available:

http://www.bpostinternational.com/en/about_bpi/mission_and_vision.html. [Accessed 14

May 2012].

[7] "Expert in Belgium, Europe and ... the world," Bpost International, 2012. [Online]. Available:

http://www.bpostinternational.com/en/about_bpi/experts_worldwide.html. [Accessed 14

May 2012].

[8] R.D. Metters, "Interdependent Transportation and Production Activity at the United States

Postal Service," The Journal of the Operational Research Society, vol. 47, no. 1, pp. 27-37,

1996.

[9] G.A. Forgionne, Quantitative Management, Dryden Press, 1999.

[10] F.S. Hillier, G.J. Liebermann, Introduction to Operations Research, Mc GrawHill, 2005.

[11] E. Kießkalt, Die Entstehung der Post, Bamberg: Gustav Duckstein Verlag, 1930.

[12] V.J. Sussex, Introducing Postal History, London: British Philatelic Trust, 1988.

[13] F.H. Nader, "Mail Trends," Working Paper, 2004.

[14] L.A. Jimenez, F.H. Nader, "Substitution Patterns," Working Paper, 2005.

Bibliography

94

[15] M.J. Iturralde, C. Quirós, "Analysis of efficiency of the European postal sector," International

Journal of Production Economics, vol. 114, no. 1, pp. 84 - 90, 2008.

[16] C. Nicholson, "Access pricing in competitive postal services," Utilities Policy, vol. 4, no. 3, pp.

199-204, 1994.

[17] S. Hong, F.A. Wolak, "Relative prices and electronic substitution: Changes in household-level

demand," Journal of Econometrics, vol. 145, no. 1, pp. 226-242, 2008.

[18] M. Tajima, "Strategic value of RFID in supply chain management," Journal of Purchasing and

Supply Management, vol. 13, no. 4, pp. 261-273, 2007.

[19] X. Zhang, S. Yue, W. Wang, "The review of RFID applications in global postal and courier

services," The Journal of China Universities of Posts and Telecommunications, vol. 13, no. 4,

pp. 106-110, 2006.

[20] J. Calzada, "Universal service obligations in the postal sector: The relationship between

quality and coverage," Information Economics and Policy, vol. 21, no. 1, pp. 10-20, 2009.

[21] M. Mussa, S. Rosen, "Monopoly and product quality," Journal of Economic Theory, no. 18, pp.

301-371, 1978.

[22] L.F. O'Brien, L.A. Pintsov, A. Obrea, Cost analysis and pricing of innovative postal products.

[23] W.L. Winston, Operations Research: Applications and Algorithms, Duxbury Press, 2004.

[24] J.N.D. Gupta, L.J. Krajewski, "Operations Research in Postal Services - A Survey," Computers

and Operational Research, vol. 4, no. 4, pp. 235-246, 1977.

[25] A.I.Z. Jarrah, J.F. Bard, A.H. de Silva, "A heuristic for machine scheduling at general mail

facilities," European Journal of European Research, vol. 63, no. 2, pp. 192-206, 1992.

[26] T. Grünert, H.J. Sebastian, M. Thärigen, "The Design of a Letter-Mail Transportation Netwrok

by Intelligent Techniques," in Proceedings of the 32nd Hawaii International Conference on

System Sciences, 1999.

[27] D.R. Anderson, D.J. Sweeney, T.A. Williams, K. Martin, "Problem Solving and decision

making," in An Introduction to Management Science, Mason, Thomson South-Western , 2008,

p. 3.

Documents

Pricing tool for transportation at Bpost International …lib.ugent.be/fulltxt/RUG01/001/887/294/RUG01-001887294...Pricing tool for transportation at Bpost International Yue Wan Promotor: