Transportation Network Optimization
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

 
View only
 
 
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1
Transportation Network Optimization
2
3
Instructions: The objective is to allocate plant output to distribution centers so as to minimize transportation costs. This spreadsheet uses the OpenSolver Add-on. The Green cells denote user input, yellow are decision variable determined by the solver, and the red cell is the problem objective. Problem data is from J. Bischopp, 1999.
4
5
Unit Costs of Transportation ($/ton)
6
PlantThis first table presents all of the problem data. Any change to problem data should be in this table.

Solution of the optimization model uses the OpenSolver add-in for Google Sheets. Check the Add-ons menu to verify that OpenSolver has been installed. If it hasn't been installed, open 'Get addons ...' and search for OpenSolver available from OperSolver.org.
7
Distribution Center
ArnhemGoudaDemand (tons)
8
London1002.5125
9
Berlin2.5100175
10
Maastricht1.62225
11
Amsterdam1.41250
12
Utrecht0.81225
13
The Hague1.40.8200
14
Capacity (tons)550700
15
16
17
Shipment Plan (tons)
18
PlantThis second table presents a shipment plant. The central component of the table are decision variables for a linear optimization. The row and column totals are determined by formula.
19
Distribution Center
ArnhemGouda
Shipments (tons)
20
London0125125
21
Berlin1750175
22
Maastricht2250225
23
Amsterdam0250250
24
Utrecht15075225
25
The Hague0200200
26
Shipments (tons)550650
27
28
29
Shipping Costs
30
PlantThis third table presents a table of costs. The central component of the table are the product of decision variables from table 2 with unit costs from table one. The objective is to minimize total costs shown in the lower right corner.
31
Distribution Center
ArnhemGoudaCosts ($)
32
London0.00312.50312.50
33
Berlin437.500.00437.50
34
Maastricht360.000.00360.00
35
Amsterdam0.00250.00250.00
36
Utrecht120.0075.00195.00
37
The Hague0.00160.00160.00
38
Costs ($)917.50797.501,715.00
39
40
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
100
Loading...
 
 
 
Sheet1
__OpenSolver__