Modeling and Simulation of Logistics Flows 2 E-Book

Jean-Michel Reveillac

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 195

Veröffentlichungsjahr: 2017

Beschreibung

Volume 2 begins with an introduction and 4 chapters implementing software tools on cases of practical applications and it ends with a conclusion: * The various tools used in this volume * Operational research with a spreadsheet * Dashboards with spreadsheets and pivot tables * Scheduling and planning with a project manager * The traffic simulation The conclusion shows the new features that are expected to emerge on spreadsheets as well as project managers, developments and convergences between traffic simulators and new infrastructure that are emerging on road networks. Annex 1 focuses on the installation Solver in Microsoft Excel and Annex 2 focuses on the installation of the Java Development Kit.

Leseprobe

Table of Contents

Cover

Title

Copyright

About This Book

Introduction

I.1. Logistics, information systems and software

1 Operational Research Using a Spreadsheet

1.1. Foreword

1.2. Dynamic programming

1.3. Scheduling

1.4. Maximal flows

1.5. Transport model

1.6. Linear programming

2 Dashboards, Spreadsheets and Pivot Tables

2.1. Spreadsheets: a versatile tool

2.2. Example database

2.3. Multiple databases

2.4. Limits and constraints with calculated fields

2.5. Conclusion

3 Scheduling and Planning Using a Project Manager

3.1. Reminders and information

3.2. Example: designing and building a machine-tool

3.3. Project monitoring

3.4. Conclusion

4 Road Traffic Simulation

4.1. Before we start

4.2. Ring road

4.3. RoadTrafficSimulator

4.4. Intersection simulator

4.5. Green Light District (GLD)

4.6. AnyLogic

4.7. Conclusion

Conclusion

Appendices

Appendix 1: Installing the Solver

A1.1. Introduction

A.1.2. Microsoft Excel 1997, 2002 and 2003 for Windows

A1.3. Microsoft Excel 2007–2010 for Windows

A1.4. Microsoft Excel 2013 for Windows

A1.5. Microsoft Excel for Mac 2008-2011

Appendix 2: Installing the Java Development Kit

A2.1. Why Java?

A2.2. Downloading

A2.3. Testing the presence of the JDK compiler

A2.4. Creating the environment variable

Glossary

Bibliography

Index

End User License Agreement

List of Tables

1 Operational Research Using a Spreadsheet

Table 1.1. The double entry matrix from Exercise 2

Table 1.2. Tasks, starts, durations and ends for the sonorization exercise

Table 1.3. The precedence table for the installation of an air-conditioning system

Table 1.4. Summary of palletized product batch flow

Table 1.5. Transport costs between factories and customers

3 Scheduling and Planning Using a Project Manager

Table 3.1. Tasks and predecessors

Table 3.2. Resources

Table 3.3. Allocation

4 Road Traffic Simulation

Table 4.1. Properties of the blocks for the two new organigrams

List of Illustrations

Introduction

Figure I.1. Knapsack with Microsoft Excel

Figure I.2. A critical path calculation matrix

Figure I.3. Gantt chart with Microsoft Excel

Figure I.4. An example of a spreadsheet for maximal flows

Figure I.5. Calculating transport costs using the Microsoft Excel solver

Figure I.6. An example of linear programming, spreadsheet and solver

Figure I.7. A pivot table mixing numbered and graphic data

Figure I.8. A design and manufacturing plan for a machine-tool within Microsoft Project

Figure I.9. A simple road network with its intersection and traffic lights

1 Operational Research Using a Spreadsheet

Figure 1.1. The table to be created in the spreadsheet

Figure 1.2. The VBA code window in VBE attached to Module 1 of the project

Figure 1.3. An accounting spreadsheet containing two (Delete and Calculate) procedure buttons (macros)

Figure 1.4. The example of paragraph 4.2.3.3 calculated. The results can be seen in the bottom table

Figure 1.5. The table to be created in the spreadsheet

Figure 1.6. The section on the right with the calculation formulae to be inputted

Figure 1.7. The cells located below the CPM matrix with their formulae

Figure 1.8. Calculating the critical field and display showing the duration

Figure 1.9. Spreadsheet with all cells filled in the matrix. Note the values of I (earliest start) and j (latest end) as well as the length of the project and the critical path that passes through nodes: 1, 2, 4, 7 and 8

Figure 1.10. The matrix corresponding to the task table with each of its columns

