final project_snow case.pdf - A 1 2 3 4 5 6 7 8 9 10 11 12...

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
40
41
42
43
44
45
46
47
A B C D E F G H I J
Sector 1 2 3 4 5
1 3.4 1.4 4.9 7.4 9.3
2 2.4 2.1 8.3 9.1 8.8
3 1.4 2.9 3.7 9.4 8.6
4 2.6 3.6 4.5 8.2 8.9
5 1.5 3.1 2.1 7.9 8.8
6
4.2 4.9 6.5 7.7 6.1
7 4.8 6.2 9.9 6.2 5.7
8 5.4 6 5.2 7.6 4.9
9 3.1 4.1 6.6 7.5 7.2
10 3.2 6.5 7.1 6 8.3
Sector 1 2 3 4 5
Total shipped by
sector
Contractual
obligation
constraint
S
i
= Total
maximum volume
of snow expected
in sector i
1 0 1 0 0 0 1 1 153
2 1 0 0 0 0 1 1 152
3
0 0 1 0 0 1 1 154
4 0 0 1 0 0 1 1 138
5
0 0 1 0 0 1 1 127
6 0 0 0 1 0 1 1 129
7 0 0 0 1 0 1 1 111
8 0 0 0 0 1 1 1 110
9
1 0 0 0 0 1 1 130
10 0 0 0 1 0 1 1 135
Total shipped to
disposal site 2 1 3 3 1
Cj= Capacity of disposal
site 350 250 500 400 200
Total volume of Snow
by Site
(=SUMPRODUCT(B19:B2
8,$I$19:$I$28))
282 153 419 375 110
Sector 1 2 3 4 5
Total distance from
sector to disposal
site (Dij*Xij) in km
S
i
= Total
maximum
volume of snow
expected in
sector i Dij*Xij*Si
1 0 1.4 0 0 0 1.4 153 214.2
2 2.4 0 0 0 0 2.4 152 364.8
3
0 0 3.7 0 0 3.7 154