Figure 1.11. Microsoft Excel: insert a 2D STACKED BAR CHART

Figure 1.12. The resized graph, positioned under the matrix

Figure 1.13. The contextual menu (right click) to format the axis

Figure 1.14. The window for configuring the axis in Microsoft Excel 2013, including the checkbox: values in reverse order (in axis options)

Figure 1.15. Configuring axes (10 and 5) and labels (high)

Figure 1.16. The fields “length” and “end” selected in the window displaying the data source

Figure 1.17. The menu and the pop-up tool bar, obtained by right-clicking on the “Start” bar. Note the tools OUTLINE and FILL above the menu

Figure 1.18. The finished Gantt chart and its associated table in a Microsoft Excel 2013 spreadsheet

Figure 1.19. The table to be created in Microsoft Excel (here the critical tasks are in gray and in bold)

Figure 1.20. The MPM chart corresponding to the project: installation of an air conditioning system

Figure 1.21. The table recalculated for workdays

Figure 1.22. The “Start” and “End” columns in STANDARD format

Figure 1.23. AXIS OPTIONS, LABELS and NUMBER

Figure 1.24. AXIS OPTIONS, ALIGNMENT -45°

Figure 1.25. The finished table and chart corresponding to the Gantt chart for the project

Figure 1.26. The spreadsheet to be created in Microsoft Excel

Figure 1.27. The spreadsheet with its two buttons (“Create tables” and “Solve”)

Figure 1.28. The data from Exercise 1 in Chapter 6 entered into the spreadsheet

Figure 1.29. The solution from exercise 1 with the set of results displayed

Figure 1.30. The table to be created in a Microsoft Excel spreadsheet

Figure 1.31. The second table positioned below the first

Figure 1.32. Configuring the solver

Figure 1.33. The results calculated by the solver

Figure 1.34. The two tables for calculating costs

Figure 1.35. Configuration of the solver

Figure 1.36. The results obtained from using the solver

Figure 1.37. The table that will host our future linear programs

Figure 1.38. The data from the example, entered into our table

Figure 1.39. The different parameters entered in the solver dialogue window

Figure 1.40. Calculated results and the SOLVER RESULTS window

2 Dashboards, Spreadsheets and Pivot Tables

Figure 2.1. Example database

Figure 2.2. The dashboard to create

Figure 2.3. The departure chart

Figure 2.4. Inserting a calculated field: Net total x 0.32

Figure 2.5. The pivot table with the margin calculation

Figure 2.6. The conditional formatting tool including its options

Figure 2.7. Size configuration in the pivot table options

Figure 2.8. The pivot table to be obtained

Figure 2.9. The initial table for the second example

Figure 2.10. The pivot table showing the total monthly bonus

Figure 2.11. The pivot table to be used as a base

Figure 2.12. Creating a conditional calculated field

Figure 2.13. The initial pivot table, before modification

Figure 2.14. Selecting the field for the slicer

Figure 2.15. The pivot table and the filter interface for selecting a slicer

Figure 2.16. Selecting the date filters

Figure 2.17. The pivot table showing only 2015 data

Figure 2.18. The filter for selecting the six least effective vendors

Figure 2.19. The pivot table showing the six least effective vendors

Figure 2.20. Configuring the calculated field for the 2016 target

Figure 2.21. The two columns created by adding the “2016 Target” field

Figure 2.22. The pivot table with the column “Net Total” for 2015 and the targets for vendors for 2016

Figure 2.23. The quantities sold by each of the agencies in 2015

Figure 2.24. The pivot table to be obtained, including columns for 2016 for Bordeaux, Caen and Nice

Figure 2.25. Creating the column that will contain the calculated item “Nice 2016”. Note the formula for calculating the 50% increase and the “CEILING” function

Figure 2.26. The pop-up menu for agencies, showing options for sorting, among others

Figure 2.27. An example of the range of cells for the “Sales” table

Figure 2.28. An example of the range of cells for the “Products” table

Figure 2.29. Creating a table

Figure 2.30. The two tables: “Sales” (on the left) and “Products” (on the right)

Figure 2.31. The dialog box for creating a relationship

Figure 2.32. The new relationship

Figure 2.33. The pivot table to be created

Figure 2.34. Creating the pivot table using multiple tables

Figure 2.35. The fields area of the pivot table showing the two active tables: “Products” and “Sales”

Figure 2.36. The renamed headers: “Qty sold”, “Designation” and “Agency”

Figure 2.37. The table to be obtained

Figure 2.38. The resulting table from our operations. The values obtained are inconsistent

Figure 2.39. The modified formula for calculating the “Net Total”

3 Scheduling and Planning Using a Project Manager

Figure 3.1. Creating the “Factory schedule” calendar

Figure 3.2. The exceptions in the factory calendar

Figure 3.3. The project calendar options

Figure 3.4. Project information

Figure 3.5. The main recap tasks and traditional tasks entered into Microsoft Project

Figure 3.6. The table featuring the filled “Task” and “Antecedents” columns

Figure 3.7. A detailed look at some tasks from the “MecaTools” project

Figure 3.8. The MPM network (or task network) corresponding to the “MecaTools” project

Figure 3.9. The table, to the left of the Gantt chart, to which the two columns: “Free slack” and “Total slack” have been added

Figure 3.10. The Gantt chart showing free and total slack. Below is shown the window for configuring style bars

Figure 3.11. Creating resource calendars using a copy of the “Factory schedule” calendar

Figure 3.12. The resources table

Figure 3.13. The table with the “Resource names” and “Resource initials” columns

Figure 3.14. The new slack calculated and the Gantt chart showing resource initials

Figure 3.15. The team planner. We can see the periods of over-allocation for the “Computer engineer”, “Fitter 1” and “Fitter 3” resources

Figure 3.16. The resource use table featuring the number of hours for each resource

Figure 3.17. A more detailed display showing the resource use table

Figure 3.18. The over-allocations in tasks no. 10, 13 and 21 have disappeared

Figure 3.19. Rescheduling task no. 16 for the next available date

Figure 3.20. There are no remaining over-allocated tasks

Figure 3.21. The detailed timeline for the “MecaTools” project

Figure 3.22. The window for creating the WBS code structure

Figure 3.23. The task table and its WBS number column

Figure 3.24. Table displaying work time for each of the tasks

Figure 3.25. Table showing the work time for each of the resources

Figure 3.26. A pivot table generated by the “Visual reports” tool in Microsoft Project 2013

Figure 3.27. The Microsoft Excel spreadsheet, created in Microsoft Project, that allowed us to generate the chart in Figure 3.26

Figure 3.28. The burndown and scheduling tools available in the ribbon under the TASK tab

Figure 3.29. The tools for managing the status date under the PROJECT tab

Figure 3.30. Here we can see the burndown (bold horizontal line in the middle of the task bars)

Figure 3.31. The functions of the different shapes of cursor that appear when hovering over a bar representing a task in a Gantt chart

4 Road Traffic Simulation

Figure 4.1. The website of Martin Treiber’s simulator “Ring road”

Figure 4.2. The parameters for simulation no. 1

Figure 4.3. The application RoadTrafficSimulator in use with its control interface at the top right

Figure 4.4. Two intersections with the indicators (green triangles) representing the traffic lights. For a color version of this figure, see www.iste.co.uk/reveillac/modeling2.zip

Figure 4.5. The 7 intersections created for our model

Figure 4.6. Our network once the different intersections have been connected

Figure 4.7. Blockage and congestion of traffic lanes around an intersection (traffic jam)

Figure 4.8. The Intersection simulator program window

Figure 4.9. The parameter table

Figure 4.10. The TRAFFIC LIGHT system for the bottom lane (Down)

Figure 4.11. The TRAFFIC LIGHT system and LEFT SIGN for the bottom lane (Down)

Figure 4.12. The ADDITIONAL ARROW, shown to the right of the TRAFFIC LIGHT system

Figure 4.13. The histogram showing average waiting times

Figure 4.14. Here we can see the number of vehicles waiting on the right-hand side for each direction. In this example, 3 at the bottom, 99 on the right, 41 at the top and 8 on the left

Figure 4.15. An example of a TXT file extracted from an exported ZIP file, containing the results of the simulation

Figure 4.16. The command for launching the compilation in Apple OSX (at the top) and in Microsoft Windows (at the bottom)

Figure 4.17. Result after compilation in Apple OSX (at the top) and in Microsoft Windows (at the bottom)

Figure 4.18. The Green Light District editor

Figure 4.19. The editor launch file, in Microsoft Windows using the command prompt (at the top) and in Apple OSX using Terminal (at the bottom)

Figure 4.20. The “GLDSim” simulator in Green Light District

Figure 4.21. The command for launching the simulator, in Microsoft Word using the command prompt (at the top) and in Apple OSX using Terminal (at the bottom)

Figure 4.22. The GLD editor toolbar

Figure 4.23. The GLD simulator toolbar

Figure 4.24. The road network for our example, featuring 4 intersections, F1 to F4. For a color version of this figure, see www.iste.co.uk/reveillac/modeling2.zip

Figure 4.25. The four “Traffic lights” type intersections in GLDEdit

Figure 4.26. The five “Edge node” type intersections” added to four “Traffic lights” blocks

Figure 4.27. Each intersection block

Figure 4.28. The model showing road junctions (in pink). The junctions show the traffic lights set up by default

Figure 4.29. The GENERAL CONFIGURATION window displayed when confirming your model

Figure 4.30. An example of a potential error during confirmation

Figure 4.31. Saving a model (in Microsoft Windows), here “MyModel.infra” in the Office folder

Figure 4.32. The model centered in the “GLDSim” window and enlarged by 150%

Figure 4.33. The different types of vehicle in the model during the simulation

Figure 4.34. Display showing the properties of a junction lane, in this case “Drivelane 24”

Figure 4.35. Display showing the properties of a node at an intersection, in this case “Junction 8”

Figure 4.36. Two graphs: average waiting time for vehicles at the intersection as a function of the time elapsed (on the left) and the number of vehicles having crossed the intersection as a function of the time elapsed (on the right)

Figure 4.37. Display showing the properties of an “Edge node”, in this case “Edgenode 0”

Figure 4.38. The graphs available from the “Edge node”

Figure 4.39. The properties panel for the node “Edgenode 0” in our model after modification at the end of roughly 15,000 cycles

Figure 4.40. The three statistical graphs for the node EDGENODE 0 in our model after roughly 15,000 simulation cycles

Figure 4.41. The categories of options available for managing traffic lights from the OPTIONS menu

Figure 4.42. Section 5b of the GLD documentation relating to traffic light options

Figure 4.43. The average waiting times at intersections in our model with a random management method (RANDOM – on the left) and using a method with the most cars (MOST CARS – on the right)

Figure 4.44. My example model showing the lanes deleted between nodes 7, 9 and 5

Figure 4.45. The dialog box for the properties of the junction and its DELETE DRIVELANE button. Here, the lane “Drivelane 25” in the junction “Road 6” has been selected. We can see the corresponding interactive link, underlined and in blue. For a color version of this figure, see www.iste.co.uk/reveillac/modeling2.zip

Figure 4.46. The dialog box for lane properties, here “Drivelane 25”, and its DELETE THIS DRIVELANE button

Figure 4.47. The plan for our model showing the bus lanes (in green). For a color version of this figure, see www.iste.co.uk/reveillac/modeling2.zip

Figure 4.48. The model featuring orange lanes restricted to buses. For a color version of this figure, see www.iste.co.uk/reveillac/modeling2.zip

Figure 4.49. A section of the model with nodes 5 and 6 surrounded by their direction indicators

Figure 4.50. The properties box with the modified direction indicators, TURN RIGHT, ticked, TURN LEFT, unticked, for the lane “Drivelane 12” of junction “Road 9”

Figure 4.51. The direction indicators in “GLDEdit”

Figure 4.52. Here we can note the green buses on the orange lanes, starting at node 4 and turning right at node 5. For a color version of this figure, see www.iste.co.uk/reveillac/modeling2.zip

Figure 4.53. The statistics window in SUMMARY mode (on the left) and TABLE mode (on the right)

Figure 4.54. A “.dat” file (top) and the data in Microsoft Excel (bottom)

Figure 4.55. The AnyLogic downloads page

Figure 4.56. Aerial image from our example (Source: Google Maps)

Figure 4.57. The SNIPPING TOOL dialog box in Microsoft Windows 7

Figure 4.58. The AnyLogic welcome screen

Figure 4.59. The PALETTE tab and its PRESENTATION tools

Figure 4.60. Selecting the captured image to be used as your background. Note the area defined by the marker xy

Figure 4.61. The image positioned where the marker is in the MAIN window. We can see the scale indicator at the top

Figure 4.62. The properties of the road (ROAD) including its name (in this case, “Avenue Boucicaut”) and the number of lanes (in this case, 1 and 1)

Figure 4.63. The route (one lane to the right and one to the left) in our model and its direction (in this instance, from left to right)

Figure 4.64. The two roads and the point where the routes cross (green dot). For a color version of this figure, see www.iste.co.uk/reveillac/modeling2.zip

Figure 4.65. The first road, divided into two sections, on both sides of the intersection

Figure 4.66. The three properties windows for my three road segments: “Avenue_Boucicaut_NORTH”, “Avenue_Boucicaut_SOUTH” and “Rue Rhin_Danube”

Figure 4.67. The traffic plan for our example. For a color version of this figure, see www.iste.co.uk/reveillac/modeling2.zip

Figure 4.68. The 3 blocks taken from the ROAD TRAFFIC LIBRARY, moved to the MAIN window and connected

Figure 4.69. The properties of the CAR SOURCE block, now named “Veh_NORTH”

Figure 4.70. The properties of the CARMOVETO block, now named “carMoveTo_SOUTH”

Figure 4.71. The simulator window featuring its RUN button below the name of the model, in this case “Chalon Boucicaut”

Figure 4.72. The main functions of the simulator toolbar

Figure 4.73. Window showing the model during simulation. In this instance, we can see six vehicles (blue, green, cyan, green, green and red) traveling from north to south on road no.1 section 1 and 2. For a color version of this figure, see www.iste.co.uk/reveillac/modeling2.zip

Figure 4.74. The organigram featuring three new blocks

Figure 4.75. The model in simulation mode featuring four vehicles (green, blue, cyan, green) traveling south, three (green, blue, magenta) traveling east and one (red) arriving from the north. For a color version of this figure, see www.iste.co.uk/reveillac/modeling2.zip

Figure 4.76. The three-vehicle management organigrams within our model

Figure 4.77. Arriving from different sources, the vehicles will travel in random directions depending on the probabilities entered when the properties were configured. For a color version of this figure, see www.iste.co.uk/reveillac/modeling2.zip

Figure 4.78. The CAR TYPE block and the NEW AGENT dialog box with CAR selected

Figure 4.79. The new tab CAR next to the MAIN tab, containing the vehicle and its scale indicator

Figure 4.80. The NEW CAR field (on the bottom) containing the value CAR, in the properties for the “Veh_NORD” source

Figure 4.81. The simulator toolbar and the NAVIGATE TO VIEW AREA icon, as well as the [WINDOWS3D] option (on the right)

Appendix 1: Installing the Solver

Figure A1.1. The ADD-INS option in the TOOLS menu

Figure A1.2. The list of add-ins with the “Solver Add-In” to be ticked

Figure A1.3. The option for opening the SOLVER from the TOOLS menu

Figure A1.4. Top left – the Office button in Excel 2007

Figure A1.5. The Excel add-ins featuring the drop-down list MANAGE (bottom right) and the GO button

Figure A1.6. The dialog box displaying the available add-ins

Figure A1.7. The dialog box showing the list of available add-ins for Microsoft Excel 2013. At the bottom, we can see the “Manage” field along with its drop-down menu and GO button

Figure A1.8. The “Add-Ins” dialog box

Figure A1.9. The ribbon in the DATA menu including the solver icon on the right

Figure A1.10. The dialog box for installing the solver add-in

Figure A1.11. The TOOLS menu and the option SOLVER… (last option on the list)

Appendix 2: Installing the Java Development Kit

Figure A2.1. The sub-folder “Utilities”, located in the “Applications” folder in OSX. Note the application “Terminal”

Figure A2.2. The field “Search programs and files” (on the left) and inputting “cmd” in the same field (on the right)

Figure A2.3. The “Run” option from “All programs” and the RUN dialog box featuring the field OPEN containing “cmd”

Figure A2.4. The “command prompt” option in the “Accessories” folder in Microsoft Windows XP

Figure A2.5. The command list for use with “javac” from the Microsoft Windows command menu (on the left) and from the OSX Terminal (on the right)

Figure A2.6. From the properties of the work post we can see all of the windows for accessing the environment variable “Path” (Microsoft Windows 7)

Guide

Cover

Table of Contents

Begin Reading

Pages

C1

iii

iv

v

ix

x

xi

xii

xiii

xv

xvi

xvii

xviii

xix

xx

xxi

xxii

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

167

168

169

171

173

174

175

176

177

178

179

181

182

183

184

185

186

187

188

189

190

191

193

194

195

196

197

198

199

200

201

202

203

G1

G2

G3

G4

G5

G6

G7

G8